首先有一个数据库池接口
import java.sql.Connection;
/**
* MySQL连接池接口
*/
public interface MySqlPool {
void init();//池子初始化
void destory();//销毁
Connection getConn();//获得连接
void release(Connection conn);//释放连接
}
二:编写数据库连接工具类
import java.sql.*;
/**
* 连接数据库
*/
public class MysqlUtils {
private static final String URL="jdbc:mysql://localhost:3306/test";
static {
try {
//从8.X开始 com.mysql.jdbc.Driver 驱动过时了
//取而代之的是com.mysql.cj.jdbc.Driver
//这里使用的是mysql-connector-java.5.x.jar
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection createConn() throws SQLException {
return DriverManager.getConnection(URL,"userName","password");
}
三、编写MySQLPool的实现类
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Objects;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;
/**
* 涉及到枚举单例、多线程、阻塞队列
*/
public class MysqlPoolImpl implements MySqlPool {
/*单例设计模式开始*/
private MysqlPoolImpl() {
idle = new LinkedBlockingQueue<>();//空闲连接
busy = new LinkedBlockingQueue<>();//繁忙连接
init();
}
//单例的最佳实践:枚举实现单例
public enum PoolEnum {
INSTANCE;
private MySqlPool pool = null;
PoolEnum() {
pool = new MysqlPoolImpl();
}
public MySqlPool getPoolInstance() {
return pool;
}
}
/*单例设计模式结束*/
//当状态是原子就不会有安全问题,有中间态就不安全
// 静态资源--cdn 静态化,动静态分离
//读请求--缓存
//写请求:集群/分布式/微服务
private final int INIT_SIZE = 10;//初始连接数为10个
private AtomicInteger maxSize = new AtomicInteger(20);//最大连接数
private AtomicInteger activeSize = new AtomicInteger(0);//当前已有的连接数
private LinkedBlockingQueue<Connection> idle;//空闲队列
private LinkedBlockingQueue<Connection> busy;//繁忙队列
public void init() {
//初始化10个连接
Connection conn;
for (int i = 0; i < INIT_SIZE; i++) {
conn = MysqlUtils.createConn();
Objects.requireNonNull(conn,"连接不能为空"+getClass().getName());
idle.offer(conn);
}
}
@Override
public void destory() {
}
@Override
public Connection getConn() {
//idle 有值 直接取
Connection conn = idle.poll();
if (null != conn) {
busy.offer(conn);
return conn;
}
//idle 无值,若池子未满,创建
//原子的双重判断,规避锁
if (activeSize.get() < maxSize.get()) {//性能开销不一样
if (activeSize.incrementAndGet() < this.maxSize.get()) {
conn = MysqlUtils.createConn();
busy.offer(conn);
return conn;
}
}
//池满,全繁忙,等待返回
try {
String name = Thread.currentThread().getName();
System.out.println(name);
//10秒钟没有拿到
conn = idle.poll(10000, TimeUnit.MILLISECONDS);
Objects.requireNonNull(conn, name + "连接超时,抛出空指针异常,正常现象");
} catch (InterruptedException e) {
e.printStackTrace();
}
return null;
}
@Override
public void release(Connection conn) {
busy.remove(conn);
idle.offer(conn);
}
}
四、编写多线程测试,随着for循环的线程数的不断增加,出现的问题越来越多。值得思考
public static void main(String[] args) {
//获取线程池实例
final MySqlPool pool = PoolEnum.INSTANCE.getPoolInstance();
for (int i = 0; i < 1000; i++) {
//创建11个线程//可以使用JDK自带的线程池ExecutorService创建 new Thread性能开销大
new Thread(() -> {
Connection conn = null;
try {
conn = pool.getConn();
//执行操作
PreparedStatement preparedStatement =
conn.prepareStatement("select COUNT(title) c from video");
ResultSet resultSet = preparedStatement.executeQuery();
System.out.println(resultSet.next());
System.out.println(resultSet.getObject("c"));
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
pool.release(conn); //关闭连接
}
}
}).start();
}
}