Result Set Mapper for hibernate and JPA

There were times when I had to use an Entity without a primary Key in the DB.

I was trying to get it work thru the Result Set Mapper functionality which was provided for Hibernate. I had faced some issues and here are my alternatives.

Step-1
Create a transformer which extends the org.hibernate.transform.ResultTransformer

import java.lang.reflect.Field;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.PropertyNotFoundException;
import org.hibernate.property.ChainedPropertyAccessor;
import org.hibernate.property.PropertyAccessor;
import org.hibernate.property.PropertyAccessorFactory;
import org.hibernate.property.Setter;
import org.hibernate.transform.ResultTransformer;
public class AdvancedSearchResultTransformer implements ResultTransformer {
 private final Class resultClass;
 private final PropertyAccessor propertyAccessor;
 private Setter[] setters;
 private Field[] fields;

 private static final long serialVersionUID = -3489302728091710568L;

 public AdvancedSearchResultTransformer(Class resultClass) {
  if ( resultClass == null ) {
   throw new IllegalArgumentException( "resultClass cannot be null" );
  }
  this.resultClass = resultClass;
  propertyAccessor = new ChainedPropertyAccessor(
    new PropertyAccessor[] {
      PropertyAccessorFactory.getPropertyAccessor( resultClass, null ),
      PropertyAccessorFactory.getPropertyAccessor( "field" )
    }
  );
 }

 public Object transformTuple(Object[] tuple, String[] aliases) {
  Object result;
  try {
   fields = resultClass.getDeclaredFields();
   if ( setters == null ) {
    setters = new Setter[aliases.length];
    for ( int i = 0; i < aliases.length; i++ ) {
     String alias = aliases[i];
     if ( alias != null ) {
      Setter setter;
      try {
                       setter = propertyAccessor.getSetter(resultClass, alias);
                    } catch (PropertyNotFoundException e) {
                       for (Field field : this.fields) {
                          String fieldName = field.getName();
                          if (fieldName.equalsIgnoreCase(alias)) {
                             alias = fieldName;
                             break;
                          }
                       }
                       setter = propertyAccessor.getSetter(resultClass, alias);
                    }
      setters[i] = setter;
     }
    }
   }
   result = resultClass.newInstance();
   for ( int i = 0; i < aliases.length; i++ ) {
    if ( setters[i] != null ) {
     setters[i].set( result, tuple[i], null );
    }
   }
  }
  catch ( InstantiationException e ) {
   throw new HibernateException( "Could not instantiate resultclass: " + resultClass.getName() );
  }
  catch ( IllegalAccessException e ) {
   throw new HibernateException( "Could not instantiate resultclass: " + resultClass.getName() );
  }
  return result;
 }
 public List transformList(List collection) {
  return collection;
 }
 public int hashCode() {
  int result;
  result = resultClass.hashCode();
  result = 31 * result + propertyAccessor.hashCode();
  return result;
 }
}

Step-2
Look at the place where I catch the PropertyNotFoundException. There is a reason for the same

Step-3
There are 2 ways of implementing the ResultSet Transformer
Create the POJO which resembles the DB Table
Create an SQL Query as

sqlQuery.append(" SELECT column1 as col1, column2 as cl2 ");
sqlQuery.append(" FROM table tbl ");

col1 and col2 should be defined as variables (case sensitive)
private String col1;
private String col2;
in yur POJO with SETTER and GETTER methods.

If you are using Hibernate here is the way

 public List executeReadAllSQLQuery(String queryString, Object[] values,
   Object[] aliasInputs, Class transformerClass) throws ORMException {
  SQLQuery sqlQuery = null;
  try {
   sessionObj = getHibSession();
   sqlQuery = sessionObj.createSQLQuery(queryString);
   sqlQuery.setResultTransformer(new AdvancedSearchResultTransformer(transformerClass));
   if(aliasInputs != null){
    for (int i = 0; i < aliasInputs.length; i++) {
     sqlQuery.addScalar(aliasInputs[i].toString());
    }
   }
   if (values != null) {
    for (int i = 0; i < values.length; i++) {
      sqlQuery.setParameter(i, values[i]);
    }
   }
   return sqlQuery.list();
  } 

If you are using JPA mode then there is n direct way to invoke ResultSetTransformer. So what I have dne is as follows

Query sqlQuery = null;
  try {
   entityMgrObj = getHibJpaEntityManager();
   sqlQuery = entityMgrObj.createNativeQuery(queryString);
   sqlQuery.unwrap(SQLQuery.class).setResultTransformer(new AdvancedSearchResultTransformer(transformerClass));
   if(aliasInputs != null){
    for (int i = 0; i < aliasInputs.length; i++) {
     sqlQuery.unwrap(SQLQuery.class).addScalar(aliasInputs[i].toString());
    }
   }
   if (values != null) {
    for (int i = 0; i < values.length; i++) {
     sqlQuery.setParameter(i, values[i]);
    }
   }
   return sqlQuery.getResultList();
  }

Look at the place where I tweak JPA to forcibly use Hibernate ResultSet Mapper.

Thats all. Now instetad of those annoying Object[] which comes as RESULT SET, we will be able to get POJO objects just like resulr Hibernate / JPA modes.

Happy hunting.

Read more...