1. What is the database connection pool?
Database connection is an expensive resource. Creating a database connection is a time-consuming operation, especially under the condition of multi-thread concurrency. The efficient management of database connection can affect the performance index of the program. The database connection pool is exactly for this problem brought up. The database connection pool is responsible for allocating, managing and releasing database connections. It allows an application to reuse an existing database connection instead of re-establishing a new connection. Using the database connection pool will significantly improve the performance of database operations;
2. What are the technical solutions for the database connection pool?
(1) C3P0
(2) DBCP
(3) Proxool
(4) Tomcat Jdbc Pool
(5) BoneCP
(6) Druid
(7) HiKariCP
Database connection pool is a kind of pooling technology;
Pooling technology: http access (httpclient), redis access (redisPool), thread (thread pool), etc.;
Interview: (Interview with on-site code writing)
1. Write a queue by hand (you cannot use the tools that come with jdk)
2. Write a stack by hand
3. Write a HashMap by hand
4. Write a producer consumer by hand
5. Write a (jvm) cache by hand, even the existing classes in jdk cannot be used
6….
Pure handwritten database connection pool implementation
——MyDataSourceInterface
package com.atqiyu.pool;
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;
/**
* @author 7575
* @version 1.0
* I don't have him either, but I'm familiar with you!
*/
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;
}
//The following method can not be implemented
@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;
}
}
——MyAbstractDataSource
package com.atqiyu.pool;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* @author 7575
* @version 1.0
* I don't have him either, but I'm familiar with you!
*/
public abstract class MyAbstractDataSource implements MyDataSourceInterface {
private String url;
private String driver;
private String user;
private String password;
//Maximum number of connections in use
private int poolMaxActiveConnections = 10;
//Maximum number of idle connections
private int poolMaxIdleConnections = 5;
/ / Get a connection from the connection pool to wait for the maximum number of milliseconds
private int poolTimeToWait = 30000;
public int getPoolMaxActiveConnections() {
return poolMaxActiveConnections;
}
public void setPoolMaxActiveConnections(int poolMaxActiveConnections) {
this. poolMaxActiveConnections = poolMaxActiveConnections;
}
public int getPoolMaxIdleConnections() {
return poolMaxIdleConnections;
}
public void setPoolMaxIdleConnections(int poolMaxIdleConnections) {
this. poolMaxIdleConnections = poolMaxIdleConnections;
}
public int getPoolTimeToWait() {
return poolTimeToWait;
}
public void setPoolTimeToWait(int poolTimeToWait) {
this. poolTimeToWait = poolTimeToWait;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getDriver() {
return driver;
}
public void setDriver(String driver) {
this.driver = driver;
}
public String getUser() {
return user;
}
public void setUser(String user) {
this. user = user;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public Connection getConnection() throws SQLException {
return getConnection(user, password);
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return doGetConnection(username, password);
}
/**
* Get database connection
*
* @param username
* @param password
* @return
*/
private Connection doGetConnection(String username, String password) throws SQLException {
Connection connection = DriverManager. getConnection(url, username, password);
return connection;
}
}
——MyDataSource
package com.atqiyu.pool;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* @author 7575
* @version 1.0
* I don't have him either, but I'm familiar with you!
*
* The connection pool of the data source
*/
public class MyDataSource extends MyAbstractDataSource {
// idle connection pool
private final List<ConnectionProxy> idleConnections = new ArrayList<ConnectionProxy>();
//activated connection pool
private final List<ConnectionProxy> activeConnections = new ArrayList<ConnectionProxy>();
//The monitor object is used for some uses related to some synchronous operations
private final Object monitor = new Object();
//The monitor object is used for some uses related to some synchronous operations
private final Object watch = new Object();
/**
* Override the method of the parent class, returning a proxy connection
* @return
* @throws SQLException
*/
@Override
public Connection getConnection() throws SQLException {
ConnectionProxy connectionProxy = getConnectionProxy(super. getUser(), super. getPassword());
return connectionProxy. getProxyConnection();
}
/**
* get connection
* @param username
* @param password
* @return
*/
public ConnectionProxy getConnectionProxy(String username,String password) throws SQLException {
boolean wait = false;
ConnectionProxy connectionProxy = null;
// Initially there is no connection
while (connectionProxy == null) {
// make a synchronous thread
synchronized (monitor) {
//If the idle connection is not empty, then you can directly get the connection
if (!idleConnections. isEmpty()) {
connectionProxy = idleConnections. remove(0);
} else {
//There is no idle connection available, then we need to obtain a new connection (that is, we need to create a connection)
if (activeConnections. size() < super. getPoolMaxActiveConnections()) {
//If the number of currently activated connections is less than the maximum number of connections we allow, then a new connection can be created at this time, otherwise it cannot be created
connectionProxy = new ConnectionProxy(super. getConnection(), this);
}
//Otherwise, you cannot create a new connection, you need to wait, etc. private int poolTimeToWait = 30000; milliseconds
}
}
if (!wait) {
wait = true;
}
if (connectionProxy == null) {
try {
//The connection object is empty, so you need to wait;
monitor.wait(super.getPoolTimeToWait());
} catch (InterruptedException e) {
e.printStackTrace();
//In case the wait is interrupted by the loop, exit the loop
break;
}
}
}
if (connectionProxy != null) {
//The connection object is not empty, indicating that the connection has been obtained
activeConnections. add(connectionProxy);
}
// return connection object
return connectionProxy;
}
/**
* Close the connection, but instead of closing the connection, return the connection pool to the connection pool
* @param connectionProxy
*/
public void closeConnection(ConnectionProxy connectionProxy) {
synchronized (monitor) {
//Close the connection, then turn the active connection into an idle connection
activeConnections. remove(connectionProxy);
if (idleConnections. size() < super. getPoolMaxIdleConnections()) {
idleConnections. add(connectionProxy);
}
//Notify, wake up the above thread waiting to get the connection
monitor. notifyAll();
}
}
}
——ConnectionProxy
package com.atqiyu.pool;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
/**
* @author 7575
* @version 1.0
* I don't have him either, but I'm familiar with you!
*
* Use dynamic proxy to realize proxy for database connection
*/
public class ConnectionProxy implements InvocationHandler {
//real connection
private Connection realConnection;
// proxy connection
private Connection proxyConnection;
//Hold the data source object
private MyDataSource myDataSource;
public Connection getRealConnection() {
return realConnection;
}
public void setRealConnection(Connection realConnection) {
this. realConnection = realConnection;
}
public Connection getProxyConnection() {
return proxyConnection;
}
public void setProxyConnection(Connection proxyConnection) {
this.proxyConnection = proxyConnection;
}
public MyDataSource getMyDataSource() {
return myDataSource;
}
public void setMyDataSource(MyDataSource myDataSource) {
this.myDataSource = myDataSource;
}
/**
* Construction method
*
* @param realConnection
* @param myDataSource
*/
public ConnectionProxy(Connection realConnection, MyDataSource myDataSource) {
//Initialize the real connection
this. realConnection = realConnection;
//Initialize the data source
this.myDataSource = myDataSource;
//Initialize proxy connection
this.proxyConnection = (Connection) Proxy.newProxyInstance(Connection.class.getClassLoader(),
new Class<?>[] {Connection. class},
this);
}
/**
* When calling a method in the Connection object, it will first be intercepted by the invoke method
* @param proxy
* @param method
* @param args
* @return
* @throws Throwable
*/
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
// Get what method of the Connection object is currently called
String methodName = method. getName();
if (methodName. equalsIgnoreCase("close")) {
//TODO return the connection to the connection pool
myDataSource. closeConnection(this);
return null;
} else {
return method. invoke(realConnection, args);
}
}
}