Detailed explanation and simple example of Java database connection pool

Detailed explanation of Java database connection pool

The principle of database connection pool is:

The basic idea of connection pool is to store the database connection as an object in memory during system initialization. When users need to access the database, they do not establish a new connection, but take out an established idle connection object from the connection pool. After use, the user does not close the connection, but puts the connection back into the connection pool for access by the next request. The establishment and disconnection of connections are managed by the connection pool itself. At the same time, you can also set the parameters of the connection pool to control the initial number of connections in the connection pool, the upper and lower limits of connections, as well as the maximum usage times and maximum idle time of each connection. You can also monitor the number and usage of database connections through its own management mechanism.

Common database connection pools:

Common database connection pools include JNDI, c3p0, Apache's Jakarta and dbcpbonecp. Among them, the third party relied on by the spin framework uses c3p0 and DBCP; Bonecp claims to be the fastest database connection pool. The datasource created and implemented by JNDI is the real implementation of javax sql. Datasource (none of the other three methods)

Now let's mainly introduce how to use JNDI, which is implemented by a web server (such as tomcat, Weblogic, WebSphere, Tomcat) sql. datasource。 The web server is responsible for initializing the data source, creating connection, allocating and managing connection. Because it is a function implemented by the web server, it is not necessary to introduce a special jar package into the project, but it is necessary to add relevant configurations in some configuration files of the server. Next, take Tomcat server (the database is MySQL) as an example to describe the use of this method.

Database creation and initialization data:

1. Drive the data to mysql-connector-java-5.0 3-bin. Jar into lib under Tomcat directory

2. Modify the context under conf of Tomcat XML file to add support for resource configuration

< resource attribute description >

1) Name: Specifies the JNDI name of the resource.

2) Auth: Specifies the manager that manages the resource. It has two optional values: container and application. Container means that the container creates and manages resources, and application means that the web application creates and manages resources.

3) Type: Specifies the Java class name of the resource.

4) Username: Specifies the user name to connect to the database.

5) Password: Specifies the password to connect to the database.

6) Driverclassname: Specifies the name of the driver implementation class in the JDBC drive connecting to the database.

7) URL: Specifies the URL to connect to the database, 127.0 0.1 is the IP of the database server to be connected, 3306 is the database server port, and bookDB is the database name.

8) Maxactive: Specifies the maximum number of active database connections in the database connection pool. The value is 0, indicating unlimited.

9) Maxidle: Specifies the maximum number of idle database connections in the database connection pool. The value is 0, indicating unlimited.

10) Maxwait: Specifies the maximum time (in milliseconds) that a database connection in the database connection pool is idle. After this time, an exception will be thrown. The value is - 1, which means you can wait indefinitely.

maxActive="100"

Indicates the maximum number of connections that can be obtained from the connection pool in the case of concurrency. If the database is not used separately for an application, setting the maxactive parameter can avoid the impact of an application's unlimited access to connections on other applications. If a database is only used to support an application, maxactive can theoretically be set to the maximum number of connections that the database can support. Maxactive only indicates the maximum number of connections that can be obtained concurrently through the connection pool. Connection acquisition and release are two-way. When the application requests the connection pool concurrently, the connection pool needs to obtain the connection from the database. However, when the application uses the connection and returns the connection to the connection pool, will the connection pool return the connection to the database at the same time? Obviously, the answer is No. in that case, the connection pool will become superfluous. It will not improve the performance, but will reduce the performance. However, after the application returns the connection, how will the connection pool deal with it?

maxIdle="30"

If maxactive = 100 is reached during concurrency, the connection pool must obtain 100 connections from the database for use by the application. After the application closes the connection, not all connections will be returned to the database because maxidle = 30. There will be 30 connections in the connection pool and the state will be idle.

minIdle=”2”

The minimum does not take effect by default. It means that when there are few minidles in the connection pool, the system monitoring thread will start the supplementary function. Generally, we do not start the supplementary thread.

Question: how to set maxactive and maxidle?

Theoretically, maxactive should be set to the maximum number of concurrent applications. In this way, even in the case of maximum concurrency, applications can still obtain connections from the connection pool. However, the difficulty is that it is difficult for us to accurately estimate the maximum number of concurrent applications. Setting the maximum number of concurrent applications is an optimal quality of service guarantee.

The connection corresponding to the maxidle is actually a long connection maintained by the connection pool, which is also a part of the advantages of the connection pool. Theoretically, maintaining more long connections can make faster response to application requests, but too many connections will consume a lot of resources in the database. Therefore, the larger the maxidle is, the better, As in the above example, we suggest setting the maxidle to a number close to 50 in 50-100, such as 55. In this way, we can maintain less database connections while taking into account the maximum concurrency, and in most cases, we can provide the fastest corresponding speed for the application.

3. Open the web of the application XML file, add the following configuration

< resource ref > attribute description:

1) Description: description of the referenced resource.

2) Res ref Name: Specifies the JNDI name of the referenced resource, corresponding to the name attribute in the < resource > element.

3) Res type: Specifies the class name of the referenced resource, corresponding to the type attribute in the < resource > element.

4) Res auth: Specifies the manager that manages the referenced resource, corresponding to the auth attribute in the < resource > element

4. Write java code and use it in Tomcat environment, as follows

Create JSP example: myjsp jsp

Enter in the browser http://localhost:8080/test/MyJsp.jsp , you can view the results

Thank you for reading, hope to help you, thank you for your support to this site!