Java ResultSet Mapping Utility

Most of the time when we are not using any ORM tool like JPA or Hibernate or Toplink for database access; we will be writing lot of code for iterating through ResultSet and populating our DTO objects. Here I am going to introduce a utility for doing this.

Most of the our DAO classes will return either an Object or a Collection of Objects. So in my mapping utility I am going to give two methods - toObject and toList.

This utility doesn't do any object mapping to database mapping. We can use this only for creating application specific DTO from our ResultSet.

Now look how a typical DAO class look like:

public class EmployeeDetailsDAO {
 public EmployeeDetailsVO getEmployeeDetails(Long employeeId) {
  EmployeeDetailsVO emp = null;
  PreparedStatement stat = null;
  Connection con = null;
  ResultSet rs = null;
  try {
   con = datasource.getConnection();
   stat = con.prepareStatement("Query to Fetch Employee Details");
   stat.setLong(1, Long.valueOf(employeeId));
   rs = stat.executeQuery();

   if(rs.next()) {
    emp = new EmployeeDetailsVO();
    // Code to populate EmployeeDetailsVO from ResultSet
    ......
    .....
   }
  } catch (SQLException e) {
   throw new DAOException(e);
  } finally {
   closeAll(rs, stat, con);
  }
  
  return emp;
 }
}

Here suppose if you are fetching like 25 columns, then you will have to write atleast 25 lines of code like

YourDTO.setField(ResultSet.getField())

which is quiet annoying.

The Basic idea of my utility is very simple:

• First get all the fields in your DTO class using java Refletction.
• Iterate through each field.
• Get the name and Type of the Field.
• Using the name and type of the field call ResultSet.get<DTO FieldType>.
• Set the value to the corresponsing DTO class object

But there are some problems with this approach.

• It is not always a good idea to name your DTO instance variables same as database column names.
• Not always database column types match your DTO types.
• Not required that all your DTO instance variable have to be mapped to ResultSet

First problem can be solved by using alias names in the query. But I feel like it is not a good idea. If I am the only one who is going to this utility I can live with alias names. But solution to all these problems are there in all the ORM tools. Use Custom Annotation!

For name and type problem I am going to create a custom annotation Column.

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Column {
 String name() default "";
 Class type() default Object.class;
}

and for not mapping instance variable with ResultSet I decided to go with the same Transient annotation available as part of JPA.

Take a loot at a typical DTO object.

public class EmployeeDetailsVO implements Serializable {

 private static final long serialVersionUID = 4865243695898605677L;

 @Column(name="emp_id)
 private Long empno;

 @column(name="dept_id",type=String.class)
 private String deptno;
 
 private String fname,lname,address1,city,
  state,zip,busphone,busext,title,internetadd,deptName;
  
 @Transient
 private String formattedEmpId;

 .......................
 Geters and Setter will go here
 .....................
}

In the above example you can see empno and deptno field is annotated with Column. empno is annotated because of the name change and deptno is annotated to handle name as well as type difference. You can see formattedEmpId filed is annotated with Transient, so Mapper will ignore this field when doing the mapping.

Now the real ResultSetMapper class:

public class ResultsetMapper<T> {
 
 @SuppressWarnings("unchecked")
 private static ResultsetMapper thisInstance;
 
 @SuppressWarnings("unchecked")
 public static <T> ResultsetMapper<T> getInstance() {
  if(thisInstance == null) {
   synchronized (ResultsetMapper.class) {
    if(thisInstance == null)
     thisInstance = new ResultsetMapper<T>();
   }
  }
  return thisInstance;
 }
 
 private ResultsetMapper() { }

 public T toObject(ResultSet rs, Class<T> toClazz) {
  T obj = null;
  Field[] fields = toClazz.getDeclaredFields();
  try {
   if(rs.next() && fields != null && fields.length > 0) {
    obj = toClazz.newInstance();
    populateFields(rs, obj, fields);
   }
  } catch (Exception e) {
   e.printStackTrace();
   throw new CustomException(e);
  }
  
  return obj;
 }
 
 public List<T> toList(ResultSet rs, Class<T> toClazz) {
  List<T> listObj = Lists.newLinkedList();
  T obj = null; 
  Field[] fields = toClazz.getDeclaredFields();
  if(fields == null || fields.length == 0) return listObj;
  try {
   while(rs.next()) {
    obj = toClazz.newInstance();
    populateFields(rs, obj, fields);
    listObj.add(obj);
   }
  } catch (Exception e) {
   e.printStackTrace();
   throw new CustomException(e);
  }
  
  return listObj;
 }
 

 private void populateFields(ResultSet rs, T obj, Field[] fields)
   throws IllegalAccessException, SQLException {
  String fieldName = null;
  for(Field f : fields) {
   if("serialVersionUID".equals(f.getName())) continue;
   f.setAccessible(true);
   fieldName = f.getName();
   Class fieldType = f.getType();
   Annotation[] annotations = f.getDeclaredAnnotations();
   if(annotations != null && annotations.length > 0) {
    if(annotations[0].annotationType() == Column.class) {
     Column colAnn = (Column) annotations[0];
     fieldName = colAnn.name();
     if(colAnn.type() != Object.class)
      fieldType = colAnn.type();
    } else if(annotations[0].annotationType() == Transient.class) continue;
   }
   if(fieldType == Long.class) 
    f.set(obj, rs.getLong(fieldName));
   
   if(fieldType == String.class) 
    f.set(obj, rs.getString(fieldName));
   
   if(fieldType == Integer.class) 
    f.set(obj, rs.getInt(fieldName));
   
   if(fieldType == Double.class) 
    f.set(obj, rs.getDouble(fieldName));
   
   if(fieldType == Date.class) {
    java.sql.Date sqlDate = rs.getDate(fieldName);
    f.set(obj, new Date(sqlDate.getTime()));
   }
   if(fieldType == BigDecimal.class) 
    f.set(obj, rs.getBigDecimal(fieldName));
   
   if(fieldType == Boolean.class) 
    f.set(obj, rs.getBoolean(fieldName));
   
   if(fieldType == Float.class) 
    f.set(obj, rs.getFloat(fieldName));
   
   if(fieldType == Short.class) 
    f.set(obj, rs.getShort(fieldName));
   
   if(fieldType == Timestamp.class) 
    f.set(obj, rs.getTimestamp(fieldName));
  }
 }
}

There are certain limitations for this utility.

• You cannot use primitive types for your DTO fields. Always use wrapper classes. Java5 AutoBoxing feature will minimize this effort.
• Wil support only certain types - Integer, Float, Long, String, BigDecimal, Boolean, Shot, TimeStamp and Date.

Now will look at our old EmployeeDetailsDAO.

public class EmployeeDetailsDAO {
 public EmployeeDetailsVO getEmployeeDetails(Long employeeId) {
  EmployeeDetailsVO emp = null;
  PreparedStatement stat = null;
  Connection con = null;
  ResultSet rs = null;
  try {
   con = datasource.getConnection();
   stat = con.prepareStatement("Query to fetch employee details");
   stat.setLong(1, Long.valueOf(employeeId));
   rs = stat.executeQuery();
   ResultsetMapper mapper = ResultsetMapper.getInstance();
   emp = mapper.toObject(rs, EmployeeDetailsVO.class);
  } catch (SQLException e) {
   throw new DAOAppException(e);
  } finally {
   closeAll(rs, stat, con);
  }
  
  return emp;
 }
}

Now look at another DAO which returns a List.

public class BranchDAO {

 public List<BranchVO> getAllBranches() throws DaoException {
  List<BranchVO> branches = null;
  PreparedStatement stat = null;
  Connection con = null;
  ResultSet rs = null;
  try {
   con = datasource.getConnection();
   stat = con.createStatement();
   rs = stat.executeQuery("get all Branches.query");
   
   ResultsetMapper<BranchVO> mapper = ResultsetMapper.getInstance();
   branches = mapper.toList(rs, BranchVO.class);
  } catch (SQLException e) {
   throw new DaoException(e);
  } finally {
   this.closeAll();
  }
  return branches;
 }
}