手写JDBC的ConnectionFactory，ConnectionPool和Template

配置文件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);
    }
}