Menu

#308 Configure how duplicate key is detected

CVS HEAD
closed
nobody
JBossCMP (86)
5
2003-09-09
2002-11-11
No

Duplicate keys are currently detected by issuing a
SELECT statement to check for existance before
performing the INSERT. This has a few issues:

1) it's not watertight as another row could be inserted
between the SELECT and subsequent INSERT. It does
however fail safely (with a CreateException)

2) it requires an extra trip to the database for the SELECT

3) it only checks uniqueness of the PK - checking other
unique constraints would be useful

This patch addresses these issues by allowing the
SELECT to be skipped and by catching the SQLException
from the database. It examines either the SQLState or
ErrorCode contained in the SQLException to see if it is
a constraint violation and if so converts the it to a
DuplicateKeyException.

The standard SQLState for this condition is "23000"
However, as different databases/drivers may return
different values, the values checked can be configured
in the type-mapping for the datasource.

This patch leaves the current mode of operation
unchanged, but includes hooks in
standardjbosscmp-jdbc.xml to enable the new behaviour.

It has been verified on Oracle and MS-SQL Server 2000
(with the MS and Opta drivers).

Discussion

  • Jeremy Boynes

    Jeremy Boynes - 2002-11-11

    jar containing cvs diff -u plus one new class

     
  • Juha Lindfors

    Juha Lindfors - 2003-09-09
    • status: open --> closed
     

Log in to post a comment.