Preface

Today let's talk about database connection pool technology. Actually, this term sounds a little taller than before, but it's not really very complicated. I believe that after I explain it and I actually write the code once, I can have a deep understanding of this technology.

Overview of Database Connection Pool Technology

The so-called database connection pooling technology is used to distribute, manage and release database connections. You may ask, as if I could implement these functions directly with JDBC.
Well, you're right, JDBC can do it, but remember, when we use JDBC technology, do we always close the connection when we run out of it, and do we have to open the database connection again when we reuse it next time?
In fact, database link resources are very valuable, we can not see in small projects, in high concurrent projects, you will find that such frequent opening and closing of database links is a kind of destroy to the server, greatly affecting efficiency.
So how does a database connection pool do that?
The idea is as follows: database connection pool allocates a database connection to users every time they have access. When users run out of connections, connection pool recycles connections and puts them back into a connection set.
That's how it works. Let's take a look at this picture to make a deep impression.

You may not be sure, and the database connection pool is more complex to consider than the above, but don't be afraid, I'll help you understand it through the actual code.
Note: The following code is to implement a simple database connection pool, focusing on the principle of database connection pool.

Implementing a database connection pool by oneself

Note: The following code is explained in blocks. You paste it down one by one, and it will certainly work. For convenience, I will give the complete implementation code after I finish.

• Define the number of initialized connections, the maximum number of connections, and the number of currently connected connections
  At the beginning, when the database connection pool is started, in order to meet the above requirements, we must first give several completed connections, so that users can access them directly; in addition, when the number of users visiting a certain period of time exceeds the number of connections in the connection pool defined by me, it is necessary to create additional connections for users to use; of course, this new connection. The number of connections built must not be unlimited, otherwise it will affect efficiency, so we need to define the maximum number of connections.

private final int init_count = 3; //Number of Initialized Links
private final int max_count = 6; //maximum connection
private int current_count = 0; //Number of connections to the current

• So where should we put the new connection pool for users to use at the beginning? It must be easy to create a connection set. Why use a LinkedList collection will be introduced in a moment.

private LinkedList<Connection> pool = new LinkedList<Connection>();

• As I said just now, at first we must initialize the connection for users, so we need to create a certain number of new links when the connection pool starts. After analysis, we find that it is best to initialize the links in the constructor, and finally put the links in the link set.

 //Constructor to initialize links into connection pool
 public MyPool() {
     for (int i=0;i<init_count;i++){
         //Record the current number of connections
         current_count++;
         //createConnection is a custom create link function.
         Connection connection = createConnection();
         pool.addLast(connection);
     }
 }

• There's nothing to say about creating a new connection. After all, all the links you want are from here.

public Connection createConnection() {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection =DriverManager.getConnection("jdbc:mysql://localhost:3306/keyan","root","root");
    return connection;
｝

• Get links
  When the user visits, we must give the user a connection. If there is no connection in the pool (all connections are occupied), then we need to create a new connection, using the createConnection() function. Of course, the number of connections must not exceed the maximum number of connections. If these two conditions are not met, then throw an exception directly.

public Connection getConnection() {
     if (pool.size() > 0){
         //removeFirst deletes the first and returns
         //Now you must understand why I'm using LinkedList, because here's the one below.
         //The removeFirst() method deletes the first element in the collection, but returns the first element as well.
         //This saves us a lot of unnecessary trouble.
         return pool.removeFirst();
     }
     if (current_count < max_count){
         //Record the number of connections currently in use
         current_count++;
         //create link
         return createConnection();
     }
     throw new RuntimeException("Current links have reached the maximum number of links");
}

• Release resources
  When the user has finished using the connection, what we need to do is not close the connection, but put the connection back into the resource pool LinkedList, which saves the connection closing time and again. This is the core content of the connection pool. Is it simple?

 public void releaseConnection(Connection connection){
    if (pool.size() < init_count){
        pool.addLast(connection);
        current_count--;
    }else {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The whole process of implementation is like this. Next, I will paste out all the code for you to learn.

//unit testing
@Test
public class MyPool {
    private final int init_count = 3; //Number of Initialized Links
    private final int max_count = 6; //Maximum
    private int current_count = 0; //Number of connections to the current
    //Connection pool, used to store initialization links
    private LinkedList<Connection> pool = new LinkedList<Connection>();

    //Constructor to initialize links into connection pool
    public MyPool() {
        for (int i=0;i<init_count;i++){
            //Record the current number of connections
            current_count++;
            Connection connection = createConnection();
            pool.addLast(connection);
        }
    }

    //Create a new connection
    public Connection createConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/keyan","root","root");
            return connection;
        }catch (Exception e){
            System.out.println("Database link exception");
            throw new RuntimeException();
        }
    }

    //Get links
    public Connection getConnection() {
        if (pool.size() > 0){
            //removeFirst deletes the first and returns
            return pool.removeFirst();
        }
        if (current_count < max_count){
            //Record the number of connections currently in use
            current_count++;
            //create link
            return createConnection();
        }
        throw new RuntimeException("Current links have reached the maximum number of links");
    }

    //Release link
    public void releaseConnection(Connection connection){
        if (pool.size() < init_count){
            pool.addLast(connection);
            current_count--;
        }else {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
｝

After running through the code yourself, did you find that the technology that looked so complicated didn't look like what we thought it was?
Well, after introducing the basic principles of database connection pool technology, we will introduce two excellent open source data connection pool technologies.
Actually, the real database connection pool is more complicated than what we just wrote. At this stage, we don't need to write such a complex thing, but if you are interested, you can see the source code of the database connection pool - yes, these two connection pools are open source. OK, let's start!

Excellent database connection pool

First of all, sun company stipulates that connection pool technology needs to implement javax.sql.DataSource interface. That is to say, if you want to implement database connection pool by yourself, you must implement this interface. Is it a good look, in fact, as a standard maker, sun company still has many requirements, which is the right of standard maker, so enterprises or countries go to. In the past, the formulation of some key technical standards has been bloody. Well, far from it.

DBPC

In fact, you may not know that if your tomcat is specially configured, it can also be used as a database connection pool, because the built-in tomcat is DBPC..
To use DBPC, you have to import three jar files: commons-dbcp2-2.2.0.jar, commons-pool2-2.5.0.jar, commons-logging-1.2.jar. I think, with your intelligence, to get these three jar files must be a piece of cake. It's a necessary skill for software developers to learn how to search.
It's very simple to use. There are two main ways to use it. One is hard-coding, that is to set various parameters manually, the other is to configure the corresponding configuration file and load it.

Hard-coded DBPC

BasicDataSource dataSource = new BasicDataSource();
//Parameter Configuration: Initial Connection Number, Maximum Connection Number, Connection String, Driver, User, Password
dataSource.setInitialSize(3);   //Maximum Initialization Link
dataSource.setMaxTotal(6);      //Maximum link
dataSource.setMaxIdle(3000);    //Maximum idle time
dataSource.setUrl("jdbc:mysql:///keyan");   //url
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUsername("root");
dataSource.setPassword("root");

//Get links
Connection connection = dataSource.getConnection();
connection.prepareStatement("SELECT * FROM e_person").execute();
connection.close();

When using the query, you just need to follow the original JDBC mode of operation, which is nothing to say, according to the configuration above, you can do, and you must not forget to import the package.

Configuration file implementation

//Create properties configuration files
Properties properties = new Properties();
//Get the file stream
InputStream in = DBCPTest.class.getResourceAsStream("db.properties");
//Loading configuration files
properties.load(in);
//Creating Data Source Objects
BasicDataSource dataSource = BasicDataSourceFactory.createDataSource(properties);

//Get links
Connection connection = dataSource.getConnection();
ResultSet resultSet = connection.prepareStatement("SELECT * FROM e_person").executeQuery();
while (resultSet.next()){
   System.out.println(resultSet.getString("work_name"));
}
connection.close();

Above is the basic operation flow, let's take a look at the configuration of this xml file.
File db.properties

url=jdbc:mysql:///keyan
driverClassName=com.mysql.jdbc.Driver
username=root
password=root
initialSize=3
maxActive=6
maxIdle=3000

It's important to note that this configuration file must be placed in the same package as your DBPC class.
After that, you can use it directly.

C3P0

C3p0 is also a very good connection pool technology, which needs to import fewer files, only one c3p0-0.9.1.2.jar. Just go to the website to find it.
There are two ways to use c3p0, hard-coded and configuration file.

Hard coding

//Configuration-related parameters
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql:///keyan");
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setUser("root");
dataSource.setPassword("root");
dataSource.setInitialPoolSize(3);
dataSource.setMaxPoolSize(6);
dataSource.setMaxIdleTime(1000);

Connection connection = dataSource.getConnection();
//sql statement
ResultSet resultSet = connection.prepareStatement("SELECT * FROM e_project").executeQuery();
while (resultSet.next()){
   System.out.println(resultSet.getString("project_name"));
}
connection.close();

As above, just use it for a while and know how to use it. As for the deeper things, if you are interested, study it slowly.

Configuration file format

ComboPooledDataSource dataSource = new ComboPooledDataSource();
Connection connection = dataSource.getConnection();
ResultSet resultSet = connection.prepareStatement("SELECT * FROM e_project").executeQuery();
while (resultSet.next()){
    System.out.println(resultSet.getString("project_name"));
}
connection.close();

At first glance, you might ask, isn't it a configuration file implementation? Yes, it's just an implicit call. You don't need to implement it. You just need to create a new ComboPooled DataSource object. By default, you call the xml file. The file path is the src root directory. That is to say, you just need to write the configuration file in the src directory. The key point is how to write the xml file.
Note that the file name c3p0-config.xml, this file name can not be changed, must be this, the file path must be src root directory, otherwise it can not be read.

<c3p0-config>
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="initialPoolSize">3</property>
        <property name="maxPoolSize">6</property>
        <property name="maxIdleTime">1000</property>
    </default-config>
</c3p0-config>

Configuration file parameters generally use so much, of course, you can also look for relevant information, understand the meaning of more detailed parameters, these parameters, we use enough daily.

Supplementary Contents: Advantages of Database Connection Pool

resource reuse

Because of the reuse of database connections, frequent creation is avoided and a large amount of performance overhead caused by connections is released. On the basis of reducing the system consumption, on the other hand, it also increases the stability of the system operation environment.

Faster system response speed

In the initialization process of database connection pool, several database connections have been created and placed in the connection pool for standby. At this point, the initialization of the connection has been completed. For business request processing, using available connections directly avoids the time overhead of initialization and release of database connections, thus reducing the response time of the system.

New means of resource allocation

For systems with multiple applications sharing the same database, the maximum number of database connections available for an application can be limited by configuring the database connection pool at the application level, thus avoiding the exclusive use of all database resources by an application.

Unified connection management to avoid database connection leakage:

In a more perfect database connection pool implementation, the occupied connection can be forcibly reclaimed according to the pre-occupied timeout setting, thus avoiding the possible resource leakage in the conventional database connection operation.

epilogue

Thank you for your reading. Welcome to correct the problems in the blog. You can also contact me, make progress and communicate with me.

Wechat Public Number: An Invasive Procedure Dog
Mailbox: roobtyan@aliyun.com
Personal blog: http://roobtyan.cn
Scan the following two-dimensional code and pay attention to me. You will get something unexpected.
We have prepared a very good video tutorial for JAVA, from JAVA Foundation to JAVAWEB, and very powerful project practice.
Just in my Wechat Public Number, reply to java can be viewed, free yo!