QueryUtil (MollyPages.org Foundation API)

java.lang.Object
- fc.jdbc.QueryUtil

public final class QueryUtil
extends Object

Various JDBC utility methods.

Nested Class Summary

Nested Classes
Modifier and Type	Class and Description
`static class`	`QueryUtil.ResultSetPrintDirection` Specifies the orientation of the result set when printed (via methods like `printResultSet()`).

Method Summary

All Methods Static Methods Concrete Methods
Modifier and Type	Method and Description
`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:
    - For the table: class QueryUtil_Table
    - For the Header row: class QueryUtil_HeaderRow
    - For the Header cells: class QueryUtil_HeaderCell
    - For a normal row: class QueryUtil_Row
    - For a normal cell: class QueryUtil_Cell
    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:
    - For the table: class QueryUtil_Table
    - For the Header row: class QueryUtil_HeaderRow
    - For the Header cells: class QueryUtil_HeaderCell
    - For a normal row: class QueryUtil_Row
    - For a normal cell: class QueryUtil_Cell
    Throws:
    
    IOException
    
    SQLException

Class QueryUtil

Nested Class Summary

Method Summary

Methods inherited from class java.lang.Object

Method Detail

init

getNewTxID

getDBName

ensureScrollable

ensureCount

getRowCount

getLastInsertID

executeQuery

executeQuery

hasExactlyOneRow

hasExactlyZeroRows

getRewindableStmt

getRewindablePreparedStmt

startTransaction

startTransaction

startTransaction

abortTransaction

abortTransaction

endTransaction

endTransaction

rollback

close

close

rsToList

rsListToString

printRS

printResultSetTable

printResultSetTable

printResultSetHTMLTable

printResultSetHTMLTable