概述
不使用连接池的缺点:
- tcp连接三次握手消耗时间
- 消耗内存严重
使用池化思想, 达到连接的可复用, 节省时间和资源
代码
连接池接口
package jdbcPool;
public interface IPool {
//返回我们自己封装的连接对象
PooledConnection getConnection();
void createConnections(int count);
}
连接类(由于jdk自己的连接对象没有状态标识 所以我们自己定义连接对象)
package jdbcPool;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class PooledConnection {
private Connection conn = null;
//标识该连接是否被占用
private boolean isBusy = false;
public PooledConnection(Connection conn, boolean isBusy) {
this.conn = conn;
this.isBusy = isBusy;
}
public ResultSet queryBySql(String sql) {
Statement sm = null;
ResultSet rs = null;
try {
sm = conn.createStatement();
rs = sm.executeQuery(sql);
}
catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public int updateBySql(String sql) {
Statement sm = null;
int count = -1;
try {
sm = conn.createStatement();
count = sm.executeUpdate(sql);
}
catch (SQLException e) {
e.printStackTrace();
}
return count;
}
public Connection getConn() {
return conn;
}
public void setConn(Connection conn) {
this.conn = conn;
}
public boolean isBusy() {
return isBusy;
}
public void setBusy(boolean isBusy) {
this.isBusy = isBusy;
}
//关闭连接 设置状态为空闲
public void close() {
isBusy = false;
}
}
连接池接口实现类
package jdbcPool;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Vector;
public class JdbcPool implements IPool {
private static String jdbcDriver = "";
private static String dbUrl = "";
private static String dbUsername = "";
private static String dbPassword = "";
private static int initConnCount;
private static int maxConnnects;
private static int incrementalcount;
private static Vector<PooledConnection> vector = new Vector<PooledConnection>();
//读取配置文件
public void init() {
InputStream in = JdbcPool.class.getClassLoader()
.getResourceAsStream("jdbcPool/jdbc.properties");
Properties pt = new Properties();
try {
pt.load(in);
}
catch (IOException e) {
e.printStackTrace();
}
jdbcDriver = pt.getProperty("jdbcDriver");
dbUrl = pt.getProperty("dbUrl");
dbUsername = pt.getProperty("dbUsername");
dbPassword = pt.getProperty("dbPassword");
initConnCount = Integer.valueOf(pt.getProperty("initConnCount"));
maxConnnects = Integer.valueOf(pt.getProperty("maxConnnects"));
incrementalcount = Integer.valueOf(pt.getProperty("incrementalcount"));
try {
//直接反射就可以
/* Driver driver = (Driver)(Class.forName(jdbcDriver).newInstance());
DriverManager.registerDriver(driver); */
Class.forName(jdbcDriver);
//我们要创建数据库的连接,然后把连接放到vector里面
createConnections(initConnCount);
}
catch (InstantiationException e) {
e.printStackTrace();
}
catch (IllegalAccessException e) {
e.printStackTrace();
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
catch (SQLException e) {
e.printStackTrace();
}
}
//synchronized很重要 PooledConnection中的isBusy状态需要被同步修改,否则会造成多个线程拿到同一个连接的情况
public synchronized PooledConnection getConnection() {
if (vector.size() <= 0) {
System.out.println("连接池中还没有连接!");
throw new RuntimeException("连接池中还没有连接!");
}
PooledConnection conn = getActiveConnection();
//等于null的情况就是连接池中的连接全部为忙碌的情况
if (conn == null) {
// 这时候就需要对连接池进行增长
createConnections(incrementalcount);
// 可能还是拿不到连接 被其他线程抢占了
conn = getActiveConnection();
// 拿不到连接则等待
while (conn == null) {
try {
Thread.sleep(300);
}
catch (InterruptedException e) {
e.printStackTrace();
}
conn = getActiveConnection();
}
}
return conn;
}
//我们从池中拿连接
private PooledConnection getActiveConnection() {
for (PooledConnection conn : vector) {
if (!conn.isBusy()) {
Connection trueconn = conn.getConn();
try {
//判断物理连接是否有效(可能存在该对象但是物理连接已经失效了的情况)
if (!trueconn.isValid(0)) {//该方法会真正的操作数据库的临时表来判断该连接是否有效 0不校验超时
//失效 创建新的物理连接 并设置到封装的连接对象中
Connection newconn = DriverManager.getConnection(dbUrl,
dbUsername,
dbPassword);
conn.setConn(newconn);
}
}
catch (SQLException e) {
e.printStackTrace();
}
//返回连接前设置该连接已被占用
conn.setBusy(true);
return conn;
}
}
return null;
}
public void createConnections(int count) {
for (int i = 0; i < count; i++) {
//我们创建连接的时候,必须要判断这个池中的连接要小于我们的最大连接
if (maxConnnects > 0 && vector.size() >= maxConnnects) {
System.out.println("连接池中的连接数量已经达到了最大值!");
throw new RuntimeException("连接池中的连接数量已经达到了最大值!");
}
try {
Connection conn = DriverManager.getConnection(dbUrl,
dbUsername,
dbPassword);
vector.add(new PooledConnection(conn, false));
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
}
properties文件
jdbcDriver = com.mysql.jdbc.Driver
dbUrl = jdbc:mysql://localhost:3306/consult
dbUsername = myConsult
dbPassword = 123456
#数据库连接池中的初始连接数
initConnCount = 10
#最大连接数
maxConnnects = 100
#增长步长
incrementalcount = 2
连接池维护类(单例模式)
package jdbcPool;
public class DBManager {
//静态内部类实现 n个线程同时加载一个内部类的时候 永远是线程互斥的
private static class CreatePool {
private static JdbcPool pool = new JdbcPool();
}
public static JdbcPool getInstance() {
return CreatePool.pool;
}
}
测试类
package jdbcPool;
import java.sql.ResultSet;
import org.junit.Before;
import org.junit.Test;
public class JdbcPoolTest {
JdbcPool pool = DBManager.getInstance();
@Before
public void before() {
pool.init();
}
@Test
public void select() throws Exception {
PooledConnection conn = pool.getConnection();
ResultSet rs = conn.queryBySql("select * from consult_contract");
System.out.println("线程名:" + Thread.currentThread().getName());
if (rs.next()) {
System.out.print(rs.getString("PSPTID") + "\t");
System.out.print(rs.getString("CONTRACT_CODE") + "\t");
System.out.print(rs.getString("ACTIVETIME") + "\t\n");
}
rs.close();
conn.close();
}
// 并发测试
@Test
public void threadTest() {
for (int i = 0; i < 100; i++) {
new Thread(new Runnable() {
public void run() {
try {
select();
}
catch (Exception e) {
e.printStackTrace();
}
}
}).start();
}
}
}