Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #14184

JDBC transaction isolation

Show all headers | View raw

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/>

Back to comp.lang.java.programmer | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

JDBC transaction isolation markspace <-@.> - 2012-05-03 07:30 -0700
  Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-03 11:53 -0400
    Re: JDBC transaction isolation markspace <-@.> - 2012-05-03 11:43 -0700
      Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-03 15:00 -0400
        Re: JDBC transaction isolation Lew <lewbloch@gmail.com> - 2012-05-03 14:00 -0700
          Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-03 17:13 -0400
        Re: JDBC transaction isolation "Richard Maher" <maher_rj@hotspamnotmail.com> - 2012-05-04 06:23 +0800
          Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-03 19:57 -0400
            Re: JDBC transaction isolation Richard Maher <maherrj@googlemail.com> - 2012-05-03 19:08 -0700
              Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-03 22:34 -0400
                Re: JDBC transaction isolation Richard Maher <maherrj@googlemail.com> - 2012-05-03 20:11 -0700
                Re: JDBC transaction isolation Richard Maher <maherrj@googlemail.com> - 2012-05-03 22:33 -0700
                Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-04 08:27 -0400
                Re: JDBC transaction isolation Lew <lewbloch@gmail.com> - 2012-05-04 06:58 -0700
          Re: JDBC transaction isolation Lew <lewbloch@gmail.com> - 2012-05-03 17:15 -0700
            Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-03 21:22 -0400
              Re: JDBC transaction isolation Lew <lewbloch@gmail.com> - 2012-05-03 18:32 -0700

csiph-web