CODEWITHSUNDEEP
mysqlhibernate
shailesh
shailesh

Reputation: 75

hibernate multiple update queries single transaction

I am using hibernate with mysql db but having trouble with multiple updates in single transaction

Here is my code

public void  updateOEMUser(OEMUserDetailsDTO userDTO) throws Exception{
        Session session = GCCPersistenceMangerFactory.getSessionFactory().openSession();
    Transaction tx = null;
    try{
        String query = "update oemuserdata set full_name=\""+userDTO.getFullName()+ "\" ,contact_no=\""+userDTO.getContactNo() + "\" where user_id="+userDTO.getUserId();
        String query1 = "update usermasterdata set account_status="+userDTO.getAccountStatus() + " ,user_name=\"" + userDTO.getUserName() + "\" where user_id="+userDTO.getUserId();
        Query q = session.createSQLQuery(query);
        Query q1 = session.createSQLQuery(query1);
        tx = session.beginTransaction();
        q.executeUpdate();
        q1.executeUpdate();
        tx.commit();
    }catch (HibernateException e) {
        if (tx!=null) tx.rollback();
        e.printStackTrace();
        throw new RuntimeException("HibernateException_updateOEMUser");
    }finally {
        session.close(); 
    }
}

The code works but when I make "q1.executeUpdate()" fail the record in "oemuserdata" is getting locked in Mysql.

Am I doing something wrong?

Upvotes: 0

Views: 2625

Answers (2)

shailesh
shailesh

Reputation: 75

Ok I didin't find the solution but I found out what's happening here.

When I remove the connection after successful execution of q.executeUpdate() exception is thrown at the execution of q1.executeUpdate() and after catch block it goes in final block and tries to execute session.close(), but since there is no DB connectivity this also fails.

But Mysql still has the lock requested by q.executeUpdate(). That's why the row is locked until Mysql releases lock itself.

Upvotes: 0

Vinayak Pingale
Vinayak Pingale

Reputation: 1315

Try using such kind of pattern.

StringBuilder query = new StringBuilder();
query.append("UPDATE  CLASSNAME_FIRST a ,");
query.append("CLASSNAME_SECOND b,");
query.append("SET a.full_name='"+userDTO.getFullName()",");
.....

int var = stmt.executeUpdate(query);

Upvotes: 1

Related Questions