001 // Copyright (c) 2001 Hursh Jain (http://www.mollypages.org)
002 // The Molly framework is freely distributable under the terms of an
003 // MIT-style license. For details, see the molly pages web site at:
004 // http://www.mollypages.org/. Use, modify, have fun !
005
006 package fc.jdbc.dbo;
007
008 import java.io.*;
009 import java.sql.*;
010 import java.util.*;
011
012 import fc.jdbc.*;
013 import fc.io.*;
014 import fc.util.*;
015 import fc.web.forms.*;
016
017 /**
018 Generates java objects that represent tables in a database. Each object represents 1
019 table in the database.
020 <p>
021 This framework is great for inserting and updating data in various tables and even
022 fetching one or more rows form individual tables.
023 <p>
024 For each table <font color="blue"><tt>foo</tt></font> in our database, the following
025 classes are generated:
026 <ol>
027 <li><font color="blue">class <tt>Foo</tt></font> which contains all columns of table
028 <tt>foo</tt> as fields and represents a row in that table. Accessor (get/set) methods are
029 provided to modify the values of fields in this class (Note, all generated fields are
030 themselves declared private and we always go through accessor methods so we can keep
031 track of various modifications etc.)
032 <li><font color="blue">class <tt>FooMgr</tt></font> which contains "manager" type
033 functions to read, save, create, etc., instances of class Foo from/to the database. The
034 methods of <tt>FooMgr</tt> could equivalently have been implemented as static methods in
035 <tt>Foo</tt> but they have been separated out in a separate manager class to reduce
036 clutter.
037 <p>
038 This framework is <i>not</i> intended to transparently allow arbitrary joins and data
039 from multiple tables. A <i>better</i> way is to use prepared statements directly to run
040 ad-hoc SQL queries including those containing arbitrary joins.
041 <p>
042 However, to somewhat facilitate arbitrary select queries/joins across multiple tables,
043 each generated "Mgr" class has a <font color=blue><code>columns()</code></font> method
044 that returns a list of columns for the corresponding table. For example, in say a Molly
045 Server Page where information from two tables (table1, table2) is displayed from both
046 tables on the same page.
047 <blockquote><pre style="background: #ccccc;">
048 String my_query = "select "
049 + <font color=blue>table1Mgr.columns()</font> + ", " + <font color=blue>table2.columns()</font>
050 + " from table1, table2 WHERE table1.x = table2.x";
051
052 PreparedStatement ps = connection.prepareStatement(my_query);
053 ResultSet rs = connection.executeQuery();
054 while (rs.next())
055 {
056 table1 t1 = table1Mgr.getFromRS(rs); //create a new table1 from the rs
057 table2 t2 = table2Mgr.getFromRS(rs); //ditto for table2
058 //..use t1 and t2...
059 //....
060 }
061 </pre></blockquote>
062 </ol>
063 <hr>
064 <h2>Configuration</h2>
065 This program uses a user specified configuration file that allows for
066 many code generation options. This file takes the following
067 <a href='doc-files/Generate_usage.txt'> configuration options</a>
068 <p>
069 Here is a minimal <a href='doc-files/sample.conf'>sample configuration</a> file.
070 <hr>
071 <h3>Notes</h3> <b>Note 1</b>: This framework always retrieves and saves data directly to
072 and fro from the database and never caches data internally. This is a design feature and
073 keeps this framework orthogonal to caching issues/implementations. The results returned
074 by the framework can always be cached as needed via say, the {@link fc.util.cache.Cache}
075 utility class(es).
076 <p>
077 <b>Note 2</b>: <b>MySQL</b> 3.x, 4.x or 5.x does not have true boolean types and silently
078 converts bool types to TINYINT. This wreaks havoc with auto-generated code which creates
079 methods with the wrong signature (TINYINT as opposed to bool).
080 <p>
081 There are 2 approaches to solving this mysql-specific problem:
082 <blockquote>
083 a) Require all boolean columns to begin with some keyword (say bool_) and if a column
084 begins with this word, then treat it as a boolean, regardless of the type returned by
085 the database meta data.<br><br>
086 b) Treat all TINYINT's as boolean types. This is the approach I have chosen since
087 TINYINT's are NOT portable across databases (for example PostgresQL does not have
088 TINYINT's). Therefore we should not use TINYINT's in physical database models; if
089 booleans are turned into TINYINT's by MySQL then so be it..since that will not clash
090 with any of our modelled types.
091 </blockquote>
092 If the flag <tt>mysqlBooleanHack</tt> is set to <tt>false</tt> in the configuration file,
093 then TINYINT's are <b>not</b> transformed to booleans. There should be no practical need
094 to do this however.
095 <p>
096 <b>Note 3</b>: <b>MySQL</b> allows its tables and columns to start with a numeral. For
097 example: <i>52_weeks</i>, <i>3_col</i>, etc. This is wrong, not-standard and
098 not-supported. From the spec:
099 </p>
100 <blockquote>
101 SQL identifiers and key words must begin with a letter (a-z, but also letters with
102 diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in
103 an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($).
104 </blockquote>
105 <p>
106 So compiling these wrongly named tables (which only MySQL and no other database allows)
107 results in a bunch of java compiler errors, which may confuse neophytes into believing
108 that the generator is outputting buggy code. No, the generated code is proper and exactly
109 the way its intended to be. Java variables/classes <b>cannot</b> start with a number.
110 Hence, compiler errors. So if you <i>must</i> use MySQL, at least don't name your tables
111 with a number.
112 </p>
113
114 @author hursh jain
115 **/
116 public final class Generate
117 {
118 static final String nl = IOUtil.LINE_SEP;
119 static final String mgrSuffix = "Mgr";
120 static final String DBO_BaseClassName = "fc.jdbc.dbo.DBO";
121 static final String DBO_MgrBaseClassName = "fc.jdbc.dbo.DBOMgr";
122
123 java.util.Date rundate;
124 DatabaseMetaData md;
125 PropertyMgr props;
126 SystemLog log;
127 Connection con;
128 String url;
129 DBName dbname;
130 DBspecific dbspecific;
131 File outputDir;
132 String catalogName;
133 String packageName;
134 String classVis;
135 String fieldVis;
136 boolean accessors;
137 String[] tableTypesToProcess;
138 List tableNamesToInclude;
139 Map tableNamesToIncludeAction;
140 final int INCLUDE_ANY_PREFIX = 1,
141 INCLUDE_ANY_SUFFIX = 2,
142 INCLUDE_CONTAINS = 3,
143 INCLUDE_EXACT = 4;
144 List tableNamesToExclude;
145 Map tableNamesToExcludeAction;
146 PrintWriter out;
147 NameWrangle wrangler;
148 int processedTablesCount;
149
150 //TODO: These need to come from a i18n resource file
151 //validate errors/messages
152 String validateNull_ErrorMsg = "Error: Required field, please enter a value";
153 String validateIntegerOnly_ErrorMsg = "Error: Please enter only numbers in this field";
154 String validateText_ErrorMsg_MaxSize = "Not enough or too many characters";
155
156 //list of tables in our database
157 List tableList;
158 //used as a temp string buffer by various methods, can
159 //be reset by setLength(0);
160 StringBuffer strbuf = new StringBuffer(2048);
161
162 //Changed/set for the CURRENT TABLE being processed
163 String beanClassName; //FOO
164 String mgrClassName; //FOO_Mgr
165 Table table; //foo
166 List pklist; //[a, b]
167 List fklist; //[b]
168 String colsCommaDelimString; //"a, b, c, d, e"
169 String pkCommaDelimString; //"a, b"
170 String fkCommaDelimString; //"b, c"
171 String pkFormalParamString; //"int a, byte b....."
172
173 //not enabled -- don't think this is good
174 //enabled again
175 boolean modifiableAutoIncrementColumns;
176
177 /*
178 INTERNAL NOTES:
179 - if a new mgr method is added, also update
180 mgrWriteMethodFields and mgrWriteMethodStats
181 */
182
183 public Generate(String[] args) throws Exception
184 {
185 log = Log.getDefault();
186 log.printTimestamp(true);
187 log.printRelativeTimestamp(true);
188
189 //turn on to debug connection manager etc.
190 //log.setLevel(SystemLog.DEBUG);
191
192 Args myargs = new Args(args);
193 myargs.setDefaultUsage(this);
194 String conf = myargs.getRequired("conf");
195
196 props = new FilePropertyMgr(new File(conf));
197
198 //default logging level for the rest of our app
199 String loglevel = props.get("log.level");
200
201 if (loglevel != null) {
202 log.setLevel(loglevel);
203 }
204
205 ConnectionMgr cmgr = new SimpleConnectionMgr(props);
206
207 String driver_name = cmgr.getDriver().getClass().getName();
208 dbname = DBName.fromDriver(driver_name);
209
210 if (dbname == null) {
211 log.bug("dbname=", dbname);
212 log.error("Could not understand the name of the target database. See documentation for more info.");
213 System.exit(1);
214 }
215
216 dbspecific = (DBspecific) Class.forName("fc.jdbc.dbo.DB" + dbname.toString().toLowerCase()).newInstance();
217
218 url = cmgr.getURL();
219 con = cmgr.getConnection();
220 md = con.getMetaData();
221
222 rundate = new java.util.Date();
223
224 String catalogName = props.get("jdbc.catalog");
225 if (catalogName == null) {
226 catalogName = "";
227 }
228
229 //Generate options
230 String output_dir = props.getRequired("generate.output.dir");
231 outputDir = new File(output_dir);
232
233 if (! outputDir.isDirectory() || ! outputDir.canWrite())
234 {
235 log.error("Specified output location '" + output_dir + "' is not a directory and/or is not writable");
236 System.exit(1);
237 }
238
239 if (! outputDir.exists())
240 {
241 System.out.print("Output directory: " + output_dir + " does not exist. Creating..");
242 boolean mkdirs = outputDir.mkdirs();
243 if (! mkdirs) {
244 System.out.println("Error creating output directory: " + output_dir);
245 System.exit(1);
246 }
247
248 System.out.println("..done");
249 }
250
251 log.info("Output Directory: ", outputDir);
252
253 modifiableAutoIncrementColumns =
254 Boolean.valueOf(
255 props.get("generate.modifiableAutoIncrementColumns", "false")
256 ).booleanValue();
257
258 packageName = props.get("generate.class_package");
259
260 accessors = Boolean.valueOf(
261 props.get("generate.accessors", "true")).
262 booleanValue();
263
264 classVis = props.get("generate.class_vis", "public");
265 fieldVis = (accessors) ?
266 props.get("generate.field_vis", "private") :
267 /* public by default if no accessors */
268 props.get("generate.field_vis", "public");
269
270
271 String tabletypes = props.get("target.types_to_process");
272 if (tabletypes != null)
273 {
274 tableTypesToProcess = tabletypes.split(",\\s*");
275 for (int n=0; n < tableTypesToProcess.length; n++) {
276 tableTypesToProcess[n] = tableTypesToProcess[n].toUpperCase();
277 }
278 }
279
280 String table_include = props.get("target.tables_to_process");
281 if (table_include != null)
282 {
283 tableNamesToInclude = new ArrayList();
284 tableNamesToIncludeAction = new HashMap();
285 createActions(table_include, tableNamesToInclude, tableNamesToIncludeAction);
286 log.bug("Table names to include:", tableNamesToInclude);
287 log.bug("Table names to include action:", tableNamesToIncludeAction);
288 }
289
290 String table_exclude = props.get("target.tables_to_ignore");
291 if (table_exclude != null)
292 {
293 tableNamesToExclude = new ArrayList();
294 tableNamesToExcludeAction = new HashMap();
295 createActions(table_exclude, tableNamesToExclude, tableNamesToExcludeAction);
296 log.bug("Table names to exclude:", tableNamesToExclude);
297 log.bug("Table names to exclude action:", tableNamesToExcludeAction);
298 }
299
300
301 wrangler = new NameWrangle(props, log);
302
303 Watch w = new Watch();
304 w.start();
305 readTables();
306 generateCode();
307 cmgr.close();
308
309 long elapsed = w.timeInSeconds();
310
311 System.out.println("Generator processed "
312 + processedTablesCount
313 + " table"
314 + ((processedTablesCount > 1) ? "s" : "")
315 + " in "
316 + elapsed
317 + (elapsed > 1 ? " seconds." : " second."));
318 }
319
320 void createActions(String parameter, List names, Map actionMap) throws Exception
321 {
322 String[] temp = parameter.split(",\\s*");
323 for (int n = 0; n < temp.length; n++)
324 {
325 //we match table names from database to those specified
326 //in the config file, both are lowercased before matching
327 //trim() in case table_to_process="foo "
328 temp[n] = temp[n].trim().toLowerCase();
329
330 boolean startsWithStar = false, endsWithStar = false;
331 if (temp[n].startsWith("*"))
332 {
333 if (temp[n].length() == 1) {
334 throw new Exception("Bad option in config file.\nIn line: " + parameter + "\nA star must be a prefix/suffix to a tablename, not standalone.");
335 }
336 temp[n] = temp[n].substring(1, temp[n].length());
337 startsWithStar = true;
338 }
339 if (temp[n].endsWith("*"))
340 {
341 if (temp[n].length() == 1) {
342 throw new Exception("Bad option in config file.\nIn line: " + parameter + "\nA star must be a prefix/suffix to a tablename, not standalone.");
343 }
344 temp[n] = temp[n].substring(0, temp[n].length()-1);
345 endsWithStar = true;
346 }
347
348 if (startsWithStar)
349 {
350 if (endsWithStar) { //both start/end star
351 actionMap.put(temp[n], INCLUDE_CONTAINS);
352 }
353 else{ //start_star
354 actionMap.put(temp[n], INCLUDE_ANY_PREFIX);
355 }
356 }
357 else {
358 if (endsWithStar) { //end_star
359 actionMap.put(temp[n], INCLUDE_ANY_SUFFIX);
360 }
361 else{ //exact_match
362 actionMap.put(temp[n], INCLUDE_EXACT);
363 }
364 }
365
366 names.add(temp[n]);
367 }
368 }
369
370 boolean matchTable(String tablename, List names, Map actions) throws IOException
371 {
372 boolean match = false;
373
374 for (int p = 0; p < names.size(); p++)
375 {
376 String target = (String) names.get(p);
377 int action = (Integer) actions.get(target);
378
379 //target=foo*, lower_tablename = foo_xxx, foo etc
380 if (action == INCLUDE_ANY_SUFFIX)
381 {
382 if (tablename.startsWith(target)) {
383 match = true;
384 break;
385 }
386 }
387 //target=*foo, lower_tablename = xxx_foo, foo etc
388 else if (action == INCLUDE_ANY_PREFIX)
389 { //*<tablee>
390 if (tablename.endsWith(target)) {
391 match = true;
392 break;
393 }
394 }
395 else if (action == INCLUDE_CONTAINS)
396 {
397 if (tablename.indexOf(target) != -1) {
398 match = true;
399 break;
400 }
401 }
402 else if (action == INCLUDE_EXACT)
403 {
404 if (tablename.equals(target)) {
405 match = true;
406 break;
407 }
408 }
409 else{
410 throw new IOException("Internal error. Unrecognized action = " + action);
411 }
412 } //for
413
414 return match;
415 }
416
417 void readTables() throws IOException, SQLException
418 {
419 tableList = new ArrayList();
420
421 Table.init(this, md, log, props, dbspecific);
422
423 //not useful generally at least with mysql, postgresql
424 String schemaPattern = null;
425 //we get all tables, maybe if this is needed at all
426 //we can add this as a config file option
427 String tableNamePattern = "%";
428
429 ResultSet rs = md.getTables(catalogName, schemaPattern,
430 tableNamePattern, tableTypesToProcess);
431
432 while (rs.next())
433 {
434 String tablename = rs.getString("TABLE_NAME");
435 //tablename cannot be null if the driver is unbroken
436 //but can it be an empty string ?
437 if (tablename.intern() == "") {
438 throw new SQLException("The returned tablename was an empty string, looks like the JDBC driver is broken");
439 }
440
441 String lower_tablename = tablename.toLowerCase();
442
443 boolean include = false;
444
445 if (tableNamesToInclude != null)
446 {
447 //including only certain tables, if it is NOT in
448 //include list we continue to the next table
449 include = matchTable(lower_tablename, tableNamesToInclude, tableNamesToIncludeAction);
450
451 if (! include) {
452 log.bug("Ignoring table: ", tablename);
453 continue;
454 }
455 }
456
457 boolean exclude = false;
458
459 if (tableNamesToExclude != null)
460 {
461 exclude = matchTable(lower_tablename, tableNamesToExclude, tableNamesToExcludeAction);
462
463 if (exclude) {
464 log.bug("Ignoring table (via exclude): ", tablename);
465 continue;
466 }
467 }
468
469 if (MiscUtil.isJavaKeyword(tablename)) {
470 throw new SQLException("The returned tablename [" + tablename + "] is a Java Reserved Keyword. Either change the name in the DB or exclude this table in the config file");
471 }
472
473 log.info(">>>> Processing table: ", tablename);
474 processedTablesCount++;
475
476 String tabletype = rs.getString("TABLE_TYPE");
477 String remarks = rs.getString("REMARKS");
478
479 Table table = new Table(con, catalogName, schemaPattern, tablename, tabletype, remarks);
480 tableList.add(table);
481 } //~while rs.next
482 } //~process
483
484
485 void generateCode() throws IOException, SQLException
486 {
487 for (int n = 0; n < tableList.size(); n++)
488 {
489 table = (Table) tableList.get(n);
490 pklist = (List) table.getPKList();
491 fklist = (List) table.getFKList();
492
493 //list of pk's for this table as formal parameters
494 //in a generated method, for example:
495 // int col_a, String col_b, Date col_c
496 int size = pklist.size();
497 StringBuffer buf = new StringBuffer(128);
498 for (int m = 0; m < size; m++)
499 {
500 ColumnData cd = (ColumnData) pklist.get(m);
501 buf.append(cd.getJavaTypeFromSQLType());
502 buf.append(" ");
503 buf.append(cd.getName());
504 if ( m < (size-1))
505 buf.append(", ");
506 }
507 pkFormalParamString = buf.toString();
508
509 colsCommaDelimString = Table.getListAsString(table.getColumnList());
510 pkCommaDelimString = Table.getListAsString(pklist);
511 fkCommaDelimString = Table.getListAsString(fklist);
512 writeBeanCode();
513 writeMgrCode();
514 }
515 }
516
517 void writeBeanCode() throws IOException, SQLException
518 {
519 beanClassName = wrangler.getClassName(table.getName());
520 String filename = beanClassName + ".java";
521 File f = new File(outputDir, filename);
522
523 /* this ensures that the new file will not have the same case as the
524 old file, jdk1.5 on osx 10.4 and possibly others keep the existing case
525 of the file if it already exists.
526 */
527 if (f.exists())
528 f.delete();
529
530 out = new PrintWriter(new BufferedWriter(new FileWriter(f)));
531
532 writePackage();
533 writePrologue();
534 writeImports();
535 beanWriteClass();
536 out.close();
537 }
538
539 void writeMgrCode() throws IOException, SQLException
540 {
541 mgrClassName =
542 wrangler.getClassName(table.getName()) + mgrSuffix;
543
544 String filename = mgrClassName + ".java";
545 File f = new File(outputDir, filename);
546
547 /* this ensures that the new file will not have the same case as the
548 old file, jdk1.5 on osx 10.4 and possibly others keep the existing case
549 of the file if it already exists.
550 */
551 if (f.exists())
552 f.delete();
553
554 out = new PrintWriter(new BufferedWriter(new FileWriter(f)));
555
556 writePackage();
557 writePrologue();
558 writeImports();
559 mgrWriteClass();
560 out.close();
561 }
562
563
564 void writePrologue()
565 {
566 String name = getClass().getName();
567 ol("/*");
568 ol(" * Auto generated on: " + rundate);
569 ol(" * JDBC url: [" + url + "]");
570 ol(" * WARNING: Manual edits will be lost if/when this file is regenerated.");
571 ol(" */");
572 }
573
574 void writePackage()
575 {
576 if (packageName != null)
577 {
578 ol("package " + packageName + ";");
579 ol();
580 }
581 }
582
583 void writeImports()
584 {
585 ol("import java.io.*;");
586 ol("import java.math.*;");
587 ol("import java.sql.*;");
588 ol("import java.util.*;");
589 ol();
590 ol("import fc.io.*;");
591 ol("import fc.jdbc.*;");
592 ol("import fc.jdbc.dbo.*;");
593 ol("import fc.util.*;");
594 ol("import fc.web.forms.*;");
595 ol();
596 }
597
598
599 /**
600 returns the comment used for get/set methods and for fields in the bean class
601 */
602 final HashMap commentMap = new HashMap();
603 String getBeanComment(ColumnData item) throws SQLException
604 {
605 String comment = (String) commentMap.get(item);
606 if (comment != null)
607 return comment;
608
609 comment =
610 "/** " + item.getSQLTypeDriverSpecificName() +
611 " (" + item.getSQLTypeName() + ")";
612 if (item.isPK()) {
613 comment += "; PK=yes";
614 }
615 if (item.isFK()) {
616 ColumnData.FKData fkdata = item.getFK();
617 comment += "; FK=yes, refers to: "
618 + fkdata.getPKTableName() + "."
619 + fkdata.getPKColName();
620 }
621 comment += "; Nullable=" + item.isNullable();
622 comment += "; AutoInc=" + item.isAutoIncrement();
623 comment += "; MaxSize=" + item.getSize();
624 if (item.hasRemarks()) {
625 comment += "; Remarks: " + item.getRemarks();
626 }
627 comment += " */";
628
629 commentMap.put(item, comment);
630 return comment;
631 }
632
633
634 void beanWriteClass() throws SQLException
635 {
636 ol("/**");
637 o ("Represents a row in the ");
638 o ( table.getName());
639 ol(" table. ");
640
641 String remarks = table.getRemarks();
642 if ( remarks != null)
643 {
644 ol("<p><b>Table Remarks: </b>");
645 ol(remarks);
646 }
647
648 ol("*/");
649 o(classVis + " class ");
650 o(beanClassName); //classname
651 o(" extends ");
652 ol(DBO_BaseClassName);
653 ol("{");
654
655 beanWriteConstructor();
656 beanWriteDBFields();
657 beanWriteDBFieldsTracking();
658 beanWriteMiscMethods();
659 beanWriteGetSet();
660 beanWriteIncDec();
661 ol("}");
662 }
663
664 void beanWriteConstructor() {
665 ol("/* Default constructor */");
666 o(classVis);
667 o(" ");
668 o(beanClassName);
669 ol("()");
670 ol(" {");
671 ol(" this.__isNew = true;");
672 ol(" }");
673 ol();
674 }
675
676 void beanWriteDBFields() throws SQLException
677 {
678 List cols = table.getColumnList();
679
680 ol("/*--------------------- Columns ------------------------*/");
681 //write database fields
682 TablePrinter.PrintConfig config = new TablePrinter.PrintConfig();
683 config.setPrintBorders(false);
684 config.setCellSpacing(1);
685 config.setCellPadding(0);
686 config.setAutoFit(true);
687 TablePrinter p = new TablePrinter(4, out, config);
688 p.startTable();
689 for (int n = 0; n < cols.size(); n++)
690 {
691 ColumnData cd = (ColumnData) cols.get(n);
692 log.bug("Processing column: ", cd);
693 p.startRow();
694 p.printCell(fieldVis);
695 p.printCell(cd.getJavaTypeFromSQLType());
696 p.printCell(cd.getName() + ";");
697 p.printCell(getBeanComment(cd));
698 p.endRow();
699 }
700 p.endTable();
701 ol("/*------------------------------------------------------*/");
702 }
703
704 void beanWriteDBFieldsTracking() throws SQLException
705 {
706 if (pklist.size() > 0)
707 {
708 ol("/*");
709 ol("Original PK saved here for updates. If a row is retrieved from the database and");
710 ol("the PK value is changed, and then if the object is saved, we need the orignal PK");
711 ol("value to find the row in the db for our update to work.");
712 ol("*/");
713 for (int n = 0; n < pklist.size(); n++)
714 {
715 ColumnData cd = (ColumnData) pklist.get(n);
716 o (cd.getJavaTypeFromSQLType());
717 o (" __orig_");
718 o (cd.getName());
719 ol(";");
720 }
721 }
722
723 ol();
724 ol(" boolean __force_update = false;");
725 ol("private Map __extra_data; ");
726 ol("private boolean __isNew = false;");
727 ol("private boolean __isModified = false;");
728
729 List cols = table.getColumnList();
730 for (int n = 0; n < cols.size(); n++)
731 {
732 //modified column ?
733 ol();
734 ColumnData cd = (ColumnData) cols.get(n);
735 String colname = cd.getName();
736
737 o("private boolean __isModified_");
738 o(colname);
739 ol(" = false;");
740 if (cd.isNullable() && cd.usesPrimitiveJavaType()) {
741 ol("/* this primitive type is nullable, this tracks whether we should save it as null to the database*/");
742 o("private boolean __isModified_");
743 o(colname);
744 ol("_setnull = false;");
745 }
746 o("protected boolean __isNullInDB_");
747 o(colname);
748 ol(" = false;");
749 o("/**returns <tt>true</tt> if ");
750 o(colname);
751 o(" has changed since it was created/loaded, <tt>false</tt> otherwise");
752 ol("*/");
753 o("public boolean ");
754 o(wrangler.getIsModifiedName(cd));
755 o("() { return this.__isModified_");
756 o(colname);
757 ol("; }");
758
759 if (cd.isNullable() && cd.usesPrimitiveJavaType()) {
760 o("/**returns <true> is primitive type ");
761 o(colname);
762 ol(" has been set to null via the setNull mechanism*/");
763 o("public boolean ");
764 o(wrangler.getIsModifiedNullName(cd));
765 o("() { return this.__isModified_");
766 o(colname);
767 ol("_setnull; }");
768 }
769
770 //column null in database ?
771 o("/**returns <tt>true</tt> if ");
772 o(colname);
773 o(" was null in the database");
774 ol("*/");
775 o("public boolean ");
776 o(wrangler.getIsNullInDBName(cd));
777 o("() { return this.__isNullInDB_");
778 o(colname);
779 ol("; }");
780 }
781 }
782
783 void beanWriteMiscMethods() throws SQLException
784 {
785 ol();
786 //isnew
787 ol();
788 ol("/** returns <tt>true</tt> if this object is newly created and has <b>not</b> been loaded from the database, <tt>false</tt> otherwise */");
789 ol("public boolean isNew() ");
790 ol(" {");
791 ol(" return this.__isNew;");
792 ol(" }");
793
794 ol();
795 ol("/** Specify <tt>true</tt> to set this object's status as newly created (and not read from a database) */");
796 ol("protected void setNew(boolean val) ");
797 ol(" {");
798 ol(" this.__isNew = val;");
799 ol(" }");
800
801 //modified
802 ol();
803 ol("/** returns <tt>true</tt> if this object's data (for any field) has changed since it was created/loaded, <tt>false</tt> otherwise */");
804 ol("public boolean isModified() ");
805 ol(" {");
806 ol(" return this.__isModified;");
807 ol(" }");
808
809 ol();
810 ol("/** Resets the modified status of this object to not-modified");
811 ol("this is useful when loading an object via a prepared statement");
812 ol("(by using various setXXX methods when we do so, we inadvertently");
813 ol("set the modified status of each field to true)");
814 ol("*/");
815 ol("void resetModified() ");
816 ol(" {");
817 List collist = table.getColumnList();
818 ol(" this.__isModified = false;");
819 for (int n = 0; n < collist.size(); n++)
820 {
821 ColumnData cd = (ColumnData) collist.get(n);
822 String colname = cd.getName();
823 o(" this.__isModified_");
824 o(colname);
825 ol(" = false;");
826
827 if (cd.isNullable() && cd.usesPrimitiveJavaType()) {
828 o(" this.__isModified_");
829 o(colname);
830 ol("_setnull = false;");
831 }
832 }
833 ol(" }");
834
835 ol();
836 ol("/**");
837 ol("Allows putting arbitrary object-specific data into this object.");
838 ol("This is useful to store additional query-specific columns when performing");
839 ol("arbitrary queries. For example: ");
840 ol("<blockquote><pre>");
841 o ("String query = \"select <font color=blue>1+1 as my_sum, \n\t\tnow() as my_time</font>, ");
842 o (table.getName());
843 o ("Mgr.columns() \n\t\tfrom ");
844 o (table.getName());
845 ol("\";");
846 ol("PreparedStatement ps = con.prepareStatment(query);");
847 ol("ResultSet rs = ps.executeQuery();");
848 ol("List list = new ArrayList();");
849 ol("while (rs.next()) {");
850 o (" <font color=green>"); o (table.getName()); o ("</font> obj = ");
851 o(table.getName());
852 ol("Mgr.getFromRS(rs);");
853 ol(" obj.<font color=blue>putExtraData(\"my_sum\"</font>, rs.getInt(\"my_sum\"));");
854 ol(" obj.<font color=blue>putExtraData(\"my_time\"</font>, rs.getDate(\"my_time\"));");
855 ol(" }");
856 o ("//use the list later on...each <font color=green>");
857 o (table.getName());
858 ol(" </font>object in the list will ");
859 ol("//have the extra data..");
860 ol("</pre></blockquote>");
861 ol("*/");
862 ol("public void putExtraData(Object key, Object value) ");
863 ol(" {");
864 ol(" synchronized (this) {");
865 ol(" if (__extra_data == null) {");
866 ol(" __extra_data = new HashMap();");
867 ol(" }");
868 ol(" }");
869 ol(" __extra_data.put(key, value);");
870 ol(" }");
871
872 ol("/*Alias for the {@link putExtraData} method */");
873 ol("public void put(Object key, Object value) ");
874 ol(" {");
875 ol(" putExtraData(key, value);");
876 ol(" }");
877
878 ol();
879 ol("/**");
880 ol("Allows retrieving arbitrary object-specific data from this object.");
881 ol("This data should have been put via the {@link #putExtraData putExtraData} ");
882 ol("method prior to invoking this method");
883 ol("*/");
884 ol("public Object getExtraData(Object key) ");
885 ol(" {");
886 ol(" synchronized (this) {");
887 ol(" if (__extra_data == null) {");
888 ol(" return null;");
889 ol(" }");
890 ol(" }");
891 ol(" return __extra_data.get(key);");
892 ol(" }");
893
894 ol("/*Alias for the {@link getExtraData} method */");
895 ol("public Object get(Object key) ");
896 ol(" {");
897 ol(" return getExtraData(key);");
898 ol(" }");
899
900
901 //toString
902 ol();
903 ol("public String toString() ");
904 ol(" {");
905 ol(" final String nl = fc.io.IOUtil.LINE_SEP;");
906 ol(" StringBuffer buf = new StringBuffer(256);");
907 o (" buf.append(\"Class Name: [");
908 o (beanClassName);
909 ol("]\");");
910 ol(" buf.append(\" [isDiscarded=\").append(this.isDiscarded()).append(\"]\");");
911 ol(" buf.append(\" [isNew=\").append(this.isNew()).append(\"]\");");
912 ol(" buf.append(\" [isModified=\").append(this.isModified()).append(\"]\");");
913 ol(" buf.append(nl);");
914 ol(" buf.append(\"Note: IsNullInDB only meaningful for existing rows (i.e., isNew=false)\");");
915 ol(" buf.append(nl);");
916 ol();
917 ol(" ByteArrayOutputStream out = new ByteArrayOutputStream(768);");
918 ol(" TablePrinter.PrintConfig config = new TablePrinter.PrintConfig();");
919 ol(" config.setPrintBorders(false);");
920 ol(" config.setCellSpacing(1);");
921 ol(" config.setCellPadding(0);");
922 ol(" config.setAutoFit(true);");
923 ol(" TablePrinter p = new TablePrinter(7, new PrintStream(out), config);");
924 ol(" p.startTable();");
925 ol();
926 ol(" p.startRow();");
927 ol(" p.printCell(\"PK\");");
928 ol(" p.printCell(\"FK\");");
929 ol(" p.printCell(\"Field\");");
930 ol(" p.printCell(\"Value\");");
931 ol(" p.printCell(\"isModified\");");
932 ol(" p.printCell(\"isNullinDB\");");
933 ol(" p.printCell(\"isSerial/AutoInc\");");
934 ol(" p.endRow();");
935
936 for (int n = 0; n < collist.size(); n++)
937 {
938 ColumnData cd = (ColumnData) collist.get(n);
939 String isPK = cd.isPK() ? "x" : "-";
940 String isFK = "-";
941 if (cd.isFK()) {
942 ColumnData.FKData fk = cd.getFK();
943 isFK = "x [" + fk.getPKTableName() + "." +
944 fk.getPKColName() + "]";
945
946 }
947 String colname = cd.getName();
948 String value = "String.valueOf(this." + colname + ")";
949 String modified = "(this.__isModified_" + colname + ")";
950 String isnull = "(this.__isNullInDB_" + colname + ")";
951 String isAI = cd.isAutoIncrement() ? "x" : "-";
952
953 ol();
954 ol(" p.startRow();");
955 o (" p.printCell(\""); o(isPK); ol("\");");
956 o (" p.printCell(\""); o(isFK); ol("\");");
957 o (" p.printCell(\""); o(colname); ol("\");");
958 o (" p.printCell("); o(value); ol(");");
959 o (" p.printCell("); o(modified); ol(" ?\"x\":\"-\");");
960 o (" p.printCell("); o(isnull); ol(" ?\"x\":\"-\");");
961 o (" p.printCell(\""); o(isAI); ol("\");");
962 ol(" p.endRow();");
963 }
964 ol();
965 ol(" p.endTable();");
966 ol(" buf.append(out.toString());");
967 ol(" return buf.toString();");
968 ol(" }");
969
970 ol();
971 ol("/**");
972 ol("Returns a map of all fields->values (as Strings) contained in this");
973 ol("object. This is useful when sending auto converting the object to JSON, etc.");
974 ol("*/");
975 ol("public Map allFieldsMap() ");
976 ol(" {");
977 ol(" final HashMap m = new HashMap();");
978 for (int n = 0; n < collist.size(); n++)
979 {
980 ColumnData cd = (ColumnData) collist.get(n);
981 String colname = cd.getName();
982
983 o (" m.put(\"");
984 o (colname);
985 o ("\", ");
986
987 if (! cd.usesPrimitiveJavaType()) {
988 o ("("); o(colname); o(" == null ? null : ");
989 o ("String.valueOf(this."); o(colname); o(")");
990 o (")");
991 }
992 else{ //primitive type
993 if (cd.isNullable())
994 {
995 ol();
996 o (" ( __isModified_");o(colname);ol(" ? ");
997 o (" ( __isModified_");o(colname);o("_setnull ? null : ");
998 o ("String.valueOf(this."); o(colname);o(") )");
999 ol(" : ");
1000 o (" ( __isNullInDB_");o(colname);o(" ? null : ");
1001 o ("String.valueOf(this.");o(colname);o(")))");
1002 }
1003 else{
1004 o ("String.valueOf(this.");o(colname);o(")");
1005 }
1006 }
1007
1008 ol(");");
1009 }
1010 ol(" return m;");
1011 ol(" }");
1012 ol();
1013 }
1014
1015 void beanWriteGetSet() throws SQLException
1016 {
1017 List cols = table.getColumnList();
1018
1019 ol("/* ============== Gets and Sets ============== */");
1020 for (int n = 0; n < cols.size(); n++)
1021 {
1022 ColumnData cd = (ColumnData) cols.get(n);
1023 ol();
1024 ol(getBeanComment(cd));
1025 String colname = cd.getName();
1026
1027 //====================== get ==================
1028 o("public ");
1029 o(cd.getJavaTypeFromSQLType());
1030 o(" ");
1031 o(wrangler.getGetName(cd));
1032 ol("() { ");
1033 o(" return ");
1034 o(colname);
1035 ol(";");
1036 ol(" }");
1037
1038 ol();
1039 //===================== set ======================
1040
1041 ol(getBeanComment(cd));
1042 if (cd.isAutoIncrement() && (! modifiableAutoIncrementColumns)) {
1043 //we need to generate set with package access
1044 //to set the id when loading the object internally
1045 //from a result set
1046 o("/* Generating set for ");
1047 o(wrangler.getSetName(cd));
1048 ol(" with non public access since it's an auto-increment column */");
1049 }
1050 else {
1051 o("public");
1052 }
1053 o(" ");
1054 o(beanClassName);
1055 o(" ");
1056 o(wrangler.getSetName(cd));
1057 o("(");
1058 o(cd.getJavaTypeFromSQLType());
1059 o(" ");
1060 o(colname);
1061 ol(") {");
1062 ol(" this." + colname + " = " + colname + ";");
1063 ol(" this.__isModified_" + colname + " = true;" );
1064 ol(" this.__isModified = true;" );
1065
1066 if (! cd.isNullable() && ! cd.usesPrimitiveJavaType()) {
1067 o(" if ("); o(colname); ol(" == null) {");
1068 o(" Log.getDefault().warn(\"");
1069 o(colname);
1070 o(" was set to null via ");
1071 o(beanClassName);
1072 o(".");
1073 o(wrangler.getSetName(cd));
1074 ol("() and it is non-nullable in the database. Queries using this object will not work properly and you will get a runtime error if saving this object\");");
1075 ol(" }");
1076 }
1077
1078 ol(" return this;");
1079 ol(" }");
1080
1081 if (cd.isNullable() && cd.usesPrimitiveJavaType())
1082 {
1083 if (cd.isAutoIncrement() && (! modifiableAutoIncrementColumns))
1084 {
1085 //we need to generate set with package access
1086 //to set the id when loading the object internally
1087 //from a result set
1088 o("/* Generating setNull for ");
1089 o(wrangler.getSetNullName(cd));
1090 ol(" with non public access since it's an auto-increment column */");
1091 }
1092 else{
1093 o("public");
1094 }
1095 o(" ");
1096 o(beanClassName);
1097 o(" ");
1098 o(wrangler.getSetNullName(cd));
1099 ol("()");
1100 ol(" {");
1101 ol(" this.__isModified_" + colname + " = true;" );
1102 ol(" this.__isModified_" + colname + "_setnull = true;" );
1103 ol(" this.__isModified = true;" );
1104 ol(" return this;");
1105 ol(" }");
1106 }
1107 }
1108 }
1109
1110
1111 void beanWriteIncDec() throws SQLException
1112 {
1113 List cols = table.getColumnList();
1114
1115 ol();
1116 ol("/* =========== Increments / Decrements =========== */");
1117 for (int n = 0; n < cols.size(); n++)
1118 {
1119 ColumnData cd = (ColumnData) cols.get(n);
1120
1121 if (! cd.usesSimpleIntegralJavaType()) {
1122 continue;
1123 }
1124
1125 if (cd.isAutoIncrement() && (! modifiableAutoIncrementColumns)) {
1126 continue;
1127 }
1128
1129 //no point inc/dec for key columns. If need be, can always be set by hand.
1130 if (cd.isPK() || cd.isFK()) {
1131 continue;
1132 }
1133
1134 ol();
1135 //ol(getBeanComment(cd));
1136 String colname = cd.getName();
1137
1138 //====================== inc ==================
1139 //inc by 1
1140 o ("/** ");
1141 o ("Increments ");
1142 o (colname);
1143 o (" by 1");
1144 ol(" */");
1145 o("public");
1146 o(" ");
1147 o(beanClassName);
1148 o(" ");
1149 o(wrangler.getIncName(cd));
1150 ol("() {");
1151 ol(" this." + colname + " = this." + colname + " + 1;");
1152 ol(" this.__isModified_" + colname + " = true;" );
1153 ol(" this.__isModified = true;" );
1154 ol(" return this;");
1155 ol(" }");
1156 ol();
1157
1158 //inc by an amount
1159 o ("/** ");
1160 o ("Increments ");
1161 o (colname);
1162 o (" by the specified amount");
1163 ol(" */");
1164 o("public");
1165 o(" ");
1166 o(beanClassName);
1167 o(" ");
1168 o(wrangler.getIncName(cd));
1169 o("(");
1170 o(cd.getJavaTypeFromSQLType());
1171 o(" amount");
1172 ol(") {");
1173 ol(" this." + colname + " = this." + colname + " + amount;");
1174 ol(" this.__isModified_" + colname + " = true;" );
1175 ol(" this.__isModified = true;" );
1176 ol(" return this;");
1177 ol(" }");
1178 ol();
1179
1180 //inc by an amount constrained by upper bound
1181 o ("/** ");
1182 o ("Increments ");
1183 o (colname);
1184 o (" by the specified amount, upto but not above the upper bound (bound is inclusive)");
1185 ol(" */");
1186 o("public");
1187 o(" ");
1188 o(beanClassName);
1189 o(" ");
1190 o(wrangler.getIncName(cd));
1191 o("(");
1192 o(cd.getJavaTypeFromSQLType());
1193 o(" amount, int upper_bound");
1194 ol(") {");
1195 ol(" this." + colname + " = Math.min(this." + colname + " + amount, upper_bound);");
1196 ol(" this.__isModified_" + colname + " = true;" );
1197 ol(" this.__isModified = true;" );
1198 ol(" return this;");
1199 ol(" }");
1200 ol();
1201
1202 //===================== dec ======================
1203
1204 //dec by 1
1205 o ("/** ");
1206 o ("Decrements ");
1207 o (colname);
1208 o (" by 1");
1209 ol(" */");
1210 o("public");
1211 o(" ");
1212 o(beanClassName);
1213 o(" ");
1214 o(wrangler.getDecName(cd));
1215 ol("() {");
1216 ol(" this." + colname + " = this." + colname + " - 1;");
1217 ol(" this.__isModified_" + colname + " = true;" );
1218 ol(" this.__isModified = true;" );
1219 ol(" return this;");
1220 ol(" }");
1221 ol();
1222
1223 //dec by an amount
1224 o ("/** ");
1225 o ("Decrements ");
1226 o (colname);
1227 o (" by the specified amount");
1228 ol(" */");
1229 o("public");
1230 o(" ");
1231 o(beanClassName);
1232 o(" ");
1233 o(wrangler.getDecName(cd));
1234 o("(");
1235 o(cd.getJavaTypeFromSQLType());
1236 o(" amount");
1237 ol(") {");
1238 ol(" this." + colname + " = this." + colname + " - amount;");
1239 ol(" this.__isModified_" + colname + " = true;" );
1240 ol(" this.__isModified = true;" );
1241 ol(" return this;");
1242 ol(" }");
1243 ol();
1244
1245 //dec by an amount constrained by lower bound
1246 o ("/** ");
1247 o ("Decrements ");
1248 o (colname);
1249 o (" by the specified amount, upto but not below the lower bound (bound is inclusive)");
1250 ol(" */");
1251 o("public");
1252 o(" ");
1253 o(beanClassName);
1254 o(" ");
1255 o(wrangler.getDecName(cd));
1256 o("(");
1257 o(cd.getJavaTypeFromSQLType());
1258 o(" amount, int lower_bound");
1259 ol(") {");
1260 ol(" this." + colname + " = Math.max(this." + colname + " - amount, lower_bound);");
1261 ol(" this.__isModified_" + colname + " = true;" );
1262 ol(" this.__isModified = true;" );
1263 ol(" return this;");
1264 ol(" }");
1265 }
1266 }
1267
1268 void mgrWriteClass() throws SQLException
1269 {
1270 //classname
1271 ol("/**");
1272 o("Manages various operations on the ");
1273 o(table.getName());
1274 ol(" table. ");
1275 ol();
1276 ol("<p>Most methods of this class take a {@link java.sql.Connection Connection} as an argument");
1277 ol("and use that connection to run various queries. The connection parameter is never closed");
1278 ol("by methods in this class and that connection can and should be used again. Methods of this");
1279 ol("class will also throw a <tt>IllegalArgumentException</tt> if the specified connection");
1280 ol("object is <tt>null</tt>.");
1281 ol();
1282 ol("<p>Thread Safety: Operations on this class are by and large thread safe in that multiple");
1283 ol("threads can call the methods at the same time. However, seperate threads should use");
1284 ol("seperate connection objects when invoking methods of this class.");
1285 ol("*/");
1286 o(classVis + " final class " + mgrClassName);
1287 o(" extends ");
1288 ol(DBO_MgrBaseClassName);
1289 ol("{");
1290
1291 mgrWriteFields();
1292 mgrWriteConstructor();
1293 mgrWriteMethods();
1294 ol("}");
1295 }
1296
1297 void mgrWriteConstructor()
1298 {
1299 ol();
1300 ol("/** Constructor is private since class is never instantiated */");
1301 o("private ");
1302 o(mgrClassName);
1303 ol("() {");
1304 ol("\t}");
1305 ol();
1306 }
1307
1308 void mgrWriteFields() throws SQLException
1309 {
1310 ol("/* --- Fields used for collecting usage statistics --- ");
1311 ol("Increments to these don't need to be synchronized since these are");
1312 ol("ints and not longs and memory visibility is not an issue in the");
1313 ol("toString() method (in which these are read).");
1314 ol("*/");
1315 ol("private static int __getall_called = 0;");
1316 ol("private static int __getlimited_called = 0;");
1317 ol("private static int __getbykey_called = 0;");
1318 ol("private static int __getwhere_called = 0;");
1319 ol("private static int __getusing_called = 0;");
1320 ol("private static int __getusing_ps_called = 0;");
1321 ol("private static int __getfromrs_called = 0;");
1322 ol("private static int __save_called = 0;");
1323 ol("private static int __delete_called = 0;");
1324 ol("private static int __deletebykey_called = 0;");
1325 ol("private static int __deletewhere_called = 0;");
1326 ol("private static int __deleteusing_called = 0;");
1327 ol("private static int __count_called = 0;");
1328 ol("private static int __countwhere_called = 0;");
1329 ol("private static int __countusing_called = 0;");
1330 ol("private static int __exists_called = 0;");
1331 ol("/* -------------- end statistics fields -------------- */");
1332 }
1333
1334 void mgrWriteMethods() throws SQLException
1335 {
1336 //get
1337 mgrWriteMethodGetAll();
1338 mgrWriteMethodGetAllNoClause();
1339 mgrWriteMethodGetLimited();
1340 mgrWriteMethodGetByKey();
1341 mgrWriteMethodGetWhere();
1342 mgrWriteMethodGetUsing();
1343 mgrWriteMethodGetUsingNoClause();
1344 mgrWriteMethodGetUsingPS();
1345 mgrWriteMethodGetUsingNamedPS();
1346 mgrWriteMethodGetColumnNames();
1347 mgrWriteMethodGetColumnNames2();
1348 mgrWriteMethodGetFromRS();
1349 mgrWriteMethodGetFromRS2();
1350 mgrWriteMethodGetFromRS1Table();
1351 mgrWriteMethodDecodeFromRS();
1352 //save
1353 mgrWriteMethodSave();
1354 mgrWriteMethodUpdate();
1355 //delete
1356 mgrWriteMethodDelete();
1357 mgrWriteMethodDeleteByKey();
1358 mgrWriteMethodDeleteUsing();
1359 mgrWriteMethodDeleteUsingNoClause();
1360 mgrWriteMethodDeleteWhere();
1361 //count, exists, misc.
1362 mgrWriteMethodCount();
1363 mgrWriteMethodCountWhere();
1364 mgrWriteMethodCountUsing();
1365 mgrWriteMethodCountUsingNoClause();
1366 mgrWriteMethodExists();
1367 mgrWriteMethodExistsUsing();
1368 mgrWriteMethodPrepareStatement();
1369 mgrWriteCheckDiscarded();
1370 mgrWriteMethodStats();
1371 mgrWriteMethodToString();
1372 //validation stuff
1373 mgrWriteValidators();
1374 }
1375
1376 /*
1377 We return a list for no particular reason, it may have
1378 been better to return a bean_type[] instead, which would
1379 save typing casts. Testing shows that:
1380
1381 bean_type[] arr = (bean_type[]) list.toArray(new bean_type[]);
1382
1383 takes 1-2 ms for a 1000 elements so speed is not an issue
1384 (although space might be since the list.toArray is newly
1385 created).
1386 */
1387 final void mgrWriteMethodGetAll()
1388 {
1389 ol();
1390 o("static private final String getAllStmt = \"SELECT ");
1391 o(colsCommaDelimString);
1392 o(" from ");
1393 o(table.getName());
1394 ol("\";");
1395 ol("/** ");
1396 ol("Returns all rows in the table. Use with care for large tables since");
1397 ol("this method can result in VM out of memory errors. <p>This method");
1398 ol("also takes an optional (can be null) <tt>clause</tt> parameter which");
1399 ol("is sent as is to the database. For example, a clause can be:");
1400 ol("<blockquote><pre>");
1401 ol("order by some_column_name");
1402 ol("</pre> </blockquote>");
1403 o("@return a list containing {@link ");
1404 o(beanClassName);
1405 o(" } objects <i>or an empty list</i> if there are no rows in the database");
1406 ol("*/");
1407 ol("public static List getAll(final Connection con, final String clause) throws SQLException");
1408 ol(" {");
1409 ol(" __getall_called++;");
1410 ol(" final List list = new ArrayList();");
1411 //prepared statement has no parameters to set, used for
1412 //caching advantage only, (thus, no need to clear params)
1413 ol(" final String getAllStmtClaused = (clause == null) ? ");
1414 ol(" getAllStmt : getAllStmt + \" \" + clause;");
1415 ol(" PreparedStatement ps = prepareStatement(con, getAllStmtClaused);");
1416 ol(" log.bug(\"Query to run: \", ps);");
1417 ol(" final ResultSet rs = ps.executeQuery();");
1418 ol(" while (true) {");
1419 ol(" " + beanClassName + " bean = decodeFromRS(rs);");
1420 ol(" if (bean == null) { break; } ");
1421 ol(" list.add(bean);");
1422 ol(" }");
1423 ol(" rs.close();");
1424 ol(" return list;");
1425 ol(" }");
1426 } //~write getall
1427
1428 final void mgrWriteMethodGetAllNoClause()
1429 {
1430 ol();
1431 ol("/** ");
1432 o ("Convenience method that invokes {@link getAll(Connection, ");
1433 o (beanClassName);
1434 ol(", String) getAll} with an empty additional clause.");
1435 ol("*/");
1436 o ("public static List getAll(final Connection con) ");
1437 ol("throws ValidateException, SQLException");
1438 ol(" {");
1439 ol(" return getAll(con, null);");
1440 ol(" }");
1441 } //~write getUsing
1442
1443 final void mgrWriteMethodGetLimited()
1444 {
1445 ol();
1446 o("static private final String getLimitedStmt = \"SELECT ");
1447 o(colsCommaDelimString);
1448 o(" from ");
1449 o(table.getName());
1450 ol("\";");
1451 ol("/** ");
1452 ol("Returns all rows in the table starting from some row number and limited");
1453 ol("by a certain number of rows after that starting row. ");
1454 ol("<p>");
1455 ol("This method takes a required (non-null) <code>order_clause</code>, since when using");
1456 ol("a limit clause, rows must be ordered for the limit to make sense. The");
1457 ol("clause should be of the form <font color=blue>order by ...</font>");
1458 ol("<p>");
1459 ol("The <code>limit</code> specifies the number of rows that will be returned. (those many");
1460 ol("or possibly lesser rows will be returned, if the query itself yields less");
1461 ol("rows).");
1462 ol("<p>");
1463 ol("The <code>offset</code> skips that many rows before returning rows. A zero offset is");
1464 ol("the same as a traditional query with no offset clause, where rows from");
1465 ol("the beginning are returned. If say, offset = 10, then rows starting from");
1466 ol("row 11 will be returned.");
1467 ol("<p>");
1468 ol("The sql-query generated by this method is database specific but will (typically) look like:");
1469 ol("<blockquote><pre>");
1470 ol("select <column_list> from <table> order by <clause> limit 5 offset 10");
1471 ol("</pre> </blockquote>");
1472 o("@return a list containing {@link ");
1473 o(beanClassName);
1474 o(" } objects <i>or an empty list</i> if there are no rows in the database");
1475 ol("*/");
1476 ol("public static List getLimited(final Connection con, final String order_clause, int limit, int offset) throws SQLException");
1477 ol(" {");
1478 ol(" __getlimited_called++;");
1479 ol(" final List list = new ArrayList();");
1480 //prepared statement has no parameters to set, used for
1481 //caching advantage only, (thus, no need to clear params)
1482 ol(" final String tmp = getLimitedStmt + \" \" + order_clause + \" LIMIT \" + limit + \" OFFSET \" + offset;");
1483 ol(" PreparedStatement ps = prepareStatement(con, tmp);");
1484 ol(" log.bug(\"Query to run: \", ps);");
1485 ol(" final ResultSet rs = ps.executeQuery();");
1486 ol(" while (true) {");
1487 ol(" " + beanClassName + " bean = decodeFromRS(rs);");
1488 ol(" if (bean == null) { break; } ");
1489 ol(" list.add(bean);");
1490 ol(" }");
1491 ol(" rs.close();");
1492 ol(" return list;");
1493 ol(" }");
1494 } //~write getlimited
1495
1496 final void mgrWriteMethodGetByKey() throws SQLException
1497 {
1498 ol();
1499 if (pklist.size() == 0) {
1500 ol("/* getByKey() not implemented since this table does not have any primary keys defined */");
1501 return;
1502 }
1503
1504 o("static private final String getByPKStmt = \"SELECT ");
1505 o(colsCommaDelimString);
1506 o(" from ");
1507 o(table.getName());
1508 o(" WHERE ");
1509 o(Table.getPreparedStmtPlaceholders(pklist));
1510 ol("\";");
1511 ol("/** ");
1512 ol("Returns <b>the</b> row corresponding to the specified primary key(s) of this table ");
1513 ol("or <b><tt>null</tt></b> if no row was found.");
1514 ol("<p>This method uses a prepared statement and is safe from SQL injection attacks");
1515 ol("*/");
1516 o("public static ");
1517 o(beanClassName);
1518 o(" getByKey(final Connection con, ");
1519 o(pkFormalParamString);
1520 ol(") throws SQLException");
1521 ol(" {");
1522 ol(" __getbykey_called++;");
1523 ol(" PreparedStatement ps = prepareStatement(con, getByPKStmt);");
1524 ol(" StringBuilder errbuf = null;");
1525 ol();
1526 for (int n = 0; n < pklist.size(); n++)
1527 {
1528 ColumnData cd = (ColumnData) pklist.get(n);
1529 if (! cd.usesPrimitiveJavaType())
1530 {
1531 o (" if ("); o(cd.getName()); ol(" == null) {");
1532 ol(" if (errbuf == null) { errbuf = new StringBuilder(); }");
1533 o (" errbuf.append(\"");
1534 o (cd.getName());
1535 ol(" was set to null (but is non-nullable)\").append(IOUtil.LINE_SEP);");
1536 ol(" }");
1537 ol();
1538 }
1539 else{
1540 o (" //");
1541 o (cd.getName());
1542 o (" [");
1543 o (cd.getJavaTypeFromSQLType());
1544 ol("] is primitive, skipping null test");
1545 }
1546
1547 String varname = cd.getName();
1548 o("\t");
1549 String pos = String.valueOf((n+1));
1550 ol(cd.getPreparedStmtSetMethod("ps.", pos, varname) );
1551 }
1552
1553 ol(" ");
1554 ol(" if (errbuf != null) {");
1555 ol(" throw new ValidateException(errbuf.toString());");
1556 ol(" }");
1557 ol(" final ResultSet rs = ps.executeQuery();");
1558 ol(" log.bug(\"Query to run: \", ps);");
1559 ol(" " + beanClassName + " bean = decodeFromRS(rs);");
1560 ol(" rs.close();");
1561 // ol(" ps.clearParameters();");
1562 ol(" return bean;");
1563 ol(" }");
1564 } //~write getbykey
1565
1566
1567 final void mgrWriteMethodGetWhere() throws SQLException
1568 {
1569 ol();
1570 ol("/** ");
1571 ol("Returns the rows returned by querying the table with the specified");
1572 ol("<tt>WHERE</tt> clause or <i>an empty list</i> if no rows were found.");
1573 ol("(note: the string <tt>\"WHERE\"</tt> does <b>not</b> have to be");
1574 ol("specified in the clause. It is added automatically by this method).");
1575 ol("<p>Queries can use database functions such as: <code>lower()</code>,");
1576 ol("<code>upper()</code>, <code>LIKE</code> etc. For example:");
1577 o ("<pre><blockquote>"); o(table.getName());
1578 ol("Mgr.getWhere(\"lower(col_a) = 'foo'\")");
1579 ol("//compares the lower case value of col_a with the string 'foo'");
1580 ol("</blockquote></pre>");
1581 ol("<p><b>The \"where\" clause is sent as-is to the database</b>. SQL");
1582 ol("injection attacks are possible if it is created as-is from a <b><u>untrusted</u></b> source.");
1583 ol();
1584 ol("@throws IllegalArgumentException if the specified <tt>where</tt> parameter is null");
1585 ol("*/");
1586 ol("public static List getWhere(final Connection con, final String where) throws SQLException");
1587 ol(" {");
1588 ol(" __getwhere_called++;");
1589 ol(" Argcheck.notnull(where, \"the where parameter was null (and should not be null)\");");
1590 ol(" final String where_stmt = \"SELECT " + colsCommaDelimString + " from " + table.getName() + " WHERE \" + where ;");
1591 ol(" Statement stmt = QueryUtil.getRewindableStmt(con);");
1592 ol(" log.bug(\"Query to run: \", stmt, \" \", where_stmt);");
1593 ol(" final List list = new ArrayList();");
1594 ol(" final ResultSet rs = stmt.executeQuery(where_stmt);");
1595 ol(" while (true) {");
1596 ol(" " + beanClassName + " bean = decodeFromRS(rs);");
1597 ol(" if (bean == null) { break; } ");
1598 ol(" list.add(bean);");
1599 ol(" }");
1600 ol(" stmt.close();");
1601 ol(" return list;");
1602 ol(" }");
1603 } //~write getwhere
1604
1605 final void mgrWriteMethodGetUsing() throws SQLException
1606 {
1607 ol();
1608 ol("/** ");
1609 ol("Returns the rows returned by querying the table with the value of the");
1610 o ("specified <tt>");
1611 o (beanClassName);
1612 ol("</tt> object or <i>an empty list</i> if no rows were found. As many");
1613 ol("fields in <tt>alltypes</tt> can be set as needed and the values of");
1614 ol("all set fields (including fields explicitly set to <tt>null</tt>)");
1615 ol("are then used to perform the query.");
1616 ol("<p>");
1617 ol("This method is often convenient/safer than the {@link #getWhere");
1618 ol("getWhere} method (because the <tt>getWhere</tt> method takes an");
1619 ol("arbitrary query string which has to be properly escaped by the");
1620 ol("user).");
1621 ol("<p>Essentially, this method is a more convenient way to use a");
1622 ol("PreparedStatement. Internally, a prepared statement is created and");
1623 ol("it's parameters are set to fields that are set in this object).");
1624 ol("Using PreparedStatements directly is also perfectly fine. For");
1625 ol("example, the following are equivalent. ");
1626 ol("<p> Using a PreparedStatement:");
1627 ol("<blockquote><pre>");
1628 ol("String foo = \"select * from table_foo where x = ? and y = ?\";");
1629 ol("PreparedStatement ps = con.prepareStatement(foo);");
1630 ol("ps.setString(1, \"somevalue\");");
1631 ol("ps.setString(2, \"othervalue\");");
1632 ol("ResultSet rs = ps.executeUpdate();");
1633 ol("while (rs.next()) {");
1634 ol(" table_foo bean = table_fooMgr.getFromRS(rs);");
1635 ol(" }");
1636 ol("</pre> </blockquote>");
1637 ol("");
1638 ol("Using this method:");
1639 ol("<blockquote><pre>");
1640 ol("table_foo <font color=blue>proto</font> = new table_foo();");
1641 ol("proto.set_x(\"somevalue\"); //compile time safety");
1642 ol("proto.set_y(\"othervalue\"); //compile time safety");
1643 ol("List beans = table_fooMgr.<font color=blue>getUsing(proto)</font>;");
1644 ol("</pre> </blockquote>");
1645 ol("<p>This method also takes an <tt>clause</tt> parameter which");
1646 ol("is sent as is to the database. For example, a clause can be:");
1647 ol("<blockquote><pre>");
1648 ol("List beans = table_fooMgr.<font color=blue>getUsing(proto, <b>\"order by some_column_name\"</b>)</font>;");
1649 ol("</pre> </blockquote>");
1650 ol("This clause is optional. Specify <tt>null</tt> to not use it at all.");
1651 ol("If the clause is specified, do <font size='+1'><b>NOT</b></font> include the word <tt>WHERE</tt>.");
1652 ol();
1653 ol("The fields that are set in the proto object (as shown above) are sent as");
1654 ol("part of a WHERE clause constructed internally. If you are specifying a clause");
1655 ol("as well, you should not specify the word <tt>WHERE</tt>. However, you may have");
1656 ol("to specify <tt>AND</tt> to add to the internal WHERE clause, if you have set any");
1657 ol("fields in the proto object. For example");
1658 ol("<blockquote><pre>");
1659 ol("List beans = table_fooMgr.<font color=blue>getUsing(proto, <b>\"and bar = 5\"</b>)</font>;");
1660 ol("</pre> </blockquote>");
1661 ol("<p>Note: For a <i>very</i> large number of rows, it may be more");
1662 ol("efficient to use a prepared statement directly (as opposed to using");
1663 ol("this method). In most cases, this is not something to worry about,");
1664 ol("but your mileage may vary...");
1665 ol("*/");
1666 o("public static List getUsing(final Connection con, final ");
1667 o(beanClassName);
1668 ol(" bean, final String clause) throws ValidateException, SQLException");
1669 ol(" {");
1670 ol(" __getusing_called++;");
1671 ol(" Argcheck.notnull(bean, \"the bean parameter was null (and should not be null)\");");
1672 ol(" if (! bean.isModified()) { ");
1673 ol(" throw new ValidateException(\"bean=\" + bean + \" not modified, ignoring query\");");
1674 ol(" }");
1675 ol();
1676 ol(" int __count = 0;");
1677 ol(" final StringBuilder buf = new StringBuilder(512);");
1678 ol(" buf.append(\"SELECT " + colsCommaDelimString + " from " +
1679 table.getName() + " WHERE \");");
1680
1681 List cols = table.getColumnList();
1682 //we allow any col to be set, including pk or partial pk
1683 List relevant_cols = cols;
1684
1685 for (int n = 0; n < cols.size(); n++)
1686 {
1687 ColumnData cd = (ColumnData) cols.get(n);
1688
1689 o (" if (bean.");
1690 o (wrangler.getIsModifiedName(cd));
1691 ol("()) { ");
1692 if (! cd.usesPrimitiveJavaType()) {
1693 o (" if (bean."); o(wrangler.getGetName(cd)); ol("() == null) {");
1694 o (" buf.append(\""); o(cd.getName()); ol(" is NULL and \");");
1695 ol(" }");
1696 ol(" else{");
1697 o (" buf.append(\""); o (cd.getName()); o ("=? and "); ol("\");");
1698 ol(" __count++;");
1699 ol(" }");
1700 }
1701 else{ //primitive type
1702 if (cd.isNullable())
1703 {
1704 o (" if (bean."); o(wrangler.getIsModifiedNullName(cd)); ol("()) {");
1705 o (" buf.append(\""); o(cd.getName()); ol(" is NULL and \");");
1706 ol(" }");
1707 ol(" else{");
1708 o (" buf.append(\""); o (cd.getName()); o ("=? and "); ol("\");");
1709 ol(" __count++;");
1710 ol(" }");
1711 }
1712 else{
1713 o (" buf.append(\""); o (cd.getName()); o ("=? and "); ol("\");");
1714 ol(" __count++;");
1715 }
1716 }
1717 ol(" }");
1718 }
1719
1720 ol();
1721 //get rid of last "and "
1722 ol(" buf.setLength(buf.length() - 4);");
1723 ol();
1724 ol(" if (clause != null) {");
1725 ol(" buf.append(\" \");");
1726 ol(" buf.append(clause);");
1727 ol(" }");
1728 ol();
1729 ol(" final String getUsingPKStmt = buf.toString();");
1730 ol(" PreparedStatement ps = prepareStatement(con, getUsingPKStmt);");
1731
1732 utilFillPStmtFromList_IfModified_Object(relevant_cols, "\t");
1733
1734 ol(" log.bug(\"Query to run: \", ps);");
1735 ol(" final List list = new ArrayList();");
1736 ol(" final ResultSet rs = ps.executeQuery();");
1737 ol(" while (true) {");
1738 ol(" " + beanClassName + " row = decodeFromRS(rs);");
1739 ol(" if (row == null) { break; } ");
1740 ol(" list.add(row);");
1741 ol(" }");
1742 ol(" rs.close();");
1743 // ol(" ps.clearParameters();");
1744 ol(" return list;");
1745 ol(" }");
1746 } //~write getusing
1747
1748 final void mgrWriteMethodGetUsingNoClause() throws SQLException
1749 {
1750 ol();
1751 ol("/** ");
1752 o ("Convenience method that invokes {@link getUsing(Connection, ");
1753 o (beanClassName);
1754 o (", String) getUsing} with an empty <i><tt>clause</tt></i> parameter.");
1755 ol("*/");
1756 o("public static List getUsing(final Connection con, final ");
1757 o(beanClassName);
1758 ol(" bean) throws ValidateException, SQLException");
1759 ol(" {");
1760 ol(" return getUsing(con, bean, null);");
1761 ol(" }");
1762 } //~write getUsingNoClause
1763
1764 //the actual method written is called getUsing(..) not getUsingPS(..)
1765 //i.e., getUsing is overloaded
1766 final void mgrWriteMethodGetUsingPS() throws SQLException
1767 {
1768 ol();
1769 ol("/**");
1770 ol("This is a <i>convenience</i> method that runs the specified ");
1771 ol("prepared statement to perform an arbitrary query. For example: ");
1772 ol("<blockquote>");
1773 ol("<pre>");
1774 ol("PreparedStatement <font color=blue>ps</font> = con.prepareStatement(");
1775 ol(" \"select * from some_table where some_column = ?\");");
1776 ol("ps.setString(1, \"foo\");");
1777 ol("List list = fooMgr.<font color=blue>getUsing</font>(con, <font color=blue>ps</font>);");
1778 ol("for (int n = 0; n < list.size(); n++) {");
1779 ol(" sometable t = (sometable) list.get(n);");
1780 ol(" //do something");
1781 ol(" }");
1782 ol("</pre>");
1783 ol("</blockquote>");
1784 ol("The effect of the above is <u>equivalent</u> to the following (larger) block ");
1785 ol("of code:");
1786 ol("<blockquote>");
1787 ol("<pre>");
1788 ol("PreparedStatement <font color=blue>ps</font> = con.prepareStatement(");
1789 ol(" \"select * from sometable where some_column = ?\"");
1790 ol(" );");
1791 ol("ps.setString(1, \"foo\");");
1792 ol("ResultSet rs = <font color=blue>ps.executeQuery()</font>;");
1793 ol("List list = new ArrayList();");
1794 ol("while (rs.next()) {");
1795 ol(" list.add(sometableMgr.<font color=blue>getFromRS(rs)</font>);");
1796 ol(" }");
1797 ol("");
1798 ol("for (int n = 0; n < list.size(); n++) {");
1799 ol(" sometable t = (sometable) list.get(n);");
1800 ol(" //do something");
1801 ol(" }");
1802 ol("</pre>");
1803 ol("</blockquote>");
1804 ol("");
1805 ol("Note: Just as with other get<i>XXX</i> methods, for large amounts of");
1806 ol("rows (say many thousands), it may be more efficient use and iterate");
1807 ol("through a JDBC result set directly.");
1808 ol("*/");
1809 o("public static List getUsing(final Connection con, ");
1810 ol(" final PreparedStatement ps) throws ValidateException, SQLException");
1811 ol(" {");
1812 ol(" __getusing_ps_called++;");
1813 ol(" log.bug(\"Query to run: \", ps);");
1814 ol(" final List list = new ArrayList();");
1815 ol(" final ResultSet rs = ps.executeQuery();");
1816 ol(" while (true) {");
1817 ol(" " + beanClassName + " row = decodeFromRS(rs);");
1818 ol(" if (row == null) { break; } ");
1819 ol(" list.add(row);");
1820 ol(" }");
1821 ol(" rs.close();");
1822 // ol(" ps.clearParameters();");
1823 ol(" return list;");
1824 ol(" }");
1825 } //~write getusingps
1826
1827
1828 //the actual method written is called getUsing(..) not getUsingPS(..)
1829 //i.e., getUsing is overloaded
1830 final void mgrWriteMethodGetUsingNamedPS() throws SQLException
1831 {
1832 ol();
1833 ol("/**");
1834 ol("This is a <i>convenience</i> method that runs the specified ");
1835 ol("{@link fc.jdbc.dbo.NamedParamStatement NamedParamStatement} to perform an arbitrary query.");
1836 ol("For example: <blockquote>");
1837 ol("<pre>");
1838 ol("NamedParamStatement <font color=blue>ps</font> = queryReadeer.getQuery(\"somequery\");");
1839 ol("ps.setString(\"some_placeholder\", \"foo\");");
1840 ol("List list = fooMgr.<font color=blue>getUsing</font>(con, <font color=blue>ps</font>);");
1841 ol("for (int n = 0; n < list.size(); n++) {");
1842 ol(" sometable t = (sometable) list.get(n);");
1843 ol(" //do something");
1844 ol(" }");
1845 ol("</pre>");
1846 ol("</blockquote>");
1847 ol("");
1848 ol("Note: Just as with other get<i>XXX</i> methods, for large amounts of");
1849 ol("rows (say many thousands), it may be more efficient use and iterate");
1850 ol("through a JDBC result set directly.");
1851 ol("*/");
1852 o("public static List getUsing(final Connection con, ");
1853 ol(" final NamedParamStatement ps) throws ValidateException, SQLException");
1854 ol(" {");
1855 ol(" __getusing_ps_called++;");
1856 ol(" log.bug(\"Query to run: \", ps);");
1857 ol(" final List list = new ArrayList();");
1858 ol(" final ResultSet rs = ps.executeQuery();");
1859 ol(" while (true) {");
1860 ol(" " + beanClassName + " row = decodeFromRS(rs);");
1861 ol(" if (row == null) { break; } ");
1862 ol(" list.add(row);");
1863 ol(" }");
1864 ol(" rs.close();");
1865 // ol(" ps.clearParameters();");
1866 ol(" return list;");
1867 ol(" }");
1868 } //~write getusingps
1869
1870
1871 final void mgrWriteMethodGetColumnNames() throws SQLException
1872 {
1873 ol();
1874 ol("/** ");
1875 o("Returns a <i>comma delimited list</i> of <i>all</i> columns in <tt>");
1876 o(table.getName());
1877 ol("</tt>. These column names are fully qualified, i.e., they contain ");
1878 ol("table name as a prefix to the column name. For example:");
1879 ol("<blockquote><pre>");
1880 ol("<tt>tablename.column1 AS tablename_column1, tablename.column2 AS tablename_column2 ...</tt>");
1881 ol("</pre></blockquote>");
1882 ol("<p>This list is suitable for placing in the column(s) clause of a select query, such as: ");
1883 ol("<blockquote>");
1884 ol("<tt>Single table: select <i><font color=blue>[column_list_A]</font></i> from table_A</tt><br>");
1885 ol("<tt>Join: select <i><font color=blue>[column_list_A], [column_list_B]</font></i> from table_A, table_B</tt>");
1886 ol("</blockquote>");
1887 ol("The ResultSet returned by the query can be used directly or can be passed");
1888 o ("to the {@link #getFromRS getFromRS} method to convert it into a list of <code>");
1889 ol(table.getName());
1890 ol("</code> objects. If the query is a join across multiple tables,");
1891 ol("then the {@link #getFromRS getFromRS} method for each table manager");
1892 ol("can be called on the same ResultSet to retrieve the row object for");
1893 ol("that table.");
1894 ol("Note: the returned list of names has a trailing space, which is good when");
1895 ol("the rest of the query is appended to this list.");
1896 ol("*/");
1897 o("public static String");
1898 ol(" columns() throws SQLException");
1899 ol(" {");
1900 o (" return \"");
1901 //has a trailing space, which is good, we want to return this
1902 //with a trailing space
1903 o ( table.getFullyQualifiedColumnString());
1904 ol("\";");
1905 ol(" }");
1906 } //~write columns
1907
1908
1909 final void mgrWriteMethodGetColumnNames2() throws SQLException
1910 {
1911 ol();
1912 ol("/** ");
1913 o("Returns a <i>comma delimited list</i> of <i>all</i> columns in <tt>");
1914 o(table.getName());
1915 ol("</tt>. These column names are prefix with the specified prefix, which corresponds to the");
1916 ol("table abbreviation used in the \"AS\" clause. For example:");
1917 ol("<blockquote><pre>");
1918 ol("<tt>xyz.column1 AS xyz_column1, xyz.column2 AS xyz_column2 ...</tt>");
1919 ol("</pre></blockquote>");
1920 ol("<p>This list is suitable for placing in the column(s) clause of a select query, such as: ");
1921 ol("<blockquote>");
1922 ol("<p><b>Note:</b> the \".\" will automatically be appended between the prefix and column name");
1923 ol("so the prefix should not end with a \".\" or \"_\", etc<p>");
1924 ol("<tt>Single table: select <i><font color=blue>[column_list_A]</font></i> from table_A <b>AS</b> xyz</tt><br>");
1925 ol("<tt>Join: select <i><font color=blue>[column_list_A], [column_list_B]</font></i> from table_A <b>AS</b> xyz, table_B <b>AS</b> zzz</tt>");
1926 ol("</blockquote>");
1927 ol("The ResultSet returned by the query can be used directly or can be passed");
1928 o ("to the {@link #getFromRS getFromRS(String)} method to convert it into a list of <code>");
1929 ol(table.getName());
1930 ol("</code> objects. If the query is a join across multiple tables,");
1931 ol("then the {@link #getFromRS getFromRS(String)} method for each table manager");
1932 ol("can be called on the same ResultSet to retrieve the row object for");
1933 ol("that table.");
1934 ol("Note: the returned list of names has a trailing space, which is good when");
1935 ol("the rest of the query is appended to this list.");
1936 ol("*/");
1937 o("public static String");
1938 ol(" columns(String prefix) throws SQLException");
1939 ol(" {");
1940 //has a trailing space, which is good, we want to return this
1941 //with a trailing space
1942 ol( table.getPrefixQualifiedColumnString());
1943 ol(" }");
1944 } //~write columns
1945
1946
1947
1948 final void mgrWriteMethodGetFromRS() throws SQLException
1949 {
1950 ol();
1951 ol("/** ");
1952 o("Creates and returns a new <tt>"); o(table.getName());
1953 ol("</tt> object that represents a row from the specified ResultSet. The ResultSet is");
1954 ol("typically obtained via a handwritten query/PreparedStatement. The resulting ");
1955 ol("ResultSet should contain all of the");
1956 ol("column names of table, and this will only happen if the handwritten query had");
1957 ol("a select statement that specified all fields or used a <tt>select <b>*</b>..</tt>");
1958 ol("clause.");
1959 ol("<p>");
1960 ol("In the select clause, we could also be selecting multiple tables. To disambiguate");
1961 ol("between the same field names that may exist in multiple tables, this method ");
1962 ol("also requires that the query should use <font color=blue>fully qualified</font>");
1963 ol("(prefixed with the table name) column names, such as:");
1964 ol("<blockquote><pre>");
1965 ol("<font color=blue>tablename</font>_column1");
1966 ol("<font color=blue>tablename</font>_column2");
1967 ol("...etc.");
1968 ol("</pre></blockquote>");
1969 ol("<p>");
1970 ol("For example:");
1971 ol("<blockquote>");
1972 ol("<code>select <font color=blue>foo</font>.a <b>AS</b> <font color=blue>foo</font>_a, <font color=red>bar</font>.a <b>AS</b> <font color=red>bar</font>_a from <font color=blue>foo</font>, <font color=red>bar</font> where foo.a = bar.a;</code>");
1973 ol("</blockquote>");
1974 ol("The {@link #columns} method conveniently returns a list of column names in fully qualified format ");
1975 ol("and is useful for this purpose.");
1976 ol("<p>Note: This method will read the <i>current</i> row from the specified result set");
1977 ol("and will <b>not</b> move the result set pointer to the next row after the current");
1978 ol("row has been read. The result set should be appropriately positioned [via <tt>rs.next()</tt>]");
1979 ol("<i>before</i> calling this method.");
1980 ol();
1981 ol("@return a new {@link ");
1982 o(beanClassName);
1983 o("} object populated with the contents of the next");
1984 ol(" row from the result set or <tt> null </tt> if");
1985 ol(" the ResultSet was empty.");
1986 ol("*/");
1987 o("public static ");
1988 o(beanClassName);
1989 ol(" getFromRS(final ResultSet rs) throws SQLException");
1990 ol(" {");
1991 ol(" __getfromrs_called++;");
1992 //decode from RS
1993 ol(" Argcheck.notnull(rs, \"the specified resultset parameter was null\");");
1994 ol(" boolean hasrow = ! rs.isAfterLast();");
1995 ol(" if (! hasrow) { ");
1996 ol(" return null; ");
1997 ol(" } ");
1998 o (" ");o(beanClassName);o(" bean = new ");o(beanClassName);ol("();");
1999 ol();
2000 List col_list = table.getColumnList();
2001 for (int n = 0; n < col_list.size(); n++)
2002 {
2003 //THIS METHOD USES COLUMN NAMES NOT COLUMN POSITIONS. THIS IS
2004 //-THE- MAIN DIFF BETWEEN THIS AND THE INTERNAL DECODE_FROM_RS METHOD
2005 // If a column does not exist in the result set and we try to get
2006 // that column by name, a sql exception (per the jdbc spec,
2007 // although the spec is not totally clear on this) is allowed
2008 // to be thrown (for example, the postgres drives does this)...
2009 // so we require all columns to be present while decoding a row
2010 // typically a select * must be issued. if the same column exists with
2011 // the same name in more than 2 tables, we have a problem. We therefore
2012 // require column names to be qualified with tablenames, such as:
2013 // select foo.a as foo_a, bar.a as bar_a from foo, bar;
2014 ColumnData cd = (ColumnData) col_list.get(n);
2015 String setmethod_name = wrangler.getSetName(cd);
2016 o("\tbean.");
2017 o(setmethod_name);
2018 o("( ");
2019
2020 if (! cd.useBooleanObject()) {
2021 o("rs.");
2022 }
2023 o(cd.getResultSetMethod(table.getName() + "_"));
2024
2025 ol(" );");
2026
2027 if (cd.isPK()) {
2028 o("\tbean.__orig_");
2029 o(cd.getName());
2030 o(" = ");
2031
2032 if (! cd.useBooleanObject()){
2033 o("rs.");
2034 }
2035 o(cd.getResultSetMethod(table.getName() + "_"));
2036
2037 ol("; /* save original PK */");
2038 }
2039
2040 ol("\tif (rs.wasNull()) {");
2041 o("\t\tbean.__isNullInDB_");
2042 o(cd.getName());
2043 ol(" = true;");
2044 ol("\t\t}");
2045 }
2046 ol();
2047 ol(" /* set to true when instantiated new, false when we populate the bean from a resultset */");
2048 ol(" bean.setNew(false);") ;
2049 ol(" /* it's not modified, just loaded from the database */");
2050 ol(" bean.resetModified();");
2051 ol(" return bean;");
2052 ol(" }");
2053 } //~write getfromrs
2054
2055
2056
2057 final void mgrWriteMethodGetFromRS2() throws SQLException
2058 {
2059 ol();
2060 ol("/** ");
2061 o("Creates and returns a new <tt>"); o(table.getName());
2062 ol("</tt> object that represents a row from the specified ResultSet. The ResultSet is");
2063 ol("typically obtained via a handwritten query/PreparedStatement. The resulting ");
2064 ol("ResultSet should contain all of the");
2065 ol("column names of table, prefixed with the specified <i>prefix</i> argument.");
2066 ol("a select statement that specified all fields or used a <tt>select <b>*</b>..</tt>");
2067 ol("clause.");
2068 ol("<p>");
2069 ol("In the select clause, we could also be selecting multiple tables. To disambiguate");
2070 ol("between the same field names that may exist in multiple tables, this method ");
2071 ol("also requires that the query should use a <font color=blue>prefix</font>");
2072 ol("(some arbitrary prefix) before column names, such as:");
2073 ol("<blockquote><pre>");
2074 ol("<font color=blue>foo</font>_column1");
2075 ol("<font color=blue>foo</font>_column2");
2076 ol("...etc.");
2077 ol("</pre></blockquote>");
2078 ol("This prefix will typically be the same as the table abbreviation chosen via the <b>AS</b> clause. ");
2079 ol("If the AS clause is not used, then it is simpler to use the {@link getFromRS(ResultSet)} method instead");
2080 ol("<p><b>Note:</b> the \".\" will automatically be appended between the prefix and column name");
2081 ol("so the prefix should not end with a \".\" or \"_\", etc<p>");
2082 ol("<p>");
2083 ol("For example:");
2084 ol("<blockquote>");
2085 ol("<code>select <font color=blue>XXX</font>.a <b>AS</b> <font color=blue>XXX</font>_a, <font color=red>YYY</font>.a <b>AS</b> <font color=red>YYY</font>_a from <font color=blue>foo as XXX</font>, <font color=red>bar as YYY</font> where foo.a = bar.a;</code>");
2086 ol("</blockquote>");
2087 ol("The {@link #columns} method conveniently returns a list of column names in fully qualified format ");
2088 ol("and is useful for this purpose.");
2089 ol("<p>Note: This method will read the <i>current</i> row from the specified result set");
2090 ol("and will <b>not</b> move the result set pointer to the next row after the current");
2091 ol("row has been read. The result set should be appropriately positioned [via <tt>rs.next()</tt>]");
2092 ol("<i>before</i> calling this method.");
2093 ol();
2094 ol("@return a new {@link ");
2095 o(beanClassName);
2096 o("} object populated with the contents of the next");
2097 ol(" row from the result set or <tt> null </tt> if");
2098 ol(" the ResultSet was empty.");
2099 ol("*/");
2100 o("public static ");
2101 o(beanClassName);
2102 ol(" getFromRS(final ResultSet rs, String prefix) throws SQLException");
2103 ol(" {");
2104 ol(" __getfromrs_called++;");
2105 //decode from RS
2106 ol(" Argcheck.notnull(rs, \"the specified resultset parameter was null\");");
2107 ol(" boolean hasrow = ! rs.isAfterLast();");
2108 ol(" if (! hasrow) { ");
2109 ol(" return null; ");
2110 ol(" } ");
2111 o (" ");o(beanClassName);o(" bean = new ");o(beanClassName);ol("();");
2112 ol();
2113 List col_list = table.getColumnList();
2114 for (int n = 0; n < col_list.size(); n++)
2115 {
2116 //THIS METHOD USES COLUMN NAMES NOT COLUMN POSITIONS. THIS IS
2117 //-THE- MAIN DIFF BETWEEN THIS AND THE INTERNAL DECODE_FROM_RS METHOD
2118 // If a column does not exist in the result set and we try to get
2119 // that column by name, a sql exception (per the jdbc spec,
2120 // although the spec is not totally clear on this) is allowed
2121 // to be thrown (for example, the postgres drives does this)...
2122 // so we require all columns to be present while decoding a row
2123 // typically a select * must be issued. if the same column exists with
2124 // the same name in more than 2 tables, we have a problem. We therefore
2125 // require column names to be qualified with tablenames, such as:
2126 // select foo.a as foo_a, bar.a as bar_a from foo, bar;
2127 ColumnData cd = (ColumnData) col_list.get(n);
2128 String setmethod_name = wrangler.getSetName(cd);
2129 o("\tbean.");
2130 o(setmethod_name);
2131 o("( ");
2132
2133 if (! cd.useBooleanObject()) {
2134 o("rs.");
2135 }
2136 o(cd.getRuntimeResultSetMethod());
2137
2138 ol(" );");
2139
2140 if (cd.isPK()) {
2141 o("\tbean.__orig_");
2142 o(cd.getName());
2143 o(" = ");
2144
2145 if (! cd.useBooleanObject()) {
2146 o("rs.");
2147 }
2148 o(cd.getRuntimeResultSetMethod());
2149
2150 ol("; /* save original PK */");
2151 }
2152
2153 ol("\tif (rs.wasNull()) {");
2154 o("\t\tbean.__isNullInDB_");
2155 o(cd.getName());
2156 ol(" = true;");
2157 ol("\t\t}");
2158 }
2159 ol();
2160 ol(" /* set to true when instantiated new, false when we populate the bean from a resultset */");
2161 ol(" bean.setNew(false);") ;
2162 ol(" /* it's not modified, just loaded from the database */");
2163 ol(" bean.resetModified();");
2164 ol(" return bean;");
2165 ol(" }");
2166 } //~write getfromrs2
2167
2168
2169 final void mgrWriteMethodGetFromRS1Table() throws SQLException
2170 {
2171 ol();
2172 ol("/** ");
2173 o("Creates and returns a new <tt>"); o(table.getName());
2174 ol("</tt> object that represents a row from the specified ResultSet. For this method");
2175 ol("to work properly, the specified ResultSet should contain <b>all</b> (typically via <b>select *");
2176 o ("</b>) of the column names of table.");
2177 o ("<tt>");
2178 o (table.getName());
2179 ol("</tt>.");
2180 ol("<p>");
2181 ol("This method does not prepend the table name to columns when reading data from");
2182 ol("the result set. It is useful when writing a JDBC query by hand that uses a single table");
2183 ol("(no joins) and then converting the returned result set into objects of this");
2184 ol("class. For example:");
2185 ol("<p>");
2186 ol("<code>select a, b, c, c*2 from foo where a = 1;</code>");
2187 ol("<p>");
2188 ol("This method will expect columns to be called <code><i>a, b, c</i></code> (no column aliases) in the returned");
2189 ol("result set. In this example, there is only one table <code>foo</code> so qualifying the column");
2190 ol("names, like <code>foo.a as foo_a</code> is not necessary). Also note, for this method to work properly, the ");
2191 ol("column list<blockquote><code>select <i>a, b, c </i></code> ...</blockquote> should be complete, i.e., contain <i>at least</i> all the columns");
2192 ol("of this table (<i>additional</i> expressions like c*2 are fine). It is slightly less efficient to retrieve all columns");
2193 ol("especially for large tables but to construct a row into an object, we need all the fields. To be safe, use <blockquote><tt>select * ....</tt></blockquote>");
2194 ol("<p>");
2195 ol("Of course, if one needs a subset of columns, one can use the ResultSet directly and forego trying to");
2196 ol("convert a ResultSet row into an corresponding object");
2197 ol("<p> ");
2198 ol("See {@link getFromRS(ResultSet)} which is more useful when writing a JDBC");
2199 ol("query that uses multiple table joins.");
2200 ol("<p>Note: This method will read the <i>current</i> row from the specified result set");
2201 ol("and will <b>not</b> move the result set pointer to the next row after the current");
2202 ol("row has been read. The result set should be appropriately positioned [via <tt>rs.next()</tt>]");
2203 ol("<i>before</i> calling this method.");
2204 ol();
2205 o ("@return a new {@link ");
2206 o (beanClassName);
2207 ol("} object populated with the contents of the next");
2208 ol(" row from the result set or <tt> null </tt> if");
2209 ol(" the ResultSet was empty.");
2210 ol("*/");
2211 o ("public static ");
2212 o (beanClassName);
2213 ol(" getFromRS1Table(final ResultSet rs) throws SQLException");
2214 ol(" {");
2215 ol(" __getfromrs_called++;");
2216 //decode from RS
2217 ol(" Argcheck.notnull(rs, \"the specified resultset parameter was null\");");
2218 ol(" boolean hasrow = ! rs.isAfterLast();");
2219 ol(" if (! hasrow) { ");
2220 ol(" return null; ");
2221 ol(" } ");
2222 o (" ");o(beanClassName);o(" bean = new ");o(beanClassName);ol("();");
2223 ol();
2224 List col_list = table.getColumnList();
2225 for (int n = 0; n < col_list.size(); n++)
2226 {
2227 //THIS METHOD USES COLUMN NAMES NOT COLUMN POSITIONS. THIS IS
2228 //-THE- MAIN DIFF BETWEEN THIS AND THE INTERNAL DECODE_FROM_RS METHOD
2229 // If a column does not exist in the result set and we try to get
2230 // that column by name, a sql exception (per the jdbc spec,
2231 // although the spec is not totally clear on this) is allowed
2232 // to be thrown (for example, the postgres driver does this)...
2233 // so we require all columns to be present while decoding a row
2234 // typically a select * must be issued. if the same column exists with
2235 // the same name in more than 2 tables, we have a problem. We therefore
2236 // require column names to be qualified with tablenames, such as:
2237 // select foo.a as foo_a, bar.a as bar_a from foo, bar;
2238 ColumnData cd = (ColumnData) col_list.get(n);
2239 String setmethod_name = wrangler.getSetName(cd);
2240 o("\tbean.");
2241 o(setmethod_name);
2242 o("( ");
2243
2244 if (! cd.useBooleanObject()){
2245 o("rs.");
2246 }
2247 o(cd.getResultSetMethod());
2248
2249 ol(" );");
2250
2251 if (cd.isPK()) {
2252 o("\tbean.__orig_");
2253 o(cd.getName());
2254 o(" = ");
2255
2256 if (! cd.useBooleanObject()) {
2257 o("rs.");
2258 }
2259 o(cd.getResultSetMethod());
2260
2261 ol("; /* save original PK */");
2262 }
2263
2264 ol("\tif (rs.wasNull()) {");
2265 o("\t\tbean.__isNullInDB_");
2266 o(cd.getName());
2267 ol(" = true;");
2268 ol("\t\t}");
2269 }
2270 ol();
2271 ol(" /* set to true when instantiated but this should be false");
2272 ol(" whenever we populate the bean from a result set */");
2273 ol(" bean.setNew(false);") ;
2274 ol(" //it's not modified, just loaded from the database");
2275 ol(" bean.resetModified();");
2276 ol(" return bean;");
2277 ol(" }");
2278 } //~write getfromrs
2279
2280 final void mgrWriteMethodDecodeFromRS() throws SQLException
2281 {
2282 //internal method. decode from RS. moves the result set via rs.next()
2283 ol();
2284 o("private static ");
2285 o(beanClassName);
2286 ol(" decodeFromRS(final ResultSet rs) throws SQLException");
2287 ol(" {");
2288 ol(" Argcheck.notnull(rs, \"the specified resultset parameter was null\");");
2289 ol(" boolean hasrow = rs.next();");
2290 ol(" if (! hasrow) { ");
2291 ol(" return null; ");
2292 ol(" } ");
2293 ol(" " + beanClassName + " bean = new " + beanClassName + "();");
2294 ol();
2295 List col_list = table.getColumnList();
2296 for (int n = 0; n < col_list.size(); n++)
2297 {
2298 ColumnData cd = (ColumnData) col_list.get(n);
2299 String setmethod_name = wrangler.getSetName(cd);
2300 o("\tbean.");
2301 o(setmethod_name);
2302 o("( ");
2303
2304 if (! cd.useBooleanObject()){
2305 o("rs.");
2306 }
2307 o(cd.getResultSetMethod());
2308
2309 ol(" );");
2310
2311 if (cd.isPK()) {
2312 o("\tbean.__orig_");
2313 o(cd.getName());
2314 o(" = ");
2315
2316 if (! cd.useBooleanObject()){
2317 o("rs.");
2318 }
2319 o(cd.getResultSetMethod());
2320
2321 ol("; /* save original PK */");
2322 }
2323
2324 ol("\tif (rs.wasNull()) {");
2325 o("\t\tbean.__isNullInDB_");
2326 o(cd.getName());
2327 ol(" = true;");
2328 ol("\t\t}");
2329 ol();
2330 }
2331
2332 ol();
2333 ol(" /* set to true when newly instantiated but this should be false");
2334 ol(" whenever we populate the bean from a result set */");
2335 ol(" bean.setNew(false);") ;
2336 ol(" //it's not modified, just loaded from the database");
2337 ol(" bean.resetModified();");
2338 ol(" return bean;");
2339 ol(" }");
2340 } //~write decodefromrs
2341
2342 final void mgrWriteMethodSave() throws SQLException
2343 {
2344 ColumnData cd = null;
2345 List cols = table.getColumnList();
2346 //we don't insert or update auto-increment columns
2347 //filter those out and put the rest in filtered_cols
2348 List filtered_cols = new ArrayList();
2349 List autoinc_cols = new ArrayList();
2350
2351 for (int n = 0; n < cols.size(); n++)
2352 {
2353 cd = (ColumnData) cols.get(n);
2354 if (cd.isAutoIncrement()) {
2355 autoinc_cols.add(cd);
2356 if (! modifiableAutoIncrementColumns) {
2357 continue; //don't add it to the filtered list
2358 }
2359 }
2360 filtered_cols.add(cd);
2361 }
2362
2363 final int filtered_count = filtered_cols.size();
2364 ol();
2365 ol("/**");
2366 ol("Saves the specified object into the database. If the specified");
2367 ol("object was newly created, then it is <span style=\"font-variant:");
2368 ol("small-caps\">insert</span>'ed into the database, else (if it was retrieved");
2369 ol("earlier from the database) it is <span ");
2370 ol("style=\"font-variant: small-caps\">update</span>'ed. (this can be");
2371 ol("overriden by the {@link #update update} method). If the object is");
2372 ol("inserted as a new row, then after insertion, the values of");
2373 ol("serial/auto-incremented columns will be automatically available via the");
2374 ol("appropriate getXXX() methods on that object.");
2375 ol("<p>");
2376 ol("<b>NOTE 1:</b> When saving an object, only modified fields are");
2377 ol("saved. Do not rely on default field values (such as null) of newly");
2378 ol("created objects; instead explicitly set the value (including to null");
2379 ol("if needed) of any field that should be saved to the database.");
2380 ol("<p>");
2381 ol("<b>NOTE 2:</b> Once an object is successfully saved, it is discarded");
2382 ol("and cannot be saved again and any attempt to save it again will");
2383 ol("result in a runtime exception. Objects that need to be modified");
2384 ol("again must be re-instantiated or re-populated from the database");
2385 ol("before they can be saved again. (the serial/auto-increment data will still be");
2386 ol("available, discarding only affects the ability to save the object");
2387 ol("again).");
2388 ol("<p>");
2389 ol("<b>Note 3:</b> <font color='red'>For various reasons/flexiblity, default database values");
2390 ol("for columns <i>other</i> than serial columns are <b>not</b> available");
2391 ol("in the saved object. To get these values, retrieve the saved object again. (this is what");
2392 ol("we would have to do internally anyway). This is relevant, for example, when a column has");
2393 ol("a default value of a now() timestamp, and we need to get that timestamp after the object");
2394 ol("has been saved</font>");
2395 ol();
2396 ol("@return the number of rows inserted or updated (typically useful ");
2397 ol(" to see if an update succeeded)");
2398 ol("@throws ValidateException on a validation error");
2399 ol("@throws SQLException on some SQL/Database error");
2400 ol("@throws IOException by the available() method if/when");
2401 ol(" setting a stream for longvar/text types");
2402 ol("*/");
2403 o("public static int save(final Connection con, final ");
2404 o(beanClassName);
2405 //we need IOException for the available() method when
2406 //setting a stream for longvar/text types in our statement
2407 ol(" bean) throws ValidateException, SQLException, IOException");
2408 ol(" {");
2409 ol(" __save_called++;");
2410 ol(" Argcheck.notnull(bean, \"the specified bean parameter was null\");");
2411 ol(" checkDiscarded(bean);");
2412 ol(" if (! bean.isModified()) { ");
2413 ol(" log.warn(\"bean=\", bean, \" not modified, IGNORING SAVE\\n====DEBUG STACK TRACE====\\n\", IOUtil.throwableToString(new Exception()));");
2414 ol(" return 0;");
2415 ol(" }");
2416 ol(" PreparedStatement ps = null;");
2417 ol();
2418 //.... insert into table_foo (col_x, col_y) values (?, ?).....
2419 //.... update table_foo set (col_x=? col_y=?) where ....
2420 ol(" boolean inserting_a_row = false;");
2421 ol(" if (bean.isNew() && ! bean.__force_update) ");
2422 //----------------- INSERT ---------------------
2423 ol(" { //insert new row");
2424 ol(" validateBeforeSaveNew(bean);");
2425 ol(" int __count = 0;");
2426 ol(" inserting_a_row = true;");
2427 ol(" final StringBuilder buf = new StringBuilder(512);");
2428 o (" buf.append(\"INSERT into ");
2429 o(table.getName());
2430 ol(" (\");");
2431
2432 for (int n = 0; n < filtered_count; n++)
2433 {
2434 cd = (ColumnData) filtered_cols.get(n);
2435 o (" if (bean.");
2436 o(wrangler.getIsModifiedName(cd));
2437 ol("()) { ");
2438 o(" buf.append(\"");
2439 o(cd.getName());
2440 ol("\").append(\", \");");
2441 ol(" __count++;");
2442 ol(" }");
2443 }
2444 ol();
2445
2446 ol(" if (__count == 0) {");
2447 ol(" throw new ValidateException(\"Cannot save this bean because no column has been modified. Use JDBC directly as needed.\\n\");");
2448 ol(" }");
2449
2450 //get rid of last ", " [ we need to do it this way
2451 //since we don't know when the last ',' will come since
2452 //any number of columns could have been modified (or not)
2453 ol(" buf.setLength(buf.length() - 2);");
2454 ol(" buf.append(\") values (\");");
2455 ol(" for (int n = 0; n < __count; n++) { ");
2456 ol(" buf.append(\"?\");");
2457 ol(" if ((n+1) < __count)");
2458 ol(" buf.append(\", \");");
2459 ol(" }");
2460 ol(" buf.append(\")\");");
2461 ol();
2462 ol(" final String insertByPKStmt = buf.toString();");
2463 ol(" ps = prepareStatement(con, insertByPKStmt);");
2464
2465 ol(" /* Insert any changed values into our prepared statement */");
2466 utilFillPStmtFromList_IfModified(filtered_cols, "\t\t");
2467 ol(" }");
2468
2469 //-------------------- UPDATE -----------------------
2470 ol(" else //update existing row ");
2471 ol(" {");
2472 if (pklist.size() == 0) {
2473 //we write pknum to shut up the compiler about unreachable statements
2474 ol(" int pknum = 0; /*we write pknum to shut up the compiler about unreachable statements*/");
2475 ol(" if (pknum == 0) {");
2476 ol(" throw new ValidateException(");
2477 ol(" \"Cannot update this bean because it has no primary keys.\"");
2478 ol(" + \"Use JDBC directly to update values to this table.\");");
2479 ol(" }");
2480 }
2481 ol(" validateBeforeSaveUpdate(bean);");
2482 ol(" int __count = 0;");
2483 ol(" final StringBuilder buf = new StringBuilder(512);");
2484 ol(" buf.append(\"UPDATE \");");
2485 o (" buf.append(\"");
2486 o(table.getName());
2487 ol("\");");
2488 ol(" buf.append(\" SET \");");
2489 for (int n = 0; n < filtered_count; n++)
2490 {
2491 cd = (ColumnData) filtered_cols.get(n);
2492 o(" if (bean.");
2493 o(wrangler.getIsModifiedName(cd));
2494 ol("()) { ");
2495 o(" buf.append(\"");
2496 o(cd.getName());
2497 o("=?, ");
2498 ol("\");");
2499 ol(" __count++;");
2500 ol(" }");
2501 }
2502 ol();
2503
2504 ol(" if (__count == 0) {");
2505 ol(" throw new ValidateException(\"Cannot save this bean because no column has been modified. Use JDBC directly as needed.\\n\");");
2506 ol(" }");
2507
2508 //get rid of last ", " [ we need to do it this way
2509 //since we don't know when the last ',' will come since
2510 //any number of columns could have been modified (or not)
2511 ol(" buf.setLength(buf.length() - 2);");
2512
2513 ol(" buf.append(\" WHERE \");");
2514 o (" buf.append(\"");
2515 o(Table.getPreparedStmtPlaceholders(pklist));
2516 ol("\");");
2517
2518 ol(" ps = con.prepareStatement(buf.toString());");
2519 //ol(" log.bug(ps);"); //we write a debug below anyway
2520 ol();
2521
2522 ol(" /* Insert any changed values into our prepared statement */");
2523 //note we don't have to worry about columns that have
2524 //not modified (including primitive columns for which
2525 //isNullInDB is true)
2526 utilFillPStmtFromList_IfModified(filtered_cols, "\t\t");
2527
2528 ol();
2529 ol(" /* Set primary keys for the WHERE part of our prepared statement */");
2530 for (int n = 0; n < pklist.size(); n++)
2531 {
2532 cd = (ColumnData) pklist.get(n);
2533 String getmethod_name = wrangler.getGetName(cd);
2534 String varname = cd.getName();
2535
2536 o("\t\t");
2537 o(cd.getJavaTypeFromSQLType());
2538 o(" ");
2539 o(varname);
2540 o(" = ");
2541
2542 //If it is a forced update, then the primary key(s) are always
2543 //provided as a param (so use that to find the row to update, not
2544 //the cached pk, since that will be null/empty in a new object anyway)
2545 //If it's not a forced update, use cached orig pk value (in case pk
2546 //itself was changed)
2547
2548 o("(bean.__force_update) ? ");
2549 o("bean.");
2550 o(getmethod_name);
2551 o("()");
2552 o(" : ");
2553 o("bean.__orig_");
2554 o(cd.getName());
2555 ol(";");
2556
2557 o("\t\t");
2558 //skip over the if_modified '?' (which are created
2559 //at runtime by the generated code using the 'pos')
2560 //integer
2561 ol(cd.getPreparedStmtSetMethod("ps.", "++pos", varname));
2562 }
2563 ol("\t\t} //~else update;");
2564 ol();
2565 ol(" log.bug(\"Query to run: \", ps);");
2566 ol(" int result = ps.executeUpdate();");
2567
2568 ol(" if (inserting_a_row) { //get auto increment info");
2569 int autoinc_size = autoinc_cols.size();
2570 if (autoinc_size > 0)
2571 {
2572 ol(" /* Retrieve values from auto-increment columns */");
2573 ol(" ResultSet rs = null; Statement stmt = null;");
2574 ol(" String query = null;");
2575 ol(" boolean found = false;");
2576 ol();
2577 for (int n = 0; n < autoinc_size; n++)
2578 {
2579
2580 //if a auto increment column is modifiable AND was modified,
2581 //then we cannot get a auto increment value for it (since
2582 //we are specifying our own value in that case, the sequence
2583 //on the database is not used)
2584
2585 o (" if (bean.");
2586 o(wrangler.getIsModifiedName(cd));
2587 ol("()) { ");
2588 o (" //column: ");
2589 ol(cd.getName());
2590 ol(" //not getting auto increment value for this column");
2591 ol(" //since not using auto increment, a value was specified manually");
2592 ol(" }");
2593 ol(" else{");
2594
2595 cd = (ColumnData) autoinc_cols.get(n);
2596 String query = dbspecific.getAutoIncrementQuery(cd);
2597 String setmethod_name = wrangler.getSetName(cd);
2598 ol(" stmt = con.createStatement();");
2599 o (" query = \""); o(query); ol("\";");
2600 ol(" rs = stmt.executeQuery(query);");
2601 ol(" found = rs.next();");
2602 ol(" if (! found) throw new SQLException(\"No last inserted id returned\");");
2603 o (" bean."); o(setmethod_name); o("( ");
2604
2605 if (! cd.useBooleanObject()){
2606 o("rs.");
2607 }
2608 o(cd.getResultSetMethod());
2609
2610 ol(");");
2611
2612 ol(" if (rs.wasNull()) {");
2613 o (" bean.__isNullInDB_");
2614 o (cd.getName());
2615 ol(" = true;");
2616 ol(" }");
2617 ol(" rs.close();");
2618 ol(" }");
2619 } //~for
2620 ol(" }");
2621 } //~if auto-inc size > 0
2622 else {
2623 ol(" //No auto inc columns in this table");
2624 ol(" }");
2625 }
2626 ol();
2627 ol(" //discard after saving/updating for safety");
2628 ol(" bean.discard();");
2629 ol(" return result;");
2630 ol(" }");
2631 }
2632
2633 final void mgrWriteMethodUpdate() throws SQLException
2634 {
2635 ol();
2636 if (pklist.size() == 0) {
2637 ol("/* update() method not implemented since this bean has no primary keys. Use JDBC directly.");
2638 return;
2639 }
2640
2641 ol("/**");
2642 ol("Uses the specified object to update existing data in the database.");
2643 ol("<p>");
2644 ol("Note, the {@link #save save} method automatically saves newly created objects");
2645 ol("as <i>inserts</i> in the database (and prior <i>retrieved</i> objects, when");
2646 ol("subsequently modified, are saved as <i>updates</i>).");
2647 ol("<p>");
2648 ol("However, sometimes it is useful to create a <i>new</i> object and then");
2649 ol("use its data to <i>update</i> an existing row in the database.");
2650 ol("This method need <b>only</b> be called to save a <u>newly</u>");
2651 ol("created object as an <u>update</u> into the database (overriding the");
2652 ol("default action of saving new objects as inserts in the database).");
2653 ol("<p>");
2654 ol("Note, also, a bean can only be updated if the corresponding table it has");
2655 ol("at least one primary key defined. To update tables with no primary keys,");
2656 ol("use JDBC directly.");
2657 ol("<p>");
2658 o("This method takes primary key(s) of {@link ");
2659 o(beanClassName);
2660 ol("} as additional arguments and sets those in the");
2661 ol("specified bean before updating the database (this way the row to update");
2662 ol("can be uniquely identified).");
2663 ol();
2664 ol("@see #save");
2665 ol();
2666 ol("@return the number of rows that were updated (typically useful ");
2667 ol(" to see if an update succeeded)");
2668 ol("@throws ValidateException on a validation error");
2669 ol("@throws SQLException on some SQL/Database error");
2670 ol("*/");
2671 o("public static int update(final Connection con, final ");
2672 o(beanClassName);
2673 o(" bean, ");
2674 o(pkFormalParamString);
2675 ol(") throws ValidateException, SQLException, IOException");
2676 ol(" {");
2677
2678 for (int n = 0; n < pklist.size(); n++)
2679 {
2680 ColumnData cd = (ColumnData) pklist.get(n);
2681 String varname = cd.getName(); /* used in pkFormalParamString too*/
2682 o("\tbean.");
2683 o( wrangler.getSetName(cd));
2684 o("(");
2685 o(varname);
2686 o(");");
2687 ol();
2688 }
2689 ol();
2690 ol(" if (bean.isNew()) { /* force update (and not insert) for new bean */");
2691 ol(" bean.__force_update = true;");
2692 ol(" }");
2693 ol(" return save(con, bean);");
2694 ol(" }");
2695 }
2696
2697 final void mgrWriteMethodDelete() throws SQLException
2698 {
2699 ol();
2700 if (pklist.size() == 0) {
2701 ol("/* delete() not implemented since this table does not have any primary keys defined. */");
2702 return;
2703 }
2704 o("static private final String deleteStmt = \"DELETE ");
2705 o(" from ");
2706 o(table.getName());
2707 o(" WHERE ");
2708 o(Table.getPreparedStmtPlaceholders(pklist));
2709 ol("\";");
2710 ol("/** ");
2711 o("Deletes this object from the database. ");
2712 ol("<p>");
2713 ol("<b>NOTE 1:</b> Only objects that were retrieved from the database can be deleted. Newly");
2714 ol("created objects cannot be deleted since they do not yet exist in the database.");
2715 ol("Use {@link #deleteByKey deleteByKey} or {@link #deleteWhere deleteWhere} instead");
2716 ol("for arbitrary deletions. <p><b>NOTE 2:</b> Once an object is successfully");
2717 ol("deleted, it is discarded and cannot be deleted again and any attempt to delete");
2718 ol("it again will result in a runtime Exception.");
2719 ol("*/");
2720 o("public static void delete(final Connection con, ");
2721 o(beanClassName);
2722 ol(" bean) throws SQLException");
2723 ol(" {");
2724 ol(" __delete_called++;");
2725 ol(" if (bean.isNew()) {");
2726 ol(" throw new DBOException(\"Cannot delete new objects using this method. Use deleteByKey() or deleteWhere() instead\");");
2727 ol(" }");
2728 ol(" checkDiscarded(bean);");
2729 ol(" final PreparedStatement ps = prepareStatement(con, deleteStmt);");
2730 //list of pk's for this table
2731 for (int n = 0; n < pklist.size(); n++)
2732 {
2733 ColumnData cd = (ColumnData) pklist.get(n);
2734 String getmethod_name = wrangler.getGetName(cd);
2735 o("\t");
2736 o(cd.getJavaTypeFromSQLType());
2737 o(" ");
2738 String varname = cd.getName();
2739 o(varname);
2740 o(" = bean.");
2741 o(getmethod_name);
2742 ol("();");
2743 o("\t");
2744 String pos = String.valueOf((n+1));
2745 ol(cd.getPreparedStmtSetMethod("ps.", pos, varname));
2746 }
2747 ol(" log.bug(\"Query to run: \", ps);");
2748 ol(" final int result = ps.executeUpdate();");
2749 // ol(" ps.clearParameters();");
2750 ol(" if (result != 1) { ");
2751 ol(" throw new DBOException(\"The number of deleted rows was: \" + result + \"; [Should have been 1 row exactly] \");");
2752 ol(" }");
2753 ol(" }");
2754 }
2755
2756 final void mgrWriteMethodDeleteByKey() throws SQLException
2757 {
2758 ol();
2759 if (pklist.size() == 0) {
2760 ol("/* deleteByKey() not implemented since this table does not have any primary keys defined */");
2761 return;
2762 }
2763
2764 o("static private final String deleteByPKStmt = \"DELETE ");
2765 o(" from ");
2766 o(table.getName());
2767 o(" WHERE ");
2768 o(Table.getPreparedStmtPlaceholders(pklist));
2769 ol("\";");
2770 ol("/** ");
2771 o("Deletes the rows with the specified primary key(s) from the database. ");
2772 ol("<p>This method uses a prepared statement and is safe from SQL injection attacks");
2773 ol("*/");
2774 o("public static void deleteByKey(final Connection con, ");
2775 o(pkFormalParamString);
2776 ol(") throws SQLException");
2777 ol(" {");
2778 ol(" __deletebykey_called++;");
2779 ol(" PreparedStatement ps = prepareStatement(con, deleteByPKStmt);");
2780
2781 for (int n = 0; n < pklist.size(); n++)
2782 {
2783 ColumnData cd = (ColumnData) pklist.get(n);
2784 String varname = cd.getName();
2785 o("\t");
2786 String pos = String.valueOf((n+1));
2787 ol( cd.getPreparedStmtSetMethod("ps.", pos, varname) );
2788 }
2789
2790 ol(" log.bug(\"Query to run: \", ps);");
2791 ol(" final int result = ps.executeUpdate();");
2792 // ol(" ps.clearParameters();");
2793 ol(" if (result != 1) { ");
2794 ol(" throw new DBOException(\"The number of deleted rows was: \" + result + \"; [Should have been 1 row exactly] \");");
2795 ol(" }");
2796 ol(" }");
2797 } //~write delete by key
2798
2799 final void mgrWriteMethodDeleteWhere()
2800 {
2801 ol();
2802 ol("/** ");
2803 ol("Deletes the rows with the specified where clause. <p><b>The");
2804 ol("where clause is sent as-is to the database and SQL injection");
2805 ol("attacks are possible if it is created as-is from a untrusted");
2806 ol("source.</b>");
2807 ol("(note: the string <tt>\"WHERE\"</tt> does <b>not</b> have to be");
2808 ol("specified in the clause. It is added automatically by this method).");
2809 ol();
2810 ol("@return the number of rows deleted by the database");
2811 ol("*/");
2812 o("public static int deleteWhere(final Connection con, final String where) throws SQLException");
2813 ol(" {");
2814 ol(" __deletewhere_called++;");
2815 ol(" Argcheck.notnull(where, \"the where parameter was null (and should not be null)\");");
2816 ol(" final String stmt_string = \"DELETE from " + table.getName() + " WHERE \" + where ;");
2817 ol(" Statement stmt = con.createStatement();");
2818 ol(" log.bug(\"Query to run: \", stmt_string);");
2819 ol(" final int result = stmt.executeUpdate(stmt_string);");
2820 ol(" return result;");
2821 ol("}");
2822 }
2823
2824 final void mgrWriteMethodDeleteUsing() throws SQLException
2825 {
2826 ol();
2827 ol("/** ");
2828 ol("Returns the rows returned by querying the table with the contents of");
2829 ol("the specified instance of <tt>alltypes</tt> or <tt>null</tt> if no");
2830 ol("rows were found. As many fields in <tt>alltypes</tt> can be set as");
2831 ol("needed and the values of all set fields (including fields explicitly");
2832 ol("set to <tt>null</tt>) are then used to perform the query. <p>Note,");
2833 ol("however that this method does use any primary key(s). If the ");
2834 ol("primary keys are known then one should use the {@link");
2835 ol("#deleteByKey deleteByKey} method to delete the data instead.");
2836 ol("<p>Likewise, to delete a previously fetched row, use the {@link ");
2837 ol("#delete delete} method. This method is really meant to create an new");
2838 ol("object, set various fields in it, and then use that to delete matching");
2839 ol("row(s) from the database in a type safe way.");
2840 ol("<p>");
2841 ol("This method is often convenient/safer than the {@link #deleteWhere");
2842 ol("deleteWhere} method (because the <tt>deleteWhere</tt> method takes");
2843 ol("an arbitrary query string which has to be properly escaped by the user).");
2844 ol();
2845 ol("<p>However, as a middle ground, this method also takes an <tt>clause</tt> parameter which");
2846 ol("is sent as is to the database. For example, a clause can be:");
2847 ol("<blockquote><pre>");
2848 ol("List beans = table_fooMgr.<font color=blue>deleteUsing(proto, <b>\"xyx > 5\"</b>)</font>;");
2849 ol("</pre> </blockquote>");
2850 ol("This clause is optional. Specify <tt>null</tt> to not use it at all.");
2851 ol("If the clause is specified, do <font size='+1'><b>NOT</b></font> include the word <tt>WHERE</tt>.");
2852 ol();
2853 ol("<p>Essentially, this method is a more convenient way to use a");
2854 ol("PreparedStatement. Internally, a prepared statement is created and");
2855 ol("it's parameters are set to fields that are set in this object).");
2856 ol("Using PreparedStatements directly is also perfectly fine. For");
2857 ol("example, the following are equivalent. ");
2858 ol("<p> Using a PreparedStatement:");
2859 ol("<blockquote><pre>");
2860 ol("String foo = \"delete from table_foo where x = ? and y = ?\";");
2861 ol("PreparedStatement ps = con.prepareStatement(foo);");
2862 ol("ps.setString(1, \"somevalue\");");
2863 ol("ps.setString(2, \"othervalue\");");
2864 ol("int rows_deleted = ps.executeUpdate();");
2865 ol("</pre> </blockquote> ");
2866 ol("");
2867 ol("Using this method:");
2868 ol("<blockquote><pre>");
2869 ol("table_foo proto = new table_foo();");
2870 ol("proto.set_x(\"somevalue\"); //compile time safety");
2871 ol("proto.set_y(\"othervalue\"); //compile time safety");
2872 ol("int rows_deleted = table_fooMgr.<font color=blue>deleteUsing</font>(proto);");
2873 ol("</pre></blockquote>");
2874 ol("@return the number of rows deleted");
2875 ol("*/");
2876 o("public static int deleteUsing(final Connection con, final ");
2877 o(beanClassName);
2878 ol(" bean, final String clause) throws ValidateException, SQLException");
2879 ol(" {");
2880 ol(" __deleteusing_called++;");
2881 ol();
2882 ol(" Argcheck.notnull(bean, \"the bean parameter was null (and should not be null)\");");
2883 ol(" if (! bean.isModified()) { ");
2884 ol(" throw new ValidateException(\"bean=\" + bean + \" not modified, ignoring query\");");
2885 ol(" }");
2886 ol();
2887 ol(" final StringBuilder buf = new StringBuilder(512);");
2888 o (" buf.append(\"DELETE from ");
2889 o (table.getName());
2890 ol(" WHERE \");");
2891 ol();
2892 ol(" int __count = 0;");
2893 List cols = table.getColumnList();
2894 List relevant_cols = new ArrayList();
2895 for (int n = 0; n < cols.size(); n++)
2896 {
2897 ColumnData cd = (ColumnData) cols.get(n);
2898 //columns that are not PK only
2899 if (cd.isPK())
2900 continue;
2901
2902 o (" if (bean.");
2903 o (wrangler.getIsModifiedName(cd));
2904 ol("()) { ");
2905
2906 //for the later call to utilFillPStmtFromList_IfModified
2907 relevant_cols.add(cd);
2908
2909 if (! cd.usesPrimitiveJavaType()) {
2910 o (" if (bean."); o(wrangler.getGetName(cd)); ol("() == null) {");
2911 o (" buf.append(\""); o(cd.getName()); ol(" is NULL and \");");
2912 ol(" }");
2913 ol(" else{");
2914 o (" buf.append(\""); o (cd.getName()); o ("=? and "); ol("\");");
2915 ol(" __count++;");
2916 ol(" }");
2917 }
2918 else{ //primitive type
2919 if (cd.isNullable())
2920 {
2921 o (" if (bean."); o(wrangler.getIsModifiedNullName(cd)); ol("()) {");
2922 o (" buf.append(\""); o(cd.getName()); ol(" is NULL and \");");
2923 ol(" }");
2924 ol(" else{");
2925 o (" buf.append(\""); o (cd.getName()); o ("=? and "); ol("\");");
2926 ol(" __count++;");
2927 ol(" }");
2928 }
2929 else{
2930 o (" buf.append(\""); o (cd.getName()); o ("=? and "); ol("\");");
2931 ol(" __count++;");
2932 }
2933 }
2934 ol(" }");
2935 } //for
2936
2937 if (relevant_cols.size() == 0) {
2938 ol(" throw new RuntimeException(\"This table contains no non-primary keys. Use the deleteByKey method instead.\");");
2939 ol(" }");
2940 return;
2941 }
2942
2943 //get rid of last "and "
2944 ol(" buf.setLength(buf.length() - 4);");
2945 ol(" if (__count == 0) {");
2946 ol(" throw new ValidateException(\"No non-PrimaryKey column was modified/set in this bean. You must set at least one such column. To delete by the Primary key, use the deleteByKey method instead.\");");
2947 ol(" }");
2948 ol(" if (clause != null) {");
2949 ol(" buf.append(\" \");");
2950 ol(" buf.append(clause);");
2951 ol(" }");
2952 ol();
2953 ol(" final String getUsingPKStmt = buf.toString();");
2954 ol(" PreparedStatement ps = prepareStatement(con, getUsingPKStmt);");
2955
2956 utilFillPStmtFromList_IfModified_Object(relevant_cols, "\t");
2957
2958 ol(" log.bug(\"Query to run: \", ps);");
2959 ol(" List list = new ArrayList();");
2960 ol(" int result = ps.executeUpdate();");
2961 // ol(" ps.clearParameters();");
2962 ol(" return result;");
2963 ol(" }");
2964 } //~write deleteusing
2965
2966
2967 final void mgrWriteMethodDeleteUsingNoClause() throws SQLException
2968 {
2969 ol();
2970 ol("/** ");
2971 o ("Convenience method that invokes {@link getUsing(Connection, ");
2972 o (beanClassName);
2973 o (", String) getUsing} with an empty <i><tt>clause</tt></i> parameter.");
2974 ol("*/");
2975 o("public static int deleteUsing(final Connection con, final ");
2976 o(beanClassName);
2977 ol(" bean) throws ValidateException, SQLException");
2978 ol(" {");
2979 ol(" return deleteUsing(con, bean, null);");
2980 ol(" }");
2981 } //~write deleteUsingNoClause
2982
2983
2984 final void mgrWriteMethodCount()
2985 {
2986 ol();
2987 ol("private final static String countStmt = \"SELECT count(*) from " + table.getName() + "\";");
2988 ol("/**");
2989 ol("Returns the count of all rows in the table. <p><b>Note</b>: This may");
2990 ol("be an expensive operation in MVCC databases like PostgresQL, Oracle and");
2991 ol("others, where an entire non-optimized table scan <i>may</i> be");
2992 ol("required -- hence speed will typically be O(n). However, on Postgres (for");
2993 ol("example), this is still very fast for small values of n (on a");
2994 ol("mid-level test machine) as of 2004, counting 4k records was about");
2995 ol("15 milli-seconds(ms); this scaled almost linearly, so count(*) for 16k records was");
2996 ol("about 70 ms, 65k records was about 370 ms, 524k records was about");
2997 ol("2000 ms and 1 million records was about 4000 ms. Results will vary");
2998 ol("on your machine and database but the general O(n) principle will");
2999 ol("remain the same.");
3000 ol("*/");
3001 ol("public static int count(final Connection con) throws SQLException");
3002 ol(" {");
3003 ol(" __count_called++;");
3004 ol(" int __count = -1;");
3005 ol(" final Statement stmt = con.createStatement();");
3006 ol(" final ResultSet rs = stmt.executeQuery(countStmt);");
3007 ol(" if (rs.next())");
3008 ol(" {");
3009 ol(" __count = rs.getInt(1);");
3010 ol(" }");
3011 ol(" else { //rs returned no count, which should never happen");
3012 ol(" throw new DBOException(\"The COUNT query [\" + countStmt + \"] returned no rows. [Should have returned 1 row exactly] \");");
3013 ol(" }");
3014 ol(" stmt.close();");
3015 ol(" return __count;");
3016 ol(" }");
3017 } //~write count
3018
3019 final void mgrWriteMethodCountWhere()
3020 {
3021 ol();
3022 ol("/**");
3023 ol("Returns the count of rows in the table using the specified <tt>where</tt> clause.");
3024 ol("(note: the string <tt>\"WHERE\"</tt> does <b>not</b> have to be");
3025 ol("specified in the clause. It is added automatically by this method).");
3026 ol();
3027 ol("@throws IllegalArgumentException if the where paramater was null");
3028 ol("*/");
3029 ol("public static int countWhere(final Connection con, final String where) throws SQLException");
3030 ol(" {");
3031 ol(" __countwhere_called++;");
3032 ol(" Argcheck.notnull(where, \"the where parameter was null\");");
3033 ol(" int __count = -1;");
3034 ol(" final String countWhereStmt = \"SELECT count(*) from " + table.getName() + " WHERE \" + where;");
3035 ol(" Statement stmt = con.createStatement();");
3036 //the mysql driver does not print stmt.toString() properly so we need
3037 //to also log the statement string
3038 ol(" log.bug(\"Query to run: \", stmt, \" \", countWhereStmt);");
3039 ol(" ResultSet rs = stmt.executeQuery(countWhereStmt);");
3040 ol(" if (rs.next())");
3041 ol(" {");
3042 ol(" __count = rs.getInt(1);");
3043 ol(" }");
3044 ol(" else { //rs returned no count, which should never happen");
3045 ol(" throw new DBOException(\"The COUNT query [\" + countWhereStmt + \"] returned no rows. [Should have returned 1 row exactly] \");");
3046 ol(" }");
3047 ol(" stmt.close();");
3048 ol(" return __count;");
3049 ol(" }");
3050 } //~write countwhere
3051
3052
3053 final void mgrWriteMethodCountUsingNoClause() throws SQLException
3054 {
3055 ol();
3056 ol("/** ");
3057 o ("Convenience method that invokes {@link getUsing(Connection, ");
3058 o (beanClassName);
3059 o (", String) getUsing} with an empty <i><tt>clause</tt></i> parameter.");
3060 ol("*/");
3061 o("public static int countUsing(final Connection con, final ");
3062 o(beanClassName);
3063 ol(" bean) throws ValidateException, SQLException");
3064 ol(" {");
3065 ol(" return countUsing(con, bean, null);");
3066 ol(" }");
3067 } //~write deleteUsingNoClause
3068
3069
3070 final void mgrWriteMethodCountUsing() throws SQLException
3071 {
3072 ol();
3073 ol("/** ");
3074 ol("Returns the rows count by querying the table with the contents of the");
3075 o ("specified instance of <tt>");
3076 o (beanClassName);
3077 ol("</tt> As many fields in <tt>alltypes</tt> can be set as needed and the");
3078 ol("values of all set fields (including fields explicitly set to");
3079 ol("<tt>null</tt>) are then used to perform the query. If the primary");
3080 ol("key(s) are known then one can also use the {@link #exists} method to");
3081 ol("see if that row exists in the database.");
3082 ol("<p>");
3083 ol("This method is often convenient/safer than the {@link #countWhere");
3084 ol("countWhere} method (because the <tt>countWhere</tt> method takes an");
3085 ol("arbitrary query string which has to be properly escaped by the");
3086 ol("user). ");
3087 ol("<p>Essentially, this method is a more convenient way to use a");
3088 ol("PreparedStatement (with parameters set to fields that are set in");
3089 ol("this object). Using PreparedStatements directly is also perfectly");
3090 ol("fine. For example, the following two are equivalent. <p>");
3091 ol("Using a PreparedStatement:");
3092 ol("<blockquote><pre>");
3093 ol("String foo = \"select <i>count(*)</i> from table_foo where x = ? and y = ?\";");
3094 ol("PreparedStatement ps = con.prepareStatement(foo);");
3095 ol("ps.setString(1, \"somevalue\");");
3096 ol("ps.setString(2, \"othervalue\");");
3097 ol("ResultSet rs = ps.executeUpdate();");
3098 ol("rs.next();");
3099 ol("int count = rs.getInt(1);");
3100 ol("</pre> </blockquote>");
3101 ol("");
3102 ol("Using this method:");
3103 ol("<blockquote><pre>");
3104 ol("table_foo proto = new table_foo();");
3105 ol("proto.set_x(\"somevalue\"); //compile time safety");
3106 ol("proto.set_y(\"othervalue\"); //compile time safety");
3107 ol("int count = table_fooMgr.<font color=blue>countUsing</font>(proto);");
3108 ol("</pre> </blockquote>");
3109 ol("The clause is optional. Specify <tt>null</tt> to not use it at all.");
3110 ol("If the clause is specified, do <font size='+1'><b>NOT</b></font> include the word <tt>WHERE</tt>.");
3111 ol("*/");
3112 o("public static int countUsing(final Connection con, final ");
3113 o(beanClassName);
3114 ol(" bean, final String clause) throws ValidateException, SQLException");
3115 ol(" {");
3116 ol(" __countusing_called++;");
3117 o (" Argcheck.notnull(bean, \"the bean parameter was null (and should not be null)\");");
3118 ol(" if (! bean.isModified()) { ");
3119 ol(" throw new ValidateException(\"bean=\" + bean + \" not modified, ignoring query\");");
3120 ol(" }");
3121 ol();
3122 ol(" int __count = 0;");
3123 ol(" final StringBuilder buf = new StringBuilder(512);");
3124 o (" buf.append(\"SELECT count(*) from " );
3125 o (table.getName());
3126 ol(" WHERE \");");
3127
3128 List cols = table.getColumnList();
3129 List relevant_cols = new ArrayList();
3130 for (int n = 0; n < cols.size(); n++)
3131 {
3132 ColumnData cd = (ColumnData) cols.get(n);
3133
3134 //for the later call to utilFillPStmtFromList_IfModified
3135 relevant_cols.add(cd);
3136
3137 o (" if (bean.");
3138 o (wrangler.getIsModifiedName(cd));
3139 ol("()) { ");
3140 if (! cd.usesPrimitiveJavaType()) {
3141 o (" if (bean."); o(wrangler.getGetName(cd)); ol("() == null) {");
3142 o (" buf.append(\""); o(cd.getName()); ol(" is NULL and \");");
3143 ol(" }");
3144 ol(" else{");
3145 o (" buf.append(\""); o (cd.getName()); o ("=? and "); ol("\");");
3146 ol(" __count++;");
3147 ol(" }");
3148 }
3149 else{ //primitive type
3150 if (cd.isNullable())
3151 {
3152 o (" if (bean."); o(wrangler.getIsModifiedNullName(cd)); ol("()) {");
3153 o (" buf.append(\""); o(cd.getName()); ol(" is NULL and \");");
3154 ol(" }");
3155 ol(" else{");
3156 o (" buf.append(\""); o (cd.getName()); o ("=? and "); ol("\");");
3157 ol(" __count++;");
3158 ol(" }");
3159 }
3160 else{
3161 o (" buf.append(\""); o (cd.getName()); o ("=? and "); ol("\");");
3162 ol(" __count++;");
3163 }
3164 }
3165 ol(" }");
3166 }
3167 ol();
3168
3169 //get rid of last "and "
3170 ol(" buf.setLength(buf.length() - 4);");
3171 ol(" if (clause != null) {");
3172 ol(" buf.append(\" \");");
3173 ol(" buf.append(clause);");
3174 ol(" }");
3175 ol();
3176 ol(" final String countUsingStmt = buf.toString();");
3177 ol(" PreparedStatement ps = prepareStatement(con, countUsingStmt);");
3178
3179 utilFillPStmtFromList_IfModified_Object(relevant_cols, "\t");
3180
3181 ol(" log.bug(\"Query to run: \", ps);");
3182 ol(" ResultSet rs = ps.executeQuery();");
3183 ol(" if (! rs.next()) {");
3184 ol(" throw new DBOException(\"The COUNT query [\" + countUsingStmt + \"] returned no rows. [Should have returned 1 row exactly] \");");
3185 ol(" }");
3186 ol(" int rows = rs.getInt(1);");
3187 ol(" rs.close();");
3188 // ol(" ps.clearParameters();");
3189 ol(" return rows;");
3190 ol(" }");
3191 } //~write countusing
3192
3193
3194 final void mgrWriteMethodExists() throws SQLException
3195 {
3196 if (pklist.size() == 0) {
3197 ol("/* exists() not implemented since this table does not have any primary keys defined */");
3198 return;
3199 }
3200
3201 ol();
3202 o("static private final String existsStmt = \"SELECT count(*) from ");
3203 o(table.getName());
3204 o(" WHERE ");
3205 o(Table.getPreparedStmtPlaceholders(pklist));
3206 ol("\";");
3207
3208 ol("/**");
3209 ol("Returns <tt>true</tt> if a row with the specified primary keys exists, <tt>false</tt> otherwise.");
3210 ol("<p>This method uses a prepared statement and is safe from SQL injection attacks");
3211 ol("*/");
3212 o("public static boolean exists(final Connection con, ");
3213 //example: int col_a, String col_b ....
3214 o(pkFormalParamString);
3215 ol(") throws SQLException");
3216 ol(" {");
3217 ol(" __exists_called++;");
3218 ol(" PreparedStatement ps = prepareStatement(con, existsStmt);");
3219 for (int n = 0; n < pklist.size(); n++)
3220 {
3221 ColumnData cd = (ColumnData) pklist.get(n);
3222 String varname = cd.getName(); //example: col_a
3223 o("\t");
3224 String pos = String.valueOf((n+1));
3225 ol(cd.getPreparedStmtSetMethod("ps.", pos, varname));
3226 }
3227 ol(" log.bug(\"Query to run: \", ps);");
3228 ol(" ResultSet rs = ps.executeQuery();");
3229 ol(" int __count = -1;");
3230 ol(" if (rs.next())");
3231 ol(" {");
3232 ol(" __count = rs.getInt(1);");
3233 ol(" }");
3234 ol(" else { //rs returned no count, which should never happen");
3235 ol(" throw new DBOException(\"The COUNT query [\" + existsStmt + \"] returned no rows. [Should have returned 1 row exactly] \");");
3236 ol(" }");
3237 ol(" rs.close();");
3238 // ol(" ps.clearParameters();");
3239 ol(" return (__count > 0); //exists if __count > 0");
3240 ol(" }");
3241 }
3242
3243 final void mgrWriteMethodExistsUsing() throws SQLException
3244 {
3245 ol("/**");
3246 o ("A thin wrapper around {@link getUsing(Connection,"); o(beanClassName);
3247 ol(") getUsing}");
3248 ol("that returns <tt>false</tt> if no rows are returned, <tt>true</tt> otherwise.");
3249 ol("*/");
3250 o("public static boolean existsUsing(final Connection con, final ");
3251 o(beanClassName);
3252 ol(" bean) throws ValidateException, SQLException");
3253 ol(" {");
3254 ol(" final List list = getUsing(con, bean, null);");
3255 ol(" return (list.size() > 0);");
3256 ol(" }");
3257 }
3258
3259 final void mgrWriteMethodPrepareStatement()
3260 {
3261 ol();
3262 ol("/**");
3263 ol("Returns a prepared statement given it's variable name.");
3264 ol();
3265 ol("Essentially speeds up the creation of prepared statements perhaps");
3266 ol("using a per connection cache -- which makes sense for pooled");
3267 ol("connections since they are not closed but returned to the pool");
3268 ol("and hence don't need to \"prepare\" statements every time (the");
3269 ol("prepareStatement call is seperate from actually filling in the");
3270 ol("placeholders in a already created prepared statement -- which");
3271 ol("does need to be done every time). <p>");
3272 ol("Prepared statements are unique per connection, so multiple threads");
3273 ol("using different connections won't stomp over each other's prepared");
3274 ol("statements. Multiple threads using the SAME connection will cause");
3275 ol("bizarre errors but multiple threads won't get the same connection");
3276 ol("from the connection manager -- ever :-), so that should never happen.");
3277 ol("*/");
3278 o("private static final PreparedStatement prepareStatement (");
3279 ol(" final Connection con, final String sql) throws SQLException");
3280 ol(" {");
3281 ol(" if (! (con instanceof fc.jdbc.PooledConnection) ) { ");
3282 ol(" return con.prepareStatement(sql);");
3283 ol(" }");
3284 ol(" final PooledConnection pc = (PooledConnection) con;");
3285 ol(" return pc.getCachedPreparedStatement(sql);");
3286 ol(" }");
3287 }
3288
3289 final void mgrWriteCheckDiscarded()
3290 {
3291 ol();
3292 ol("private static final void checkDiscarded(final DBO bean) throws DBOException");
3293 ol(" {");
3294 ol(" if (bean.isDiscarded()) {");
3295 ol(" throw new DBOException(\"===== Attempt to save a discarded object === \" + bean);");
3296 ol(" }");
3297 ol(" }");
3298 }
3299
3300 final void mgrWriteMethodStats()
3301 {
3302 ol();
3303 ol("private static final java.util.Date __loadDate = new java.util.Date();");
3304 ol("/** Returns usage statistics for this class */");
3305 ol("public static String stats() ");
3306 ol(" {");
3307 ol(" //locally created _numberFormat for thread safety");
3308 ol(" final java.text.NumberFormat _numberFormat = java.text.NumberFormat.getInstance();");
3309 ol(" final String nl = fc.io.IOUtil.LINE_SEP;");
3310 ol(" StringBuffer buf = new StringBuffer(256);");
3311 o (" buf.append(\"Class Name: [");
3312 o (mgrClassName);
3313 ol("]; Class loaded on: \");");
3314 ol(" buf.append(__loadDate);");
3315 ol(" buf.append(nl);");
3316 o (" buf.append(\"---- Start Usage Statistics ----\")"); ol(".append(nl);");
3317 ol();
3318 ol(" ByteArrayOutputStream out = new ByteArrayOutputStream(512);");
3319 ol(" TablePrinter.PrintConfig config = new TablePrinter.PrintConfig();");
3320 ol(" config.setPrintBorders(false);");
3321 ol(" config.setCellSpacing(1);");
3322 ol(" config.setCellPadding(0);");
3323 ol(" config.setAutoFit(true);");
3324 ol(" TablePrinter p = new TablePrinter(2, new PrintStream(out), config);");
3325 ol(" p.startTable();");
3326 ol();
3327 ol(" p.startRow();");
3328 ol(" p.printCell(\"Method\");");
3329 ol(" p.printCell(\"# times called\");");
3330 ol(" p.endRow();");
3331 ol();
3332 ol(" p.startRow();");
3333 ol(" p.printCell(\"getAll()\");");
3334 ol(" p.printCell(_numberFormat.format(__getall_called));");
3335 ol(" p.endRow();");
3336 ol();
3337 ol(" p.startRow();");
3338 ol(" p.printCell(\"getLimited()\");");
3339 ol(" p.printCell(_numberFormat.format(__getlimited_called));");
3340 ol(" p.endRow();");
3341 ol();
3342 ol(" p.startRow();");
3343 ol(" p.printCell(\"getByKey()\");");
3344 ol(" p.printCell(_numberFormat.format(__getbykey_called));");
3345 ol(" p.endRow();");
3346 ol();
3347 ol(" p.startRow();");
3348 ol(" p.printCell(\"getWhere()\");");
3349 ol(" p.printCell(_numberFormat.format(__getwhere_called));");
3350 ol(" p.endRow();");
3351 ol();
3352 ol(" p.startRow();");
3353 ol(" p.printCell(\"getUsing()\");");
3354 ol(" p.printCell(_numberFormat.format(__getusing_called));");
3355 ol(" p.endRow();");
3356 ol();
3357 ol(" p.startRow();");
3358 ol(" p.printCell(\"getUsing(prepared_stmt)\");");
3359 ol(" p.printCell(_numberFormat.format(__getusing_ps_called));");
3360 ol(" p.endRow();");
3361 ol();
3362 ol(" p.startRow();");
3363 ol(" p.printCell(\"getFromRS()\");");
3364 ol(" p.printCell(_numberFormat.format(__getfromrs_called));");
3365 ol(" p.endRow();");
3366 ol();
3367 ol(" p.startRow();");
3368 ol(" p.printCell(\"save()\");");
3369 ol(" p.printCell(_numberFormat.format(__save_called));");
3370 ol(" p.endRow();");
3371 ol();
3372 ol(" p.startRow();");
3373 ol(" p.printCell(\"delete()\");");
3374 ol(" p.printCell(_numberFormat.format(__delete_called));");
3375 ol(" p.endRow();");
3376 ol();
3377 ol(" p.startRow();");
3378 ol(" p.printCell(\"deleteByKey()\");");
3379 ol(" p.printCell(_numberFormat.format(__deletebykey_called));");
3380 ol(" p.endRow();");
3381 ol();
3382 ol(" p.startRow();");
3383 ol(" p.printCell(\"deleteWhere()\");");
3384 ol(" p.printCell(_numberFormat.format(__deletewhere_called));");
3385 ol(" p.endRow();");
3386 ol();
3387 ol(" p.startRow();");
3388 ol(" p.printCell(\"deleteUsing()\");");
3389 ol(" p.printCell(_numberFormat.format(__deleteusing_called));");
3390 ol(" p.endRow();");
3391 ol();
3392 ol(" p.startRow();");
3393 ol(" p.printCell(\"count()\");");
3394 ol(" p.printCell(_numberFormat.format(__count_called));");
3395 ol(" p.endRow();");
3396 ol();
3397 ol(" p.startRow();");
3398 ol(" p.printCell(\"countWhere()\");");
3399 ol(" p.printCell(_numberFormat.format(__countwhere_called));");
3400 ol(" p.endRow();");
3401 ol();
3402 ol(" p.startRow();");
3403 ol(" p.printCell(\"countUsing()\");");
3404 ol(" p.printCell(_numberFormat.format(__countusing_called));");
3405 ol(" p.endRow();");
3406 ol();
3407 ol(" p.startRow();");
3408 ol(" p.printCell(\"exists()\");");
3409 ol(" p.printCell(_numberFormat.format(__exists_called));");
3410 ol(" p.endRow();");
3411 ol();
3412 ol(" p.endTable();");
3413 ol(" buf.append(out.toString());");
3414 ol(" return buf.toString();");
3415 ol(" }");
3416 }
3417
3418 final void mgrWriteMethodToString()
3419 {
3420 ol();
3421 ol("public String toString() ");
3422 ol(" {");
3423 ol(" return getClass().getName() + \" [call stats() for more info]\";");
3424 ol(" }");
3425 }
3426
3427 void mgrWriteValidators() throws SQLException
3428 {
3429 ol();
3430 ol("// ================ Validation ==================== ");
3431 ol();
3432 ol("/** ");
3433 ol("Creates and attaches validators for all the fields in the");
3434 ol("specified {@link fc.web.forms.Form}. These fields should");
3435 o("<i>have the same name</i> in the form as in {@link "); o(beanClassName);
3436 ol("}. If this is not the case, then the then the differences can be specifed");
3437 ol("as follows. <p>");
3438 ol("<dl>");
3439 ol("<dt>with a prefix</dt>");
3440 o (" <dd><tt>(prefix + ");o(beanClassName);ol(" column)</tt> should equal <tt>form fieldname</tt></dd>");
3441 ol("<dt>with a suffix</dt> ");
3442 o (" <dd><tt>(");o(beanClassName);ol(" column + suffix)</tt> should equal <tt>form fieldname</tt></dd>");
3443 ol("<dt>with both a prefix/suffix</dt> ");
3444 o (" <dd><tt>(prefix + ");o(beanClassName);ol(" + suffix)</tt> should equal <tt>form fieldname</tt></dd>");
3445 ol("<dt>with a arbitrary map</dt> ");
3446 o (" <dd>[key] <tt>");o(beanClassName);ol(" column</tt> -> [value] <tt>form fieldname</tt>");
3447 ol(" <u>If a map is specified, then the prefix/suffix are not used.</u>");
3448 ol(" </dd>");
3449 ol("</dl>");
3450 ol("<p>These validators are for database constraints such as <i>nullability</i> & <i>column length</i>.");
3451 ol("These validators save a lot of grunt-work in adding such schema");
3452 ol("constraints to the front-end {@link fc.web.forms.Form}. <p><b>However, <i>business and");
3453 ol("other validation constraints</i> still need to be manually added to");
3454 ol("the application code/front-end forms as/when needed</b>.");
3455 ol("<p>");
3456 ol("");
3457 ol("The following table shows the kind of validators added by this method");
3458 ol("<table border=1 width=90%>");
3459 ol("<tr bgcolor='#CCCCCC'>");
3460 ol(" <td>Database SQL Type</td>");
3461 ol(" <td><b>Nullable</b>validator</td>");
3462 ol(" <td><b>Length</b> validator</td>");
3463 ol(" <td><b>Digits only</b> input validator ({@link VText#allowIntegersOnly})</td>");
3464 ol("</tr>");
3465 ol(" <tr>");
3466 ol(" <td><tt>CHAR</tt>, <tt>VARCHAR</tt></td>");
3467 ol(" <td>Yes (maximum length constraint).<br><font size='-1' color=red>This");
3468 ol(" only applies to form fields that are subclasses of {@link ");
3469 ol(" fc.web.forms.MaxSizable} </font></td>");
3470 ol(" <td>-NO-</td>");
3471 ol(" </tr>");
3472 ol(" <tr>");
3473 ol(" <td><tt>TINYINT, MEDIUMINT, INT, BIGINT (integral types)</tt></td>");
3474 ol(" <td>Yes</td>");
3475 ol(" <td>-NO-</td>");
3476 ol(" <td>Yes to integer columns displayed using form fields that are subclasses of {@link fc.web.forms.AbstractText}<br> Note: <b>not</b> added non-<i>integral</i> number types such as <tt>FLOAT, REAL, DOUBLE, NUMERIC/DECIMAL</tt></td>");
3477 ol(" </tr>");
3478 ol(" <tr>");
3479 ol(" <td>All other SQL types</td>");
3480 ol(" <td>Yes</td>");
3481 ol(" <td>-NO-</td>");
3482 ol(" </tr>");
3483 ol("</table>");
3484 ol("<p>Automatic validators are very useful but can be very tricky to understand. It is");
3485 ol("suggested to invoke this method, print the form using it's <tt>toString</tt>");
3486 ol("method and then examine the output to see what validators were added If those");
3487 ol("automatic validators are too little, too many or too hard to understand, <u>then");
3488 ol("simply enoough, do NOT invoke this method and simply add validators by");
3489 ol("hand</u>. In particular, do <i>not</i> add automatic validators for");
3490 ol("<b>tables</b> in which a row is optional but <i>if</i> some column is filled in");
3491 ol("the front end form, <i>then</i> all columns must be filled.");
3492 ol();
3493 ol("@param form the form containing fields (some or all) representing");
3494 ol(" this and possible other tables. These field");
3495 ol(" objects must have been added to the form prior");
3496 ol(" to calling this method");
3497 ol("@param prefix an optional (null allowed) prefix to this table's column name with which the");
3498 ol(" corresponding column was added to the form.");
3499 ol(" A <tt>*</tt> specifies all possible prefixes");
3500 ol("@param suffix an optional suffix (null allowed) to this table's column name with which the ");
3501 ol(" corresponding column was added to the form.");
3502 ol(" A <tt>*</tt> specifies all possible suffixes");
3503 ol("@param map an optional map (null allowed) that maps this table's column name with which the ");
3504 ol(" corresponding column was added to the form. ");
3505 ol(" [key] <tt>table's column_name</tt> -> [value] <tt>form's fieldname</tt>");
3506 ol("*/");
3507 ol("public static void addValidators(final fc.web.forms.Form form, final String prefix, final String suffix, final Map map) ");
3508 ol(" {");
3509 ol(" addValidators(form, prefix, suffix, map, false);");
3510 ol(" }");
3511 ol();
3512
3513 ol("private static void addValidators(fc.web.forms.Form form, String prefix, String suffix, final Map map, final boolean onlyOnFilled) ");
3514 ol(" {");
3515 ol(" List list = null;");
3516 ol(" Argcheck.notnull(form, \"How can I add validators to the form when the form parameter was null ?\");");
3517 ol(" Field field = null;");
3518 ol(" FieldValidator fv = null;");
3519 ol(" String colname_in_form = null;");
3520 ol(" //fields can be null if they are not being used in the html form");
3521 List cols = table.getColumnList();
3522 for (int n = 0; n < cols.size(); n++)
3523 {
3524 ColumnData cd = (ColumnData) cols.get(n);
3525 String colname = cd.getName();
3526 String sqltypename = cd.getSQLTypeName().intern();
3527 ol();
3528 o ("\t");ol(getBeanComment(cd));
3529 o (" list = getFieldFromForm(form, \"");
3530 o (colname);
3531 o ("\", prefix, suffix, map, ");
3532
3533 //false=no warn message if this field not in form
3534 if (cd.isAutoIncrement())
3535 o("false");
3536 else
3537 o("true");
3538
3539 ol(");");
3540 ol(" if (list.size() > 0) ");
3541 ol(" { //add applicable automatic validators, empty if n/a");
3542 ol(" for (int n = 0; n < list.size(); n++)");
3543 ol(" {");
3544 ol(" field = (Field) list.get(n);");
3545
3546 //nullable
3547 if (! cd.isNullable())
3548 {
3549 if (cd.hasDefaultValue()) {
3550 o (" /* field is non-nullable but has a default value [");
3551 o (cd.getDefaultValue());
3552 ol("], skipping non-nullability validation */");
3553 }
3554 else{
3555 ol(" if (field instanceof Choice) {");
3556 ol(" //choice fields are ignored because they can");
3557 ol(" //mean false even when NOT selected/filled");
3558 ol(" continue;");
3559 ol(" }");
3560 ol(" /* field is non-nullable */");
3561 o (" fv = new VFilled(field, \"");
3562 o(validateNull_ErrorMsg);
3563 ol("\");");
3564 }
3565 }
3566 else{
3567 ol(" /* field is nullable, skipping non-nullability validation */");
3568 }
3569
3570 //numbers only
3571 if (sqltypename.toLowerCase().indexOf("int") >= 0)
3572 {
3573 ol();
3574 ol(" /* database type for this field is integral */");
3575 ol(" if (field instanceof AbstractText) {");
3576 o (" fv = new VText((AbstractText)field, \"");
3577 o(validateIntegerOnly_ErrorMsg);
3578 ol("\")");
3579 ol(" .allowIntegersOnly();");
3580 ol(" }");
3581 }
3582
3583 //length
3584 if ((sqltypename == "CHAR" || sqltypename == "VARCHAR"))
3585 /*
3586 colsize IS NOT accurate/reliable for non text types.
3587 (therefore we only set maxsize for char/varchar)
3588
3589 text(longvarchar) size is not knowable with postgres and postgres
3590 returns -1 for LONGVARCHAR Except now we have another bug, the
3591 current driver returns VARCHAR even for text columns (instead of
3592 LONGVARCHAR). What the fuck ? They are fucking up the best free
3593 database on the planet with a shitty jdbc driver. god, it's almost
3594 5am and i'm tired. p.s: postgres jdbc driver + support is still
3595 waaay better than all other competing db's though.
3596 */
3597 {
3598 //bug workaround
3599 int colsize = cd.getSize();
3600 if (colsize < 0)
3601 colsize = Integer.MAX_VALUE;
3602 //end workaround
3603
3604 ol(" if (! (field instanceof MaxSizable)) {");
3605 o (" log.warn(\"Skipping maximum length validator for field '\" + field.getName() + \"'; [database type='");o(sqltypename);ol("', field.type='\" + field.getType() + \"' is not MaxSizable]\"); ");
3606 ol(" }");
3607 ol(" else{");
3608 o (" VText vt = new VText((MaxSizable) field, \"");
3609 o(validateText_ErrorMsg_MaxSize);
3610 ol("\");");
3611 o (" vt.setMaxSize(");
3612 o (String.valueOf(colsize));
3613 ol(");");
3614 ol(" }");
3615 }
3616 ol(" }"); //generated for
3617 ol(" }");
3618 }
3619 ol(" }");
3620 ol();
3621
3622 ol("/** ");
3623 ol("Convenience method that calls {@link #addValidators(Form, String, String, Map)} with a ");
3624 ol("<tt>null</tt> prefix/suffix and the specified map");
3625 ol("*/");
3626 ol("public static void addValidators(fc.web.forms.Form form, Map map) ");
3627 ol(" {");
3628 ol(" addValidators(form, null, null, map);");
3629 ol(" }");
3630 ol();
3631
3632 ol("/** ");
3633 ol("Convenience method that calls {@link #addValidators(Form, String, String, Map)} with a ");
3634 ol("<tt>null</tt> prefix/suffix/map");
3635 ol("*/");
3636 ol("public static void addValidators(fc.web.forms.Form form) ");
3637 ol(" {");
3638 ol(" addValidators(form, null, null, null);");
3639 ol(" }");
3640 ol();
3641
3642 ol("/** ");
3643 ol("Convenience method that calls {@link #addValidators(Form, String, String, map)} with the ");
3644 ol("specified prefix and a <tt>null</tt> suffix/map");
3645 ol("*/");
3646 ol("public static void addValidators(fc.web.forms.Form form, String prefix) ");
3647 ol(" {");
3648 ol(" addValidators(form, prefix, null, null);");
3649 ol(" }");
3650 ol();
3651
3652 ol("/** ");
3653 ol("Validates a form field <i>if</i> it is filled by the user. Leaves empty fields alone.");
3654 ol("This is very useful for fields that are optional but must have the correct value when");
3655 ol("filled by the user");
3656 ol("*/");
3657 ol("public static void addIfFilledValidators(Form form, String prefix) ");
3658 ol(" {");
3659 ol(" addValidators(form, prefix, null, null, true);");
3660 ol(" }");
3661 ol();
3662
3663 ol("/** implementation helper method -- not for public use */");
3664 ol("static List getFieldFromForm(Form form, String colname, String prefix, String suffix, Map map, boolean warn)");
3665 ol(" {");
3666 ol(" Field field = null;");
3667 ol(" List list = Form.empty_list;");
3668 ol(" boolean getwhere = false;");
3669 ol(" getwhere = false;");
3670 ol(" String colname_in_form = colname;");
3671 ol();
3672 ol(" if (map != null) {");
3673 ol(" String str = (String) map.get(colname);");
3674 ol(" if (str != null) {");
3675 ol(" prefix = null; /*ignored when there is a mapping*/");
3676 ol(" suffix = null; /*ignored when there is a mapping*/");
3677 ol(" colname_in_form = str; /* else if not in map, colname remains as-is*/");
3678 ol(" }");
3679 ol(" }");
3680 ol();
3681 ol(" if (prefix != null) ");
3682 ol(" { ");
3683 ol(" if (prefix.equals(\"*\")) { ");
3684 ol(" getwhere = true;");
3685 ol(" }");
3686 ol(" else{");
3687 ol(" colname_in_form = prefix + colname_in_form;");
3688 ol(" }");
3689 ol(" }");
3690 ol();
3691 ol(" if (suffix != null) ");
3692 ol(" {");
3693 ol(" if (suffix.equals(\"*\")) { ");
3694 ol(" getwhere = true;");
3695 ol(" }");
3696 ol(" else{");
3697 ol(" colname_in_form = colname_in_form + suffix;");
3698 ol(" }");
3699 ol(" }");
3700 ol();
3701 ol(" if (getwhere) { ");
3702 ol(" list = form.getContaining(colname_in_form);");
3703 ol(" if (list.size() == 0 && warn) warn(form, colname_in_form, suffix, prefix, map);");
3704 ol(" return list;");
3705 ol(" }");
3706 ol(" else{");
3707 ol(" //containsField() check prevents an un-necessary warning with form.get()");
3708 ol(" if (! form.containsField(colname_in_form)) {");
3709 ol(" if (warn) warn(form, colname_in_form, suffix, prefix, map);");
3710 ol(" return list;");
3711 ol(" }");
3712 ol(" field = form.get(colname_in_form);" );
3713 ol(" list = new ArrayList();");
3714 ol(" list.add(field);");
3715 ol(" }");
3716 ol(" return list;");
3717 ol(" }");
3718
3719 ol();
3720 ol("private static final void warn(Form form, String name, String suffix, String prefix, Map map) {");
3721 ol(" log.warn(form.getName(),\": No automatic validators will be added for Field [\",name,\"]. This field does not exist in the front-end form. (this could be normal). suffix=[\"+suffix+\"] prefix=[\"+prefix+\"] map=\", map);");
3722 ol(" }");
3723
3724 //ValidateBeforeSaveNew
3725 ol();
3726 ol("/** ");
3727 ol("Validates the bean before saving it to the database. This");
3728 ol("method is called internally by the {@link save()} method");
3729 ol("before saving a new bean (i.e., inserting a new row) to the");
3730 ol("database.");
3731 ol("<p>");
3732 ol("The validation is somewhat basic and there can exist many");
3733 ol("constraints and conditions on the database that might results in a");
3734 ol("insert/update error anyway. But by doing some basic validation");
3735 ol("against some known constraints, we save a needless trip to the");
3736 ol("database.");
3737 ol("We check to see that: <ol>");
3738 ol("<li><i>non-nullable and non auto-increment</i> columns [and with no default");
3739 ol("column value in the database] are modified (via a set method) since these");
3740 ol("columns must have a explicitly set value.</li>");
3741 ol("<li>for non-nullable columns that hold non-primitive (i.e., Object)");
3742 ol("java types, the modified value for non-nullable columns is a");
3743 ol("non-null object. </li>");
3744 ol("</ol>");
3745 ol("*/");
3746 o ("protected static void validateBeforeSaveNew(");
3747 o (beanClassName);
3748 ol(" bean) throws ValidateException");
3749 ol(" {");
3750 ol(" boolean error = false;");
3751 ol(" final StringBuffer buf = new StringBuffer(\"The following validation errors were found\").append(IOUtil.LINE_SEP);");
3752 for (int n = 0; n < cols.size(); n++)
3753 {
3754 ColumnData cd = (ColumnData) cols.get(n);
3755 String colname = cd.getName();
3756 ol();
3757 if (cd.isNullable()) {
3758 o (" //["); o (colname);
3759 ol("]=>nullable, no modification check necessary, skipping...");
3760 }
3761 else if ( ! cd.isNullable() && cd.hasDefaultValue()) {
3762 o (" //["); o (colname);
3763 o ("]=>is not nullable but has a default column value [");
3764 o (cd.getDefaultValue());
3765 ol("], no modification check necessary, skipping...");
3766 }
3767 else if (cd.isAutoIncrement()) {
3768 o (" //["); o (colname);
3769 ol("]=>auto-increment, no modification check necessary, skipping...");
3770 }
3771 else { //not nullable and value required
3772 o(" if (! bean.");
3773 o(wrangler.getIsModifiedName(cd));
3774 ol("()) {");
3775 ol(" error = true;");
3776 o (" buf.append(\"");
3777 o (colname);
3778 o (" was not set (this field is required in the database)\").append(\";current value=\"");
3779 o (").append(bean.");
3780 o (wrangler.getGetName(cd));
3781 ol("()).append(IOUtil.LINE_SEP);");
3782 ol(" }");
3783
3784 if (! cd.usesPrimitiveJavaType())
3785 {
3786 ol(" else { //was modified but to null");
3787 o (" if (bean.");
3788 o (wrangler.getGetName(cd));
3789 ol("() == null) {");
3790 ol(" error = true;");
3791 o (" buf.append(\"");
3792 o (colname);
3793 o (" was set to null (but is non-nullable)\").append(\";current value=\"");
3794 o (").append(bean.");
3795 o (wrangler.getGetName(cd));
3796 ol("()).append(IOUtil.LINE_SEP);");
3797 ol(" }");
3798 ol(" }");
3799 }
3800 else{
3801 o (" //");
3802 o (cd.getJavaTypeFromSQLType());
3803 ol(" is primitive, skipping null test");
3804 }
3805 }
3806 } //for
3807
3808 ol(" if (error) { ");
3809 ol(" throw new ValidateException(buf.toString());");
3810 ol(" }");
3811 ol(" }");
3812
3813 //ValidateBeforeSaveUpdate
3814 ol();
3815 ol("/** ");
3816 ol("Validates the bean before saving it to the database. This method is");
3817 ol("called internally by the {@link save()} method before updating an");
3818 ol("existing bean (i.e., updating a row) in the database.");
3819 ol("<p>");
3820 ol("For <i>each modified column</i>, if that column is non-nullable in");
3821 ol("the database, then it must have a non-null value in the bean before");
3822 ol("it is saved. This check is only done for fields of");
3823 ol("<i>non</i>-primitive (Object) java types. [There is no way to ensure");
3824 ol("a non-null value for <i>primitive</i> types since all values");
3825 ol("[including 0] are non-null for those types].");
3826 ol("*/");
3827 o ("protected static void validateBeforeSaveUpdate(");
3828 o (beanClassName);
3829 ol(" bean) throws ValidateException");
3830 ol(" {");
3831 ol(" boolean error = false;");
3832 ol(" final StringBuffer buf = new StringBuffer(\"The following validation errors were found\").append(IOUtil.LINE_SEP);");
3833 ol();
3834
3835 for (int n = 0; n < cols.size(); n++)
3836 {
3837 ColumnData cd = (ColumnData) cols.get(n);
3838 String colname = cd.getName();
3839
3840 if (cd.isNullable()) {
3841 o (" //["); o (colname);
3842 ol("]=>nullable, no modification check necessary, skipping...");
3843 }
3844 /*
3845 dont check for existence of default column value here, if it's an
3846 update, prolly a good idea for the bean to have a updated value and not
3847 rely on column defaults
3848 */
3849 else if (cd.isAutoIncrement()) {
3850 o (" //["); o (colname);
3851 ol("]=>auto-increment, no modification check necessary, skipping...");
3852 }
3853 /*
3854 else if (cd.isPK()) { //must be able to find the row to update
3855 o(" if (! bean.");
3856 o(wrangler.getIsModifiedName(cd));
3857 ol("()) {");
3858 ol(" error = true;");
3859 o (" buf.append(\"");
3860 o (colname);
3861 ol(" was not set (this field is a primary key and is needed to find the row to update.\");");
3862 ol(" }");
3863 }
3864 */
3865 /* unlike validatebeforesave, we don't check to see if every
3866 required column has been modified (an update allows updates
3867 whatever is modified, and leaves the rest (required or non required)
3868 alone
3869 */
3870 else{
3871 o(" if (bean.");
3872 o(wrangler.getIsModifiedName(cd));
3873 ol("()) {");
3874 if (! cd.usesPrimitiveJavaType())
3875 {
3876 o (" if (bean.");
3877 o (wrangler.getGetName(cd));
3878 ol("() == null) {");
3879 ol(" error = true;");
3880 o (" buf.append(\"");
3881 o (colname);
3882 o (" was set to null (but is non-nullable)\").append(\";current value=\"");
3883 o (").append(bean.");
3884 o (wrangler.getGetName(cd));
3885 ol("()).append(IOUtil.LINE_SEP);");
3886 ol(" }");
3887 }
3888 else{
3889 o (" //");
3890 o (cd.getJavaTypeFromSQLType());
3891 ol(" is primitive, skipping null test");
3892 }
3893 ol(" }");
3894 }
3895 } //for
3896
3897 ol(" if (error) { ");
3898 ol(" throw new ValidateException(buf.toString());");
3899 ol(" }");
3900 ol(" }");
3901
3902 }//~mgrWriteValidators()
3903
3904 void o(String str) {
3905 out.print(str);
3906 }
3907
3908 void o(String str, String str2) {
3909 out.print(str);
3910 out.print(str2);
3911 }
3912
3913 void ol(String str) {
3914 out.println(str);
3915 }
3916
3917 void ol() {
3918 out.println();
3919 }
3920
3921 void utilFillPStmtFromList_IfModified (
3922 final List list, final String tabprefix)
3923 throws SQLException
3924 {
3925 final String tabprefix2 = tabprefix + "\t";
3926 final String tabprefix3 = tabprefix + "\t\t";
3927
3928 o(tabprefix);
3929 ol("int pos = 0;");
3930
3931 for (int n = 0; n < list.size(); n++)
3932 {
3933 ColumnData cd = (ColumnData) list.get(n);
3934 String colname = cd.getName();
3935
3936 o(tabprefix);
3937 o("if (bean.");
3938 o(wrangler.getIsModifiedName(cd));
3939 ol("()) {");
3940
3941 o(tabprefix2);
3942 ol("pos++;");
3943
3944 /*
3945 if it's modified, then we save it, whether it was originally null in the
3946 db is irrelevant. of course, if it's set to null, we save it as null,
3947 else a real value otherwise
3948 */
3949
3950 if (cd.usesPrimitiveJavaType() && cd.isNullable())
3951 {
3952 o(tabprefix2);
3953 o ("if (bean.");
3954 o (wrangler.getIsModifiedNullName(cd));
3955 ol("()) { /* nullable in db */");
3956 o(tabprefix3);
3957 o("ps.");
3958 o(cd.getPreparedStmtSetNullMethod("pos", colname));
3959 ol(";");
3960 o(tabprefix3);
3961 ol("}");
3962 o(tabprefix2);
3963 ol("else{");
3964 o(tabprefix3);
3965 o(cd.getJavaTypeFromSQLType()); o(" "); o(colname);
3966 o(" = bean."); o(wrangler.getGetName(cd)); ol("();");
3967 o(tabprefix3);
3968 ol(cd.getPreparedStmtSetMethod("ps.", "pos", colname));
3969 o(tabprefix3);
3970 ol("}");
3971 o(tabprefix2);
3972 ol("}");
3973 }
3974 else{
3975 o(tabprefix2);
3976 o(cd.getJavaTypeFromSQLType()); o(" "); o(colname);
3977 o(" = bean."); o(wrangler.getGetName(cd)); ol("();");
3978 o(tabprefix2);
3979 ol(cd.getPreparedStmtSetMethod("ps.", "pos", colname));
3980 //ol("log.bug(ps);");
3981 o(tabprefix2);
3982 ol("}");
3983 }
3984 } //~for
3985 }
3986
3987
3988 void utilFillPStmtFromList_IfModified_Object (final List list, final String tabprefix)
3989 throws SQLException
3990 {
3991 final String tabprefix2 = tabprefix + "\t";
3992 final String tabprefix3 = tabprefix + "\t\t";
3993
3994 o(tabprefix);
3995 ol("int pos = 0;");
3996
3997 for (int n = 0; n < list.size(); n++)
3998 {
3999 ColumnData cd = (ColumnData) list.get(n);
4000 String colname = cd.getName();
4001
4002 o(tabprefix);
4003 o("if (bean.");
4004 o(wrangler.getIsModifiedName(cd));
4005 ol("()) {");
4006
4007 if (! cd.usesPrimitiveJavaType() || cd.isNullable())
4008 {
4009 o(tabprefix2);
4010 o ("if (bean.");
4011 if (! cd.usesPrimitiveJavaType()) {
4012 o (wrangler.getGetName(cd));
4013 ol("() == null) { ");
4014 }
4015 else{
4016 o (wrangler.getIsModifiedNullName(cd));
4017 ol("()) { ");
4018 }
4019 o (tabprefix3);
4020 ol("/* no value to set here, uses [xxx IS NULL] syntax*/");
4021 o (tabprefix3);
4022 ol("}");
4023 o(tabprefix2);
4024 ol("else{");
4025 }
4026
4027 o(tabprefix3);
4028 ol("pos++;");
4029 o(tabprefix3);
4030 o(cd.getJavaTypeFromSQLType());
4031 o(" ");
4032 o(colname);
4033 o(" = bean.");
4034 o(wrangler.getGetName(cd));
4035 ol("();");
4036 o(tabprefix3);
4037 ol(cd.getPreparedStmtSetMethod("ps.", "pos", colname));
4038 o(tabprefix3);
4039 ol("}");
4040
4041 if (! cd.usesPrimitiveJavaType() || cd.isNullable()) {
4042 o(tabprefix2);
4043 ol("}");
4044 }
4045 } //~for
4046 }
4047
4048
4049 public boolean isPostgres() {
4050 return dbname == DBName.postgres;
4051 }
4052
4053 public boolean isMysql() {
4054 return dbname == DBName.mysql;
4055 }
4056
4057
4058 /**
4059 Usage:
4060 java fc.jdbc.dbobjects.Generate -conf
4061 <path-to-configuration-file> No flags will produce a list of
4062 options and usage information.
4063 **/
4064 public static void main(String[] args) throws Exception
4065 {
4066 Generate gen = new Generate(args);
4067 }
4068
4069 }