cool hit counter Write your own manual code to implement database connection pooling_Intefrankly

Write your own manual code to implement database connection pooling


reservoir: An advanced collection of( Set storage elements + Management style–> Improving efficiency), is a collection of objects of the same type available externally, as if( thread pool、 Database connection pools) characteristics: compound use sex( Each connection can be used repeatedly use), isolation( Each connection can only be fetched by one thread at a time)!! Connection Pooling Features: 1. Maximize concurrent compounding use, Improving response efficiency 2.Significant savings in server resources and (to a certain extent) reduction of significant initialization problems

Code implementation.

1. POOL Interface Design Ideas

I. External provision of copies use Connecting the inside of the package<—[pool]—> Create a connection in a pair

 // Extraction of connection pooling architecture interface
public interface MyPool {
// External availability of replicable use Connecting the inside of the package
PooledConnection getConnection();
 // Create connections internally
void createConnections(int count);

Wrapping classes for custom connection poolsbean( protozoaConnection not have a copy use sign, if notclose recycle off, then it cannot be judged that theconnection Is it in the use conditions) The member variable:

// Indicates busy sign      compound use sign  thread safety
private boolean busy = false;
 //real sql connectionconnection(java.sql.)
private Connection con;
// merely use come round Test today'sconnectionName, Easy to observe
private String connName;

Externally available closure methods

 Make the connection unavailable instead of actually turning it off
public void close() {
    this.busy = false;

Externally, a simple Test approach, That is, the connection was obtained afterwards, It will be possible to make usestatement Carry out implementationSql statement;

public ResultSet queryBySql(String sql) {
    Statement sttm = null;
    ResultSet rs = null;
    try {
        sttm = con.createStatement();
        rs = sttm.executeQuery(sql);
        //System.out.println(" The current connection number is:" + connName);
    } catch (SQLException e) {
    return rs;

The above simple PooledConnection connection packaging class is completed, the main function is as mentioned earlier, the DriverManager obtained Connection packaging to make it reusable (the connection is not used up to turn off), isolation is determined by a simple flag bit property busy (when busy is false, indicating that the connection is free, for true indicates that the connection is already in use)

3. MyPoolImpl (Key Key Implementation Class) is pool's implementation class

functionalities: 1. Initializing the data connectiondriver 2. Initialize connection pool capacity( Capacity Expansion) 3. Get the connection Single connection The member variable

private static String driver = null;
private static String url = null;
private static String user = null;
private static String password = null;
/**Connect the pipeline parameters in the pool**/
private static int initCount = 5;
private static int stepSize = 10;
private static int poolMaxSize = 55;
private static int expandTime = 0;
 /-Thread-safe collection for putting (reusable) database connection pipelines (collections previously used Vector, but found multiple threads and issued the wrong ConcurrentModificationException during testing) this also needs to be studied carefully.
 Connect the pool
private static CopyOnWriteArrayList<PooledConnection> pooledCons = new CopyOnWriteArrayList<PooledConnection>();

A few important implementations 1.Register the driver (modify the initialization parameters JdbcUtil operation according to the configuration file properties file)

private void init() {
    InputStream inStream = this.getClass().getClassLoader().getResourceAsStream("");
    Properties properties = new Properties();
    try {
    } catch (IOException e) {
        // If an exception is thrown here, it does not run below
    this.driver = properties.getProperty("jdbc_driver");
    this.url = properties.getProperty("jdbc_url");
    this.user = properties.getProperty("jdbc_username");
    this.password = properties.getProperty("jdbc_password");
    if(Integer.valueOf(properties.getProperty("initCount")) > 0) {
        this.initCount = Integer.valueOf(properties.getProperty("initCount"));
    }else if(Integer.valueOf(properties.getProperty("stepSize")) > 0) {
        this.stepSize = Integer.valueOf(properties.getProperty("stepSize"));
    }else if(Integer.valueOf(properties.getProperty("poolMaxSize")) > 0) {
        this.poolMaxSize = Integer.valueOf(properties.getProperty("poolMaxSize"));
    // Prepare to createDriverManager
    try {
        Driver dbDriver = (Driver) Class.forName(this.driver).newInstance();
    } catch (Exception e) {
        // TODO Auto-generated catch block
    // Get the connection, usecreate Method to get
    /**DriverManager.getConnection(url, user, password);**/

2. Initialize connection pool capacity( That is, to achievePool Interface method of creating a connection pool) All Consoles are for easy viewing

public void createConnections(int count) {
    //System.out.println(" (prefix indicating ordinal number, e.g. first, number two etc)"+expandTime+" sub-volume expansion, The expansion capacity is:"+count);
    if((pooledConnections.size() + count) <= poolMaxSize) {
        for(int i = 0;i < count ;i++) {
            try {
                // Get the connection Put in a thread-safe connection pool
                Connection conn = DriverManager.getConnection(url, user, password);
                PooledConnection pooledConnection = new PooledConnection(conn,false,String.valueOf(i));
                //System.out.println(" initialize"+(i + 1) + " individual connection");
            } catch (SQLException e) {
    System.out.println(" Number of current connection pool connections:"+ pooledConnections.size());
    System.out.println(" The maximum number of connection pools is:"+ this.poolMaxSize);

3.1 Externally available to obtain connection packaging classes (also a way to implement Internet)

public PooledConnection getConnection() {
//spring thought to take the connection, first determine whether there is a connection in the pipe collection
    if(this.pooledConnections.size() == 0) {
        System.out.println(" The connection pool is not connected!");
         // If not, manually create another handful of connection pools
    PooledConnection connection = getRealConnection();
     // If you still don't get it, all threads are in a busy state and need to be expanded
    while(connection == null) {
        connection = getRealConnection();
         try {//get the connection and wait for a while, to prevent the connection from being grabbed by another thread
        } catch (InterruptedException e) {
    return connection;

3.2 real Get the connection Packaging categorygetRealConnection()

// Synchronization methods, real Get the connection( Included in the connection package are:connection and flag bitsbusy)
private synchronized PooledConnection getRealConnection() {
    for(PooledConnection conn:pooledConnections) {
        // Determine if the connection is already occupied use
        if(!conn.isBusy()) {//false in order to use( leisure time),true take up responsibility for use( bustling)
            Connection connection = conn.getConnection();
            try {
                 // determine if the connection is connected to the database at the set time (true if the connection is connected)
                if(!connection.isValid(2000)) {
                    connection = DriverManager.getConnection(url, user, password);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
            return conn;
    // If the connections in the connection pool are all occupied use, returnnull, consist of a transfer use Function Handling( i.e. capacity expansion)
    return null;

The above connection pool to achieve the general function is complete, mainly includes initial registration, connection pool expansion and access to the connection method

4.Wrapper PoolManager for connection pool management class object
public class PoolManager {
private PoolManager(){}
  * Internal class unary pattern Fake class loader loading principle: Implementing thread safety issues (class loader loading internal class instances is mutually exclusive feature)
private static class createPool {
    private static MyPoolImpl poolImpl = new MyPoolImpl();
public static MyPool getInstace() {
    return createPool.poolImpl;

Attribute: Each thread is mutually exclusive when loading an internal class instance of a class


Test kindTest.class Test main method

// Test2000 thread jdk1.8  internal class uselambda express
public static void main(String[] args) {
    for(int i =0; i < 2000; i++) {
        new Thread(() -> selectDate()).start();

Test method selectDate()

 The member variable
public static MyPool myPool = PoolManager.getInstace();
// Method addedsynchronized afterwards, The connection pool is not expanded???
public static void selectDate() {       
    PooledConnection connection = myPool.getConnection();
    String sql = "select * from t_area";
    ResultSet rs = connection.queryBySql(sql);
    try {
        while( {
            String name = rs.getString("name");
            Integer id = rs.getInt("id");
            //System.out.println(" The current thread:"+ Thread.currentThread().getName() +",id:"+ id + ",name" + name);
    } catch (SQLException e) {

issues: 1. The collection problem of connecting pools in the myPoolImpl class is a Java MomentumException exception that occurs in multiple tests using thread-safe Vector The cause and resolution of the Java ConcurrentModificationException anomaly 2. When in Test method selectDate() with the addition ofsynchronized afterwards, Connection pools do not expand the problem???, Related lock issues are not yet well understood 3. Operational observation problem: The number of expansion connections is much larger than the number of connections actually used in operation (basically 0-10 connections in use) ???

1、A First Look at Hibernates Associative Mapping I
2、Machine Learning Interviews for Deviation Variance
3、K8S Basic Terminology Concepts
4、3 Classic Convolutional Neural Network Case Studies
5、How to tell if you have the potential to be a good programmer

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送