使用JDBC编写通用的查询方法

实现的效果：
通过传入SQL语句和参数值，返回相应的实体类,而又不依赖实体类。也即模拟MyBatis结果集与相应的实体类的映射。

思路：
- SQL查询参数可以使用可变参数列表。
- 利用反射来设置实体类的相应的字段。

public static <T> List<T> query(Class<T> tClass, String sql, Object ...args) {
        T entity = null;
        List<T> list = null;
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;

        try {
            conn = getConnection();
            statement = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                statement.setObject(i + 1, args[i]);
            }
            rs = statement.executeQuery();

            //创建 ResultSetMetaData 来获得数据库表的字段名等元数据
            ResultSetMetaData metaData = rs.getMetaData();
            // 创建一个 Map<String, Object> 对象
            // 键: SQL 查询的列的别名
            // 值: SQL 查询的列的值
            Map<String, Object> map = new HashMap<>();
            //保存查询得到的数据
            list = new ArrayList<>();

            while (rs.next()) {
                map.clear();
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Object columnValue = rs.getObject(columnLabel);
                    map.put(columnLabel, columnValue);
                }
                //若 Map 不为空集, 利用反射创建 tClass 对应的对象
                if (!map.isEmpty()) {
                    try {
                        entity = tClass.newInstance();
                        //遍历 Map 对象, 利用反射为 Class 对象的对应的属性赋值
                        for (Map.Entry<String, Object> entry : map.entrySet()) {
                            String name = entry.getKey();
                            Object value = entry.getValue();
                            try {
                                //利用反射设置实体类的字段
                                Field field = tClass.getDeclaredField(name);
                                field.setAccessible(true);
                                field.set(entity, value);
                            } catch (NoSuchFieldException e) {
                                e.printStackTrace();
                            }
                        }
                    } catch (InstantiationException | IllegalAccessException e) {
                        e.printStackTrace();
                    }
                    list.add(entity);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeQuietly(conn, statement, rs);
        }
        return list;
    }

对应的Person实体类：

package com.xiya.entity;

import java.util.Date;

/**
 * Created by N3verL4nd on 2017/4/17.
 */
public class Person {
    private int id;
    private String name;
    private int age;
    private Date birth;
    private String email;

    public Person() {
    }

    public Person(int id, String name, int age, Date birth, String email) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.birth = birth;
        this.email = email;
    }

    public Person(String name, int age, Date birth, String email) {
        this.name = name;
        this.age = age;
        this.birth = birth;
        this.email = email;
    }

    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 int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", birth=" + birth +
                ", email='" + email + '\'' +
                '}';
    }
}

测试：

String sql = "SELECT * FROM persons";
        List<Person> list = JDBCTools.query(Person.class, sql);
        list.forEach(System.out::println);

完整测试代码：
JDBCTools.java

package com.xiya.test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

/**
 * Created by N3verL4nd on 2017/4/18.
 */
public class JDBCTools {

    /**
     * 获取数据库连接
     * @return Connection
     */
    public static Connection getConnection() throws SQLException{
        Properties properties = new Properties();
        InputStream in;
        in = JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
        try {
            properties.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }

        //String driverClassName = properties.getProperty("jdbc.driverClassName");
        String jdbcUrl = properties.getProperty("jdbc.url");
        String user = properties.getProperty("jdbc.username");
        String password = properties.getProperty("jdbc.password");

        /*
        * 因为 ServiceLoader 所以不再需要如下函数调用。
        try {
            Class.forName(driverClassName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }*/

        return DriverManager.getConnection(jdbcUrl, user, password);
    }


    /**********Copied from DBUtil**********/
    public static void close(ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
    }

    public static void close(Statement stmt) throws SQLException {
        if (stmt != null) {
            stmt.close();
        }
    }

    public static void close(Connection conn) throws SQLException {
        if (conn != null) {
            conn.close();
        }
    }

    public static void closeQuietly(Connection conn) {
        try {
            close(conn);
        } catch (SQLException e) {
            //e.printStackTrace();
        }
    }

    public static void closeQuietly(ResultSet rs) {
        try {
            close(rs);
        } catch (SQLException e) {
            //e.printStackTrace();
        }
    }

    public static void closeQuietly(Statement stmt) {
        try {
            close(stmt);
        } catch (SQLException e) {
            //e.printStackTrace();
        }
    }

    /**
     * 释放数据库资源
     * @param conn  Connection
     * @param stmt  Statement
     * @param rs    ResultSet
     */
    public static void closeQuietly(Connection conn, Statement stmt, ResultSet rs) {
        try {
            closeQuietly(rs);
        } finally {
            try {
                closeQuietly(stmt);
            } finally {
                closeQuietly(conn);
            }
        }
    }
    /**********Copied from DBUtil**********/
}

Dao.java

package com.xiya.test;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.converters.DateConverter;

import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Dao {

    /**
     * 获取结果集的 ColumnLabel 对应的 List
     * @param rs
     * @return
     * @throws SQLException
     */
    private List<String> getColumnLabels(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = null;
        List<String> labels = new ArrayList<>();
        metaData = rs.getMetaData();
        for (int i = 0; i < metaData.getColumnCount(); i++) {
            labels.add(metaData.getColumnLabel(i + 1));
        }
        return labels;
    }

    /**
     * 通用查询方法
     * @param tClass
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T> List<T> query(Class<T> tClass, String sql, Object ...args) {
        T entity = null;
        List<T> list = null;
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;

        try {
            conn = JDBCTools.getConnection();
            statement = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                statement.setObject(i + 1, args[i]);
            }
            rs = statement.executeQuery();

            //数据库字段别名列表
            List<String> labels = getColumnLabels(rs);

            // 创建一个 Map<String, Object> 对象
            // 键: SQL 查询的列的别名
            // 值: SQL 查询的列的值
            Map<String, Object> map = new HashMap<>();
            //保存查询得到的数据
            list = new ArrayList<>();

            while (rs.next()) {
                map.clear();
                //每个 Map 对象对应一条数据库记录
                for (String columnLabel : labels) {
                    map.put(columnLabel, rs.getObject(columnLabel));
                }
                //若 Map 不为空集, 利用反射创建 tClass 对应的对象
                if (!map.isEmpty()) {
                    try {
                        entity = tClass.newInstance();
                        //遍历 Map 对象, 利用反射为 Class 对象的对应的属性赋值
                        for (Map.Entry<String, Object> entry : map.entrySet()) {
                            String name = entry.getKey();
                            Object value = entry.getValue();
                            try {

                                //利用反射设置实体类的字段
                                //Field field = tClass.getDeclaredField(name);
                                //field.setAccessible(true);
                                //field.set(entity, value);

                                //自定义转换格式（不设置，如果有Date字段为null，则出现异常）
                                ConvertUtils.register(new DateConverter(null), java.util.Date.class);
                                BeanUtils.setProperty(entity, name, value);
                            } catch (InvocationTargetException e) {
                                e.printStackTrace();
                            }
                        }
                    } catch (InstantiationException | IllegalAccessException e) {
                        e.printStackTrace();
                    }
                    list.add(entity);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.closeQuietly(conn, statement, rs);
        }
        return list;
    }

    /**
     * 通用更新方法
     * @param sql sql语句
     * @param args  参数
     */
    public int update(String sql, Object ...args) {
        Connection conn = null;
        PreparedStatement statement = null;
        int result = 0;
        try {
            conn = JDBCTools.getConnection();
            statement = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                statement.setObject(i + 1, args[i]);
            }
            result = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.closeQuietly(statement);
            JDBCTools.closeQuietly(conn);
        }
        return result;
    }

    public <E> E getValue(String sql, Object... args) {
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;

        try {
            conn = JDBCTools.getConnection();
            statement = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                statement.setObject(i + 1, args[i]);
            }
            rs = statement.executeQuery();
            if (rs.next()) {
                return (E) rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.closeQuietly(conn, statement, rs);
        }
        return null;
    }

}