fc.jdbc
Class QueryUtil

java.lang.Object
  extended by fc.jdbc.QueryUtil

public final class QueryUtil
extends Object

Various JDBC utility methods.


Nested Class Summary
static class QueryUtil.ResultSetPrintDirection
          Specifies the orientation of the result set when printed (via methods like printResultSet()).
 
Method Summary
static boolean abortTransaction(Connection con)
          Calls abortTransaction with an empty message string.
static boolean abortTransaction(Connection con, String message)
          Aborts (rolls back) the current transaction on the specified connection.
static void close(Connection con)
          Closes the specified connection, ignoring any exceptions encountered in the connection.close() method itself.
static void close(ResultSet rs, Statement stmt, Connection con)
          Closes the specified connection, statement and resultset, logging any errors to the stderr.
static boolean endTransaction(Connection con)
          Calls endTransaction with an empty message string.
static boolean endTransaction(Connection con, String message)
          Commits the specified connection.
static void ensureCount(int rowcount, int expected)
          Checks to see if actual row count was same as expected row count, for some query.
static void ensureScrollable(ResultSet rs)
          Checks to see if the specified result set is scrollable.
static List executeQuery(Connection con, String query)
          Convenience method that calls executeQuery(Connection, String, boolean) specifying no header options (i.e., column headers are not returned as part of the query results).
static List executeQuery(Connection con, String query, boolean headers)
          Performs the specified query and returns a List (the result of converting the ResultSet via the rsToList(java.sql.ResultSet, boolean) method).
static DBName getDBName(Connection con)
          Returns the dbname corresponding that the database for the specified jdbc connection.
static long getLastInsertID(Connection con, DBName dbname, Object info)
          Gets the last inserted id, typically auto-increment(mysql) or serial (postgresql) columns.
static long getNewTxID()
          Returns a montonically increasing number starting from 1.
static PreparedStatement getRewindablePreparedStmt(Connection con, String sql)
          Creates a new connection that will return ResultSet's of TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY.
static Statement getRewindableStmt(Connection con)
          Creates a new connection that will return ResultSet's of TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY.
static long getRowCount(ResultSet rset)
          Returns the number of rows in the specified ResultSet.
static boolean hasExactlyOneRow(Connection con, String query)
          Peforms the specified query and returns true if the query has only one row of data.
static boolean hasExactlyZeroRows(Connection con, String query)
          Peforms the specified query and returns true if the query returns no data.
static void init(Log logger)
          This method can be called to initialize a logger for this class (else a default logger will used).
static void printResultSetHTMLTable(ResultSet rs, javax.servlet.jsp.JspWriter out, QueryUtil.ResultSetPrintDirection direction)
          Prints the given ResultSet as a HTML table to the specified JspWriter.
static void printResultSetHTMLTable(ResultSet rs, PrintWriter out, QueryUtil.ResultSetPrintDirection direction)
          Prints the given ResultSet as a HTML table to the specified PrintWriter.
static void printResultSetTable(ResultSet rs, PrintStream ps)
          Delegates to printResultSetTable(ResultSet, PrintStream, ResultSetPrintDirection, TablePrinter.PrintConfig, boolean) so that the table is printed horizontally with the default table style and headers set to true.
static void printResultSetTable(ResultSet rs, PrintStream ps, QueryUtil.ResultSetPrintDirection direction, TablePrinter.PrintConfig config, boolean headers)
          Prints the specified result set in a tabular format.
static void printRS(ResultSet rs)
          Prints the given java.sql.ResultSet (including result set headers) in a simple straightforward fashion to System.out
static void rollback(Connection con)
          Rollsback the transaction, ignoring any errors in the rollback itself.
static String rsListToString(List list)
          Converts the list returned by the method to a String, consisting of all the rows contained in the list.
static List rsToList(ResultSet rs, boolean headers)
          Converts a java.sql.ResultSet into a List of Object[], where each Object[] represents all the columns in one row.
static boolean startTransaction(Connection con)
          Calls startTransaction with a empty message string.
static boolean startTransaction(Connection con, int transactionIsolation, String message)
          Starts a transaction on the specified connection.
static boolean startTransaction(Connection con, String message)
          Starts a transaction on the specified connection.
 
Methods inherited from class java.lang.Object
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Method Detail

init

public static final void init(Log logger)
This method can be called to initialize a logger for this class (else a default logger will used).

Parameters:
logger - Sets the log to which methods in this class will send logging output.

getNewTxID

public static final long getNewTxID()
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.


getDBName

public static DBName getDBName(Connection con)
                        throws SQLException
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 ConnectionMgr

Throws:
SQLException

ensureScrollable

public static final void ensureScrollable(ResultSet rs)
                                   throws SQLException
Checks to see if the specified result set is scrollable.

Throws:
SQLException - if the specified result set is not scrollable.

ensureCount

public static final void ensureCount(int rowcount,
                                     int expected)
                              throws SQLException
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.

Parameters:
rowcount - the actual count (returned by executeUpdate() etc.)
expected - the expected count
Throws:
SQLException - if the actual and expected counts don't match

getRowCount

public static final long getRowCount(ResultSet rset)
                              throws SQLException
Returns the number of rows in the specified ResultSet. If the ResultSet is of type 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).

Note: the count can also be retreived directly (and more efficiently) for many queries by including the COUNT() SQL function as part of the query (in which case one would read the returned count column directly and NOT call this method)

Parameters:
rset - the result set to examine
Returns:
the number of rows in the rowset
Throws:
SQLException

getLastInsertID

public static final long getLastInsertID(Connection con,
                                         DBName dbname,
                                         Object info)
                                  throws SQLException
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.

Parameters:
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).
dbname - the name of the target database. Needed because this must be implemented in a database specific way.
info - optional further info needed to implement this method for some databases. This currently is:
  • mysql: not needed, specify null
  • postgresql: specify the name of the sequence, which for serial columns is (by default) tablename_colname_seq, for example "tablefoo_myserialid_seq"
Throws:
SQLException - if the last insert id could not be found or if some other datbase problem occurred.

executeQuery

public static final List executeQuery(Connection con,
                                      String query)
                               throws SQLException
Convenience method that calls executeQuery(Connection, String, boolean) specifying no header options (i.e., column headers are not returned as part of the query results).

Parameters:
con - the database connection to use.
query - the query to be performed
headers - if true, the first row contains the column headers
Throws:
SQLException

executeQuery

public static final List executeQuery(Connection con,
                                      String query,
                                      boolean headers)
                               throws SQLException
Performs the specified query and returns a List (the result of converting the ResultSet via the rsToList(java.sql.ResultSet, boolean) method).

Important Note: 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.

Parameters:
con - the database connection to use.
query - the query to be peformed
headers - if true, the first row contains the column headers
Throws:
SQLException

hasExactlyOneRow

public static final boolean hasExactlyOneRow(Connection con,
                                             String query)
                                      throws SQLException
Peforms the specified query and returns true if the query has only one row of data.

Parameters:
con - the database connection to use.
Throws:
SQLException

hasExactlyZeroRows

public static final boolean hasExactlyZeroRows(Connection con,
                                               String query)
                                        throws SQLException
Peforms the specified query and returns true if the query returns no data.

Parameters:
con - the database connection to use.
Throws:
SQLException

getRewindableStmt

public static final Statement getRewindableStmt(Connection con)
                                         throws SQLException
Creates a new connection that will return ResultSet's of TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY.

Throws:
SQLException

getRewindablePreparedStmt

public static final PreparedStatement getRewindablePreparedStmt(Connection con,
                                                                String sql)
                                                         throws SQLException
Creates a new connection that will return ResultSet's of TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY. (i.e., the PreparedStatement returned by this method should be rewindable).

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 getRowCount(java.sql.ResultSet) and also be able to then rewind the ResultSet and read it's contents.

Throws:
SQLException

startTransaction

public static final boolean startTransaction(Connection con,
                                             int transactionIsolation,
                                             String message)
Starts a transaction on the specified connection. The connection is set to not autocommit any statements from here onwards and the endTransaction method must be called to end this transaction.

If the transaction cannot be started, the connection is closed.

Parameters:
con - the connection to commit
transactionIsolation - the transaction isolation level for this transaction
message - commit description; shown when debug logging is turned on
Returns:
true if the transaction was started successfully, false if the transaction could not start for some reason.
Throws:
IllegalArgumentException - if the transaction isolation level is not a valid value (as defined in Connection

startTransaction

public static final boolean startTransaction(Connection con,
                                             String message)
Starts a transaction on the specified connection. The connection is set to not autocommit any statements from here onwards and the endTransaction(java.sql.Connection, java.lang.String) 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 startTransaction(Connection, int, String).

If the transaction cannot be started, the connection is closed.

Parameters:
con - the connection to commit
message - commit description; shown if debug logging is turned on
Returns:
true if the transaction was started successfully, false if the transaction could not start for some reason.

startTransaction

public static final boolean startTransaction(Connection con)
Calls startTransaction with a empty message string.


abortTransaction

public static final boolean abortTransaction(Connection con,
                                             String message)
Aborts (rolls back) the current transaction on the specified connection.

After the transaction is rolled back, the connection is set to autoCommit(true).

Parameters:
con - the connection to rollback
message - a description; shown if logging is turned on
Returns:
true if the transaction was rolledback successful, false if the transaction rollback for some reason.

abortTransaction

public static final boolean abortTransaction(Connection con)
Calls abortTransaction with an empty message string.


endTransaction

public static final boolean endTransaction(Connection con,
                                           String message)
Commits the specified connection. Attemps a rollback if the commit() 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).

After the transaction completes, the connection is set to autoCommit(true).

Parameters:
con - the connection to commit
message - commit description; shown if logging is turned on
Returns:
true if the transaction was committed successful, false if the transaction failed for some reason.

endTransaction

public static final boolean endTransaction(Connection con)
Calls endTransaction with an empty message string.


rollback

public static final void rollback(Connection con)
Rollsback the transaction, ignoring any errors in the rollback itself.


close

public static final void close(ResultSet rs,
                               Statement stmt,
                               Connection con)
Closes the specified connection, statement and resultset, logging any errors to the stderr. Ignores any parameters with null values.


close

public static final void close(Connection con)
Closes the specified connection, ignoring any exceptions encountered in the connection.close() method itself.


rsToList

public static final List rsToList(ResultSet rs,
                                  boolean headers)
                           throws SQLException
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 null (or returns only the headers if headers are to be printed).

Parameters:
rs - the ResultSet
headers - if set to true, the first row of the returned List will contain an Object[] of the column header names.
Throws:
SQLException

rsListToString

public static final String rsListToString(List list)
Converts the list returned by the method to a String, consisting of all the rows contained in the list. Each row is rendered within brackets [..row1..], with different rows seperated by commas ([..row1..], [..row2..], ...) but this format may be changed in the future.


printRS

public static final void printRS(ResultSet rs)
                          throws SQLException
Prints the given java.sql.ResultSet (including result set headers) in a simple straightforward fashion to System.out

Parameters:
rs - the result set to print
Throws:
SQLException

printResultSetTable

public static final void printResultSetTable(ResultSet rs,
                                             PrintStream ps)
                                      throws SQLException
Delegates to printResultSetTable(ResultSet, PrintStream, ResultSetPrintDirection, TablePrinter.PrintConfig, boolean) so that the table is printed horizontally with the default table style and headers set to true.

Throws:
SQLException

printResultSetTable

public static final void printResultSetTable(ResultSet rs,
                                             PrintStream ps,
                                             QueryUtil.ResultSetPrintDirection direction,
                                             TablePrinter.PrintConfig config,
                                             boolean headers)
                                      throws SQLException
Prints the specified result set in a tabular format. The printed table style is according specified PrintConfig object.

Parameters:
rs - the ResultSet
ps - the destination print stream
direction - the result set printing orientation.
config - the printing configuration. Specify nullfor to use the default style.
headers - true to print headers, false to omit headers. Headers are obtained from the ResultSet's Meta Data.
Throws:
SQLException

printResultSetHTMLTable

public static final void printResultSetHTMLTable(ResultSet rs,
                                                 javax.servlet.jsp.JspWriter out,
                                                 QueryUtil.ResultSetPrintDirection direction)
                                          throws IOException,
                                                 SQLException
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 QueryUtil.ResultSetPrintDirection.HORIZONTAL).

The output table has the following CSS styles added to it:

Throws:
IOException
SQLException

printResultSetHTMLTable

public static final void printResultSetHTMLTable(ResultSet rs,
                                                 PrintWriter out,
                                                 QueryUtil.ResultSetPrintDirection direction)
                                          throws IOException,
                                                 SQLException
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 QueryUtil.ResultSetPrintDirection.HORIZONTAL).

The output table has the following CSS styles added to it:

Throws:
IOException
SQLException