M
markspace
So I'm looking a bit more at the JDBC, and I realize there's a bit more
to transactions than turning auto-commit on or off.
Transaction isolation allows the user to select the database locking
scheme used by the driver. Basically rather than deal with locks
yourself you let the driver do it. However, besides descriptions of the
transaction levels, I'm not seeing much in-depth discussion how to use
transaction levels or any examples either.
<http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html>
So here's my example. I want to manually retrieve all the rows of a
table, then find the maximum value of column. Then I increment that
value, and store a new row with the max+1 value in it. I.e., I'm
creating a new unique ID for a row, and not using any "auto" type
functions to do it.
How does that go in the JDBC? It seems to require that phantom reads
are not allowed, the highest level of transaction isolation. Here's my
implementation:
public int createNew( UserBean user )
{
Connection conn = null;
try {
QueryRunner run = new QueryRunner();
conn = dataSource.getConnection();
// IMPORTANT BIT HERE
// set transaction isolation
conn.setAutoCommit( false );
conn.setTransactionIsolation(
Connection.TRANSACTION_SERIALIZABLE );
ScalarHandler max = new ScalarHandler();
Integer ident = (Integer)run.query( conn,
"select max(id) from UserTable" , max );
if( ident == null )
user.setId( 1 );
else
user.setId( ident+1 );
int updates = run.update( conn,
"insert into UserTable values (?,?,?,?,?)",
user.getName(), user.getPassword(), user.getScreenName(),
user.getPermissions(), user.getId() );
conn.commit();
return updates;
} catch( SQLException ex ) {
Logger.getLogger( UserDataMapper.class.getName() ).
log( Level.SEVERE, null, ex );
return 0;
} finally {
SimpleSql.closeAll( conn );
}
}
It uses dbutils, which I posted about earlier. If you need an SSCCE, I
can put one together (mostly, you'll need jdbutils and derby/jdb). But
I'm hoping this is complete enough for discussion.
<http://commons.apache.org/dbutils/>
to transactions than turning auto-commit on or off.
Transaction isolation allows the user to select the database locking
scheme used by the driver. Basically rather than deal with locks
yourself you let the driver do it. However, besides descriptions of the
transaction levels, I'm not seeing much in-depth discussion how to use
transaction levels or any examples either.
<http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html>
So here's my example. I want to manually retrieve all the rows of a
table, then find the maximum value of column. Then I increment that
value, and store a new row with the max+1 value in it. I.e., I'm
creating a new unique ID for a row, and not using any "auto" type
functions to do it.
How does that go in the JDBC? It seems to require that phantom reads
are not allowed, the highest level of transaction isolation. Here's my
implementation:
public int createNew( UserBean user )
{
Connection conn = null;
try {
QueryRunner run = new QueryRunner();
conn = dataSource.getConnection();
// IMPORTANT BIT HERE
// set transaction isolation
conn.setAutoCommit( false );
conn.setTransactionIsolation(
Connection.TRANSACTION_SERIALIZABLE );
ScalarHandler max = new ScalarHandler();
Integer ident = (Integer)run.query( conn,
"select max(id) from UserTable" , max );
if( ident == null )
user.setId( 1 );
else
user.setId( ident+1 );
int updates = run.update( conn,
"insert into UserTable values (?,?,?,?,?)",
user.getName(), user.getPassword(), user.getScreenName(),
user.getPermissions(), user.getId() );
conn.commit();
return updates;
} catch( SQLException ex ) {
Logger.getLogger( UserDataMapper.class.getName() ).
log( Level.SEVERE, null, ex );
return 0;
} finally {
SimpleSql.closeAll( conn );
}
}
It uses dbutils, which I posted about earlier. If you need an SSCCE, I
can put one together (mostly, you'll need jdbutils and derby/jdb). But
I'm hoping this is complete enough for discussion.
<http://commons.apache.org/dbutils/>