利用注解將JDBC結果集轉成Java對象

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";
}

}</pre> 


 ResultSetMapper.java 

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;
}

}</pre> 


 使用方法 

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();
}

}

}</pre>