The is just a question to see if Hibernate will handle something for me or if I need to change my code and/or database.

I have an Oracle table named Location with two fields: LocationID (Long primary key generated by an Oracle Sequence) and LocationName (Varchar2). The LocationName field should always be unique.

I have a MessageDrivenBean (MDB) in Java that handles processing the messages that arrive on a JMS queue. These messages will contain Location data and will either insert a Location record into the DB or use an existing one if the Location already exists. I have a test set up where 5 messages are sent to the Queue, each with the same Location data. At the time the messages are sent, the Location in the messages does not yet exist in Oracle. What I would expect to happen is that the first message processed would result in a new Location being inserted into the DB, and that the following 4 messages would then find this new record in the DB and use it instead of inserting more Location records. This is not what happens. As it turns out, the messages are all processed more or less simultaneously and they all insert new Location records. Each inserted record has a unique sequence-generated LocationID but have the same LocationName (which is a bad thing).

The code I use in my MDB is something like:

1) Based upon the LocationName in the message, find the matching record in the Location table.
2) If I get no record back from step 1 then assume this is a new Location. Create a new Location record and call saveOrUpdate to push it to the DB.


What I need to do is find a way to make sure that duplicate records do NOT get inserted. I can add a unique constraint to the LocationName field in the DB but then Hibernate just throws an error. Do I need to catch this error and then use it as my “trigger” to re-look for the existing Location record in the DB?

Thanks for the help

I would suggest that you select all the location records before processing any message, and use Hibernate's second-level cache. And also use locationName(Business unique key) as id to distinguish each record. If no existing record found, the record needs to be inserted to the DB as well as populated to the cache(This part needs to be synchronized.).

I'm afraid that is what you need to do, unless you want the first MDB to get an exclusive lock on the whole table, which defeats the whole point of using MDBs.