Hi,
I'm using a method taken from a BEA WebLogic example to generate new primary
keys for my entity beans.
The example is at
http://www.weblogic.com/docs/examples/ejb/sequence/jdbc/index.html
I don't have the time to explain in detail how the whole thing works, so
this mail is addressed to people who are using a similar approach.
Basically, the method reserves a range of primary keys and updates a
database counter accordingly.
While the primary key range is not exhausted, the primary key allocation
method doesn't go to the database and just returns a new primary key from
the reserved range.
This method reduces the number of database queries.
If, for some reason, there is a rollback just after a new range is reserved
then the database counter is rollbacked to its previous value but the method
still returns primary keys from the reserved range (static variables are a
bad thing ;-) ). From there things start to go bad...
My current workaround is to force the range width to 1, thus loosing all the
benefits of this method.
Other workarounds I imagined are:
- resetting the range when there is a rollback, but I don't like it because
the method will not be self contained anymore.
- using nested transactions (I don't know if it is possible and will need to
investigate).
So, I'm warning people who are using the same approach that they might also
encounter this problem.
Any input or insight on this problem is welcome,
Philippe.
PS: I included the offending method.
My code is different but is derived from this method. I just went a step
further and packaged it into a generic primary key factory class.
I can send you the class if you are interrested, but after reading this mail
I doubt you are.
/**
* This method gets the next account ID. If there are no
* more IDs available in the current range, it goes to the
* database and retreives a new range of IDs.
* The range is set by a parameter in the Deployment Descriptor.
* The current range of available IDs is stored in
* <code>lastKey</code> and <code>maxKey</code>.
*
* @return String Next account ID
* @exception Exception
* if there is a problem getting an account ID
*/
private String getNewAccountId() throws Exception {
if (VERBOSE)
System.out.println("getNewAccountId(); lastKey: " + lastKey + "
maxKey: " + maxKey);
if (lastKey >= maxKey) {
// Go to database and set new values for lastKey and maxKey
if (VERBOSE)
System.out.println("lastKey>=maxKey: getting new range of
accountIds");
Connection con = null;
Statement stat = null;
ResultSet results = null;
String idTableName = getIdTableName();
String tableName = getJdbcProperty("tableName");
int range = getRange();
int dbMaxKey;
int newMaxKey;
try {
con = getConnection();
stat = con.createStatement();
int i;
stat.executeQuery("select maxKey from " + idTableName +
" where tablename = '" + tableName + "'");
results = stat.getResultSet();
if ((results != null) && (results.next())) {
dbMaxKey = results.getInt(1);
newMaxKey = dbMaxKey + range;
i = stat.executeUpdate("update " + idTableName +
" set maxKey = " + new
Integer(newMaxKey).toString() +
" where tablename = '" + tableName + "'");
if (i == 0) {
throw new CreateException ("ejbCreate: idTableName (" +
idTableName + ") not updated");
}
} else {
dbMaxKey = maxKey;
newMaxKey = maxKey + range;
i = stat.executeUpdate("insert into " + idTableName +
" (tablename, maxKey) values ('" +
tableName +
"', " + new Integer(newMaxKey).toString() +
")");
if (i == 0) {
throw new CreateException ("ejbCreate: idTableName (" +
idTableName +
") not inserted");
}
}
lastKey = dbMaxKey;
maxKey = newMaxKey;
}
catch (SQLException sqe) {
throw new CreateException (sqe.getMessage());
}
finally {
if (stat != null)
try { stat.close();} catch (Exception e) {}
if (con != null)
try { con.close();} catch (Exception e) {}
if (results != null)
try { results.close();} catch (Exception e) {}
}
}
return new Integer(++lastKey).toString();
}
Philippe Renon
d�calog - 5, rue Bachaumont - 75002 PARIS
Voice : +33 (0) 1 40 28 44 64
Fax : +33 (0) 1 40 28 24 54
E-mail : mailto:[EMAIL PROTECTED]
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
