自定义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();        }    }}

CloseUtils：

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出现异常!!!");            }        }    }}

DEMO：

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);    }}