Dec 8, 2012

doWork in Hibernate


Hibernate 4 onwards method connection() for fetching java.sql.Connection is removed (in later versions of Hibernate 3 it was deprecated). Instead of getting connection from Hibernate Session we need to use two new methods provided by Hibernate depending on whether you we need to return some value after performing JDBC operation. These two methods are
  • doWork
  • doReturningWork
To explain difference between these two methods consider following scenario.
We need to run two Stored Procedure
  1. Run a procedure to generate salary data for the organization and return an ARRAY to Java code which will in turn generate salary sleeps in PDF format and a mail API will send this information to employees. So here we will use doReturingWork of HibernateSession.
  2. Run a procedure which will Perform Bank’s EOD (End Of Day) operations and will not return anything in case of Successful execution but will store cause of Error in some table for audit purpose. Here we are not expecting any value from Stored Procedure so we will use doWork method of Hibernate Session.
For simplicity we will use much simpler examples here to understand working of these two methods.
Hibernate doWork()
Let’s say we have a table EMP_DETAILS as shown in Figure 1
Figure 1

And we have a list of Employees who have moved out of company and their id is passed in a java.util.List. We want to set IS_ACTIVE for these employees to ’N’.  So we will call doWork method on Hibernate Session. This method will accept single argument of org.hibernate.jdbc.Work interface.
Interface org.hibernate.jdbc.Work has single method
public void execute(Connection arg0) throws SQLException
So we have two options
1)      Implement this method within the call
2)      Create a separate class that will implement this method and pass this class in doWork method while calling from Session.
We will use first approach. So first we write a method that will accept list of Employee IDs for which we want to set IS_ACTIVE to false.

public void updateEmployeeStatus(final List<String> employeeList) throws DBException
{
       Session session = null;
       try
       {
              session = HibernateUtil.getSession();
              session.beginTransaction();
              session.doWork(new Work() {
                     @Override
                     public void execute(Connection conn) throws SQLException {
                           PreparedStatement pStmt = null;
                           try
                           {
                                  String sqlQry = "UPDATE EMP_DETAILS set IS_ACTIVE='N' WHERE EMP_ID=?";
                                  pStmt = conn.prepareStatement(sqlQry);
                                  for(String empId:employeeList)
                                  {
                                         pStmt.setString(1, empId);
                                         pStmt.addBatch();
                                  }
                                  pStmt.executeBatch();
                           }
                           finally
                           {
                                  pStmt.close();
                           }                                
                     }
              });
              session.getTransaction().commit();
       }
       catch(HibernateException e)
       {
              throw new DBException("Error occured while updating Employee Status",e);
       }
       finally
       {
              HibernateUtil.closeSession(session);
       }            
}

And then we will call this method with list of Employee IDs.
List<String> employeeList = new ArrayList<>();
employeeList.add("A001");
updateEmployeeStatus(employeeList);


Now we are done with doWork so let’s return something after performing JDBC operation with the help of doReturingWork
Hibernate doReturningWork()
So now let’s reconsider previous example with a slight change. We just do not want to update records but also want to get number of records updated (assuming a scenario where we have few employee Ids missing in our table so by end of the day we want to compare number of records passed in List and number of records updated and count returned from DAO.
So we need to change our function as follows
public int updateEmployeeStatusWithCount(final List<String> employeeList) throws DBException
{
       Session session = null;
       try
       {
              session = HibernateUtil.getSession();
              session.beginTransaction();
              int cnt = session.doReturningWork(new ReturningWork<Integer>() {
                     @Override
                     public Integer execute(Connection conn) throws SQLException {
                           PreparedStatement pStmt = null;
                           try
                           {
                                  int updatedCnt = 0;
                                  String sqlQry = "UPDATE EMP_DETAILS set IS_ACTIVE='N' WHERE EMP_ID=?";
                                  pStmt = conn.prepareStatement(sqlQry);
                                  for(String empId:employeeList)
                                  {
                                         System.out.println(empId);
                                         pStmt.setString(1, empId);
                                         int cnt = pStmt.executeUpdate();
                                         updatedCnt+=cnt;
                                  }
                                  return updatedCnt;
                           }
                           finally
                           {
                                  pStmt.close();
                           }                                
                     }
              });
              session.getTransaction().commit();
              return cnt;
       }
       catch(HibernateException e)
       {
              throw new DBException("Error occured while updating Employee Status",e);
       }
       finally
       {
              HibernateUtil.closeSession(session);
       }            
}
                                                                                                                                                                                                                                                        
We will call our function slightly in a different way as shown below.
List<String> employeeList = new ArrayList<>();
employeeList.add("A001");
employeeList.add("A002");
int cnt = dao.updateEmployeeStatusWithCount(employeeList);
if(employeeList.size()!=cnt)
{
       System.out.println("Number of ids passed and number of records update not matching.");
}

So this will give us output as shown.
INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate: UPDATE EMP_DETAILS set IS_ACTIVE='N' WHERE EMP_ID=?
A001
A002
Number of ids passed and number of records update not matching

So this works…Happy coding J

Same blog is available on my  blogging site