// Copyright (c) 2001 Hursh Jain (http://www.mollypages.org) 
// The Molly framework is freely distributable under the terms of an
// MIT-style license. For details, see the molly pages web site at:
// http://www.mollypages.org/. Use, modify, have fun !

package fc.jdbc;

import java.io.*;
import java.util.*;
import java.sql.*;
import javax.servlet.*;

import fc.io.*;
import fc.web.*;
import fc.util.*;

/** 
Various JDBC utility methods. 
<p>
@author hursh jain
**/
public final class QueryUtil
{
private static long txID = 1;

private QueryUtil() { /*no construction*/ }

static Log log = Log.get("fc.web.servlet.QueryUtil");

/** 
This method can be called to initialize a logger for this class (else
a default logger will used).

@param  logger  Sets the log to which methods in this class 
        will send logging output.
**/
public static final void init(Log logger) {
  log = logger;
  }

/**  
I don't think this is needed for anything.

Often foreign keys are cascaded to null when the referenced key is deleted.
Many times keys are integral types; the JDBC getInt(..) method returns a
integer with value 0 when the actual value is null (since primitive java
types cannot be null). When saving a previously retrieved record with some
key value of 0, we would like to save that key back as null in the
database.
<p>
This method examines the given keyvalue and if it is 0, sets the value in
the specified position as null (otherwise sets it to the specified value).
<p>
This does imply a record creation convention whereby keys never have the
value of 0 in normal circumstances.
<p>
We could alternatively use object types such as Integer (and not primitive
types). This would have the advantage of natively being capable of being
null. However, that has the big disadvantage of making html form/GUI code
more complicated.

public static final void setNullableKey(PreparedStatement pstmt, 
                  int pos, int keyvalue)
throws SQLException 
  {
  if (keyvalue == 0) {
    pstmt.setObject(pos, null);
    }
  else {
    pstmt.setInt(pos, keyvalue);
    }
  }
*/

/** 
Returns a montonically increasing number starting from 1. This is true for
a given JVM invocation, this value will start from 1 again the next time
the JVM is invoked.
**/
public static final long getNewTxID() {
  synchronized (QueryUtil.class) {
    return txID++;
    }
  }

/**
Returns the dbname corresponding that the database for the specified
jdbc connection. Useful for writing database specific code as/when applicable.
A similar method is also available in {@link ConnectionMgr}
*/
public static DBName getDBName(final Connection con) throws SQLException
  {
  final DatabaseMetaData md = con.getMetaData();
  return DBName.fromDriver(md.getDriverName());
  }

/** 
Checks to see if the specified result set is scrollable.

@throws SQLException  if the specified result set is <b>not</b>
            scrollable.
**/
public static final void ensureScrollable(ResultSet rs) 
throws SQLException
  {
  if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
    throw new SQLException("Specified ResultSet not scrollable; need scrollable to proceed. [ResultSet=" + rs + "]");
    }
  }
/**
Checks to see if actual row count was same as expected row count, for some
query. Takes 2 integers as parameters and simply sees if the first equals
the second.

@param  rowcount  the actual count (returned by executeUpdate() etc.)
@param  expected  the expected count
@throws SQLException if the actual and expected counts don't match
**/
public static final void ensureCount(int rowcount, int expected) 
throws SQLException 
  {
  if (rowcount != expected) {
    throw new SQLException("row count mismatch, recieved [" + rowcount + "], expected [" + expected + "]");
    }
  }
  
/**
Returns the number of rows in the specified ResultSet. If the ResultSet is
of type {@link ResultSet#TYPE_SCROLL_INSENSITIVE}, then this method moves
the result set pointer back to the beginning, after it is finished. If the
result set is not scroll insensitive, then this method will still work
properly, but the contents of the result set will not be usable again
(since it cannot be rewinded).
<p>
<b>Note</b>: the count can also be retreived directly (and more
efficiently) for many queries by including the <tt>COUNT()</tt> SQL
function as part of the query (in which case one would read the returned
count column directly and NOT call this method)

@param    rset    the result set to examine
@return   the number of rows in the rowset
**/
public static final long getRowCount(ResultSet rset) 
throws SQLException
  {
  int rowcount = 0;
    
  if (rset != null) 
    {
    int type = rset.getType();
    if (type == ResultSet.TYPE_FORWARD_ONLY) 
      {
      while (rset.next()) {
        rowcount++;
        }
      }
    else { //scrollable
      rset.last(); //note, afterlast() will return 0 for getRow
      rowcount = rset.getRow();
      rset.beforeFirst();
      }
    }
  return rowcount;
  }

/**
Gets the last inserted id, typically auto-increment(mysql) or serial
(postgresql) columns. Since auto increment values can be integers or longs,
this method always returns it's value as a long. The caller can narrow this
down to an int if that's what's defined in the database.

@param  con   the connection to get the last id for. Both
        mysql and postgresql (and probably other db's) treat the
        last inserted id as a per connection value (associated with
        the last statement on that connection that returned such a
        value).
@param  dbname  the name of the target database. Needed because 
        this must be implemented in a database specific 
        way.
@param  info  optional further info needed to implement this 
        method for some databases. This currently is:
        <blockquote>
        <ul>
          <li>mysql: not needed, specify <tt>null</tt>
          <li>postgresql: specify the name of the sequence, which
          for serial columns is (by default)
          <tt>tablename_colname_seq</tt>, for example
          <tt>"tablefoo_myserialid_seq"</tt>
        </blockquote>
        
@throws SQLException if the last insert id could not be found
           or if some other datbase problem occurred.
*/
public static final long getLastInsertID(
  Connection con, DBName dbname, Object info)  
throws SQLException
  {
  //at some point we can refactor this into separate classes
  //for each db but that's overkill for us now
  
  if (dbname == DBName.mysql) {
    String query = "select last_insert_id()";
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(query);
    boolean found = rs.next();
    if (! found) throw new SQLException("No last inserted id returned");
    return rs.getLong(1);
    }
  if (dbname == DBName.postgres) {
    if (! (info instanceof String)) throw new SQLException("postgres requires the info parameter as a String");
    String query = "select currval('" + info + "')";
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(query);
    boolean found = rs.next();
    if (! found) throw new SQLException("No last inserted id returned");
    return rs.getLong(1);
    }
  else {
    throw new SQLException("Method not yet implemented for database of type: " + dbname);
    }
  }

/** 
Convenience method that calls {@link #executeQuery(Connection, String, boolean)}
specifying no header options (i.e., column headers are not returned as part
of the query results).

@param  con     the database connection to use.
@param  query   the query to be performed
@param  headers   if true, the first row contains the column headers
**/
public static final List executeQuery(
  Connection con, String query) throws SQLException
  {
  return executeQuery(con, query, false);
  }

/**
Performs the specified query and returns a <tt>List</tt> (the result of
converting the ResultSet via the {@link #rsToList} method).
<p>
<b>Important Note</b>: this method is useful for program generated queries,
but should not be used for queries where unknown data could be send by a
malicious user (since the query string is sent as-is to the server). For
secure queries, use PreparedStatements instead.

@param  con     the database connection to use.
@param  query   the query to be peformed
@param  headers   if true, the first row contains the column headers
**/
public static final List executeQuery(
  Connection con, String query, boolean headers) 
throws SQLException
  {
  Statement stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery(query);
  List list = rsToList(rs, headers);
  return list;
  }

/**
Peforms the specified query and returns true if the query has
only one row of data.

@param  con     the database connection to use.
**/
public static final boolean hasExactlyOneRow(
  Connection con, String query) throws SQLException
  {
  Statement stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery(query);
  boolean valid = getRowCount(rs) == 1;  
  return valid;
  }

/**
Peforms the specified query and returns true if the query returns
no data.

@param  con     the database connection to use.
**/
public static final boolean hasExactlyZeroRows(
  Connection con, String query) throws SQLException
  {
  Statement stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery(query);
  boolean valid = getRowCount(rs) == 0;  
  return valid;
  }

/**  
Creates a new connection that will return ResultSet's of
<tt>TYPE_SCROLL_INSENSITIVE</tt> and <tt>CONCUR_READ_ONLY</tt>. 
**/
public static final Statement getRewindableStmt(Connection con) 
throws SQLException
  {
  Statement stmt = con.createStatement( 
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
  return stmt;
  }

/**  
Creates a new connection that will return ResultSet's of
<tt>TYPE_SCROLL_INSENSITIVE</tt> and <tt>CONCUR_READ_ONLY</tt>. (i.e., the
PreparedStatement returned by this method should be rewindable).
<p>
Note, by default a java.sql.Connection.prepareStatment(String) method
returns statements that support ResultSet's of forward_only. This means we
will not be able to determine the row count via the {@link #getRowCount}
and <b>also</b> be able to then rewind the ResultSet and read it's
contents.
<p> 
**/
public static final PreparedStatement 
getRewindablePreparedStmt(Connection con, String sql) 
throws SQLException
  {
  PreparedStatement stmt = con.prepareStatement(  
            sql,
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
  return stmt;
  }

/**
Starts a transaction on the specified connection. The
connection is set to <i>not</i> autocommit any statements from
here onwards and the {@link #endTransaction endTransaction}
method must be called to end this transaction.
<p>
If the transaction cannot be started, the connection is closed.

@param  con           the connection to commit
@param  transactionIsolation  the transaction isolation level
                for this transaction
@param  message         commit description; shown when debug 
                logging is turned on

@return <tt>true</tt> if the transaction was started successfully, 
    <tt>false</tt> if the transaction could not start for some reason.

@throws IllegalArgumentException if the transaction isolation level is not a valid value (as defined in {@link java.sql.Connection}
*/
public static final boolean startTransaction(Connection con, int transactionIsolation, String message)
  {
  switch (transactionIsolation) 
    { 
    case Connection.TRANSACTION_NONE:
    case Connection.TRANSACTION_READ_COMMITTED:
    case Connection.TRANSACTION_READ_UNCOMMITTED:
    case Connection.TRANSACTION_REPEATABLE_READ:
    case Connection.TRANSACTION_SERIALIZABLE:
      break;
    default:
      throw new IllegalArgumentException("The specfied transaction isolation level " + transactionIsolation + " is not a valid value");
    }
  try {
    con.setTransactionIsolation(transactionIsolation);
    }
  catch (Exception e) {
    log.error("Could not set transaction isolation;", IOUtil.throwableToString(e));
    return false;
    }

  return startTransaction(con, message);
  }

/**
Starts a transaction on the specified connection. The connection is set to
not autocommit any statements from here onwards and the {@link
#endTransaction} method must be called to end this transaction. The
transaction isolation is whatever the default transaction isolation is for
this connection, driver or database (this method does not explicitly set
the isolation level). See {@link #startTransaction(Connection, int,
String)}.
<p>
If the transaction cannot be started, the connection is closed.

@param  con     the connection to commit
@param  message   commit description; shown if debug logging 
          is turned on

@return <tt>true</tt> if the transaction was started successfully, 
    <tt>false</tt> if the transaction could not start for some reason.
*/
public static final boolean startTransaction(Connection con, String message)
  {
  boolean result = true;

  try {
    con.setAutoCommit(false);
    }
  catch (Exception e) 
    {
    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));
    try {
      con.close();
      }
    catch (Exception e2) {
      log.error(message, "Connection does not allow autoCommit(false) and connetion cannot even be closed. ", con, IOUtil.throwableToString(e2));
      }
    result = false;
    }

  log.bug(message, "/START: Transaction [Isolation Level=", getTransactionLevelString(con), "];", con);
  return result;
  }


private static final String t_str = "*** ";

/**
Calls {@link startTransaction(Connection, String) startTransaction} with
a empty message string.
*/
public static final boolean startTransaction(Connection con)
  {
  return startTransaction(con, t_str);
  }

/** 
Aborts (rolls back) the current transaction on the specified connection.
<p> 
After the transaction is rolled back, the connection is set to
autoCommit(true).

@param  con   the connection to rollback
@param  message a description; shown if logging is turned on

@return <tt>true</tt> if the transaction was rolledback successful, 
    <tt>false</tt> if the transaction rollback for some reason.
**/
public static final boolean abortTransaction(Connection con, String message)
  {
  boolean result = true;
  
  try {
    if (con.getAutoCommit() == true) //throws an Exception
      {
      log.error("Connection not in transaction, autoCommit is true. Did you call QueryUtil.startTransaction() on this connection ?", message, con);
      return false;
      }
    }
  catch (Exception e) {
    log.error(IOUtil.throwableToString(e));
    return false; 
    }

  try {
    con.rollback();
    }
  /*catch problems, exception, incl. all runtime related*/
  catch (Throwable e) 
    { 
    result = false;
    log.error(message, "*** Transaction could not be aborted/rolled back ***", IOUtil.throwableToString(e));
    }
  finally 
    {
    try {
      con.setAutoCommit(true);
      }
    catch (Exception e) {
      log.error(message, "Could NOT reset connection to be autoCommit=true", con, IOUtil.throwableToString(e));
      result = false;
      }
    }
  
  if (log.canLog(Log.DEBUG)) 
    log.bug(message, "/ABORT: Transaction rolled back [Time=", new java.util.Date(), "]; connection=", con);

  return result;
  }   //~doCommit


/**
Calls {@link abortTransaction(Connection, String) abortTransaction} with
an empty message string.
*/
public static final boolean abortTransaction(Connection con)
  {
  return abortTransaction(con, t_str);
  }

/** 
Commits the specified connection. Attemps a rollback if the
<tt>commit()</tt> call fails for some reason. This method should only be
called after all queries in the transaction have been sent to the
database (so if any of those fail, the entire transaction can be rolled
back).
<p>
After the transaction completes, the connection is set to
autoCommit(true).

@param  con   the connection to commit
@param  message commit description; shown if logging is turned on

@return <tt>true</tt> if the transaction was committed successful, 
    <tt>false</tt> if the transaction failed for some reason.
**/
public static final boolean endTransaction(Connection con, String message)
  {
  boolean result = true;
  
  try {
    if (con.getAutoCommit() == true) //throws an Exception
      {
      log.error("Connection not in transaction, autoCommit is true. Did you prior call QueryUtil.startTransaction() on this connection ?", message, con);
      return false;
      }
    }
  catch (Exception e) {
    log.error(IOUtil.throwableToString(e));
    return false; 
    }

  try {
    con.commit();
    }
  /*catch problems, exception, incl. all runtime related*/
  catch (Throwable e) 
    { 
    result = false;
    log.error(message, "*** Transaction could not complete: attempting roll back ***", IOUtil.throwableToString(e));
    try {
      con.rollback();
      }
    catch (SQLException e2) {
      log.error(message, "*** Transaction could not be rolled back ***", IOUtil.throwableToString(e2));
      }
    //throw e;
    }
  finally 
    {
    try {
      con.setAutoCommit(true);
      }
    catch (Exception e) {
      log.error(message, "Could NOT reset connection to be autoCommit=true", con, IOUtil.throwableToString(e));
      result = false;
      }
    }
  
  log.bug(message, "/FINISH: Transaction completed. Connection=", con);

  return result;
  }   

/**
Calls {@link endTransaction(Connection, String) endTransaction} with
an empty message string.
*/
public static final boolean endTransaction(Connection con)
  {
  return endTransaction(con, t_str);
  }

private static String getTransactionLevelString(Connection con)
  {
  try {
    final int level = con.getTransactionIsolation();
    switch (level)
      {
      case Connection.TRANSACTION_NONE: 
        return "TRANSACTION_NONE";
      case Connection.TRANSACTION_READ_COMMITTED:
        return "TRANSACTION_READ_COMMITTED";
      case Connection.TRANSACTION_READ_UNCOMMITTED:
        return "TRANSACTION_READ_UNCOMMITTED";
      case Connection.TRANSACTION_REPEATABLE_READ:
        return "TRANSACTION_REPEATABLE_READ";
      case Connection.TRANSACTION_SERIALIZABLE:
        return "TRANSACTION_SERIALIZABLE";
      default:
        return "Unknown level (not a legal value)";
      }
    }
  catch (Exception e) {
    log.error(IOUtil.throwableToString(e));
    }
  return "";
  }

/** 
Rollsback the transaction, ignoring any errors in the rollback itself.
**/
public static final void rollback(Connection con) 
  {
  try { 
    if (con.isClosed()) {
      log.bug("tried to rollback a already closed connection: ", con);
      return;
      }
      
    con.rollback(); 
    }
  catch (Exception e) {
    log.error(IOUtil.throwableToString(e));
    }
  }


/** 
Closes the specified connection, statement and resultset, logging any
errors to the stderr. Ignores any parameters with <tt>null</tt> values.
**/
public static final void close(ResultSet rs, Statement stmt, Connection con) 
  {
  //objects _must_ be closed in the following order
  if (rs != null) 
  try { 
    rs.close(); 
    }
  catch (Exception e) {
    log.warn("", e);
    }
      
  if (stmt != null) try {
    stmt.close();
    }
  catch (Exception e) {
    log.warn("", e);
    }
  
  if (con != null) try {
    con.close();
    }
  catch (Exception e) {
    log.warn("", e);
    }
  }


/** 
Closes the specified connection, ignoring any exceptions encountered
in the connection.close() method itself.
**/
public static final void close(Connection con) 
  {
  close(null, null, con);
  }


/**
Converts a java.sql.ResultSet into a List of Object[], where each Object[]
represents all the columns in one row. All column values are stored in the
Object[] via the getObject() method of the ResultSet. If the ResultSet is
empty, this method returns <tt>null</tt> (or returns only the headers if
headers are to be printed).

@param  rs      the ResultSet
@param  headers   if set to true, the first row of the returned List 
          will contain an Object[] of the column header names. 
*/
public static final List rsToList(
 java.sql.ResultSet rs, boolean headers) throws SQLException
  {
  List list = new ArrayList();
  ResultSetMetaData metadata = rs.getMetaData();
  int numcols = metadata.getColumnCount();
  Object[] arow = null;
  int i;

  if (headers) 
    {
    // get column header info
    arow = new Object[numcols];
    for ( i = 1; i <= numcols; i++)  {
      arow[i-1] = metadata.getColumnLabel(i);
      }
    list.add(arow);
    }
    
  while (rs.next()) {
    arow = new Object[numcols];
    for (i=1; i <= numcols; i++) {
      arow[i-1] = rs.getObject(i); 
      } 
    list.add(arow);
    } 
    
  return list;
  }     //~rsToList()

/** 
Converts the list returned by the {@link #rsToList method} to a String,
consisting of all the rows contained in the list. Each row is rendered
within brackets <tt>[..row1..]</tt>, with different rows seperated by
commas (<tt>[..row1..], [..row2..], ...</tt>) but this format may be
changed in the future.
**/
public static final String rsListToString(List list)
  {
  String result = "";
  if (list == null) {
    return result;
    }
  int size = list.size();
  result += "Total records: " + size + IOUtil.LINE_SEP;
  for (int n = 0; n < size; n++) 
    {
    Object[] row = (Object[]) list.get(n);
    int rowlen = row.length;
    result += "[";
    //1 row
    for (int k= 0; k < rowlen; k++) {     
      result += row[k];
      if ((k + 1) != rowlen) {
        result += ", ";
        }
      }
    //end 1 row   
    result += "]";
    if ((n + 1) != size) {
      result += ", ";
      }
    } 
  return result;  
  }  //~rsListToString()


/**
Prints the given java.sql.ResultSet (including result set headers) in a
simple straightforward fashion to <tt>System.out</tt>

@param  rs    the result set to print
*/
public static final void printRS(ResultSet rs) 
throws SQLException
  {
  ResultSetMetaData metadata = rs.getMetaData();
  int numcols = metadata.getColumnCount();

  System.out.print("[Headers] ");
  for (int i = 1; i <= numcols; i++)  {
    System.out.print(metadata.getColumnLabel(i));
    if (i != numcols) System.out.print(", ");
    }
  System.out.println("");
  int rowcount = 0;
  while (rs.next()) 
    {
    System.out.print("[Row #" + ++rowcount + " ] ");
    for(int n=1 ; n <= numcols; n++) {
      Object obj = rs.getObject(n);
      String str = (obj != null) ? obj.toString() : "null";
      if (str.length() > 0)
        System.out.print(str);
      else
        System.out.print("\"\"");
      if (n != numcols) System.out.print(", ");
      }
    System.out.println("");
    }
  }


/** 
Delegates to {@link printResultSetTable(ResultSet, PrintStream,
ResultSetPrintDirection, TablePrinter.PrintConfig, boolean)} so that the
table is printed {@link ResultSetPrintDirection.HORIZONTAL horizontally}
with the default table style and headers set to true.
**/
public static final void printResultSetTable(ResultSet rs, PrintStream ps) 
throws SQLException
  {
  printResultSetTable(rs, ps, ResultSetPrintDirection.HORIZONTAL, null, true);
  }

/** 
Prints the specified result set in a tabular format. The printed table
style is according specified {@link TablePrinter.PrintConfig PrintConfig}
object.

@param  rs      the ResultSet
@param  ps      the destination print stream
@param  direction the result set printing orientation.  
@param  config    the printing configuration. 
          <b>Specify <tt>null</tt>for to use the 
          default style</b>.
@param  headers   <tt>true</tt> to print headers, <tt>false</tt> to omit 
          headers. Headers are obtained from the
          ResultSet's Meta Data.
**/
public static final void printResultSetTable(
  ResultSet rs, PrintStream ps, ResultSetPrintDirection direction, 
  TablePrinter.PrintConfig config, boolean headers) 
throws SQLException
  {
  ResultSetMetaData metadata = rs.getMetaData();
  int numcols = metadata.getColumnCount();
  TablePrinter fprint = null;
  if (config == null) {
    config = new TablePrinter.PrintConfig();
    config.setCellPadding(1);
    }
        
  if (direction == ResultSetPrintDirection.HORIZONTAL) 
    {     
    fprint = new TablePrinter(numcols, ps, config);
    fprint.startTable();
    
    //print headers
    if (headers)  
      {
      fprint.startRow();
      for (int i = 1; i <= numcols; i++)  {
        fprint.printCell(metadata.getColumnLabel(i));
        }
      fprint.endRow();
      }

    while (rs.next()) {
      fprint.startRow();
      for (int i = 1; i <= numcols; i++) {
        fprint.printCell(rs.getString(i));
        }
      fprint.endRow();
      }
    fprint.endTable();
    }

  else if (direction == ResultSetPrintDirection.VERTICAL)
    {
    if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
      ps.println("QueryUtil.printResultSet(): Vertical print orientation requires a scrollable resultset"); 
      return;
      }
    
    rs.last();
    int rows = rs.getRow(); //number of row in rs == our columns
    int printcols = (headers) ? rows + 1 : rows;
  
    //we don't support this cause we don't know how many times
    //the header will be repeated etc., and we need to know 
    //the exact column size (which equals number of rows in Vertical)
    //before printing (and number of rows includes and extra headers
    //possibly repeated
    if (config.getHeader() != null) {
      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");
      return;
      }
  
    fprint = new TablePrinter(printcols, ps, config);
    rs.beforeFirst();
    
    fprint.startTable();      
    for (int n = 1; n <= numcols; n++) 
      {
      fprint.startRow();      

      if (headers) {
        fprint.printCell(metadata.getColumnLabel(n));
        }

      while (rs.next()) {
        fprint.printCell(rs.getString(n));    
        }
      
      fprint.endRow();
      rs.beforeFirst();
      }
    fprint.endTable();  
    }

  else    
    ps.println("QueryUtil.printResultSet(): PrintConfig not understood"); 

  }


//we have to implement this method identically twice --once
//for printstream, once for jspwriter
//because darn it, jspwriter is NOT a subclass of printwriter
//or printstream, freaking idiots designed jsp's 

/**
Prints the given ResultSet as a HTML table to the specified JspWriter. The
ResultSet is transversed/printed based on the direction parameter (normal
output where each row is printed horizontally is specified via {@link
QueryUtil.ResultSetPrintDirection#HORIZONTAL}).
<p>
The output table has the following CSS styles added to it:
<ul>
<li>For the table: class <tt>QueryUtil_Table</tt>
<li>For the Header row: class <tt>QueryUtil_HeaderRow</tt>
<li>For the Header cells: class <tt>QueryUtil_HeaderCell</tt>
<li>For a normal row: class <tt>QueryUtil_Row</tt>
<li>For a normal cell: class <tt>QueryUtil_Cell</tt>
</ul>
**/
public static final void printResultSetHTMLTable(
  ResultSet rs, javax.servlet.jsp.JspWriter out, 
  ResultSetPrintDirection direction) throws IOException, SQLException
  {
  boolean headers = true;
  ResultSetMetaData metadata = rs.getMetaData();
  int numcols = metadata.getColumnCount();
  
  out.println("<table class=\"QueryUtil_Table\">");
  if (direction == ResultSetPrintDirection.HORIZONTAL) 
    {     
    //print headers
    if (headers)  
      {
      out.print("<tr class=\"QueryUtil_HeaderRow\">");
      for (int i = 1; i <= numcols; i++)  {
        out.print("<td class=\"QueryUtil_HeaderCell\">");
        out.print(metadata.getColumnLabel(i));
        out.print("</td>");
        }
      out.println("</tr>");
      }

    while (rs.next()) {
      out.println("<tr class=\"QueryUtil_Row\">");
      for (int i = 1; i <= numcols; i++) {
        out.print("<td class=\"QueryUtil_Cell\">");
        out.print(rs.getString(i));
        out.print("</td>");
        }
      out.println("</tr>");
      }
    out.println("</table>");
    }

  else if (direction == ResultSetPrintDirection.VERTICAL)
    {
    if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
      out.println("QueryUtil.printResultSet(): Vertical print orientation requires a scrollable resultset");  
      return;
      }
    
    rs.last();
    int rows = rs.getRow(); //number of row in rs == our columns
    int printcols = (headers) ? rows + 1 : rows;
  
    rs.beforeFirst();
    
    out.println("<table class=\"QueryUtil_Table\">");
    for (int n = 1; n <= numcols; n++) 
      {
      out.println("<tr>");

      if (headers) {
        out.print("<td class=\"QueryUtil_HeaderCell\">");
        out.println(metadata.getColumnLabel(n));
        out.println("</td>");
        }

      while (rs.next()) {
        out.println("<td class=\"QueryUtil_Cell\">");
        out.println(rs.getString(n));   
        out.println("</td>");
        }
      
      out.println("</tr>");
      rs.beforeFirst();
      }
    out.println("</table>");
    }

  }


/**
Prints the given ResultSet as a HTML table to the specified PrintWriter.
The ResultSet is transversed/printed based on the direction parameter
(normal output where each row is printed horizontally is specified via
{@link QueryUtil.ResultSetPrintDirection#HORIZONTAL}).
<p>
The output table has the following CSS styles added to it:
<ul>
<li>For the table: class <tt>QueryUtil_Table</tt>
<li>For the Header row: class <tt>QueryUtil_HeaderRow</tt>
<li>For the Header cells: class <tt>QueryUtil_HeaderCell</tt>
<li>For a normal row: class <tt>QueryUtil_Row</tt>
<li>For a normal cell: class <tt>QueryUtil_Cell</tt>
</ul>
**/
public static final void printResultSetHTMLTable(
  ResultSet rs, PrintWriter out, 
  ResultSetPrintDirection direction) throws IOException, SQLException
  {
  boolean headers = true;
  ResultSetMetaData metadata = rs.getMetaData();
  int numcols = metadata.getColumnCount();
  
  out.println("<table class=\"QueryUtil_Table\">");
  if (direction == ResultSetPrintDirection.HORIZONTAL) 
    {     
    //print headers
    if (headers)  
      {
      out.print("<tr class=\"QueryUtil_HeaderRow\">");
      for (int i = 1; i <= numcols; i++)  {
        out.print("<td class=\"QueryUtil_HeaderCell\">");
        out.print(metadata.getColumnLabel(i));
        out.print("</td>");
        }
      out.println("</tr>");
      }

    while (rs.next()) {
      out.println("<tr class=\"QueryUtil_Row\">");
      for (int i = 1; i <= numcols; i++) {
        out.print("<td class=\"QueryUtil_Cell\">");
        out.print(rs.getString(i));
        out.print("</td>");
        }
      out.println("</tr>");
      }
    out.println("</table>");
    }

  else if (direction == ResultSetPrintDirection.VERTICAL)
    {
    if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
      out.println("QueryUtil.printResultSet(): Vertical print orientation requires a scrollable resultset");  
      return;
      }
    
    rs.last();
    int rows = rs.getRow(); //number of row in rs == our columns
    int printcols = (headers) ? rows + 1 : rows;
  
    rs.beforeFirst();
    
    out.println("<table class=\"QueryUtil_Table\">");
    for (int n = 1; n <= numcols; n++) 
      {
      out.println("<tr>");

      if (headers) {
        out.print("<td class=\"QueryUtil_HeaderCell\">");
        out.println(metadata.getColumnLabel(n));
        out.println("</td>");
        }

      while (rs.next()) {
        out.println("<td class=\"QueryUtil_Cell\">");
        out.println(rs.getString(n));   
        out.println("</td>");
        }
      
      out.println("</tr>");
      rs.beforeFirst();
      }
    out.println("</table>");
    }
  }


/** 
Specifies the orientation of the result set when printed (via
methods like {@link  printResultSet()}).
**/
public static final class ResultSetPrintDirection
  {
  /** 
  The result set will be printed in the typical table format
  with the columns going across the page and the rows going
  downwards. 
  **/
  public static final ResultSetPrintDirection HORIZONTAL = 
    new ResultSetPrintDirection("QueryUtil.ResultSetPrintDirection.HORIZONTAL");
    
  /** 
  The result set will be printed with columns going downwards
  and rows going across the page.
  **/ 
  public static final ResultSetPrintDirection VERTICAL =  
    new ResultSetPrintDirection("QueryUtil.ResultSetPrintDirection.VERTICAL");

  private String name;
  private ResultSetPrintDirection(String name) {
    this.name = name;
    }
  public String toString() { 
    return name;
    }
  }     //~ResultSetPrintDirection  

/* this is not really needed for anything so taken out

//Used to specify the printing options (used by methods that print
//result sets for example). Printed fields and records are simply seperated 
//by field and record seperators respectively.

public static class SimplePrintConfig
  {
  The newline for this JVM's platform. Used to separate horizontal
  records by default.
  public static final String NEWLINE = IOUtil.LINE_SEP;
  
  Used to separate vertical records by default.
  public static final String COMMA = ",";

  private String  fieldsep = COMMA;
  private String  recsep = NEWLINE;
  private String  name;
  
  public SimplePrintConfig() {
    }
    
  Sets the string used to separate records. The default value is:
  {@link #NEWLINE}.
  public void setRecordSeperator(String str) {
    recsep = str;
    }

  Sets the string used to separate fields. The default value is:
  {@link #COMMA}.
  public void setFieldSeperator(String str) {
    fieldsep = str;
    }

  public String getRecordSeperator() {
    return recsep;
    }

  public String getFieldSeperator() {
    return fieldsep;
    }

  Prints a short description of this object. Field and record 
  seperators containing control characters are printed as
  readable equivalents. 
  public String toString() { 
    return "SimplePrintConfig: Field seperator=" + 
      StringUtil.viewableAscii(fieldsep) + 
      "; Record seperator=" + 
      StringUtil.viewableAscii(recsep);
    }
    
  } //~SimplePrintConfig

*/

} //~class QueryUtil