Notes on JDBC connections

  • Autocommit behavior   The JDBC specification requires that, by default, a COMMIT is performed after each data modification statement. Currently, the client-side JDBC behavior is to commit (autocommit is true) and the server-side behavior is to not commit (autocommit is false). To obtain the same behavior in both client-side and server-side applications, you can use a statement such as the following:
    con.setAutoCommit( false );

    In this statement, con is the current connection object. You could also set autocommit to true.

  • Connection defaults   From server-side JDBC, only the first call to getConnection( "jdbc:default:connection" ) creates a new connection with the default values. Subsequent calls return a wrapper of the current connection with all connection properties unchanged. If you set autocommit to false in your initial connection, any subsequent getConnection calls within the same Java code return a connection with autocommit set to false.

    You may want to ensure that closing a connection restores the connection properties to their default values, so that subsequent connections are obtained with standard JDBC values. The following code achieves this:

    Connection con = 
        DriverManager.getConnection("jdbc:default:connection");
    
    boolean oldAutoCommit = con.getAutoCommit();
    try {
         // main body of code here
    }
    finally {
        con.setAutoCommit( oldAutoCommit );
    }

    This discussion applies not only to autocommit, but also to other connection properties such as transaction isolation level and read-only mode.

    For more information about the getTransactionIsolation, setTransactionIsolation, and isReadOnly methods, see documentation on the java.sql.Connection interface.