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
To
explain difference between these two methods consider following scenario.
We
need to run two Stored Procedure
- 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.
- 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
