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