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