Before
we start let me tell you this is not a best or even say a common practice in
Hibernate but this approach is used when there is a sudden addition/change in
requirements and you for unavoidable reasons do not want to add a new entity or
change existing entities and you want to go ahead with your DTO/VO instead of
Hibernate bean classes. One of the example is you are generating some report
and client wants you to add few more fields that are not mapped to this entity
bean so only option you have is to change your HQL to SQL and load this data in
some DTO/VO and print it.
So
let’s start with it. For the sack of brevity here we will use simple SQL query
but you are free to use any complicated query with this approach. We have a
table with following fields
·
EMP_ID
·
EMP_FIRST_NAME
·
EMP_LAST_NAME
·
EMP_DOB
·
EMP_DOJ (Date of Joining)
·
IS_ACTIVE (If employee is still part of
organization)
So
we have following data in our table.
Now
we want to load data from this table to our VO class EmpUnmanagedVO which looks
like Table 1
Table
1: VO Class
package com.study.hibernate.vo;
import java.util.Date;
public class
EmpUnmanagedVO {
private String id;
private String name;
private String lastname;
private Date dob;
private Date doj;
private boolean isActive;
public String
getId() {
return id;
}
public void setId(String
id) {
this.id = id;
}
public String
getName() {
return name;
}
public void
setName(String name) {
this.name = name;
}
public String
getLastname() {
return lastname;
}
public void
setLastname(String lastname) {
this.lastname = lastname;
}
public Date getDob()
{
return dob;
}
public void setDob(Date
dob) {
this.dob = dob;
}
public Date getDoj()
{
return doj;
}
public void setDoj(Date
doj) {
this.doj = doj;
}
public boolean isActive() {
return isActive;
}
public void setActive(boolean isActive) {
this.isActive = isActive;
}
public String toString()
{
StringBuilder
sbuild = new StringBuilder();
sbuild.append("Id:").append(id).append(",
Name:").append(name).append(", Last Name:").append(lastname);
return sbuild.toString();
}
}
|
Now
we will write our method to call a simple SQL query to get details from
EMP_DETAILS table and load the data in this VO.
@SuppressWarnings("unchecked")
public List<EmpUnmanagedVO>
getUnmanagedEmpDetails() throws DBException {
Session sess = null;
try
{
sess =
HibernateUtil.getSession();
Query qry =
sess.createSQLQuery("SELECT * FROM EMP_DETAILS");
qry.setResultTransformer(new ResultTransformer()
{
public Object
transformTuple(Object[] values, String[] colnames) {
EmpUnmanagedVO
emp = new EmpUnmanagedVO();
emp.setId(String.valueOf(values[0]));
emp.setName(String.valueOf(values[1]));
emp.setLastname(String.valueOf(values[2]));
emp.setDob((Date)values[3]);
emp.setDoj((Date)values[4]);
emp.setActive("Y".equals(String.valueOf(values[5])));
return emp;
}
public List
transformList(List arg0) {
return arg0;
}
});
return qry.list();
}
catch(HibernateException
e)
{
throw new DBException("Error
occured while loading unmanaged bean",e);
}
finally
{
HibernateUtil.closeSession(sess);
}
}
|
So
when we call this method we will have following output.
Feb 28, 2013 7:22:52 AM org.hibernate.annotations.common.Version
<clinit>
INFO: HCANN000001: Hibernate Commons Annotations {4.0.1.Final}
Feb 28, 2013 7:22:52 AM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {4.1.7.Final}
Feb 28, 2013 7:22:52 AM org.hibernate.cfg.Environment
<clinit>
INFO: HHH000206: hibernate.properties not found
Feb 28, 2013 7:22:52 AM org.hibernate.cfg.Environment
buildBytecodeProvider
INFO: HHH000021: Bytecode provider name : javassist
Feb 28, 2013 7:22:52 AM org.hibernate.cfg.Configuration configure
INFO: HHH000043: Configuring from resource: /hibernate.cfg.xml
Feb 28, 2013 7:22:52 AM org.hibernate.cfg.Configuration
getConfigurationInputStream
INFO: HHH000040: Configuration resource: /hibernate.cfg.xml
Feb 28, 2013 7:22:52 AM org.hibernate.cfg.Configuration
doConfigure
INFO: HHH000041: Configured SessionFactory: null
Feb 28, 2013 7:22:52 AM
org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl
configure
INFO: HHH000402: Using Hibernate built-in connection pool (not for
production use!)
Feb 28, 2013 7:22:52 AM org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl
configure
INFO: HHH000115: Hibernate connection pool size: 20
Feb 28, 2013 7:22:52 AM
org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl
configure
INFO: HHH000006: Autocommit mode: false
Feb 28, 2013 7:22:52 AM
org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl
configure
INFO: HHH000401: using driver [com.mysql.jdbc.Driver] at URL
[jdbc:mysql://localhost:3306/test]
Feb 28, 2013 7:22:52 AM
org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl
configure
INFO: HHH000046: Connection properties: {user=root, password=****}
Feb 28, 2013 7:22:52 AM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect:
com.hibernate.custom.dialect.CustomMySQLDialect
Feb 28, 2013 7:22:52 AM
org.hibernate.engine.jdbc.internal.LobCreatorBuilder useContextualLobCreation
INFO: HHH000423: Disabling contextual LOB creation as JDBC driver
reported JDBC version [3] less than 4
Feb 28, 2013 7:22:52 AM
org.hibernate.engine.transaction.internal.TransactionFactoryInitiator
initiateService
INFO: HHH000399: Using default transaction strategy (direct JDBC
transactions)
Feb 28, 2013 7:22:52 AM org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory
<init>
INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate: SELECT * FROM EMP_DETAILS
[Id:A001, Name:Keyur, Last Name:Joshi]
|
There is an option for creating a separate class for
Result Transformer which will implement org.hibernate.transform.ResultTransformer interface
but since this is not what we do as a generic design better we follow inline
implementation of this interface. Also you can use second argument of tranformTuple function (i.e. colnames) to understand what is the index of each column.
Happy Coding J