Notes on JDBC Connections

Familiarize yourself with autocommit behavior, transaction isolation levels, and connection defaults.

• Autocommit behavior – The JDBC specification requires that, by default, a COMMIT is performed after each data manipulation 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:

  The JDBC specification requires that, by default, a COMMIT is performed after each data manipulation 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.

• Setting transaction isolation level – To set the transaction isolation level, the application must call the Connection.setTransactionIsolation method with one of the following values.

  To set the transaction isolation level, the application must call the Connection.setTransactionIsolation method with one of the following values.

  For the SQL Anywhere JDBC 4.0 driver use:

  • TRANSACTION_NONE
  • TRANSACTION_READ_COMMITTED
  • TRANSACTION_READ_UNCOMMITTED
  • TRANSACTION_REPEATABLE_READ
  • TRANSACTION_SERIALIZABLE
  • sybase.jdbc4.sqlanywhere.IConnection.SA_TRANSACTION_SNAPSHOT
  • sybase.jdbc4.sqlanywhere.IConnection.SA_TRANSACTION_STATEMENT_SNAPSHOT
  • sybase.jdbc4.sqlanywhere.IConnection.SA_TRANSACTION_STATEMENT_READONLY_SNAPSHOT

  The following example sets the transaction isolation level to SNAPSHOT using the JDBC 4.0 driver.

  try
  {
      con.setTransactionIsolation(
          sybase.jdbc4.sqlanywhere.IConnection.SA_TRANSACTION_SNAPSHOT
      );
  }
  catch( Exception e )
  {
      System.err.println( "Error! Could not set isolation level" );
      System.err.println( e.getMessage() );
      printExceptions( (SQLException)e );
  }

  For more information about the getTransactionIsolation and setTransactionIsolation, see documentation on the java.sql.Connection interface at http://docs.oracle.com/javase/6/docs/technotes/guides/jdbc/.

• 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 at http://docs.oracle.com/javase/6/docs/technotes/guides/jdbc/.