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 {
if (rs != null) {
if (outputClass.isAnnotationPresent(Entity.class)) {
ResultSetMetaData rsmd = rs.getMetaData();
Field[] fields = outputClass.getDeclaredFields();
while (rs.next()) {
T bean = (T) outputClass.newInstance();
for (int _iterator = 0; _iterator < rsmd
.getColumnCount(); _iterator++) {
String columnName = rsmd
.getColumnName(_iterator + 1);
Object columnValue = rs.getObject(_iterator + 1);
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 {
}
} 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;
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();
List<SamplePojo> pojoList = resultSetMapper.mapRersultSetToObject(resultSet, SamplePojo.class);
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.