Mapping JDBC ResultSet to Object using Annotations

Introduction

This tip demonstrates how JDBC ResultSet can be mapped to an Object using Annotations

Background 

This tip uses Annotations and Reflection. So a basic knowledge of these will be helpful. 

Using the code  

First we need an Annotated POJO (Plain Old Java Object) to hold values retrieved in ResultSet

The attribute name can be anything, the annotation 'column' specifies the SQL column name. The annotation 'Entity' is required to mark the class capable of hold database values

Below is a SamplePojo.java

import javax.persistence.Column;
import javax.persistence.Entity;

@Entity
public class SamplePojo {
	@Column(name="User_Id")
	private int id;
	@Column(name="User_Name")
	private String name;
	@Column(name="Address")
	private String address;
	@Column(name="Gender")
	private boolean gender;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public boolean isGender() {
		return gender;
	}
	public void setGender(boolean gender) {
		this.gender = gender;
	}
	
	@Override
	public String toString() {
		return 	"id: " + id + "\n" + 
				"name: " + name + "\n"+
				"address: " + address + "\n" +
				"gender: " + (gender ? "Male" : "Female") + "\n\n";
	}
}

Code to map ResultSet to Object - ResultSetMapper.java

 Below is the code that maps the ResultSet to an Object of this SamplePojo and returns ArrayList containing SamplePojo objects  

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;

import org.apache.commons.beanutils.BeanUtils;

public class ResultSetMapper<T> {
	@SuppressWarnings("unchecked")
	public List<T> mapRersultSetToObject(ResultSet rs, Class outputClass) {
		List<T> outputList = null;
		try {
			// make sure resultset is not null
			if (rs != null) {
				// check if outputClass has 'Entity' annotation
				if (outputClass.isAnnotationPresent(Entity.class)) {
					// get the resultset metadata
					ResultSetMetaData rsmd = rs.getMetaData();
					// get all the attributes of outputClass
					Field[] fields = outputClass.getDeclaredFields();
					while (rs.next()) {
						T bean = (T) outputClass.newInstance();
						for (int _iterator = 0; _iterator < rsmd
								.getColumnCount(); _iterator++) {
							// getting the SQL column name
							String columnName = rsmd
									.getColumnName(_iterator + 1);
							// reading the value of the SQL column
							Object columnValue = rs.getObject(_iterator + 1);
							// iterating over outputClass attributes to check if any attribute has 'Column' annotation with matching 'name' value
							for (Field field : fields) {
								if (field.isAnnotationPresent(Column.class)) {
									Column column = field
											.getAnnotation(Column.class);
									if (column.name().equalsIgnoreCase(
											columnName)
											&& columnValue != null) {
										BeanUtils.setProperty(bean, field
												.getName(), columnValue);
										break;
									}
								}
							}
						}
						if (outputList == null) {
							outputList = new ArrayList<T>();
						}
						outputList.add(bean);
					}

				} else {
					// throw some error
				}
			} else {
				return null;
			}
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		return outputList;
	}
}

The function  mapRersultSetToObject maps resultset to an ArrayList of type SamplePojo.  It first checks if the outputClass has the 'Entity' annotation. The core logic of this function is that, it gets the attributes in the outputClass having annotation 'Column'. If this attribute has the 'Column' annotation with 'name' value same as that of SQL column name, the value of the SQL column is set to the current attribute in outputClass.

Using the ResultSetMapper class 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class SampleMain {
	public static void main(String ...args){

	try {
		ResultSetMapper<SamplePojo> resultSetMapper = new ResultSetMapper<SamplePojo>();
		ResultSet resultSet = null;
		// simple JDBC code to run SQL query and populate resultSet - START
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String database = "jdbc:odbc:AkDb"; 
		Connection connection = DriverManager.getConnection( database ,"","");
		PreparedStatement statement = connection.prepareStatement("SELECT * FROM UsersSample");
		resultSet = statement.executeQuery();
		// simple JDBC code to run SQL query and populate resultSet - END
		List<SamplePojo> pojoList = resultSetMapper.mapRersultSetToObject(resultSet, SamplePojo.class);
		// print out the list retrieved from database
		if(pojoList != null){
			for(SamplePojo pojo : pojoList){
				System.out.println(pojo);
			}
		}else{
			System.out.println("ResultSet is empty. Please check if database table is empty");
		}
		connection.close();
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	
	}
}

Points of Interest 

Without using any framework (ibatis/hibernate etc), the pain of mapping individual columns from ResultSet can be avoided. If you want to map more columns, just add more attributes to the POJO, annotate them with correct column names and you are done !

 If the query is complex, may be very complex containing lot of joins on lot of columns, just use SQL alias to rename each column with a unique name and use this column alias while annotating the POJO

Please feel free to share your comments, complements or complaints.