上篇文章中写了一个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); } }