Hibernate 3 SQL update using createSQLQuery?

Posted on 2005-04-07
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
    LVL 4

    Expert Comment

    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

    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

    sorry: replace query.setLockMode(query, LockMode.UPGRADE); with query.setLockMode("pick_a_name", LockMode.UPGRADE);

    Author Comment

    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

    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

    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

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Suggested Solutions

    Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
    Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
    Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
    Video by: Michael
    Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now