Class QueryUtil

java.lang.Object
fc.jdbc.QueryUtil
public final class QueryUtil extends Object
Various JDBC utility methods.

  • Method Details

    • 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(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(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(Connection, 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(java.sql.ResultSet,java.io.PrintStream,fc.jdbc.QueryUtil.ResultSetPrintDirection,fc.io.TablePrinter.PrintConfig,boolean) so that the table is printed
      invalid reference 
      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, jakarta.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