Java Reference
In-Depth Information
when you're inserting a record into a database if you need to know the primary
key after the insert completes.
Most database vendors provide a way to determine the last-generated key for
the current session using standard SQL to facilitate using this functionality from a
stored procedure. Several database vendors (including Oracle and Postgre SQL )
also provide a way to generate identity values without inserting a column. Also,
with JDBC 3.0 , the API was modified to allow fetching generated keys when insert-
ing data.
If you design your database to use generated primary keys, you can use i BATIS
to get those generated keys back into your object by using the <selectKey> ele-
ment, which is a special child element of the <insert> element. There are two pat-
terns you can follow with this approach, and the choice will be driven by the key-
generation technique you are using.
The first approach is to fetch the key after you have inserted the record and
the database has generated the key. Be aware that you will need to ensure that the
driver you are using is guaranteed to return the key generated by the last insert
statement you performed. For example, if two threads execute insert statements
nearly simultaneously, the order of the execution could be [insert for user #1] ,
[insert for user #2] , [selectKey for user #1] , [selectKey for user #2] . If the
driver simply returns the last-generated key (globally), then the [selectKey for
user #1] will get the generated key for user #2, which would wreak havoc in the
application. Most drivers should work fine for this, but be sure to test this if you
are not absolutely certain whether yours does or not. Also, be aware that triggers
may also cause problems with this approach. For example, with Microsoft SQL
Server, the @@identity value is affected by triggers, so if you insert a record into a
table with a trigger that inserts a record that also generates a key value, the value
returned by @@identity will be the generated key for the record inserted by the
trigger, not the generated key for the first record you inserted. In that case, you
would want to use the SCOPE_IDENTITY function instead.
The second approach to consider is fetching the key before you insert the
record. This method entails more work if you are inserting records using an inter-
active database tool, because you have to allocate a key value before inserting a
record. However, this strategy avoids the potential risks associated with threading
and getting the key after it has been inserted, and is generally the safest since it
requires very few assumptions in the code. With the first method, there are poten-
tial issues that could cause things to not work as expected. With this approach, all
we need is a guarantee that the generated key is unique when we get it. We do not
Search WWH ::




Custom Search