Hibernate 3 SQL update using createSQLQuery?

Posted on 2005-04-07
Medium Priority
Last Modified: 2008-01-09
I want to execute a SQL UPDATE using createSQLQuery. However, when I run executeUpate() it gives the exception:
    java.lang.RuntimeException: java.lang.UnsupportedOperationException: Update queries only supported through HQL

The code is as follows:
        Session s = getSession();
        Query query = s.createSQLQuery("UPDATE know.ObjTypeCnt set cnt=cnt+:cntToAdd where objType=:objType");

I have to do this in SQL because I need to have the cnt column updated atomically and doing it this way is easier than figuring out how to use the hibernate row locking stuff.
Question by:HappyEngineer
  • 3
  • 2

Expert Comment

ID: 13725447
You can not use a "UPDATE SQL"-Query in Hibernate.
You Objects and the Database would get out of sync. For Example, an unmodified transient object has different values from its persistent counterpart. But hibernate would not detect this.
LVL 15

Expert Comment

ID: 13730090
I think you will need to get the object using a query (where objType=:objType)  with select for update
then modify its attributes and then call update (2 DB operation instead of 1).

Something like that:
Query suery = s.createQuery("from know.ObjTypeCnt foo where foo.objType=:objType");
query.setLockMode(query, LockMode.UPGRADE);
<the_object_type> x = query.list().get(0);
LVL 15

Expert Comment

ID: 13730231
sorry: replace query.setLockMode(query, LockMode.UPGRADE); with query.setLockMode("pick_a_name", LockMode.UPGRADE);
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 13749338
What if the object doesn't exist? I suppose in that case the query returns null and I just have to create a new one, save it, and if an exception occurs then do the update again.

What if two servers access this table simultaneously? Will the row be locked? If row locking is occurring, how do I release it if an exception occurs in my code? If I have autocommit turned off for anything then I always wrap it in a try{}finally so that I ensure that I always turn autocommit back on. I'd want to make sure to release the row here if it's necessary to do that.
LVL 15

Accepted Solution

aozarov earned 1500 total points
ID: 13749745
If the object does not exists the query will return an empy list (the code above doesn't check for that).
This query will be a "select for update" query (applies write lock on the selected records) and therefore if there is another thread trying to access the same logic it will be locked until the first transaction has commited.
If you are running hibernate in a non-managed mode (outside of the ejb container) then when closing the session (which always should
be in a finaly block) will also close the transaction and that will release the lock from the DB.

Author Comment

ID: 13913337
I'm just doing it with a trigger in the DB. It's not worth trying to do it client side if I have to go through hoops to get it to work in a multi-server environment.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses
Course of the Month16 days, 23 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question