池:同一类对象集合
连接池的作用
1. 资源重用
由于数据库连接得到重用,避免了频繁创建、释放连接引起的大量性能开销。在减少系统消耗的基础上,另一方面也增进了系统运行环境的平稳性(减少内存碎片以及数据库临时进程/线程的数量)。
2. 更快的系统响应速度
数据库连接池在初始化过程中,往往已经创建了若干数据库连接置于池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言,直接利用现有可用连接,避免了数据库连接初始化和释放过程的时间开销,从而缩减了系统整体响应时间。
3. 新的资源分配手段
对于多应用共享同一数据库的系统而言,可在应用层通过数据库连接的配置,使用数据库连接池技术。设置某一应用最大可用数据库连接数,避免某一应用独占所有数据库资源。
4. 统一的连接管理,避免数据库连接泄漏
在较为完备的数据库连接池实现中,可根据预先设定的连接占用超时时间,强制收回被超时占用的连接。从而避免了常规数据库连接操作中可能出现的资源泄漏(当程序存在缺陷时,申请的连接忘记关闭,这时候,就存在连接泄漏了)。
示例
配置文件
jdbcDriver=com.mysql.jdbc.Driver
jdbcurl=jdbc:mysql://localhost:3306/mybatis
userName=root
password=root
initCount=10
stepSize=4
poolMaxSize=150
数据库连接封装
public class PooledConnection {
private boolean isBusy = false;
private Connection connection;
public Connection getConnection() {
return connection;
}
public void setConnection(Connection connection) {
this.connection = connection;
}
public boolean isBusy() {
return isBusy;
}
public void setBusy(boolean isBusy) {
this.isBusy = isBusy;
}
public PooledConnection(Connection connection, boolean isBusy) {
this.connection = connection;
this.isBusy = isBusy;
}
public void close() {
this.isBusy = false;
}
public ResultSet queryBysql(String sql) {
Statement sm = null;
java.sql.ResultSet rs = null;
try {
sm = connection.createStatement();
rs = sm.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
}
连接池接口
public interface IMyPool {
PooledConnection getConnection();
void createConnection(int count);
}
实现
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 MyPoolImpl implements IMyPool {
private static String jdbcDriver = null;
private static String jdbcUrl = null;
private static String userName = null;
private static String password = null;
private static int initCount;
private static int stepSize;
private static int poolMaxSize;
private static Vector pooledConnections = new Vector<>();
public MyPoolImpl() {
init();
}
private void init() {
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
//字节信息 key value 形式化
Properties pro = new Properties();
try {
pro.load(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
jdbcDriver = pro.getProperty("jdbcDriver");
jdbcUrl = pro.getProperty("jdbcurl");
userName = pro.getProperty("userName");
password = pro.getProperty("password");
initCount = Integer.valueOf(pro.getProperty("initCount"));
stepSize = Integer.valueOf(pro.getProperty("stepSize"));
poolMaxSize = Integer.valueOf(pro.getProperty("poolMaxSize"));
try {
Driver mysqlDriver = (Driver) Class.forName(jdbcDriver).newInstance();
DriverManager.registerDriver(mysqlDriver);
createConnection(initCount);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public PooledConnection getConnection() {
if (pooledConnections.size() == 0) {
System.out.println("获取链接管道失败,原因是连接池中没有可用管道");
throw new RuntimeException("创建管道对象失败,原因是即将超过最大上限值");
}
//连接池中的管道是没有超时 没有其他线程占用
PooledConnection connection = getRealConnection();
while (connection == null) {
createConnection(stepSize);
connection = getRealConnection();
try {
Thread.sleep(300);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
return connection;
}
@Override
public void createConnection(int count) {
if (poolMaxSize > 0 && pooledConnections.size() + count > poolMaxSize) {
System.out.println("创建管道对象失败,原因是即将超过最大上限值");
throw new RuntimeException("创建管道对象失败,原因是即将超过最大上限值");
}
for (int i = 0; i < count; i++) {
try {
Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);
PooledConnection pooledConnection = new PooledConnection(conn, false);
pooledConnections.add(pooledConnection);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private synchronized PooledConnection getRealConnection() {
for (PooledConnection conn : pooledConnections) {
if (!conn.isBusy()) {
Connection connection = conn.getConnection();
try {
//发送一个信息给数据库 2000毫秒内 收到返回信息 认为 这个管道没有超时
if (!connection.isValid(2000)) {
Connection validConn = DriverManager.getConnection(jdbcUrl, userName, password);
conn.setConnection(validConn);
}
} catch (SQLException e) {
e.printStackTrace();
}
conn.setBusy(true);
return conn;
}
}
return null;
}
}
连接池管理
public class PoolManager {
private static class createPool {
private static MyPoolImpl poolImpl = new MyPoolImpl();
}
/**
* 内部类单利模式产生使用对象
*@return
*/
public static MyPoolImpl getInstance() {
return createPool.poolImpl;
}
}
测试类
import java.sql.ResultSet;
import java.sql.SQLException;
public class MypoolTest {
private static MyPoolImpl poolImpl = PoolManager.getInstance();
public synchronized static void selctData() {
PooledConnection connection = poolImpl.getConnection();
ResultSet rs = connection.queryBysql("SELECT * FROM user");
try {
while (rs.next()) {
System.out.println(rs.getString("ID") + "\t\t");
System.out.println(rs.getString("USERNAME") + "\t\t");
System.out.println(rs.getString("PASSWORD") + "\t\t");
System.out.println();
}
rs.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
for (int i = 0; i < 1500; i++) {
new Thread(new Runnable() {
@Override
public void run() {
selctData();
}
}).start();
}
}
}