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;
007    
008    import java.io.*;
009    import java.util.*;
010    import java.sql.*;
011    import javax.servlet.*;
012    
013    import fc.io.*;
014    import fc.web.*;
015    import fc.util.*;
016    
017    /** 
018    Various JDBC utility methods. 
019    <p>
020    @author hursh jain
021    **/
022    public final class QueryUtil
023    {
024    private static long txID = 1;
025    
026    private QueryUtil() { /*no construction*/ }
027    
028    static Log log = Log.get("fc.web.servlet.QueryUtil");
029    
030    /** 
031    This method can be called to initialize a logger for this class (else
032    a default logger will used).
033    
034    @param  logger  Sets the log to which methods in this class 
035            will send logging output.
036    **/
037    public static final void init(Log logger) {
038      log = logger;
039      }
040    
041    /**  
042    I don't think this is needed for anything.
043    
044    Often foreign keys are cascaded to null when the referenced key is deleted.
045    Many times keys are integral types; the JDBC getInt(..) method returns a
046    integer with value 0 when the actual value is null (since primitive java
047    types cannot be null). When saving a previously retrieved record with some
048    key value of 0, we would like to save that key back as null in the
049    database.
050    <p>
051    This method examines the given keyvalue and if it is 0, sets the value in
052    the specified position as null (otherwise sets it to the specified value).
053    <p>
054    This does imply a record creation convention whereby keys never have the
055    value of 0 in normal circumstances.
056    <p>
057    We could alternatively use object types such as Integer (and not primitive
058    types). This would have the advantage of natively being capable of being
059    null. However, that has the big disadvantage of making html form/GUI code
060    more complicated.
061    
062    public static final void setNullableKey(PreparedStatement pstmt, 
063                      int pos, int keyvalue)
064    throws SQLException 
065      {
066      if (keyvalue == 0) {
067        pstmt.setObject(pos, null);
068        }
069      else {
070        pstmt.setInt(pos, keyvalue);
071        }
072      }
073    */
074    
075    /** 
076    Returns a montonically increasing number starting from 1. This is true for
077    a given JVM invocation, this value will start from 1 again the next time
078    the JVM is invoked.
079    **/
080    public static final long getNewTxID() {
081      synchronized (QueryUtil.class) {
082        return txID++;
083        }
084      }
085    
086    /**
087    Returns the dbname corresponding that the database for the specified
088    jdbc connection. Useful for writing database specific code as/when applicable.
089    A similar method is also available in {@link ConnectionMgr}
090    */
091    public static DBName getDBName(final Connection con) throws SQLException
092      {
093      final DatabaseMetaData md = con.getMetaData();
094      return DBName.fromDriver(md.getDriverName());
095      }
096    
097    /** 
098    Checks to see if the specified result set is scrollable.
099    
100    @throws SQLException  if the specified result set is <b>not</b>
101                scrollable.
102    **/
103    public static final void ensureScrollable(ResultSet rs) 
104    throws SQLException
105      {
106      if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
107        throw new SQLException("Specified ResultSet not scrollable; need scrollable to proceed. [ResultSet=" + rs + "]");
108        }
109      }
110    /**
111    Checks to see if actual row count was same as expected row count, for some
112    query. Takes 2 integers as parameters and simply sees if the first equals
113    the second.
114    
115    @param  rowcount  the actual count (returned by executeUpdate() etc.)
116    @param  expected  the expected count
117    @throws SQLException if the actual and expected counts don't match
118    **/
119    public static final void ensureCount(int rowcount, int expected) 
120    throws SQLException 
121      {
122      if (rowcount != expected) {
123        throw new SQLException("row count mismatch, recieved [" + rowcount + "], expected [" + expected + "]");
124        }
125      }
126      
127    /**
128    Returns the number of rows in the specified ResultSet. If the ResultSet is
129    of type {@link ResultSet#TYPE_SCROLL_INSENSITIVE}, then this method moves
130    the result set pointer back to the beginning, after it is finished. If the
131    result set is not scroll insensitive, then this method will still work
132    properly, but the contents of the result set will not be usable again
133    (since it cannot be rewinded).
134    <p>
135    <b>Note</b>: the count can also be retreived directly (and more
136    efficiently) for many queries by including the <tt>COUNT()</tt> SQL
137    function as part of the query (in which case one would read the returned
138    count column directly and NOT call this method)
139    
140    @param    rset    the result set to examine
141    @return   the number of rows in the rowset
142    **/
143    public static final long getRowCount(ResultSet rset) 
144    throws SQLException
145      {
146      int rowcount = 0;
147        
148      if (rset != null) 
149        {
150        int type = rset.getType();
151        if (type == ResultSet.TYPE_FORWARD_ONLY) 
152          {
153          while (rset.next()) {
154            rowcount++;
155            }
156          }
157        else { //scrollable
158          rset.last(); //note, afterlast() will return 0 for getRow
159          rowcount = rset.getRow();
160          rset.beforeFirst();
161          }
162        }
163      return rowcount;
164      }
165    
166    /**
167    Gets the last inserted id, typically auto-increment(mysql) or serial
168    (postgresql) columns. Since auto increment values can be integers or longs,
169    this method always returns it's value as a long. The caller can narrow this
170    down to an int if that's what's defined in the database.
171    
172    @param  con   the connection to get the last id for. Both
173            mysql and postgresql (and probably other db's) treat the
174            last inserted id as a per connection value (associated with
175            the last statement on that connection that returned such a
176            value).
177    @param  dbname  the name of the target database. Needed because 
178            this must be implemented in a database specific 
179            way.
180    @param  info  optional further info needed to implement this 
181            method for some databases. This currently is:
182            <blockquote>
183            <ul>
184              <li>mysql: not needed, specify <tt>null</tt>
185              <li>postgresql: specify the name of the sequence, which
186              for serial columns is (by default)
187              <tt>tablename_colname_seq</tt>, for example
188              <tt>"tablefoo_myserialid_seq"</tt>
189            </blockquote>
190            
191    @throws SQLException if the last insert id could not be found
192               or if some other datbase problem occurred.
193    */
194    public static final long getLastInsertID(
195      Connection con, DBName dbname, Object info)  
196    throws SQLException
197      {
198      //at some point we can refactor this into separate classes
199      //for each db but that's overkill for us now
200      
201      if (dbname == DBName.mysql) {
202        String query = "select last_insert_id()";
203        Statement stmt = con.createStatement();
204        ResultSet rs = stmt.executeQuery(query);
205        boolean found = rs.next();
206        if (! found) throw new SQLException("No last inserted id returned");
207        return rs.getLong(1);
208        }
209      if (dbname == DBName.postgres) {
210        if (! (info instanceof String)) throw new SQLException("postgres requires the info parameter as a String");
211        String query = "select currval('" + info + "')";
212        Statement stmt = con.createStatement();
213        ResultSet rs = stmt.executeQuery(query);
214        boolean found = rs.next();
215        if (! found) throw new SQLException("No last inserted id returned");
216        return rs.getLong(1);
217        }
218      else {
219        throw new SQLException("Method not yet implemented for database of type: " + dbname);
220        }
221      }
222    
223    /** 
224    Convenience method that calls {@link #executeQuery(Connection, String, boolean)}
225    specifying no header options (i.e., column headers are not returned as part
226    of the query results).
227    
228    @param  con     the database connection to use.
229    @param  query   the query to be performed
230    @param  headers   if true, the first row contains the column headers
231    **/
232    public static final List executeQuery(
233      Connection con, String query) throws SQLException
234      {
235      return executeQuery(con, query, false);
236      }
237    
238    /**
239    Performs the specified query and returns a <tt>List</tt> (the result of
240    converting the ResultSet via the {@link #rsToList} method).
241    <p>
242    <b>Important Note</b>: this method is useful for program generated queries,
243    but should not be used for queries where unknown data could be send by a
244    malicious user (since the query string is sent as-is to the server). For
245    secure queries, use PreparedStatements instead.
246    
247    @param  con     the database connection to use.
248    @param  query   the query to be peformed
249    @param  headers   if true, the first row contains the column headers
250    **/
251    public static final List executeQuery(
252      Connection con, String query, boolean headers) 
253    throws SQLException
254      {
255      Statement stmt = con.createStatement();
256      ResultSet rs = stmt.executeQuery(query);
257      List list = rsToList(rs, headers);
258      return list;
259      }
260    
261    /**
262    Peforms the specified query and returns true if the query has
263    only one row of data.
264    
265    @param  con     the database connection to use.
266    **/
267    public static final boolean hasExactlyOneRow(
268      Connection con, String query) throws SQLException
269      {
270      Statement stmt = con.createStatement();
271      ResultSet rs = stmt.executeQuery(query);
272      boolean valid = getRowCount(rs) == 1;  
273      return valid;
274      }
275    
276    /**
277    Peforms the specified query and returns true if the query returns
278    no data.
279    
280    @param  con     the database connection to use.
281    **/
282    public static final boolean hasExactlyZeroRows(
283      Connection con, String query) throws SQLException
284      {
285      Statement stmt = con.createStatement();
286      ResultSet rs = stmt.executeQuery(query);
287      boolean valid = getRowCount(rs) == 0;  
288      return valid;
289      }
290    
291    /**  
292    Creates a new connection that will return ResultSet's of
293    <tt>TYPE_SCROLL_INSENSITIVE</tt> and <tt>CONCUR_READ_ONLY</tt>. 
294    **/
295    public static final Statement getRewindableStmt(Connection con) 
296    throws SQLException
297      {
298      Statement stmt = con.createStatement( 
299                ResultSet.TYPE_SCROLL_INSENSITIVE,
300                ResultSet.CONCUR_READ_ONLY);
301      return stmt;
302      }
303    
304    /**  
305    Creates a new connection that will return ResultSet's of
306    <tt>TYPE_SCROLL_INSENSITIVE</tt> and <tt>CONCUR_READ_ONLY</tt>. (i.e., the
307    PreparedStatement returned by this method should be rewindable).
308    <p>
309    Note, by default a java.sql.Connection.prepareStatment(String) method
310    returns statements that support ResultSet's of forward_only. This means we
311    will not be able to determine the row count via the {@link #getRowCount}
312    and <b>also</b> be able to then rewind the ResultSet and read it's
313    contents.
314    <p> 
315    **/
316    public static final PreparedStatement 
317    getRewindablePreparedStmt(Connection con, String sql) 
318    throws SQLException
319      {
320      PreparedStatement stmt = con.prepareStatement(  
321                sql,
322                ResultSet.TYPE_SCROLL_INSENSITIVE,
323                ResultSet.CONCUR_READ_ONLY);
324      return stmt;
325      }
326    
327    /**
328    Starts a transaction on the specified connection. The
329    connection is set to <i>not</i> autocommit any statements from
330    here onwards and the {@link #endTransaction endTransaction}
331    method must be called to end this transaction.
332    <p>
333    If the transaction cannot be started, the connection is closed.
334    
335    @param  con           the connection to commit
336    @param  transactionIsolation  the transaction isolation level
337                    for this transaction
338    @param  message         commit description; shown when debug 
339                    logging is turned on
340    
341    @return <tt>true</tt> if the transaction was started successfully, 
342        <tt>false</tt> if the transaction could not start for some reason.
343    
344    @throws IllegalArgumentException if the transaction isolation level is not a valid value (as defined in {@link java.sql.Connection}
345    */
346    public static final boolean startTransaction(Connection con, int transactionIsolation, String message)
347      {
348      switch (transactionIsolation) 
349        { 
350        case Connection.TRANSACTION_NONE:
351        case Connection.TRANSACTION_READ_COMMITTED:
352        case Connection.TRANSACTION_READ_UNCOMMITTED:
353        case Connection.TRANSACTION_REPEATABLE_READ:
354        case Connection.TRANSACTION_SERIALIZABLE:
355          break;
356        default:
357          throw new IllegalArgumentException("The specfied transaction isolation level " + transactionIsolation + " is not a valid value");
358        }
359      try {
360        con.setTransactionIsolation(transactionIsolation);
361        }
362      catch (Exception e) {
363        log.error("Could not set transaction isolation;", IOUtil.throwableToString(e));
364        return false;
365        }
366    
367      return startTransaction(con, message);
368      }
369    
370    /**
371    Starts a transaction on the specified connection. The connection is set to
372    not autocommit any statements from here onwards and the {@link
373    #endTransaction} method must be called to end this transaction. The
374    transaction isolation is whatever the default transaction isolation is for
375    this connection, driver or database (this method does not explicitly set
376    the isolation level). See {@link #startTransaction(Connection, int,
377    String)}.
378    <p>
379    If the transaction cannot be started, the connection is closed.
380    
381    @param  con     the connection to commit
382    @param  message   commit description; shown if debug logging 
383              is turned on
384    
385    @return <tt>true</tt> if the transaction was started successfully, 
386        <tt>false</tt> if the transaction could not start for some reason.
387    */
388    public static final boolean startTransaction(Connection con, String message)
389      {
390      boolean result = true;
391    
392      try {
393        con.setAutoCommit(false);
394        }
395      catch (Exception e) 
396        {
397        log.error(message, "Could not set autoCommit(false) on this connection; Transaction will not be started and the connection will be closed. ", con, IOUtil.throwableToString(e));
398        try {
399          con.close();
400          }
401        catch (Exception e2) {
402          log.error(message, "Connection does not allow autoCommit(false) and connetion cannot even be closed. ", con, IOUtil.throwableToString(e2));
403          }
404        result = false;
405        }
406    
407      log.bug(message, "/START: Transaction [Isolation Level=", getTransactionLevelString(con), "];", con);
408      return result;
409      }
410    
411    
412    private static final String t_str = "*** ";
413    
414    /**
415    Calls {@link startTransaction(Connection, String) startTransaction} with
416    a empty message string.
417    */
418    public static final boolean startTransaction(Connection con)
419      {
420      return startTransaction(con, t_str);
421      }
422    
423    /** 
424    Aborts (rolls back) the current transaction on the specified connection.
425    <p> 
426    After the transaction is rolled back, the connection is set to
427    autoCommit(true).
428    
429    @param  con   the connection to rollback
430    @param  message a description; shown if logging is turned on
431    
432    @return <tt>true</tt> if the transaction was rolledback successful, 
433        <tt>false</tt> if the transaction rollback for some reason.
434    **/
435    public static final boolean abortTransaction(Connection con, String message)
436      {
437      boolean result = true;
438      
439      try {
440        if (con.getAutoCommit() == true) //throws an Exception
441          {
442          log.error("Connection not in transaction, autoCommit is true. Did you call QueryUtil.startTransaction() on this connection ?", message, con);
443          return false;
444          }
445        }
446      catch (Exception e) {
447        log.error(IOUtil.throwableToString(e));
448        return false; 
449        }
450    
451      try {
452        con.rollback();
453        }
454      /*catch problems, exception, incl. all runtime related*/
455      catch (Throwable e) 
456        { 
457        result = false;
458        log.error(message, "*** Transaction could not be aborted/rolled back ***", IOUtil.throwableToString(e));
459        }
460      finally 
461        {
462        try {
463          con.setAutoCommit(true);
464          }
465        catch (Exception e) {
466          log.error(message, "Could NOT reset connection to be autoCommit=true", con, IOUtil.throwableToString(e));
467          result = false;
468          }
469        }
470      
471      if (log.canLog(Log.DEBUG)) 
472        log.bug(message, "/ABORT: Transaction rolled back [Time=", new java.util.Date(), "]; connection=", con);
473    
474      return result;
475      }   //~doCommit
476    
477    
478    /**
479    Calls {@link abortTransaction(Connection, String) abortTransaction} with
480    an empty message string.
481    */
482    public static final boolean abortTransaction(Connection con)
483      {
484      return abortTransaction(con, t_str);
485      }
486    
487    /** 
488    Commits the specified connection. Attemps a rollback if the
489    <tt>commit()</tt> call fails for some reason. This method should only be
490    called after all queries in the transaction have been sent to the
491    database (so if any of those fail, the entire transaction can be rolled
492    back).
493    <p>
494    After the transaction completes, the connection is set to
495    autoCommit(true).
496    
497    @param  con   the connection to commit
498    @param  message commit description; shown if logging is turned on
499    
500    @return <tt>true</tt> if the transaction was committed successful, 
501        <tt>false</tt> if the transaction failed for some reason.
502    **/
503    public static final boolean endTransaction(Connection con, String message)
504      {
505      boolean result = true;
506      
507      try {
508        if (con.getAutoCommit() == true) //throws an Exception
509          {
510          log.error("Connection not in transaction, autoCommit is true. Did you prior call QueryUtil.startTransaction() on this connection ?", message, con);
511          return false;
512          }
513        }
514      catch (Exception e) {
515        log.error(IOUtil.throwableToString(e));
516        return false; 
517        }
518    
519      try {
520        con.commit();
521        }
522      /*catch problems, exception, incl. all runtime related*/
523      catch (Throwable e) 
524        { 
525        result = false;
526        log.error(message, "*** Transaction could not complete: attempting roll back ***", IOUtil.throwableToString(e));
527        try {
528          con.rollback();
529          }
530        catch (SQLException e2) {
531          log.error(message, "*** Transaction could not be rolled back ***", IOUtil.throwableToString(e2));
532          }
533        //throw e;
534        }
535      finally 
536        {
537        try {
538          con.setAutoCommit(true);
539          }
540        catch (Exception e) {
541          log.error(message, "Could NOT reset connection to be autoCommit=true", con, IOUtil.throwableToString(e));
542          result = false;
543          }
544        }
545      
546      log.bug(message, "/FINISH: Transaction completed. Connection=", con);
547    
548      return result;
549      }   
550    
551    /**
552    Calls {@link endTransaction(Connection, String) endTransaction} with
553    an empty message string.
554    */
555    public static final boolean endTransaction(Connection con)
556      {
557      return endTransaction(con, t_str);
558      }
559    
560    private static String getTransactionLevelString(Connection con)
561      {
562      try {
563        final int level = con.getTransactionIsolation();
564        switch (level)
565          {
566          case Connection.TRANSACTION_NONE: 
567            return "TRANSACTION_NONE";
568          case Connection.TRANSACTION_READ_COMMITTED:
569            return "TRANSACTION_READ_COMMITTED";
570          case Connection.TRANSACTION_READ_UNCOMMITTED:
571            return "TRANSACTION_READ_UNCOMMITTED";
572          case Connection.TRANSACTION_REPEATABLE_READ:
573            return "TRANSACTION_REPEATABLE_READ";
574          case Connection.TRANSACTION_SERIALIZABLE:
575            return "TRANSACTION_SERIALIZABLE";
576          default:
577            return "Unknown level (not a legal value)";
578          }
579        }
580      catch (Exception e) {
581        log.error(IOUtil.throwableToString(e));
582        }
583      return "";
584      }
585    
586    /** 
587    Rollsback the transaction, ignoring any errors in the rollback itself.
588    **/
589    public static final void rollback(Connection con) 
590      {
591      try { 
592        if (con.isClosed()) {
593          log.bug("tried to rollback a already closed connection: ", con);
594          return;
595          }
596          
597        con.rollback(); 
598        }
599      catch (Exception e) {
600        log.error(IOUtil.throwableToString(e));
601        }
602      }
603    
604    
605    /** 
606    Closes the specified connection, statement and resultset, logging any
607    errors to the stderr. Ignores any parameters with <tt>null</tt> values.
608    **/
609    public static final void close(ResultSet rs, Statement stmt, Connection con) 
610      {
611      //objects _must_ be closed in the following order
612      if (rs != null) 
613      try { 
614        rs.close(); 
615        }
616      catch (Exception e) {
617        log.warn("", e);
618        }
619          
620      if (stmt != null) try {
621        stmt.close();
622        }
623      catch (Exception e) {
624        log.warn("", e);
625        }
626      
627      if (con != null) try {
628        con.close();
629        }
630      catch (Exception e) {
631        log.warn("", e);
632        }
633      }
634    
635    
636    /** 
637    Closes the specified connection, ignoring any exceptions encountered
638    in the connection.close() method itself.
639    **/
640    public static final void close(Connection con) 
641      {
642      close(null, null, con);
643      }
644    
645    
646    /**
647    Converts a java.sql.ResultSet into a List of Object[], where each Object[]
648    represents all the columns in one row. All column values are stored in the
649    Object[] via the getObject() method of the ResultSet. If the ResultSet is
650    empty, this method returns <tt>null</tt> (or returns only the headers if
651    headers are to be printed).
652    
653    @param  rs      the ResultSet
654    @param  headers   if set to true, the first row of the returned List 
655              will contain an Object[] of the column header names. 
656    */
657    public static final List rsToList(
658     java.sql.ResultSet rs, boolean headers) throws SQLException
659      {
660      List list = new ArrayList();
661      ResultSetMetaData metadata = rs.getMetaData();
662      int numcols = metadata.getColumnCount();
663      Object[] arow = null;
664      int i;
665    
666      if (headers) 
667        {
668        // get column header info
669        arow = new Object[numcols];
670        for ( i = 1; i <= numcols; i++)  {
671          arow[i-1] = metadata.getColumnLabel(i);
672          }
673        list.add(arow);
674        }
675        
676      while (rs.next()) {
677        arow = new Object[numcols];
678        for (i=1; i <= numcols; i++) {
679          arow[i-1] = rs.getObject(i); 
680          } 
681        list.add(arow);
682        } 
683        
684      return list;
685      }     //~rsToList()
686    
687    /** 
688    Converts the list returned by the {@link #rsToList method} to a String,
689    consisting of all the rows contained in the list. Each row is rendered
690    within brackets <tt>[..row1..]</tt>, with different rows seperated by
691    commas (<tt>[..row1..], [..row2..], ...</tt>) but this format may be
692    changed in the future.
693    **/
694    public static final String rsListToString(List list)
695      {
696      String result = "";
697      if (list == null) {
698        return result;
699        }
700      int size = list.size();
701      result += "Total records: " + size + IOUtil.LINE_SEP;
702      for (int n = 0; n < size; n++) 
703        {
704        Object[] row = (Object[]) list.get(n);
705        int rowlen = row.length;
706        result += "[";
707        //1 row
708        for (int k= 0; k < rowlen; k++) {      
709          result += row[k];
710          if ((k + 1) != rowlen) {
711            result += ", ";
712            }
713          }
714        //end 1 row   
715        result += "]";
716        if ((n + 1) != size) {
717          result += ", ";
718          }
719        } 
720      return result;  
721      }  //~rsListToString()
722    
723    
724    /**
725    Prints the given java.sql.ResultSet (including result set headers) in a
726    simple straightforward fashion to <tt>System.out</tt>
727    
728    @param  rs    the result set to print
729    */
730    public static final void printRS(ResultSet rs) 
731    throws SQLException
732      {
733      ResultSetMetaData metadata = rs.getMetaData();
734      int numcols = metadata.getColumnCount();
735    
736      System.out.print("[Headers] ");
737      for (int i = 1; i <= numcols; i++)  {
738        System.out.print(metadata.getColumnLabel(i));
739        if (i != numcols) System.out.print(", ");
740        }
741      System.out.println("");
742      int rowcount = 0;
743      while (rs.next()) 
744        {
745        System.out.print("[Row #" + ++rowcount + " ] ");
746        for(int n=1 ; n <= numcols; n++) {
747          Object obj = rs.getObject(n);
748          String str = (obj != null) ? obj.toString() : "null";
749          if (str.length() > 0)
750            System.out.print(str);
751          else
752            System.out.print("\"\"");
753          if (n != numcols) System.out.print(", ");
754          }
755        System.out.println("");
756        }
757      }
758    
759    
760    /** 
761    Delegates to {@link printResultSetTable(ResultSet, PrintStream,
762    ResultSetPrintDirection, TablePrinter.PrintConfig, boolean)} so that the
763    table is printed {@link ResultSetPrintDirection.HORIZONTAL horizontally}
764    with the default table style and headers set to true.
765    **/
766    public static final void printResultSetTable(ResultSet rs, PrintStream ps) 
767    throws SQLException
768      {
769      printResultSetTable(rs, ps, ResultSetPrintDirection.HORIZONTAL, null, true);
770      }
771    
772    /** 
773    Prints the specified result set in a tabular format. The printed table
774    style is according specified {@link TablePrinter.PrintConfig PrintConfig}
775    object.
776    
777    @param  rs      the ResultSet
778    @param  ps      the destination print stream
779    @param  direction the result set printing orientation.  
780    @param  config    the printing configuration. 
781              <b>Specify <tt>null</tt>for to use the 
782              default style</b>.
783    @param  headers   <tt>true</tt> to print headers, <tt>false</tt> to omit 
784              headers. Headers are obtained from the
785              ResultSet's Meta Data.
786    **/
787    public static final void printResultSetTable(
788      ResultSet rs, PrintStream ps, ResultSetPrintDirection direction, 
789      TablePrinter.PrintConfig config, boolean headers) 
790    throws SQLException
791      {
792      ResultSetMetaData metadata = rs.getMetaData();
793      int numcols = metadata.getColumnCount();
794      TablePrinter fprint = null;
795      if (config == null) {
796        config = new TablePrinter.PrintConfig();
797        config.setCellPadding(1);
798        }
799            
800      if (direction == ResultSetPrintDirection.HORIZONTAL) 
801        {     
802        fprint = new TablePrinter(numcols, ps, config);
803        fprint.startTable();
804        
805        //print headers
806        if (headers)  
807          {
808          fprint.startRow();
809          for (int i = 1; i <= numcols; i++)  {
810            fprint.printCell(metadata.getColumnLabel(i));
811            }
812          fprint.endRow();
813          }
814    
815        while (rs.next()) {
816          fprint.startRow();
817          for (int i = 1; i <= numcols; i++) {
818            fprint.printCell(rs.getString(i));
819            }
820          fprint.endRow();
821          }
822        fprint.endTable();
823        }
824    
825      else if (direction == ResultSetPrintDirection.VERTICAL)
826        {
827        if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
828          ps.println("QueryUtil.printResultSet(): Vertical print orientation requires a scrollable resultset"); 
829          return;
830          }
831        
832        rs.last();
833        int rows = rs.getRow(); //number of row in rs == our columns
834        int printcols = (headers) ? rows + 1 : rows;
835      
836        //we don't support this cause we don't know how many times
837        //the header will be repeated etc., and we need to know 
838        //the exact column size (which equals number of rows in Vertical)
839        //before printing (and number of rows includes and extra headers
840        //possibly repeated
841        if (config.getHeader() != null) {
842          ps.println("QueryUtil.printResultSet(): Seperate headers not supported when using Vertical orientation. To print Result Set headers, specify 'true' for the 'header' parameter when invoking this method");
843          return;
844          }
845      
846        fprint = new TablePrinter(printcols, ps, config);
847        rs.beforeFirst();
848        
849        fprint.startTable();      
850        for (int n = 1; n <= numcols; n++) 
851          {
852          fprint.startRow();      
853    
854          if (headers) {
855            fprint.printCell(metadata.getColumnLabel(n));
856            }
857    
858          while (rs.next()) {
859            fprint.printCell(rs.getString(n));    
860            }
861          
862          fprint.endRow();
863          rs.beforeFirst();
864          }
865        fprint.endTable();  
866        }
867    
868      else    
869        ps.println("QueryUtil.printResultSet(): PrintConfig not understood"); 
870    
871      }
872    
873    
874    //we have to implement this method identically twice --once
875    //for printstream, once for jspwriter
876    //because darn it, jspwriter is NOT a subclass of printwriter
877    //or printstream, freaking idiots designed jsp's 
878    
879    /**
880    Prints the given ResultSet as a HTML table to the specified JspWriter. The
881    ResultSet is transversed/printed based on the direction parameter (normal
882    output where each row is printed horizontally is specified via {@link
883    QueryUtil.ResultSetPrintDirection#HORIZONTAL}).
884    <p>
885    The output table has the following CSS styles added to it:
886    <ul>
887    <li>For the table: class <tt>QueryUtil_Table</tt>
888    <li>For the Header row: class <tt>QueryUtil_HeaderRow</tt>
889    <li>For the Header cells: class <tt>QueryUtil_HeaderCell</tt>
890    <li>For a normal row: class <tt>QueryUtil_Row</tt>
891    <li>For a normal cell: class <tt>QueryUtil_Cell</tt>
892    </ul>
893    **/
894    public static final void printResultSetHTMLTable(
895      ResultSet rs, javax.servlet.jsp.JspWriter out, 
896      ResultSetPrintDirection direction) throws IOException, SQLException
897      {
898      boolean headers = true;
899      ResultSetMetaData metadata = rs.getMetaData();
900      int numcols = metadata.getColumnCount();
901      
902      out.println("<table class=\"QueryUtil_Table\">");
903      if (direction == ResultSetPrintDirection.HORIZONTAL) 
904        {     
905        //print headers
906        if (headers)  
907          {
908          out.print("<tr class=\"QueryUtil_HeaderRow\">");
909          for (int i = 1; i <= numcols; i++)  {
910            out.print("<td class=\"QueryUtil_HeaderCell\">");
911            out.print(metadata.getColumnLabel(i));
912            out.print("</td>");
913            }
914          out.println("</tr>");
915          }
916    
917        while (rs.next()) {
918          out.println("<tr class=\"QueryUtil_Row\">");
919          for (int i = 1; i <= numcols; i++) {
920            out.print("<td class=\"QueryUtil_Cell\">");
921            out.print(rs.getString(i));
922            out.print("</td>");
923            }
924          out.println("</tr>");
925          }
926        out.println("</table>");
927        }
928    
929      else if (direction == ResultSetPrintDirection.VERTICAL)
930        {
931        if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
932          out.println("QueryUtil.printResultSet(): Vertical print orientation requires a scrollable resultset");  
933          return;
934          }
935        
936        rs.last();
937        int rows = rs.getRow(); //number of row in rs == our columns
938        int printcols = (headers) ? rows + 1 : rows;
939      
940        rs.beforeFirst();
941        
942        out.println("<table class=\"QueryUtil_Table\">");
943        for (int n = 1; n <= numcols; n++) 
944          {
945          out.println("<tr>");
946    
947          if (headers) {
948            out.print("<td class=\"QueryUtil_HeaderCell\">");
949            out.println(metadata.getColumnLabel(n));
950            out.println("</td>");
951            }
952    
953          while (rs.next()) {
954            out.println("<td class=\"QueryUtil_Cell\">");
955            out.println(rs.getString(n));   
956            out.println("</td>");
957            }
958          
959          out.println("</tr>");
960          rs.beforeFirst();
961          }
962        out.println("</table>");
963        }
964    
965      }
966    
967    
968    /**
969    Prints the given ResultSet as a HTML table to the specified PrintWriter.
970    The ResultSet is transversed/printed based on the direction parameter
971    (normal output where each row is printed horizontally is specified via
972    {@link QueryUtil.ResultSetPrintDirection#HORIZONTAL}).
973    <p>
974    The output table has the following CSS styles added to it:
975    <ul>
976    <li>For the table: class <tt>QueryUtil_Table</tt>
977    <li>For the Header row: class <tt>QueryUtil_HeaderRow</tt>
978    <li>For the Header cells: class <tt>QueryUtil_HeaderCell</tt>
979    <li>For a normal row: class <tt>QueryUtil_Row</tt>
980    <li>For a normal cell: class <tt>QueryUtil_Cell</tt>
981    </ul>
982    **/
983    public static final void printResultSetHTMLTable(
984      ResultSet rs, PrintWriter out, 
985      ResultSetPrintDirection direction) throws IOException, SQLException
986      {
987      boolean headers = true;
988      ResultSetMetaData metadata = rs.getMetaData();
989      int numcols = metadata.getColumnCount();
990      
991      out.println("<table class=\"QueryUtil_Table\">");
992      if (direction == ResultSetPrintDirection.HORIZONTAL) 
993        {     
994        //print headers
995        if (headers)  
996          {
997          out.print("<tr class=\"QueryUtil_HeaderRow\">");
998          for (int i = 1; i <= numcols; i++)  {
999            out.print("<td class=\"QueryUtil_HeaderCell\">");
1000            out.print(metadata.getColumnLabel(i));
1001            out.print("</td>");
1002            }
1003          out.println("</tr>");
1004          }
1005    
1006        while (rs.next()) {
1007          out.println("<tr class=\"QueryUtil_Row\">");
1008          for (int i = 1; i <= numcols; i++) {
1009            out.print("<td class=\"QueryUtil_Cell\">");
1010            out.print(rs.getString(i));
1011            out.print("</td>");
1012            }
1013          out.println("</tr>");
1014          }
1015        out.println("</table>");
1016        }
1017    
1018      else if (direction == ResultSetPrintDirection.VERTICAL)
1019        {
1020        if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
1021          out.println("QueryUtil.printResultSet(): Vertical print orientation requires a scrollable resultset");  
1022          return;
1023          }
1024        
1025        rs.last();
1026        int rows = rs.getRow(); //number of row in rs == our columns
1027        int printcols = (headers) ? rows + 1 : rows;
1028      
1029        rs.beforeFirst();
1030        
1031        out.println("<table class=\"QueryUtil_Table\">");
1032        for (int n = 1; n <= numcols; n++) 
1033          {
1034          out.println("<tr>");
1035    
1036          if (headers) {
1037            out.print("<td class=\"QueryUtil_HeaderCell\">");
1038            out.println(metadata.getColumnLabel(n));
1039            out.println("</td>");
1040            }
1041    
1042          while (rs.next()) {
1043            out.println("<td class=\"QueryUtil_Cell\">");
1044            out.println(rs.getString(n));   
1045            out.println("</td>");
1046            }
1047          
1048          out.println("</tr>");
1049          rs.beforeFirst();
1050          }
1051        out.println("</table>");
1052        }
1053      }
1054    
1055    
1056    /** 
1057    Specifies the orientation of the result set when printed (via
1058    methods like {@link  printResultSet()}).
1059    **/
1060    public static final class ResultSetPrintDirection
1061      {
1062      /** 
1063      The result set will be printed in the typical table format
1064      with the columns going across the page and the rows going
1065      downwards. 
1066      **/
1067      public static final ResultSetPrintDirection HORIZONTAL = 
1068        new ResultSetPrintDirection("QueryUtil.ResultSetPrintDirection.HORIZONTAL");
1069        
1070      /** 
1071      The result set will be printed with columns going downwards
1072      and rows going across the page.
1073      **/ 
1074      public static final ResultSetPrintDirection VERTICAL =  
1075        new ResultSetPrintDirection("QueryUtil.ResultSetPrintDirection.VERTICAL");
1076    
1077      private String name;
1078      private ResultSetPrintDirection(String name) {
1079        this.name = name;
1080        }
1081      public String toString() { 
1082        return name;
1083        }
1084      }     //~ResultSetPrintDirection  
1085    
1086    /* this is not really needed for anything so taken out
1087    
1088    //Used to specify the printing options (used by methods that print
1089    //result sets for example). Printed fields and records are simply seperated 
1090    //by field and record seperators respectively.
1091    
1092    public static class SimplePrintConfig
1093      {
1094      The newline for this JVM's platform. Used to separate horizontal
1095      records by default.
1096      public static final String NEWLINE = IOUtil.LINE_SEP;
1097      
1098      Used to separate vertical records by default.
1099      public static final String COMMA = ",";
1100    
1101      private String  fieldsep = COMMA;
1102      private String  recsep = NEWLINE;
1103      private String  name;
1104      
1105      public SimplePrintConfig() {
1106        }
1107        
1108      Sets the string used to separate records. The default value is:
1109      {@link #NEWLINE}.
1110      public void setRecordSeperator(String str) {
1111        recsep = str;
1112        }
1113    
1114      Sets the string used to separate fields. The default value is:
1115      {@link #COMMA}.
1116      public void setFieldSeperator(String str) {
1117        fieldsep = str;
1118        }
1119    
1120      public String getRecordSeperator() {
1121        return recsep;
1122        }
1123    
1124      public String getFieldSeperator() {
1125        return fieldsep;
1126        }
1127    
1128      Prints a short description of this object. Field and record 
1129      seperators containing control characters are printed as
1130      readable equivalents. 
1131      public String toString() { 
1132        return "SimplePrintConfig: Field seperator=" + 
1133          StringUtil.viewableAscii(fieldsep) + 
1134          "; Record seperator=" + 
1135          StringUtil.viewableAscii(recsep);
1136        }
1137        
1138      } //~SimplePrintConfig
1139    
1140    */
1141    
1142    } //~class QueryUtil