自定义JDBC连接，以及实现查询数据库内容转Map、集合、JavaBean

一、自定义JDBC连接
创建util工具包，在util工具包内创建JdbcUtils连接类。
分析：
1.使用JDBC连接数据库需要先准备JdbcUrl，username，password，所以需要预先准备好上述的三个变量，这里需要注意的是，这三个变量使用private static进行修饰

    private static String jdbcUrl;
    private static String user;
    private static String password;

原因为，我们使用JdbcUtils类进行连接时，不希望实例化对象，而直接使用连接方法，所以之后定义的连接方法需要使用static进行修饰，这样上面的三个资源就必须使用static进行修饰

2.上面说到使用JdbcUtils类进行连接时，我们希望直接调用连接方法，那么连接方法使用static修饰即可，但是我们知道在创建JDBC连接时，必须要先加载JDBC连接驱动，所以这里采用了静态代码块的方式进行JDBC连接驱动的类加载。静态代码块修饰的内容在JdbcUtils类加载时执行，这样当后面在使用JdbcUtils中连接方法时，JDBC连接驱动已经加载完成。

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

3.接下来创建JDBC连接方法，在该方法中使用DriverManager驱动管理类进行数据库的连接。

    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(jdbcUrl, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

这里出现了另外一个问题，那就是我们上面准备的JdbcUrl，username，password，并没有值，当然我们也不能直接在JdbcUtils类中对其进行赋值，这样就将该连接方式写死了，以后再用就需要修改代码，这是不合理的，在这里需要引入配置文件，可以将JdbcUrl，username，password的信息保存在配置文件中，然后在JdbcUtils类中读取该配置文件中的内容即可

创建db.properties配置文件，文件内容如下：

#JDBC连接驱动
className=com.mysql.jdbc.Driver
#连接地址
jdbcUrl=jdbc:mysql://localhost:3306/jdbcstudy?useSSL=false
#用户名
user=root
#密码
password=123123

更改上诉静态代码块中的内容，在执行连接方法之前将配置文件中的内容加载到JdbcUtils类中

    static {
        try {
//            利用JdbcUtilsPlus.class对象获取当前类加载器在当前class文件所处项目位置检索指定名称的文件，
//            获取对应文件的InputStream字节输入流对象
            InputStream resourceAsStream = JdbcUtilsPlus.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            jdbcUrl = properties.getProperty("jdbcUrl");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            Class.forName(properties.getProperty("className"));
        } catch (ClassNotFoundException | IOException e) {
            e.printStackTrace();
        }
    }

4.创建JDBC关闭方法，在执行完JDBC操作后需要关闭相应资源，在这里需要注意先用后关的基本要求

定义关闭方法如下：

    private static void close(AutoCloseable... resources) {
        for (AutoCloseable resource : resources) {
            try {
                if (resource != null) {
                    resource.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

这里使用了不定长参数，因为并不能确定用户打开的资源个数，这里就拿常用的三个资源来说：Connection connection, Statement statement, ResultSet resultSet，数据库连接对象，sql语句执行对象，结果集对象，上面三类均实现了AutoCloseable 接口，所以这里可以使用接口多态。

在给用户提供多种关闭资源的方式

    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        close(resultSet, statement, connection);
    }

    public static void close(Connection connection, Statement statement) {
        close(statement, connection);
    }

    public static void close(Connection connection) {
        close(connection);
    }

这样就完成了JdbcUtils连接工具的创建。

二、自定义BaseDao工具类

public class BaseDao {

    
    public int update(String sql, Object... parameters) throws SQLException {
        check(sql);
        Connection connection = null;
        PreparedStatement statement = null;
     
        connection = JdbcUtils.getConnection();
        int affectedRows = 0;
        try {
            statement = connection.prepareStatement(sql);
            
            ParameterMetaData parameterMetaData = statement.getParameterMetaData();
            
            int parameterCount = parameterMetaData.getParameterCount();

            if (parameterCount != parameters.length) {
                throw new IllegalArgumentException("Sql语句所需参数不符");
            }
            for (int i = 0; i < parameters.length; i++) {
                statement.setObject(i + 1, parameters[i]);
            }
            affectedRows = statement.executeUpdate();
        } catch (SQLException e) {
            throw e;
        } finally {
            JdbcUtils.close(connection, statement);
        }
        return affectedRows;
    }

    
    public Map queryMap(String sql, Object... parameters) throws SQLException {
        check(sql);
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
       
        connection = JdbcUtils.getConnection();

        Map map = new HashMap<>(16);

        try {
            statement = connection.prepareStatement(sql);
            handlereStatement(statement, parameters);
            resultSet = statement.executeQuery();

//            获取结果集元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
//            解析结果集
            if (resultSet.next()) {
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    map.put(metaData.getColumnName(i), resultSet.getObject(i));
                }
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            JdbcUtils.close(connection, statement, resultSet);
        }
        return map.isEmpty() ? null : map;
    }

    
    public List> queryListMap(String sql, Object... parameters) throws SQLException {
        check(sql);
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        connection = JdbcUtils.getConnection();

        ArrayList> maps = new ArrayList<>();

        try {
            statement = connection.prepareStatement(sql);
            handlereStatement(statement, parameters);
            resultSet = statement.executeQuery();

//            获取结果集元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
//            解析结果集
            while (resultSet.next()) {
                Map map = new HashMap<>(16);
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    map.put(metaData.getColumnName(i), resultSet.getObject(i));
                }
                maps.add(map);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            JdbcUtils.close(connection, statement, resultSet);
        }
        return maps.isEmpty() ? null : maps;
    }

    
    public List queryArrayList(String sql, Object... parameters) throws SQLException {
        check(sql);
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        connection = JdbcUtils.getConnection();

        ArrayList list = new ArrayList<>();
        try {
            statement = connection.prepareStatement(sql);
            handlereStatement(statement, parameters);
            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            while (resultSet.next()) {
                Object[] obj = new Object[metaData.getColumnCount()];
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    obj[i] = resultSet.getObject(i + 1);
                }
                list.add(obj);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            JdbcUtils.close(connection, statement, resultSet);
        }
        return list.isEmpty() ? null : list;
    }

    
    public Object[] queryArray(String sql, Object... parameters) throws SQLException {
        check(sql);
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        connection = JdbcUtils.getConnection();

        try {
            statement = connection.prepareStatement(sql);
            handlereStatement(statement, parameters);
            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            Object[] obj = new Object[metaData.getColumnCount()];
            if (resultSet.next()) {
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    obj[i] = resultSet.getObject(i + 1);
                }
            }
            return obj;
        } catch (SQLException e) {
            throw e;
        } finally {
            JdbcUtils.close(connection, statement, resultSet);
        }
    }


    
    public  List queryListBean(String sql, Class cls, Object... parameters) throws SQLException {
        check(sql);
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        connection = JdbcUtils.getConnection();
        ArrayList list = new ArrayList<>();
        try {
            statement = connection.prepareStatement(sql);
            handlereStatement(statement, parameters);
            resultSet = statement.executeQuery();
            Constructor constructor = cls.getConstructor();
            int columnCount = resultSet.getMetaData().getColumnCount();
            while (resultSet.next()) {
                T t = constructor.newInstance();
                for (int i = 1; i <= columnCount; i++) {
                    BeanUtils.setProperty(t, resultSet.getMetaData().getColumnName(i), resultSet.getObject(i));
                }
                list.add(t);
            }
        } catch (SQLException e) {
            throw e;
        } catch (NoSuchMethodException | InvocationTargetException | InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection, statement, resultSet);
        }
        return list;
    }


    
    public  T queryBean(String sql, Class cls, Object... parameters) throws SQLException {
        check(sql);
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        connection = JdbcUtils.getConnection();
        T t = null;
        try {
            statement = connection.prepareStatement(sql);
            handlereStatement(statement, parameters);
            resultSet = statement.executeQuery();
            Constructor constructor = cls.getConstructor();
            int columnCount = resultSet.getMetaData().getColumnCount();
            if (resultSet.next()) {
                t = constructor.newInstance();
                for (int i = 1; i <= columnCount; i++) {
                    BeanUtils.setProperty(t, resultSet.getMetaData().getColumnName(i), resultSet.getObject(i));
                }
            }
        } catch (SQLException e) {
            throw e;
        } catch (NoSuchMethodException | InvocationTargetException | InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection, statement, resultSet);
        }
        return t;
    }


    
    private void check(String sql) {
        if (null == sql || sql.isEmpty()) {
            throw new IllegalArgumentException("非法sql语句异常");
        }
    }

    
    private void handlereStatement(PreparedStatement statement, Object[] parameters) throws SQLException {
        ParameterMetaData parameterMetaData = statement.getParameterMetaData();
        if (parameterMetaData.getParameterCount() != parameters.length) {
            throw new IllegalArgumentException("Sql语句所需参数不符");
        }
        for (int i = 0; i < parameters.length; i++) {
            statement.setObject(i + 1, parameters[i]);
        }
    }
}

从上面的代码可以看出代码非常的冗余，下面让我们采用另外一种方式将其改进。

分析：对上述代码的查询功能进行分析，可知，上面的建立数据库连接，执行sql语句，以及下面关闭资源的操作基本一样，不同点仅仅在于对数据的处理，这样我们可以设计一个接口，接口中只有一个数据处理方法，然后在各个实现类中完成数据的处理。
接口如下：

package util;

import java.sql.ResultSet;
import java.sql.SQLException;


public interface ResultSetHandler {
    
    T handler(ResultSet resultSet) throws SQLException;
}

首先设计如下代码：

    
    public  T query(String sql, ResultSetHandler rsh, Object... parameters) throws SQLException {
        check(sql);
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        FileUtils.addJdbcInformation("D:/JavaSE_Study/MysqlStudy/src/jdbcstudy/path.txt");
        connection = JdbcUtils.getConnection();
        T t = null;
        try {
            statement = connection.prepareStatement(sql);
            handlereStatement(statement, parameters);
            resultSet = statement.executeQuery();
            t = rsh.handler(resultSet);
        } catch (SQLException e) {
            throw e;
        } finally {
            JdbcUtils.close(connection, statement, resultSet);
        }
        return t;
    }

在来设计实现类，以ListBean为例：

package util.impl;

import org.apache.commons.beanutils.BeanUtils;
import util.ResultSetHandler;

import java.lang.reflect.Constructor;
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;


public class ListBeanHandler implements ResultSetHandler> {
    private final Class cls;
    public ListBeanHandler(Class cls) {
        this.cls = cls;
    }

    @Override
    public List handler(ResultSet resultSet) throws SQLException {
        T t = null;
        ArrayList list = null;
        try {
            Constructor constructor = cls.getConstructor();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            list = new ArrayList<>(columnCount);
            while (resultSet.next()) {
                t = constructor.newInstance();
                for (int i = 1; i <= columnCount; i++) {
                    BeanUtils.setProperty(t, metaData.getColumnName(i), resultSet.getObject(i));
                }
                list.add(t);
            }
        } catch (NoSuchMethodException | InvocationTargetException | InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return list.isEmpty() ? null : list;
    }
}

后面对于不同的方法设计不同的类即可，这里面的BeanUtils.setProperty使用了第三方jar包

若不想使用，可以使用反射完成上诉操作

package com.test1;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Map;


public class BeanUtils {
    
    public static void setProperty(Object bean, String fieldName, String value) {
//        获取该JavaBean对象的Class对象
        Class cls = bean.getClass();
        try {
            Field field = cls.getDeclaredField(fieldName);
            field.setAccessible(true);
            Class type = field.getType();

            if (String.class.equals(type)) {
                field.set(bean, value);
            } else if (Integer.class.equals(type)) {
                field.set(bean, Integer.parseInt(value));
            } else if (Character.class.equals(type)) {
                field.set(bean, value.charAt(0));
            } else {
                Method method = type.getMethod("parse" + type.getName().substring(type.getName().lastIndexOf(".") + 1), String.class);
                field.set(bean, method.invoke(null, value));
            }
        } catch (NoSuchFieldException | IllegalAccessException | NoSuchMethodException | InvocationTargetException e) {
            e.printStackTrace();
        }
    }

    
    public static String getProperty(Object bean, String fieldName) {
        Class cls = bean.getClass();
        Object o = null;
        try {
            Field field = cls.getDeclaredField(fieldName);
            field.setAccessible(true);
            o = field.get(bean);
        } catch (IllegalAccessException | NoSuchFieldException e) {
            e.printStackTrace();
        }
        return String.valueOf(o);
    }

    public static void populate(Object bean, Map map) {
        Class cls = bean.getClass();
        for (Map.Entry entry : map.entrySet()) {
            setProperty(bean, entry.getKey(), entry.getValue());
        }
    }

}

这里不再多说

改进完的代码如下：

package util;

import util.impl.*;

import java.sql.*;
import java.util.List;
import java.util.Map;


public class BaseDaoPlus {

    
    public int update(String sql, Object... parameters) throws SQLException {
        check(sql);
        Connection connection = null;
        PreparedStatement statement = null;
        FileUtils.addJdbcInformation("D:/JavaSE_Study/MysqlStudy/src/jdbcstudy/path.txt");
        connection = JdbcUtils.getConnection();
        int affectedRows = 0;
        try {
            statement = connection.prepareStatement(sql);
            
            ParameterMetaData parameterMetaData = statement.getParameterMetaData();
            
            int parameterCount = parameterMetaData.getParameterCount();

            if (parameterCount != parameters.length) {
                throw new IllegalArgumentException("Sql语句所需参数不符");
            }
            for (int i = 0; i < parameters.length; i++) {
                statement.setObject(i + 1, parameters[i]);
            }
            affectedRows = statement.executeUpdate();
        } catch (SQLException e) {
            throw e;
        } finally {
            JdbcUtils.close(connection, statement);
        }
        return affectedRows;
    }

    
    public Map queryMap(String sql, Object... parameters) throws SQLException {
        return query(sql, new MapHandler(), parameters);
    }

    
    public List> queryListMap(String sql, Object... parameters) throws SQLException {
        return query(sql, new MapListHandler(), parameters);
    }

    
    public List queryArrayList(String sql, Object... parameters) throws SQLException {
        return query(sql, new ArrayListHandler(), parameters);
    }

    
    public Object[] queryArray(String sql, Object... parameters) throws SQLException {
        return query(sql, new ArrayHandler(), parameters);
    }

    
    public  List queryListBean(String sql, Class cls, Object... parameters) throws SQLException {
        return query(sql, new ListBeanHandler<>(cls), parameters);
    }

    
    public  T queryBean(String sql, Class cls, Object... parameters) throws SQLException {
        return query(sql, new BeanHandler<>(cls), parameters);
    }

    
    private void check(String sql) {
        if (null == sql || sql.isEmpty()) {
            throw new IllegalArgumentException("非法sql语句异常");
        }
    }

    
    private void handlereStatement(PreparedStatement statement, Object[] parameters) throws SQLException {
        ParameterMetaData parameterMetaData = statement.getParameterMetaData();
        if (parameterMetaData.getParameterCount() != parameters.length) {
            throw new IllegalArgumentException("Sql语句所需参数不符");
        }
        for (int i = 0; i < parameters.length; i++) {
            statement.setObject(i + 1, parameters[i]);
        }
    }

    
    public  T query(String sql, ResultSetHandler rsh, Object... parameters) throws SQLException {
        check(sql);
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Connection connection = null;
    
        connection = JdbcUtils.getConnection();
        T t = null;
        try {
            statement = connection.prepareStatement(sql);
            handlereStatement(statement, parameters);
            resultSet = statement.executeQuery();
            t = rsh.handler(resultSet);
        } catch (SQLException e) {
            throw e;
        } finally {
            JdbcUtils.close(connection, statement, resultSet);
        }
        return t;
    }
}