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