I've been using CMP. I don't use the counter.jar, instead, I've got a
stored procedure in SQL Server that I call in the ejbCreate method like this
(note that there is no code here that actually creates the record in the
database, I let the container do that):
public Long ejbCreate(String firstName, String lastName,
String loginName, String loginPassword)
throws CreateException, RemoteException,
DataIntegrityException {
// Assign values to the entity's fields.
// These setter methods will throw a DataIntegrityException if the
// values do not meet our data integrity constraints.
setFirstName(firstName);
setLastName(lastName);
setLoginName(loginName);
setLoginPassword(loginPassword);
setLoginBlocked("N");
// Now, get the StaffMemberID from the database stored procedure.
// Start by getting the DataSource object for the database connection.
Context initCtx = null;
try { // to get the initial context.
initCtx = new InitialContext();
} catch (Exception e) {
throw new RemoteException("Caught \"" + e.getClass().getName() +
"\" while attempting to create the initial JNDI context.",
e);
}
String dsName = "jdbc/MSSQLTimecardDS";
DataSource ds = null;
try { // to lookup the data source.
ds = (DataSource) initCtx.lookup(dsName);
} catch (Exception e) {
throw new RemoteException("Caught \"" + e.getClass().getName() +
"\" while attempting to lookup the DataSource \"" +
dsName + "\".", e);
}
// Call the sp_GetNextSequenceNumber stored procedure to get the
// StaffMemberID.
Connection conn = null;
CallableStatement cstmt = null;
String command = null;
try { // to make the connection and call the stored procedure.
conn = ds.getConnection();
command = "{call sp_GetNextSequenceNumber(?, ?)}";
cstmt = conn.prepareCall(command);
cstmt.setString(1, "Staff_Member");
cstmt.registerOutParameter(2, Types.NUMERIC);
cstmt.execute();
// !! Here is the primary key assignment !!
this.staffMemberID = new Long(cstmt.getLong(2));
} catch (Exception e) {
throw new RemoteException("Caught \"" + e.getClass().getName() +
"\" while attempting to get the new StaffMemberID with \"" +
command + "\".", e);
} finally {
try { // to close the callable statement.
if (cstmt != null) cstmt.close();
} catch (Exception e) {}
try { // to close the connection.
if (conn != null) conn.close();
} catch (Exception e) {}
}
// Return the primary key object.
return this.staffMemberID;
}
The stored procedure pulls the next sequence number from the Sequence_Number
table that looks like this:
TABLE_NAME SEQUENCE_NUMBER
-------------------------------
Staff_Member 456
Security_Role 178
.
.
.
Here's the stored procedure:
CREATE PROCEDURE [sp_GetNextSequenceNumber]
(@table varchar(50),
@seq_number int OUTPUT)
AS
BEGIN
DECLARE @incr_number [int]
BEGIN TRANSACTION
SELECT @seq_number = [sequence_number], @incr_number = [increment]
FROM [Timecard].[dbo].[Sequence_Numbers] WITH (UPDLOCK)
WHERE [table_name] = @table
SET @seq_number = @seq_number + @incr_number
UPDATE [Timecard].[dbo].[Sequence_Numbers]
SET [sequence_number] = @seq_number
WHERE [table_name] = @table
COMMIT TRANSACTION
END
The key to this is to serialize access and update of the current sequence
number (which I'm guessing the counter.jar solution gets around by not
guaranteeing that the records will be numbered sequentially). In the stored
procedure this is accomplished with the "WITH (UPDLOCK)" clause in the first
select query. This locks the selected record until the transaction is
completed, that is, until the sequence_number field has been updated with
it's next value. The "WITH (UPDLOCK)" is a SQL "hint" that is specific to
SQL Server, but other databases have other ways to accomplish an update lock
on selected records. In Oracle you do a "SELECT FOR UPDATE" (of course, in
Oracle you would just use a sequence object to provide this functionality,
unless you wanted the records numbered sequentially).
This is an old, well established, database-centric solution that has worked
very nicely in my environment, however, it may be that the counter.jar
solution would give better performance because it doesn't require a database
round-trip. Of course, the significance of this improvement would be
dependent on the application's usage and architecture (are we talking about
clustered EJB containers? if so, does each container run its own counters?
how is uniqueness of numbers guaranteed?). We can conjecture about the
relative performance merits of the two solutions, but ultimately only
testing of a specific workload with a specific application in a specific
environment would tell us for sure.
-=michael=-
======================================================================
Michael S. Kelly ____ _
Axian, Inc. // |_ __(_) ___ _ __
4800 SW Griffith Dr., Ste. 202 //| |\\/ /| |/ _ \| '_ \
Beaverton, OR 97005 USA _____//_| | / / | | |_| | | | |
Voice: (503)644-6106 x122 (( // |_|/_/\\|_|\_/|_|_| |_|
Fax: (503)643-8425 ``-'' ``-''
http://www2.axian.com Software Consulting and Training
mailto:[EMAIL PROTECTED] <Enterprise and Internet Group>
======================================================================
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Juan Lorandi
(Chile)
Sent: Tuesday, January 30, 2001 10:17 AM
To: Orion-Interest
Subject: RE: When using autonumber for the primarykey...
but of course Krista, that would only work with BMP wouldn't it?
there's an article on orionsupport regarding counter.jar and how you can use
it to GENERATE unique id's
if you are required to use an IDENTITY field on the db, you're stuck with
BMP
JP
-----Original Message-----
From: Krista Richmond [mailto:[EMAIL PROTECTED]]
Sent: Martes, 30 de Enero de 2001 12:03
To: Orion-Interest
Subject: RE: When using autonumber for the primarykey...
If it's actually an autonumber field (at least with SQL Server), just insert
the rest of the fields (not including the autonumbered field), e.g.
(presuming your table includes First_Name,Last_Name, and an
autonumber...say, a Customer_ID field):
"INSERT INTO tblMyData(First_Name,Last_Name) VALUES('Sam','Adams')"
The table will automatically generate the autonumber for the ID field, hence
the name ;-). Additionally, if you *try* to insert a value into the
autonumber field, SQL Server will generate an error. I'm not sure whether
there may be different behaviors in different DBs...anyone?
HTH
-----Original Message-----
From: Øystein Walle [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 30, 2001 3:37 AM
To: Orion-Interest
Subject: When using autonumber for the primarykey...
I'm just wondering what to do when inserting into a table where the
primarykey has incremental int type?
Is this possible?
Please help.
Walle
