基于JDBC封装通用Dao层、数据库连接池类

最近闲来无事，想起一些JavaWeb的开发基础，例如：JDBC、Servlet之类的。因此，基于JDBC的基础上封装了一套通用的Dao层API和数据库连接池，如下：

项目目录

db_config.properties配置文件（数据库连接信息）

core_pool_size=10
driver_name=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&useSSL=false
user=root
password=***

DBConnectionPool数据库连接池类

public class DBConnectionPool implements DataSource {

    // 每个线程独占单个连接，直到释放
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

    // 连接池集合（使用阻塞队列）
    private static LinkedBlockingQueue<Connection> connections;

    /**
     * 读取db_config.properties文件的数据库信息，并初始化数据库连接池
     */
    static {
        try {
            Properties properties = new Properties();
            InputStream DBConfig = DBConnectionPool.class.getClassLoader().getResourceAsStream("db_config.properties");
            properties.load(DBConfig);
            Class.forName(properties.getProperty("driver_name"));
            String url = properties.getProperty("url");
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            int corePoolSize = Integer.parseInt(properties.getProperty("core_pool_size"));
            connections = new LinkedBlockingQueue<>(corePoolSize);
            for (int i = 0; i < corePoolSize; i++) {
                Connection connection = generateProxyConnection(url, user, password);
                connection.setAutoCommit(false); // 关闭自动提交事务
                connections.add(connection);
            }
        } catch (IOException | SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 生成代理connection，重写close方法
     *
     * @param url      数据库连接地址
     * @param user     账号
     * @param password 密码
     * @return 代理connection
     * @throws SQLException
     */
    private static Connection generateProxyConnection(String url, String user, String password) throws SQLException {
        Connection connection = DriverManager.getConnection(url, user, password);
        return (Connection) Proxy.newProxyInstance(DBConnectionPool.class.getClassLoader(),
                connection.getClass().getInterfaces(), new InvocationHandler() {
                    @Override
                    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
                        if (method.getName().equals("close")) { // 若调用connection的close方法，则放回集合中复用，并释放当前线程拥有的connection
                            connections.put((Connection) proxy); // 将代理connection放回连接池中
                            threadLocal.remove();
                            return null;
                        } else { // 否则，执行原connection的对应方法
                            return method.invoke(connection, args);
                        }
                    }
                });
    }

    @Override
    public Connection getConnection() {
        try {
            Connection connection = threadLocal.get();
            if (connection == null) {
                connection = connections.take(); // 获取连接（池中无连接时陷入等待，直到获取连接）
                threadLocal.set(connection); // 独占连接
            }
            return connection;
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return null;
    }

    @Override
    public <T> T unwrap(Class<T> iFace) throws SQLException {
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> iFace) throws SQLException {
        return false;
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {

    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {

    }

    @Override
    public int getLoginTimeout() throws SQLException {
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }
}

DBUtil数据库工具类

public class DBUtil {

    private static DBConnectionPool dbConnectionPool = new DBConnectionPool();

    private DBUtil() {
    }

    /**
     * 获取数据库连接（仅限于当前线程）
     *
     * @return 数据库连接
     */
    public static Connection getConnection() {
        return dbConnectionPool.getConnection();
    }

    /**
     * 提交事务
     */
    public static void commit() {
        try {
            Connection connection = getConnection();
            if (connection != null) {
                connection.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 回滚事务
     */
    public static void rollback() {
        try {
            Connection connection = getConnection();
            if (connection != null) {
                connection.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 关闭数据库资源
     *
     * @param resources 数据库资源
     */
    public static void close(AutoCloseable... resources) {
        for (AutoCloseable resource : resources) {
            if (resource != null) {
                try {
                    resource.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

User实体类

public class User {

    private String id;

    private String name;

    private int age;

    public User() {
    }

    public User(String id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public String getId() {
        return id;
    }

    public void setId(String 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;
    }

    @Override
    public String toString() {
        return "User{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

Dao层基类

public abstract class Dao<E> {

    private Class<E> entityClass;

    /**
     * 无参构造（初始化泛型类型entityClass属性）
     */
    public Dao() {
        ParameterizedType type = (ParameterizedType) getClass().getGenericSuperclass();
        entityClass = (Class<E>) type.getActualTypeArguments()[0];
    }

    /**
     * 单个/批量insert
     *
     * @param sql        sql语句
     * @param parameters 参数
     */
    protected void insert(String sql, Object... parameters) {
        executeIDUSql(sql, parameters);
    }

    /**
     * 单个/批量delete
     *
     * @param sql        sql语句
     * @param parameters 参数
     */
    protected void delete(String sql, Object... parameters) {
        executeIDUSql(sql, parameters);
    }

    /**
     * 单个/批量update
     *
     * @param sql        sql语句
     * @param parameters 参数
     */
    protected void update(String sql, Object... parameters) {
        executeIDUSql(sql, parameters);
    }

    /**
     * 执行插入、删除、更新sql语句（执行并提交事务）
     *
     * @param sql        sql语句
     * @param parameters 参数
     */
    private void executeIDUSql(String sql, Object... parameters) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            setParameter(statement, parameters);
            statement.execute();
            connection.commit();
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            close(connection, statement);
        }
    }

    /**
     * 单个select
     *
     * @param sql        sql语句
     * @param parameters 参数
     * @return 实体对象
     */
    protected E select(String sql, Object... parameters) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql + " limit 1");
            setParameter(statement, parameters);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                E entity = mapping(resultSet);
                return entity;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, statement, resultSet);
        }
        return null;
    }

    /**
     * 批量select
     *
     * @param sql        sql语句
     * @param parameters 参数
     * @return 实体对象列表
     */
    protected List<E> selectInBatch(String sql, Object... parameters) {
        List<E> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            setParameter(statement, parameters);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(mapping(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, statement, resultSet);
        }
        return list;
    }

    /**
     * 获取数据库连接
     *
     * @return 数据库连接
     */
    private Connection getConnection() {
        return DBUtil.getConnection();
    }

    /**
     * statement设置参数（参数需要按照占位符顺序依次传入）
     *
     * @param statement  statement
     * @param parameters 参数列表
     */
    private void setParameter(PreparedStatement statement, Object... parameters) {
        try {
            for (int i = 0; i < parameters.length; i++) {
                statement.setObject(i + 1, parameters[i]);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 结果集映射生成实体对象
     *
     * @param resultSet 结果集
     * @return 实体对象
     */
    private E mapping(ResultSet resultSet) {
        E entity = null;
        try {
            entity = entityClass.newInstance();
            Field[] fields = entityClass.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                Object value = resultSet.getObject(field.getName());
                if (value instanceof Timestamp) {
                    value = DateTimeUtil.toLocalDateTime((Date) value); // DateTimeUtil工具类：https://blog.csdn.net/weixin_41083377/article/details/115491673
                }
                field.set(entity, value);
            }
        } catch (InstantiationException | IllegalAccessException | SQLException e) {
            e.printStackTrace();
        }
        return entity;
    }

    /**
     * 关闭资源
     *
     * @param resources 数据库连接资源
     */
    private void close(AutoCloseable... resources) {
        DBUtil.close(resources);
    }
}

Dao层实现类

public class UserDao extends Dao<User> {

	// 单例
    private static UserDao instance;

    private UserDao() {
    }

    public static UserDao getInstance() {
        if (instance == null) {
            synchronized (UserDao.class) {
                if (instance == null) {
                    instance = new UserDao();
                }
            }
        }
        return instance;
    }

    public void insertUser(User user) {
        insert("insert into user (id, name, age, date) values (?, ?, ?, ?)",
                user.getId(), user.getName(), user.getAge(), user.getDate());
    }

    public void deleteUserByName(String name) {
        delete("delete from user where name = ?", name);
    }

    public void updateUserByName(String name) {
        update("update user set id = 111 where name = ?", name);
    }

    public User selectUserByName(String name) {
        return select("select id, name, age, date from user where name = ?", name);
    }

    public List<User> selectUsersLikeName(String name) {
        return selectInBatch("select id, name, age, date from user where name like ?", "%" + name + "%");
    }
}