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