自定义JDBC数据库连接池小例子

上篇文章中写了一个JDBC的小例子，这篇文章写个数据库连接池的小例子吧。

package com.zkn.newlearn.jdbc.mysql.third;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.Properties;
import java.util.concurrent.locks.Condition;
import java.util.concurrent.locks.ReentrantLock;

/**
 * Created by wb-zhangkenan on 2017/5/3.
 *
 * @date 2017/05/03
 */
public class DataSourcePoolNew {
    /**
     * 最大连接数
     */
    private static final int COUNT = 10;
    /**
     * 存放数据库
     */
    private static final LinkedList<Connection> connections = new LinkedList<>();
    /**
     * 创建锁
     */
    private static final ReentrantLock lock = new ReentrantLock();
    private static final Condition notEmpty = lock.newCondition();
    private static final Condition notFull = lock.newCondition();
    /**
     * 数据库连接
     */
    private static String URL;
    /**
     * 用户名
     */
    private static String USER_NAME;
    /**
     * 密码
     */
    private static String PASS_WORD;
    /**
     * 驱动类型
     */
    private static String DRIVER_CLASS_NAME;
    /**
     * 存放属性信息
     */
    private static Properties properties = new Properties();

    /**
     * 初始化信息
     */
    static {
        InputStream is = DataSourcePoolNew.class.getResourceAsStream("driver.properties");
        try {
            properties.load(is);
            URL = (String) properties.get("url");
            USER_NAME = (String) properties.get("userName");
            PASS_WORD = (String) properties.get("passWord");
            DRIVER_CLASS_NAME = (String) properties.get("driverClassName");
            //加载驱动
            Class.forName(DRIVER_CLASS_NAME);
            Connection connection = null;
            for (int i = 0; i < 10; i++) {
                connection = DriverManager.getConnection(URL, USER_NAME, PASS_WORD);
                connections.add(connection);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取Connection
     */
    public static Connection getConnection() {
        final ReentrantLock reentrantLock = lock;
        reentrantLock.lock();
        try {
            //如果没有连接了，则等待着新放入的连接
            if (connections.isEmpty()) {
                notEmpty.await();
            }
            Connection connection = connections.removeFirst();
            notFull.signalAll();
            return connection;
        } catch (InterruptedException e) {
            e.printStackTrace();
        } finally {
            reentrantLock.unlock();
        }
        return null;
    }

    /**
     * 释放连接
     *
     * @param connection
     */
    public static void release(Connection connection) {
        final ReentrantLock reentrantLock = lock;
        reentrantLock.lock();
        try {
            if (connections.size() == COUNT) {
                notFull.await();
            }
            if (connection == null || connection.isClosed()) {
                connections.add(DriverManager.getConnection(URL, USER_NAME, PASS_WORD));
                notEmpty.signalAll();
                return;
            }
            //恢复默认值
            if (connection.getAutoCommit() == false) {
                connection.setAutoCommit(true);
            }
            connections.add(connection);
            notEmpty.signalAll();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            reentrantLock.unlock();
        }
    }

}

package com.zkn.newlearn.jdbc.mysql.second;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Created by wb-zhangkenan on 2017/5/3.
 *
 * @date 2017/05/03
 */
public final class CloseUtils {

    private static Logger log = LoggerFactory.getLogger(CloseUtils.class);

    /**
     * 关闭ResultSet
     *
     * @param resultSet
     */
    public static void close(ResultSet resultSet) {
        if (resultSet != null) {
            try {
                log.info("关闭了");
                resultSet.close();
            } catch (SQLException e) {
                log.error("关闭resultSet出现异常!!!");
            }
        }
    }

    /**
     * 关闭statement
     *
     * @param statement
     */
    public static void close(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                log.error("关闭statement出现异常!!!");
            }
        }
    }

    /**
     * 关闭preparedStatement
     *
     * @param preparedStatement
     */
    public static void close(PreparedStatement preparedStatement) {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                log.error("关闭preparedStatement出现异常!!!");
            }
        }
    }

    /**
     * 关闭connection
     * @param connection
     */
    public static void close(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                log.error("关闭connection出现异常!!!");
            }
        }
    }
}

package com.zkn.newlearn.jdbc.mysql.third;

import com.zkn.newlearn.jdbc.mysql.second.CloseUtils;
import org.junit.Test;
import java.sql.*;

/**
 * Created by zkn on 2017/5/3.
 */
public class JDBCPoolTest {


    /**
     * 查询操作
     */
    @Test
    public void testStatementQuery() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DataSourcePoolNew.getConnection();
            if(connection == null){
                return;
            }
            //获取sql的声明
            statement = connection.createStatement();
            //执行查询的操作
            resultSet = statement.executeQuery("SELECT * FROM province_china");
            //取出查询出来的数据
            StringBuilder sb = new StringBuilder();
            while (resultSet.next()) {
                sb.append(resultSet.getLong("id")).append("  ");
                //这里需要注意的是下标是从1开始的，不是从0开始的
                sb.append(resultSet.getString(2)).append("  ");
                sb.append(resultSet.getString("cname")).append("  ");
                System.out.println(sb.toString());
                //清空原来的数据
                sb.delete(0, sb.length());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            CloseUtils.close(resultSet);
            CloseUtils.close(statement);
            DataSourcePoolNew.release(connection);
        }
    }

    /**
     * 预编译查询
     */
    @Test
    public void testPreparedStatement() {
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet resultSet = null;
        try {
            //获取连接
            connection = DataSourcePoolNew.getConnection();
            if(connection == null){
                return;
            }
            String sql = "SELECT * FROM TABLE_NAME WHERE EMPID = ?";
            //获取sql声明
            pst = connection.prepareStatement(sql);
            //pst.setLong(1,2);
            //封装查询条件
            pst.setString(1, "32151");
            //执行sql的操作
            resultSet = pst.executeQuery();
            StringBuilder sb = new StringBuilder();
            while (resultSet.next()) {
                sb.append(resultSet.getLong("id")).append(" ");
                sb.append(resultSet.getString(2));
                System.out.println(sb.toString());
                //清空原来的数据
                sb.delete(0, sb.length());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResource(connection,pst,resultSet);
        }
    }

    /**
     * 单条插入
     */
    @Test
    public void testInsert() {
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet resultSet = null;
        try {
            //获取连接
            connection = DataSourcePoolNew.getConnection();
            if(connection == null){
                return;
            }
            //自动提交为false
            connection.setAutoCommit(false);
            //创建sql声明
            pst = connection.prepareStatement(
                    "INSERT INTO TABLE_NAME (NAME,EMPID,ORG_ID,ORG_CODE,IS_ADMIN,GMT_CREATE,GMT_MODIFIED) VALUES (?,"
                            + "?,?,?,?,now(),now())",
                    Statement.RETURN_GENERATED_KEYS);
            pst.setString(1, "张三");
            pst.setString(2, "784550");
            pst.setLong(3, 2);
            pst.setString(4, "0.1.2");
            pst.setInt(5, 1);
            //执行插入操作
            int count = pst.executeUpdate();
            if (count > 0) {
                System.out.println("插入成功!");
            } else {
                System.out.println("插入失败!");
            }
            resultSet = pst.getGeneratedKeys();
            while (resultSet.next()) {
                System.out.println(String.format("主键值为%d", resultSet.getLong(1)));
            }
            //提交操作
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            //异常回滚
            try {
                connection.rollback();
            } catch (SQLException ee) {
                ee.printStackTrace();
            }
        } finally {
            closeResource(connection,pst,resultSet);
        }
    }

    /**
     * 批量插入 需要设置 rewriteBatchedStatements=true
     */
    @Test
    public void testBatchInsert() {
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet resultSet = null;
        try {
            //获取连接
            connection = DataSourcePoolNew.getConnection();
            if(connection == null){
                return;
            }
            //自动提交为false
            connection.setAutoCommit(false);
            //创建sql声明
            pst = connection.prepareStatement(
                    "INSERT INTO TABLE_NAME (NAME,EMPID,ORG_ID,ORG_CODE,IS_ADMIN,GMT_CREATE,GMT_MODIFIED) VALUES (?,"
                            + "?,?,?,?,now(),now())",
                    Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < 10; i++) {
                pst.setString(1, "张三");
                pst.setString(2, "784550");
                pst.setLong(3, 2);
                pst.setString(4, "0.1.2");
                pst.setInt(5, 1);
                pst.addBatch();
            }
            int[] count = pst.executeBatch();
            if (count != null && count.length > 0) {
                System.out.println("插入成功!");
            } else {
                System.out.println("插入失败!");
            }
            resultSet = pst.getGeneratedKeys();
            while (resultSet.next()) {
                System.out.println(String.format("主键值为%d", resultSet.getLong(1)));
            }
            //提交操作
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            //异常回滚
            try {
                connection.rollback();
            } catch (SQLException ee) {
                ee.printStackTrace();
            }
        } finally {
            closeResource(connection,pst,resultSet);
        }
    }

    /**
     * 测试更新操作
     */
    @Test
    public void testUpdate() {
        Connection connection = null;
        PreparedStatement pst = null;
        try {
            //获取连接
            connection = DataSourcePoolNew.getConnection();
            if(connection == null){
                return;
            }
            //自动提交为false
            connection.setAutoCommit(false);
            //创建sql声明
            pst = connection.prepareStatement("update TABLE_NAME set name = ? where id >=? and id <= ? ");
            pst.setString(1, "李思思");
            pst.setLong(2, 1972);
            pst.setLong(3, 1995);
            int count = pst.executeUpdate();
            if (count > 0) {
                System.out.println("更新成功!");
            } else {
                System.out.println("更新失败");
                return;
            }
            //提交操作
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            //异常回滚
            try {
                connection.rollback();
            } catch (SQLException ee) {
                ee.printStackTrace();
            }
        } finally {
            closeResource(connection,pst);
        }
    }

    /**
     * 测试删除操作
     */
    @Test
    public void testDelete() {
        Connection connection = null;
        PreparedStatement pst = null;
        try {
            //获取连接
            connection = DataSourcePoolNew.getConnection();
            if(connection == null){
                return;
            }
            //自动提交为false
            connection.setAutoCommit(false);
            //创建sql的声明
            pst = connection.prepareStatement("DELETE FROM TABLE_NAME WHERE ID >=? AND ID <=? ");
            pst.setLong(1, 1972);
            pst.setLong(2, 1995);
            //执行sql
            pst.executeUpdate();
            //提交
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            //异常回滚
            try {
                connection.rollback();
            } catch (SQLException ee) {
                ee.printStackTrace();
            }
        } finally {
            closeResource(connection,pst);
        }
    }

    private void closeResource(Connection connection, PreparedStatement statement, ResultSet resultSet) {
        CloseUtils.close(resultSet);
        closeResource(connection, statement);
    }

    private void closeResource(Connection connection, PreparedStatement statement) {
        CloseUtils.close(statement);
        DataSourcePoolNew.release(connection);
    }

}