数据库连接池
为什么要用连接池?
https://www.zhihu.com/question/349816338
简单说,为每一个请求都去获取数据库连接的,用完又断开连接的行为非常奢侈的,且耗时。线程池同样如此。
数据库连接池的原理如下图:
首次请求:
1. 用户请求进来
2. java代码
3. 到数据库连接池拿连接,初始化的连接池连接数量为0
4. 返回空连接
5. jdbc获取数据库连接
6. 返回连接,java程序curd
7. 把连接放回连接池,以便下次使用
后续请求:1->2->3->4返回连接,java程序curd->5 关闭连接,连接放回连接池
简单实现连接池
1、定义一个继承了DataSource的接口
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.logging.Logger;
public interface MyDataSourceInterface extends DataSource {
@Override
default Connection getConnection() throws SQLException {
return null;
}
@Override
default Connection getConnection(String username, String password) throws SQLException {
return null;
}
@Override
default <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
default boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@Override
default PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
default void setLogWriter(PrintWriter out) throws SQLException {
}
@Override
default void setLoginTimeout(int seconds) throws SQLException {
}
@Override
default int getLoginTimeout() throws SQLException {
return 0;
}
@Override
default Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
}
2、定义一个抽象类并实现自定义接口
import java.sql.*;
public abstract class MyAbstractDataSource implements MyDataSourceInterface{
private String userName;
private String password;
private Driver driver;
private String url;
//最大连接数
private int maxActivePool;
//最大空闲数
private int maxIdlePool;
//最大连接等待时间
private int maxWaitTimeOut;
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Driver getDriver() {
return driver;
}
public void setDriver(Driver driver) {
this.driver = driver;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public int getMaxActivePool() {
return maxActivePool;
}
public void setMaxActivePool(int maxActivePool) {
this.maxActivePool = maxActivePool;
}
public int getMaxIdlePool() {
return maxIdlePool;
}
public void setMaxIdlePool(int maxIdlePool) {
this.maxIdlePool = maxIdlePool;
}
public int getMaxWaitTimeOut() {
return maxWaitTimeOut;
}
public void setMaxWaitTimeOut(int maxWaitTimeOut) {
this.maxWaitTimeOut = maxWaitTimeOut;
}
@Override
public Connection getConnection() throws SQLException {
return this.getConnection();
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return DriverManager.getConnection(url,username,password);
}
}
3、定义一个代理对象,代理连接对象,拦截关闭连接的操作
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
public class ConnectionProxy implements InvocationHandler {
/**被代理的连接对象*/
private Connection connection;
/**代理的连接对象*/
private Connection connectionProxy;
/**数据源*/
private MyDataSource myDataSource;
public Connection getConnection() {
return connection;
}
public void setConnection(Connection connection) {
this.connection = connection;
}
public Connection getConnectionProxy() {
return connectionProxy;
}
public void setConnectionProxy(Connection connectionProxy) {
this.connectionProxy = connectionProxy;
}
public MyDataSource getMyDataSource() {
return myDataSource;
}
public void setMyDataSource(MyDataSource myDataSource) {
this.myDataSource = myDataSource;
}
public ConnectionProxy(Connection connection, MyDataSource myDataSource) {
this.connection = connection;
this.myDataSource = myDataSource;
/**
* loader: 用哪个类加载器去加载代理对象
* interfaces:动态代理类需要实现的接口
* h:动态代理方法在执行时,会调用h里面的invoke方法去执行
*/
this.connectionProxy= (Connection) Proxy.newProxyInstance(Thread.currentThread().getContextClassLoader(),new Class<?>[]{Connection.class},this);
}
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
String methodName = method.getName();
if ("close".equalsIgnoreCase(methodName)){
myDataSource.close(this);
return null;
}else {
return method.invoke(connection,args);
}
}
}
4、定义一个连接对象继承自定义的抽象连接对象
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class MyDataSource extends MyAbstractDataSource {
/**连接池*/
private List<ConnectionProxy> activePool = new ArrayList<>();
/**空闲池*/
private List<ConnectionProxy> idlePool = new ArrayList<>();
private final Object monitor = new Object();
@Override
public Connection getConnection() throws SQLException {
ConnectionProxy connectionProxy = getConnectionProxy(super.getUserName(),super.getPassword());
return connectionProxy.getConnectionProxy();
}
private ConnectionProxy getConnectionProxy(String url, String password) throws SQLException {
ConnectionProxy connectionProxy = null;
while(connectionProxy == null){
synchronized (monitor){
if (!idlePool.isEmpty()){
connectionProxy=idlePool.remove(0);
}else {
if (activePool.size()<super.getMaxActivePool()){
connectionProxy = new ConnectionProxy(super.getConnection(),this);
}
}
}
try {
monitor.wait(super.getLoginTimeout());
} catch (InterruptedException e) {
e.printStackTrace();
}
}
if (connectionProxy != null){
activePool.add(connectionProxy);
}
return connectionProxy;
}
public void close(ConnectionProxy connectionProxy){
synchronized (monitor){
activePool.remove(connectionProxy);
if (idlePool.size()<super.getMaxIdlePool()){
idlePool.add(connectionProxy);
}
monitor.notify();
}
}
}
常用的数据库连接池技术
Druid、HikariCP
Druid:https://github.com/alibaba/druid/wiki/druid连接池介绍
Druid和Spring的使用
druid.properties配置文件
druid.url=jdbc:mysql://127.0.0.1/db_student?serverTimezone=UTC
#这个可以缺省的,会根据url自动识别
druid.driverClassName=com.mysql.cj.jdbc.Driver
druid.username=root
druid.password=abcd
##初始连接数,默认0
druid.initialSize=10
#最大连接数,默认8
druid.maxActive=30
#最小闲置数
druid.minIdle=10
#获取连接的最大等待时间,单位毫秒
druid.maxWait=2000
#缓存PreparedStatement,默认false
druid.poolPreparedStatements=true
#缓存PreparedStatement的最大数量,默认-1(不缓存)。大于0时会自动开启缓存PreparedStatement,所以可以省略上一句设置
druid.maxOpenPreparedStatements=20
spring配置文件
<!--引入druid配置文件-->
<context:property-placeholder location="classpath:druid.properties" />
<!--druid连接池-->
<bean name="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="url" value="${druid.url}" />
<property name="driverClassName" value="${druid.driverClassName}" />
<property name="username" value="${druid.username}" />
<property name="password" value="${druid.password}" />
<property name="initialSize" value="${druid.initialSize}"/>
<property name="maxActive" value="${druid.maxActive}" />
<property name="minIdle" value="${druid.minIdle}" />
<property name="maxWait" value="${druid.maxWait}" />
<property name="poolPreparedStatements" value="${druid.poolPreparedStatements}" />
<property name="maxOpenPreparedStatements" value="${druid.maxOpenPreparedStatements}" />
</bean>
HikariCP官方优化说明:
- 字节码精简 :优化代码,直到编译后的字节码最少,这样,CPU缓存可以加载更多的程序代码;
- 优化代理和拦截器:减少代码,例如HikariCP的Statement proxy只有100行代码,只有BoneCP的十分之一;
- 自定义数组类型(FastList)代替ArrayList:避免每次get()调用都要进行range check,避免调用remove()时的从头到尾的扫描;
- 自定义集合类型(ConcurrentBag:提高并发读写的效率;
- 其他针对BoneCP缺陷的优化,比如对于耗时超过一个CPU时间片的方法调用的研究。
详细介绍:http://blog.didispace.com/Springboot-2-0-HikariCP-default-reason/