JDBC封装

JDBC封装

前言

正如在前面的小节中看到的，原生的JDBC的操作其实是比较繁琐的，而且其中有很多的重复性代码，这个时候，我们可以考虑自己对JDBC进行一个简单的封装，封装成自己想要的，比较方便的小工具

封装JDBC

为了使得编辑参数更加方便，首先将数据库连接参数抽取出来放在配置文件jdcb.properties中

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring
jdbc.user=root
jdbc.password=huanfeng

然后在类加载的时候加载该配置文件，并且注册驱动类信息

    private static Properties properties;
    // 使用静态代码块保证在类加载的时候立即加载对应的配置文件
    static {
        properties = new Properties();
        try {
            InputStream ins = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
            properties.load(ins);
            System.out.println("load config file finish");

            Class.forName(properties.getProperty("jdbc.driver"));

        } catch (FileNotFoundException e) {
            System.out.println("jdbc config file not found " + e.getMessage());
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            System.out.println("jdbc class not found " + e.getMessage());
        }
    }

接着封装获取连接对象getConnection()

    /**
     * 获得数据库连接Connection
     * @return Connection 数据库连接
     */
    private static Connection getConnection(){

        Connection connection = null;
        try {
          connection =  DriverManager.getConnection(
                  properties.getProperty("jdbc.url"),
                  properties.getProperty("jdbc.user"),
                  properties.getProperty("jdbc.password"));
        } catch (SQLException e) {
            System.out.println("cannot get connection " + e.getMessage());
        }
        return connection;
    }

然后封装关闭各种资源的方法close()

    /**
     * 关闭connection
     * @param connection 连接池对象
     */
    private static void close(Connection connection){
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭Statement
     * @param statement
     */
    private static void close(Statement statement){
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭ResultSet
     * @param resultSet
     */
    private static void close(ResultSet resultSet){
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭Connection 以及Statement
     * @param connection
     * @param statement
     */
    private static void close(Connection connection, Statement statement){
        close(connection);
        close(statement);
    }

    /**
     * 关闭Connection，Statement以及ResultSet
     * @param connection
     * @param statement
     * @param resultSet
     */
    private static void close(Connection connection, Statement statement, ResultSet resultSet){
        close(connection, statement);
        close(resultSet);
    }

接着封装更新方法，正如所知道的，更新包括插入、删除、修改，所以可以将这些操作封装在一个方法

    /**
     * 更新操作
     * @param sql 执行的SQL语句
     * @param param 对应的参数列表
     * @return true 更新成功， false 更新失败
     */
    public static boolean update(String sql, Object[] param){

        PreparedStatement preparedStatement = null;
        Connection connection = getConnection();
        try {
            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false){
                return false;
            }

            int result = preparedStatement.executeUpdate();
            if (result > 0){
                return true;
            }
            return false;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(connection, preparedStatement);
        }
        return false;
    }

上面封装了一个方法settingParams()，作用是利用元数据信息，根据SQL语句中的占位符个数，将对应的参数注入进去

    /**
     * 设置参数
     * @param preparedStatement Statement对象
     * @param param 参数列表
     * @return
     * @throws SQLException
     */
    private static boolean settingParams(PreparedStatement preparedStatement, Object[] param) throws SQLException {

        if (param != null && param.length > 0){
            // 获取ParameterMetaData
            ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
            // 获得SQL中占位符个数
            int paramCount = parameterMetaData.getParameterCount();

            // 占位符个数与参数个数不一致，返回false表示出错
            if (paramCount != param.length){
                return false;
            }
            // 设置对应的参数信息
            for (int i = 0; i < paramCount; i++){
                preparedStatement.setObject(i+1, param[i]);
            }
        }
        return true;
    }

由于查询返回的类型非常多，这里只是封装两个常用的方法，一个是queryForBean()返回单个对象，一个是queryForList()返回List，这里需要注意的是，由于只是简单的封装，所以没有做对象属性名与数据表列名的映射，所以需要保证属性名与列名相同，为了更加方便进行操作，使用了泛型机制，并且使用了Apache的BeanUtils工具进行属性的注入操作,BeanUtils的Maven配置如下所示

    <dependency>
        <groupId>commons-beanutils</groupId>
        <artifactId>commons-beanutils</artifactId>
        <version>1.9.3</version>
    </dependency>

    /**
     * 获取单个Bean
     * @param sql 执行SQL语句
     * @param param 对应的参数列表
     * @param clazz 所要获取的对象的类型
     * @param <T>  对象的类型
     * @return
     */
    public static <T> T queryForBean(String sql, Object[] param, Class<T> clazz){

        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {

            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false){
                return null;
            }

            resultSet = preparedStatement.executeQuery();
            if (resultSet == null){
                return null;
            }

            if (resultSet.next()){
                // 利用反射机制创建对象
                T data = clazz.newInstance(); 
                // 获得ResultSetMetaData
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                // 获得列的数量
                int columnCount = resultSetMetaData.getColumnCount();
                for (int i = 0; i < columnCount; i++){
                    // 获得对应的列的名称
                    String name = resultSetMetaData.getColumnName(i + 1);
                    // 获得对应的列的值
                    Object rData = resultSet.getObject(name);
                    // 使用BeanUtils工具对属性进行注入
                    BeanUtils.copyProperty(data, name, rData);
                }
             return data;

            }else {
                return null;
            }

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultSet);
        }
        return null;
    }

/**
     * 获取Bean并且封装成List
     * @param sql 执行SQL语句
     * @param param 对应的参数列表
     * @param clazz 所要获取的对象的类型
     * @param <T>  对象的类型
     * @return list
     */
    public static <T> List<T> queryForList(String sql, Object[] param, Class<T> clazz){

        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {

            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false){
                return null;
            }

            resultSet = preparedStatement.executeQuery();
            if (resultSet == null){
                return null;
            }
            List<T> results = new ArrayList<>();

            while (resultSet.next()){
                // 创建对象
                T data = clazz.newInstance();
                // 获得ResultSetMetaData
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                // 获得列的数量
                int columnCount = resultSetMetaData.getColumnCount();
                for (int i = 0; i < columnCount; i++){
                    // 获得对应的列的名称
                    String name = resultSetMetaData.getColumnName(i + 1);
                    // 获得对应的列的值
                    Object rData = resultSet.getObject(name);
                    // 使用BeanUtils工具对属性进行注入
                    BeanUtils.copyProperty(data, name, rData);
                }
                results.add(data);

            }
            return results;

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultSet);
        }
        return null;
    }

至此，通过简单的封装，一个简单的JDBC操作工具就封装好了，这样，当需要的时候就可以非常方便地使用了，而且，当需要其他返回类型的时候，基本上只要按照上面的思路进行处理即可。

完整的代码如下所示

package cn.xuhuanfeng.jdbc;

import org.apache.commons.beanutils.BeanUtils;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * Created by Huanfeng.Xu on 2017-07-09.
 */
public class JDBCUtils {

    private static Properties properties;
    static {
        properties = new Properties();
        try {
            InputStream inputStream = JDBCUtils.class.getResourceAsStream("jdbc.properties");
            InputStream ins = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
            properties.load(ins);
            System.out.println("load config file finish");

            Class.forName(properties.getProperty("jdbc.driver"));

        } catch (FileNotFoundException e) {
            System.out.println("jdbc config file not found " + e.getMessage());
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            System.out.println("jdbc class not found " + e.getMessage());
        }
    }

    /**
     * 获得数据库连接Connection
     * @return Connection 数据库连接
     */
    private static Connection getConnection(){

        Connection connection = null;
        try {
          connection =  DriverManager.getConnection(
                  properties.getProperty("jdbc.url"),
                  properties.getProperty("jdbc.user"),
                  properties.getProperty("jdbc.password"));
        } catch (SQLException e) {
            System.out.println("cannot get connection " + e.getMessage());
        }
        return connection;
    }


    /**
     * 设置参数
     * @param preparedStatement Statement对象
     * @param param 参数列表
     * @return
     * @throws SQLException
     */
    private static boolean settingParams(PreparedStatement preparedStatement, Object[] param) throws SQLException {

        if (param != null && param.length > 0){
            ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
            int paramCount = parameterMetaData.getParameterCount();

            if (paramCount != param.length){
                return false;
            }

            for (int i = 0; i < paramCount; i++){
                preparedStatement.setObject(i+1, param[i]);
            }
        }
        return true;
    }


    /**
     * 更新操作
     * @param sql 执行的SQL语句
     * @param param 对应的参数列表
     * @return
     */
    public static boolean update(String sql, Object[] param){

        PreparedStatement preparedStatement = null;
        Connection connection = getConnection();
        try {
            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false){
                return false;
            }

            int result = preparedStatement.executeUpdate();
            if (result > 0){
                return true;
            }
            return false;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(connection, preparedStatement);
        }
        return false;
    }

    /**
     * 获取单个Bean
     * @param sql 执行SQL语句
     * @param param 对应的参数列表
     * @param clazz 所要获取的对象的类型
     * @param <T>  对象的类型
     * @return bean
     */
    public static <T> T queryForBean(String sql, Object[] param, Class<T> clazz){

        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {

            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false){
                return null;
            }

            resultSet = preparedStatement.executeQuery();
            if (resultSet == null){
                return null;
            }

            if (resultSet.next()){

                T data = clazz.newInstance();
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                for (int i = 0; i < columnCount; i++){
                    String name = resultSetMetaData.getColumnName(i + 1);
                    Object rData = resultSet.getObject(name);
                    BeanUtils.copyProperty(data, name, rData);
                }
             return data;

            }else {
                return null;
            }

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 获取Bean并且封装成List
     * @param sql 执行SQL语句
     * @param param 对应的参数列表
     * @param clazz 所要获取的对象的类型
     * @param <T>  对象的类型
     * @return list
     */
    public static <T> List<T> queryForList(String sql, Object[] param, Class<T> clazz){

        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {

            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false){
                return null;
            }

            resultSet = preparedStatement.executeQuery();
            if (resultSet == null){
                return null;
            }
            List<T> results = new ArrayList<>();

            while (resultSet.next()){

                T data = clazz.newInstance();
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                for (int i = 0; i < columnCount; i++){
                    String name = resultSetMetaData.getColumnName(i + 1);
                    Object rData = resultSet.getObject(name);
                    BeanUtils.copyProperty(data, name, rData);
                }
                results.add(data);

            }
            return results;

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 关闭connection
     * @param connection 连接池对象
     */
    private static void close(Connection connection){
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭Statement
     * @param statement
     */
    private static void close(Statement statement){
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭ResultSet
     * @param resultSet
     */
    private static void close(ResultSet resultSet){
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭Connection 以及Statement
     * @param connection
     * @param statement
     */
    private static void close(Connection connection, Statement statement){
        close(connection);
        close(statement);
    }

    /**
     * 关闭Connection，Statement以及ResultSet
     * @param connection
     * @param statement
     * @param resultSet
     */
    private static void close(Connection connection, Statement statement, ResultSet resultSet){
        close(connection, statement);
        close(resultSet);
    }
}