FileDocCategorySizeDatePackage
JDBCGenerator.javaAPI DocExample4829Wed Aug 23 21:56:52 BST 2000com.imaginary.lwp.jdbc

JDBCGenerator

public class JDBCGenerator extends com.imaginary.lwp.SequenceGenerator
A JDBC-based sequence generator that implements LWP's SequenceGenerator interface. To use this sequence generator, your database must have the following data model: 
CREATE TABLE ORA_SEQGEN (
NAME VARCHAR(25) NOT NULL PRIMARY KEY,
NEXT_SEQ BIGINT NOT NULL DEFAULT 1,
LUTS BIGINT NOT NULL);

CREATE UNIQUE INDEX SEQGEN_IDX ON ORA_SEQGEN(NAME, LUTS);

Last modified $Date: 1999/11/07 19:32:30 $
version
$Revision: 1.1 $
author
George Reese (borg@imaginary.com)

Fields Summary
public static final String
INSERT
The SQL to insert a new sequence number in the table.
public static final String
SELECT
Selects the next sequence number from the database.
public static final String
UPDATE
The SQL to one-up the current sequence number.
Constructors Summary
Methods Summary
private voidcreateSequence(java.sql.Connection conn, java.lang.String seq)
Creates a new sequence.

param
conn the JDBC connection to use
param
seq the sequence name
throws
java.sql.SQLException a database error occurred


                               
         
          
        PreparedStatement stmt = conn.prepareStatement(INSERT);

        stmt.setString(1, seq);
        stmt.setLong(2, 1L);
        stmt.setLong(3, (new java.util.Date()).getTime());
        stmt.executeUpdate();
public synchronized longgenerate(java.lang.String seq)
Generates a sequence for the specified sequence in accordance with the SequenceGenerator interface.

param
seq the name of the sequence to generate
return
the next value in the sequence
throws
com.imaginary.lwp.SequenceException an error occurred generating the sequence

        Connection conn = null;
        
        try {
            PreparedStatement stmt;
            ResultSet rs;
            long nid, lut, tut;
            
            conn = JDBCTransactionImpl.getJDBCConnection();
            stmt = conn.prepareStatement(SELECT);
            stmt.setString(1, seq);
            rs = stmt.executeQuery();
            if( !rs.next() ) {
                try {
                    createSequence(conn, seq);
                }
                catch( SQLException e ) {
                    String state = e.getSQLState();

                    // if a duplicate was found, retry sequence generation
                    if( state.equalsIgnoreCase("SQL0803N") ) {
                        return generate(seq);
                    }
                    throw new SequenceException("Database error: " +
                                                e.getMessage());
                }
                return 0L;
            }
            nid = rs.getLong(1);
            lut = rs.getLong(2);
            tut = (new java.util.Date()).getTime();
            if( tut == lut ) {
                tut++;
            }
            stmt = conn.prepareStatement(UPDATE);
            stmt.setLong(1, nid+1);
            stmt.setLong(2, tut);
            stmt.setString(3, seq);
            stmt.setLong(4, lut);
            try {
                stmt.executeUpdate();
                conn.commit();
            }
            catch( SQLException e ) {
                String state = e.getSQLState();

                // someone else grabbed the row,
                // we need to try again
                if( state.equals("SQL0100W") ) {
                    return generate(seq);
                }
                throw new SequenceException("Database error: " +
                                            e.getMessage());
            }
            return nid;
        }
        catch( SQLException e ) {
            throw new SequenceException("Database error: " +
                                        e.getMessage());
        }
        finally {
            if( conn != null ) {
                try { conn.close(); }
                catch( SQLException e ) { }
            }
        }