手写JDBC的ConnectionFactory，ConnectionPool和Template

今天药忘吃喽~ 2022-03-22 07:58 177阅读 0赞

## **配置文件jdbc.properties** ##

通过读取配置文件数据库连接加载配置和连接池配置

#mysql
    mysql.driver-name=com.mysql.cj.jdbc.Driver
    mysql.url=jdbc:mysql://localhost:3306/test
    mysql.username=×××××
    mysql.password=*****
    #pool
    pool.init-connections=3
    pool.max-connections=5
    pool.min-connections=2

## **连接工厂JdbcConnectionFactory** ##

单例模式、创建连接

package com.cjx913.jdbc;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.Properties;
    
    public class JdbcConnectionFactory {
        private static String DRIVER;
        private static String URL;
        private static String USERNAME;
        private static String PASSWORD;
    
        private static JdbcConnectionFactory instance;
    
        private JdbcConnectionFactory() throws JdbcException {
            init();
        }
    
        public static JdbcConnectionFactory getInstance() throws JdbcException {
            if (instance == null) {
                instance = new JdbcConnectionFactory();
            }
            return instance;
        }
    
        public void init() throws JdbcException {
            Properties properties = new Properties();
            InputStream is =
                    JdbcConnectionFactory.class.getResourceAsStream("/jdbc.properties");
            try {
                properties.load(is);//加载配置文件
                DRIVER = properties.getProperty("mysql.driver-name");//读取文件配置数据库驱动
                URL = properties.getProperty("mysql.url");//读取文件配置数据库URL
                USERNAME = properties.getProperty("mysql.username");//读取文件配置数据库用户
                PASSWORD = properties.getProperty("mysql.password");//读取文件配置数据库用户密码
            } catch (IOException e) {
                throw new JdbcException("配置文件读取错误！");
            } finally {
                if (is != null) {
                    try {
                        is.close();
                    } catch (IOException e) {
                        throw new JdbcException("配置文件输入流关闭错误！");
                    }
                }
            }
        }
    
        /**
         * 提供getConnection()方法
         *
         * @return Connection
         */
        public Connection getConnection() throws JdbcException {
            Connection conn = null;
            try {
                Class.forName(DRIVER);
                conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
                return conn;
            } catch (ClassNotFoundException | SQLException e) {
                throw new JdbcException("创建数据库连接错误！");
            }
        }
    }

## **连接池JdbcConnectionPool** ##

单例、数据库连接池。

获取连接和回收连接

package com.cjx913.jdbc;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.LinkedList;
    import java.util.Properties;
    
    public class JdbcConnectionPool {
        private int initConnections = 3;
        private int maxConnections = 5;
        private int minConnections = 2;
    
        private static LinkedList <Connection> connections = new LinkedList <>();
    
        private JdbcConnectionFactory connectionFactory = JdbcConnectionFactory.getInstance();
    
        private static JdbcConnectionPool instance = null;
    
        /**
         * 单例模式设计连接池
         * 读取配置
         * 初始化连接池
         */
        private JdbcConnectionPool() throws JdbcException {
            //读取配置
            Properties properties = new Properties();
            InputStream is =
                    JdbcConnectionFactory.class.getResourceAsStream("/jdbc.properties");
            try {
                properties.load(is);//加载配置文件
                initConnections = Integer.valueOf(properties.getProperty("pool.init-connections"));//读取文件配置连接池的初始连接数
                maxConnections = Integer.valueOf(properties.getProperty("pool.max-connections"));//读取文件配置连接池的最大连接数
                minConnections = Integer.valueOf(properties.getProperty("pool.min-connections"));//读取文件配置连接池的最小连接数
            } catch (IOException e) {
                throw new JdbcException("加载配置文件错误");
            } finally {
                if (is != null) {
                    try {
                        is.close();
                    } catch (IOException e) {
                        throw new JdbcException("配置文件输入流关闭错误！");
                    }
                }
            }
    
            //初始化连接池
            for (int i = 0; i < initConnections; i++) {
                connections.add(connectionFactory.getConnection());
            }
        }
    
        public static JdbcConnectionPool getInstance() throws JdbcException {
            if (instance == null) {
                try {
                    instance = new JdbcConnectionPool();
                } catch (JdbcException e) {
                    throw new JdbcException("创建连接池错误！");
                }
            }
            return instance;
        }
    
        /**
         * 获取连接
         *
         * @return
         */
        public Connection getConnection() throws JdbcException {
            synchronized (connections) {
                if (connections.size() >= minConnections) {
                    return connections.pop();
                } else {
                    try {
                        return connectionFactory.getConnection();
                    } catch (JdbcException e) {
                        throw new JdbcException("连接池创建数据库连接错误！");
                    }
                }
            }
        }
    
        /**
         * 回收连接
         *
         * @param connection
         * @throws JdbcException
         */
        public void recycleConnection(Connection connection) throws JdbcException {
            if (connection != null) {
                synchronized (connections) {
                    if (connections.size() > maxConnections) {
                        try {
                            connection.close();
                        } catch (SQLException e) {
                            throw new JdbcException("JDBC连接关闭错误！");
                        }
                    } else {
                        connections.add(connection);
                    }
                }
            }
        }
    }

## **模板JdbcTemplate** ##

jdbc增删改查的通用方法，通过反射实现实体关联。如果查询字段与实体成员变量不对应可以 as 成员变量，或返回Map，需要指定返回类型的class，现只分Map.class和其他类.class

package com.cjx913.jdbc;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.Collection;
    
    public class JdbcTemplate {
    
        public <T> T queryOne(Class <T> clazz,Connection connection, String querySql, Object... parameters) throws JdbcException {
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            try {
                pstmt = connection.prepareStatement(querySql);
                JdbcUtil.setParametersValue(pstmt, parameters);
                rs = pstmt.executeQuery();
                return JdbcUtil.getOneResult(rs, clazz);
            } catch (SQLException | JdbcException e) {
                throw new JdbcException("查询数据错误！", e);
            } finally {
                JdbcUtil.closeResultSet(rs);
                JdbcUtil.closePreparedStatement(pstmt);
            }
        }
    
        public <T> Collection <T> query(Class <T> clazz,Connection connection, String querySql, Object... parameters) throws JdbcException {
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            try {
                pstmt = connection.prepareStatement(querySql);
                JdbcUtil.setParametersValue(pstmt, parameters);
                rs = pstmt.executeQuery();
                return JdbcUtil.getResults(rs, clazz);
            } catch (SQLException | JdbcException e) {
                throw new JdbcException("查询数据错误！", e);
            } finally {
                JdbcUtil.closeResultSet(rs);
                JdbcUtil.closePreparedStatement(pstmt);
            }
        }
    
        public int insertOne(Connection connection,String insertSql, Object... parameters) throws JdbcException {
            try {
                if (insertSql.toUpperCase().startsWith("INSERT")) {
                    return executeOne(connection,insertSql, parameters);
                } else {
                    throw new JdbcException("插入数据的sql语句错误");
                }
            } catch (JdbcException e) {
                throw new JdbcException("插入数据错误！", e);
            }
        }
    
        public int[] insert(Connection connection,String insertSql, Collection <Object[]> parameters) throws JdbcException {
            try {
                if (insertSql.toUpperCase().startsWith("INSERT")) {
                    return execute(connection,insertSql, parameters);
                } else {
                    throw new JdbcException("插入数据的sql语句错误");
                }
            } catch (JdbcException e) {
                throw new JdbcException("批量插入数据错误！", e);
            }
        }
    
        public int updateOne(Connection connection,String updateSql, Object... parameters) throws JdbcException {
            try {
                if (updateSql.toUpperCase().startsWith("UPDATE")) {
                    return executeOne(connection,updateSql, parameters);
                } else {
                    throw new JdbcException("更新数据的sql语句错误");
                }
            } catch (JdbcException e) {
                throw new JdbcException("更新数据错误！", e);
            }
        }
    
        public int[] update(Connection connection,String updateSql, Collection <Object[]> parameters) throws JdbcException {
            try {
                if (updateSql.toUpperCase().startsWith("UPDATE")) {
                    return execute(connection,updateSql, parameters);
                } else {
                    throw new JdbcException("更新数据的sql语句错误");
                }
            } catch (JdbcException e) {
                throw new JdbcException("批量更新数据错误！", e);
            }
        }
    
        public int deleteOne(Connection connection,String deleteSql, Object... parameters) throws JdbcException {
            try {
                if (deleteSql.toUpperCase().startsWith("DELETE")) {
                    return executeOne(connection,deleteSql, parameters);
                } else {
                    throw new JdbcException("删除数据的sql语句错误");
                }
            } catch (JdbcException e) {
                throw new JdbcException("删除数据错误！", e);
            }
        }
    
        public int[] delete(Connection connection,String deleteSql, Collection <Object[]> parameters) throws JdbcException {
            try {
                if (deleteSql.toUpperCase().startsWith("DELETE")) {
                    return execute(connection,deleteSql, parameters);
                } else {
                    throw new JdbcException("删除数据的sql语句错误");
                }
            } catch (JdbcException e) {
                throw new JdbcException("批量删除数据错误！", e);
            }
        }
    
        public int executeOne(Connection connection, String sql, Object... parameters) throws JdbcException {
            PreparedStatement pstmt = null;
            try{
                pstmt = connection.prepareStatement(sql);
                JdbcUtil.setParametersValue(pstmt, parameters);
                return pstmt.executeUpdate();
            }catch (SQLException e){
                throw new JdbcException("执行sql语句错误！",e);
            }finally {
                JdbcUtil.closePreparedStatement(pstmt);
            }
    
        }
    
        public int[] execute(Connection connection, String sql, Collection <Object[]> parameters) throws JdbcException {
            PreparedStatement pstmt = null;
            try {
                pstmt = connection.prepareStatement(sql);
                for (Object[] objects : parameters) {
                    JdbcUtil.setParametersValue(pstmt, objects);
                    pstmt.addBatch();
                }
                return pstmt.executeBatch();
            }catch (SQLException e){
                throw new JdbcException("批量执行sql语句错误！",e);
            }finally {
                JdbcUtil.closePreparedStatement(pstmt);
            }
        }
    }

## **JdbcUtil** ##

包括设置参数的方法（？的替换），返回结果的处理和资源的关闭，可以再具体功能分开写

package cn.cjx913.telecom_charging.jdbc;
    
    import java.beans.IntrospectionException;
    import java.beans.PropertyDescriptor;
    import java.lang.reflect.InvocationTargetException;
    import java.lang.reflect.Method;
    import java.sql.*;
    import java.util.*;
    import java.util.Date;
    
    public class JdbcUtil {
        public static void setParametersValue(PreparedStatement pstmt, Object... parameters) {
            if (parameters != null && parameters.length > 0) {
                try {
                    for (int i = 0; i < parameters.length; i++) {
                        Object p = parameters[i];
                        if (p == null) {
                            pstmt.setObject(i + 1, null);
                        } else if (p instanceof String) {
                            pstmt.setString(i + 1, (String) p);
                        } else if (p instanceof Integer) {
                            pstmt.setInt(i + 1, (Integer) p);
                        } else if (p instanceof Double) {
                            pstmt.setDouble(i + 1, (Double) p);
                        } else if (p instanceof Float) {
                            pstmt.setFloat(i + 1, (Float) p);
                        } else if (p instanceof Boolean) {
                            pstmt.setBoolean(i + 1, (Boolean) p);
                        } else if (p instanceof Date) {
                            pstmt.setTimestamp(i + 1, new Timestamp(((Date) p).getTime()));
                        } else if (p instanceof Blob) {
                            pstmt.setBlob(i + 1, (Blob) p);
                        } else if (p instanceof Clob) {
                            pstmt.setClob(i + 1, (Clob) p);
                        } else {
                            throw new JdbcException("参数(\"?\")替换错误！不支持该类型参数->" + p.getClass().getName());
                        }
                    }
                } catch (SQLException e) {
                    throw new JdbcException("参数(\"?\")替换错误！参数类型不配置", e);
                }
            }
        }
    
        public static Map <String, Object> getOneResult(ResultSet rs) {
            return getOneResult(rs, Map.class);
        }
    
        public static <T> T getOneResult(ResultSet rs, Class <T> clazz) {
            if (rs == null) {
                return null;
            }
            try {
                //获取结果集信息
                ResultSetMetaData rsmd = rs.getMetaData();
                //数据处理
                if (clazz == Integer.class && clazz.equals(Integer.class)) {
                    if (rs.next()) {
                        return (T) new Integer(rs.getInt(1));
                    } else {
                        return null;
                    }
    
                } else if (clazz == Double.class && clazz.equals(Double.class)) {
                    if (rs.next()) {
                        return (T) new Double(rs.getDouble(1));
                    } else {
                        return null;
                    }
                } else if (clazz == String.class && clazz.equals(String.class)) {
                    if (rs.next()) {
                        return (T) new String(rs.getString(1));
                    } else {
                        return null;
                    }
                } else if (clazz != Map.class || !clazz.equals(Map.class)) {
                    if (rs.next()) {
                        T result = clazz.newInstance();
                        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                            PropertyDescriptor pd = new PropertyDescriptor(rsmd.getColumnLabel(i), clazz);
                            Method method = pd.getWriteMethod();
                            method.invoke(result, rs.getObject(i));
                        }
                        return result;
                    } else {
                        return null;
                    }
                } else {
                    if (rs.next()) {
                        Map <String, Object> result = new HashMap <>();
                        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                            result.put(rsmd.getColumnLabel(i), rs.getObject(i));
                        }
                        return (T) result;
                    } else {
                        return null;
                    }
                }
            } catch (SQLException | InstantiationException | IllegalAccessException | IntrospectionException | InvocationTargetException e) {
                throw new JdbcException("处理结果集错误！", e);
            } finally {
                closeResultSet(rs);
            }
        }
    
        public static List <Map> getResults(ResultSet rs) {
            return getResults(rs, Map.class);
        }
    
        public static <T> List <T> getResults(ResultSet rs, Class <T> clazz) {
            if (rs == null) {
                return null;
            }
            try {
                List <T> results = new ArrayList <>();
                //获取结果集信息
                ResultSetMetaData rsmd = rs.getMetaData();
                //数据处理
                if (clazz != Map.class || !clazz.equals(Map.class)) {
                    while (rs.next()) {
                        T result = clazz.newInstance();
                        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                            PropertyDescriptor pd = new PropertyDescriptor(rsmd.getColumnLabel(i), clazz);
                            Method method = pd.getWriteMethod();
                            method.invoke(result, rs.getObject(i));
                        }
                        results.add(result);
                    }
                } else {
                    while (rs.next()) {
                        Map <String, Object> result = new HashMap <>();
                        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                            result.put(rsmd.getColumnLabel(i), rs.getObject(i));
                        }
                        results.add((T) result);
                    }
                }
                return results.size() > 0 ? results : null;
            } catch (SQLException | InstantiationException | IllegalAccessException | IntrospectionException |
                    InvocationTargetException e) {
                throw new JdbcException("处理结果集错误！", e);
            } finally {
                closeResultSet(rs);
            }
        }
    
        public static void closeResultSet(ResultSet rs) {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new JdbcException("ResultSet关闭错误", e);
                }
            }
        }
    
        public static void closePreparedStatement(PreparedStatement pstmt) {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    throw new JdbcException("PreparedStatement关闭错误", e);
                }
            }
        }
    
        public static void closeConnection(Connection conn) {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new JdbcException("Connection关闭错误", e);
                }
            }
        }
    
        public static void rollbackConnection(Connection connection) {
            try {
                connection.rollback();
            } catch (SQLException e) {
                throw new JdbcException("Connection回滚错误！", e);
            }
        }
    }

JdbcException

package com.cjx913.jdbc;
    
    public class JdbcException extends Exception {
        public JdbcException() {
        }
    
        public JdbcException(String message) {
            super(message);
        }
    
        public JdbcException(String message, Throwable cause) {
            super(message, cause);
        }
    
        public JdbcException(Throwable cause) {
            super(cause);
        }
    
        public JdbcException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
            super(message, cause, enableSuppression, writableStackTrace);
        }
    }