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