Hibernate Books

All times are UTC - 5 hours [ DST ]

Post new topic Reply to topic  [ 1 post ] 
Author Message
 Post subject: Getting the autogenerated ID when using SQLQuery
PostPosted: Thu Apr 16, 2009 10:32 am 

Joined: Thu Apr 16, 2009 10:06 am
Posts: 1
Hello, I have a problem when trying to get the last inserted ID's for manually exectued SQL queries when using the SQLQuery class.

The table i'm querying gets created at runtime and the columns it contains can change at a moments notice so i can't use traditional pojo/entity classes for this table.

The code listed below works fine when inserting records one at a time but once a decent amount of traffic hits there's a race condition causing the IDs to get mixed up. (the IDs returned go 130, 131, 133, 133 for example rather then 130,131,132,133).

I tried wrapping the thing in a transaction but that causes locking exceptions when there's a decent amount of traffic. That and a transaction shoudn't be needed as I am pretty sure the jdbc returns the correct value.(java.sql.PreparedStatement.getGeneratedKeys()) I'm just not sure how to access this value when using the hibernate SQLQuery class.

Can someone tell me a way to find the id of the record i just inserted that isn't sensitive to race conditions and does perform well with dozens of concurrent updates?

Hibernate version:

Code between sessionFactory.openSession() and session.close():
SQLQuery insertQuery = session.createSQLQuery("INSERT INTO foo VALUES ('bar')");
Integer id = (Integer)session.createSQLQuery("SELECT MAX(id) FROM foo").uniqueResult();

Name and version of the database you are using:
IBM DB2 9.1

Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.