Package de.aristaflow.adept2.base.dbaccess

Class JDBCTools

  • public final class JDBCTools
extends Object
    Contains helper methods that are intended to reduce the usual clutter in JDBC-related code, make it more readable and safer.
    • The closeQuietly(..) methods allow short and easy clean-up code to be used in the finally-block. They swallow SQLExceptions and check for null, so you don't have to do it. Don't use them as the only mechanism for closing result sets, statements etc.! (See the suggested pattern below.)
    • The close(..) methods allow to close a ResultSet, a (Prepared)Statement and a Connection (with variations for different combinations) in a single line. There are versions for single objects that currently provide no other advantage than consistency.


    This is the suggested pattern: 
     Connection con = null;
 Statement stmt = null;
 ResultSet rs = null;
 try
 {
   con = getDataSource().getConnection();
   stmt = con.createStatement();
   rs = stmt.executeQuery(<query>);

   // read from the result set

   rs = JDBCTools.close(rs);
   stmt = JDBCTools.close(stmt);
   con = JDBCTools.close(con);
   // now all three variables are null and it won't be tried to close them
   // again in the finally block
 }
 catch (SQLException ex) // the catch part is optional, depending on which exceptions your method may throw
 {
   throw new DataSourceException(ex);
 }
 finally
 {
   JDBCTools.closeQuietly(con, stmt, rs);
 }
    Note, that the resources are also closed in the try-block and not just in the finally-block. The first ensures that potential exceptions during the regular releasing of resources are not swallowed, while the latter ensures that even after runtime exceptions the resources are properly released. This differs from a common so called "best practice" where everything is closed exclusively in the finally-block.

    Exceptions in the finally-block are swallowed since they would override exceptions from the try- or catch-blocks.

    This pattern may of course be extended if more variables for (prepared) statements and/or result sets are needed.

    To use transactions only auto-commit has to be turned off. Everything else stays the same: 

     Connection con = null;
 Statement stmt = null;
 ResultSet rs = null;
 try
 {
   con = getDataSource().getConnection();
   con.setAutoCommit(false);
   stmt = con.createStatement();
   rs = stmt.executeQuery(<query>);

   // read from the result set

   rs = JDBCTools.close(rs);
   stmt = JDBCTools.close(stmt);
   con = JDBCTools.close(con); // this will attempt a commit before closing the connection
   // now all three variables are null and it won't be tried to close them
   // again or to rollback in the finally block
 }
 catch (SQLException ex) // the catch part is optional, depending on which exceptions your method may throw
 {
   throw new DataSourceException(ex);
 }
 finally
 {
   JDBCTools.closeQuietly(con, stmt, rs); // this will also attempt a rollback on the connection
 }

    TODO: example with transaction(s) where connection is created elsewhere and auto commit state is not known in advance

    • Field Detail

      • MIN_TIMESTAMP

        public static final long MIN_TIMESTAMP
        timestamp for 0001-01-01 00:00:00 (UTC)
        See Also:
        Constant Field Values

      • MAX_TIMESTAMP

        public static final long MAX_TIMESTAMP
        timestamp for 9999-12-31 23:59:59 (UTC)
        See Also:
        Constant Field Values

    • Method Detail

      • turnOffAutoCommit

        public static Boolean turnOffAutoCommit​(Connection con)
                                 throws SQLException
        Turns off auto-commit of the given connection and returns the previous state.
        Parameters:
        con - the connection on which to set the auto-commit state
        Returns:
        the previous auto-commit state
        Throws:
        SQLException - if a database access error occurs

      • close

        public static <R extends ResultSet> R close​(R rs)
                                     throws SQLException
        Closes the given result set and returns null. The intended usage pattern is as follows: 
         rs = JDBCTools.close(rs)
        This ensures that the variable is null and the object can't (accidentally) be used any longer. Also, the finally block does not try to close the object again, since its reference is null now.
        Parameters:
        rs - the result set to be closed
        Returns:
        null
        Throws:
        SQLException - if a database access error occurs

      • close

        public static <S extends Statement> S close​(S stmt)
                                     throws SQLException
        Closes the given statement and returns null. The intended usage pattern is as follows: 
         stmt = JDBCTools.close(stmt)
        This ensures that the variable is null and the object can't (accidentally) be used any longer. Also, the finally block does not try to close the object again, since its reference is null now.
        Type Parameters:
        S - to support all subtypes of Statement
        Parameters:
        stmt - the statement to be closed
        Returns:
        null
        Throws:
        SQLException - if a database access error occurs

      • close

        public static <C extends Connection> C close​(C con)
                                      throws SQLException
        Closes the given connection and returns null after attempting a commit (only if auto-commit is turned off). The intended usage pattern is as follows: 
         con = JDBCTools.close(con);
        This ensures that the variable is null and the object can't (accidentally) be used any longer. Also, the finally block does not try to close the object again, since its reference is null now.
        Type Parameters:
        C - to support all subtypes of Connection
        Parameters:
        con - the connection set to be closed
        Returns:
        null
        Throws:
        SQLException - if a database access error occurs

      • commit

        public static <C extends Connection> C commit​(C con)
                                       throws SQLException
        Performs a commit and returns null. Intended for cases where the connection is provided from outside (e.g. given as method parameter) and should therefore not be closed.
        Type Parameters:
        C - to support all subtypes of Connection
        Parameters:
        con - the connection on which to perform the commit
        Returns:
        null
        Throws:
        SQLException - if a database access error occurs

      • restoreAutoCommit

        public static <C extends Connection> C restoreAutoCommit​(C con,
                                                         Boolean previousAutoCommitState)
                                                  throws SQLException
        If auto-commit was previously on, a commit is performed and auto-commit is turned on again. If auto-commit was previously off, nothing is done.
        Type Parameters:
        C - to support all subtypes of Connection
        Parameters:
        con - the connection on which to perform the commit
        previousAutoCommitState - the previous state of auto-commit or null
        Returns:
        null
        Throws:
        SQLException - if a database access error occurs

      • restoreAutoCommit

        public static <C extends Connection> C restoreAutoCommit​(C con,
                                                         Savepoint sp,
                                                         Boolean previousAutoCommitState)
                                                  throws SQLException
        If auto-commit was previously on, a commit is performed and auto-commit is turned on again. If auto-commit was previously off, the savepoint is released.
        Type Parameters:
        C - to support all subtypes of Connection
        Parameters:
        con - the connection on which to perform the commit
        sp - the savepoint to release
        previousAutoCommitState - the previous state of auto-commit or null
        Returns:
        null
        Throws:
        SQLException - if a database access error occurs

      • closeQuietly

        public static void closeQuietly​(Connection con,
                                Statement stmt,
                                ResultSet rs)
        Closes the given result set, statement and connection ignoring null values and swallowing SQLExceptions. Before closing the connection a rollback is attempted. This method is only intended to be used in a finally-block.
        Parameters:
        con - the connection to be silently closed (if not null)
        stmt - the statement to be silently closed (if not null)
        rs - the result set to be silently closed (if not null)

      • closeQuietly

        public static void closeQuietly​(Connection con,
                                Statement stmt)
        Closes the given connection and statement, ignoring null values and swallowing SQLExceptions. Before closing the connection a rollback is attempted. This method is only intended to be used in a finally-block.
        Parameters:
        con - the connection to be silently closed (if not null)
        stmt - the statement to be silently closed (if not null)

      • closeQuietly

        public static void closeQuietly​(Statement stmt,
                                ResultSet rs)
        Closes the given statement and result set, ignoring null values and swallowing SQLExceptions. This method is only intended to be used in a finally-block.
        Parameters:
        stmt - the statement to be silently closed (if not null)
        rs - the result set to be silently closed (if not null)

      • closeQuietly

        public static void closeQuietly​(Connection con)
        Attempts a rollback (if auto-commit is off) and then (in either case) closes the given connection, ignoring null values and swallowing SQLExceptions. This method is only intended to be used in a finally-block.
        Parameters:
        con - the connection to be silently closed (if not null)

      • closeQuietly

        public static void closeQuietly​(Statement... stmts)
        Closes the given statements, ignoring null values and swallowing SQLExceptions. This method is only intended to be used in a finally-block.
        Parameters:
        stmts - the statements to be silently closed

      • closeQuietly

        public static void closeQuietly​(Statement stmt)
        Closes the given statement, ignoring null values and swallowing SQLExceptions. This method is only intended to be used in a finally-block.
        Parameters:
        stmt - the statement to be silently closed (if not null)

      • closeQuietly

        public static void closeQuietly​(ResultSet rs)
        Closes the given result set, ignoring null values and swallowing SQLExceptions. This method is only intended to be used in a finally-block.
        Parameters:
        rs - the result set to be silently closed (if not null)

      • rollbackQuietly

        public static void rollbackQuietly​(Connection con)
        Will try to rollback the current transaction of the given connection, but only if auto commit is turned off. In that case auto commit will also be turned on. The connection will not be closed.
        Parameters:
        con - the connection to rollback

      • rollbackQuietly

        public static void rollbackQuietly​(Connection con,
                                   Boolean previousAutoCommitState)
        Will try to rollback the current transaction of the given connection, but only if auto commit is turned off. The connection will not be closed.
        Parameters:
        con - the connection to rollback
        previousAutoCommitState - the previous state of the auto-commit flag that will be restored (if possible); null to leave the flag as is

      • rollbackQuietly

        public static void rollbackQuietly​(Connection con,
                                   Savepoint sp)
        Will try to rollback the current transaction of the given connection to the given savepoint, but only if auto commit is turned off. In that case auto commit will also be turned on. The connection will not be closed.
        Parameters:
        con - the connection to rollback
        sp - the savepoint to roll back to

      • rollbackQuietly

        public static void rollbackQuietly​(Connection con,
                                   Savepoint sp,
                                   Boolean previousAutoCommitState)
        Will try to rollback the current transaction of the given connection to the given savepoint, but only if auto commit is turned off. The connection will not be closed.
        Parameters:
        con - the connection to rollback
        sp - the savepoint to roll back to
        previousAutoCommitState - the previous state of the auto-commit flag that will be restored (if possible); null to leave the flag as is

      • toString

        public static String toString​(Date d)
        Formats an SQL date to JDBC date escape format. Unlike Date.toString() this method does not convert to the default timezone, but leaves the date as is.
        Parameters:
        d - the date to be formatted
        Returns:
        the date in JDBC date escape format
        Throws:
        IllegalArgumentException - if the date is before MIN_TIMESTAMP or after MAX_TIMESTAMP

      • toString

        public static String toString​(Time t)
        Formats an SQL time to JDBC time escape format. Unlike Time.toString() this method does not convert to the default timezone, but leaves the time as is.
        Parameters:
        t - the time to be formatted
        Returns:
        the time in JDBC time escape format

      • toString

        public static String toString​(Timestamp ts)
        Formats an SQL timestamp to JDBC timestamp escape format. Unlike Timestamp.toString() this method does not convert to the default timezone, but leaves the timestamp as is.
        Parameters:
        ts - the timestamp to be formatted
        Returns:
        the timestamp in JDBC timestamp escape format
        Throws:
        IllegalArgumentException - if the timestamp is before MIN_TIMESTAMP or after MAX_TIMESTAMP

      • trim

        public static Date trim​(Date d)
        Returns a new date that is guaranteed to be after MIN_TIMESTAMP and before MAX_TIMESTAMP, if the given date is not within these bounds.
        Parameters:
        d -
        Returns:

      • trim

        public static Timestamp trim​(Timestamp ts)
        Returns a new timestamp that is guaranteed to be after MIN_TIMESTAMP and before MAX_TIMESTAMP, if the given timestamp is not within these bounds.
        Parameters:
        ts -
        Returns:

      • createUTCCalendar

        public static Calendar createUTCCalendar()
        Returns a new Calendar with UTC as its time zone.
        Returns:
        a new Calendar with UTC as its time zone

      • generalise

        public static SQLException generalise​(SQLException ex,
                                      Logger generalLogger)
        Converts every JDBC-driver specific subclass of SQLException to a general SQLException. This is useful if DBMS-specific subclasses will be part of exception cause chains that may be transmitted to clients that don't necessarily have these JDBC driver classes present. Subclasses included in the JRE will be returned as is since the client will have them. So it is possible indiscriminately pass every SQLException through this method to ensure they are safe. This method will also process next exceptions.
        Parameters:
        ex - any subclass of SQLException
        generalLogger - the logger used to log generalised exceptions as Level.FINE; null to not log them
        Returns:
        a plain SQLException or any passed-through subclass from the packages java.sql and javax.sql

      • generaliseSQLClientInfoException

        public static SQLClientInfoException generaliseSQLClientInfoException​(SQLClientInfoException ex,
                                                                      Logger generalLogger)
        Converts every JDBC-driver specific subclass of SQLException to a general SQLException. This is useful if DBMS-specific subclasses will be part of exception cause chains that may be transmitted to clients that don't necessarily have these JDBC driver classes present. Subclasses included in the JRE will be returned as is since the client will have them. So it is possible indiscriminately pass every SQLException through this method to ensure they are safe. This method will also process next exceptions.
        Parameters:
        ex - any subclass of SQLException
        generalLogger - the logger used to log generalised exceptions as Level.FINE; null to not log them
        Returns:
        a plain SQLException or any passed-through subclass from the packages java.sql and javax.sql

      • setMaximumIsolationLevel

        public static int setMaximumIsolationLevel​(Connection con,
                                           int isoLevel)
                                    throws SQLException
        Sets the isolation level of the designated connection to at most the designated one. If the underlying database does not support a level, the next lower is chosen.
        Parameters:
        con - The connection of which to set the isolation level.
        isoLevel - The maximum isolation level which to have with the designated connection.
        Returns:
        The isolation level that has been set.
        Throws:
        SQLException - If there are problems setting the isolation level, an SQLException will be thrown.

      • setMinimumIsolationLevel

        public static int setMinimumIsolationLevel​(Connection con,
                                           int isoLevel)
                                    throws SQLException
        Sets the isolation level of the designated connection to at least the designated one. If the underlying database does not support a level, the next higher is chosen.
        Parameters:
        con - The connection of which to set the isolation level.
        isoLevel - The minimum isolation level which to have with the designated connection.
        Returns:
        The isolation level that has been set.
        Throws:
        SQLException - If there are problems setting the isolation level, an SQLException will be thrown.