Detailed explanation of Java database connection pool
The principle of database connection pooling is:
The basic idea of connection pooling is to store the database connection as an object in memory when the system is initialized. When the user needs to access the database, instead of establishing a new connection, a established free connection object is taken out from the connection pool. After use, the user does not close the connection, but puts the connection back into the connection pool for access to the next request. The establishment and disconnection of connections are managed by the connection pool itself. At the same time, you can also control the initial number of connections in the connection pool, the upper and lower limits of connections, the maximum number of usage times for each connection, the maximum idle time, etc. by setting the parameters of the connection pool. It can also monitor the number of database connections, usage, etc. through its own management mechanism.
Commonly used database connection pools:
Commonly used database connection pools include JNDI, C3p0, Apache's Jakarta and DBCPBoneCP. Among them, third parties relying on the sing framework use c3p0 and dbcp; and bonecp is said to be the fastest database connection pool. The datasource created by JNDI method really implements javax.sql.datasource (none of the other three methods)
Now we will mainly introduce how to use JNDI method. This method is implemented by a web server (for example: tomcat, weblogic, websphere, tomcat) to implement java.sql.datasource. The web server is responsible for initializing the data source, creating connections, allocating and managing connections. Since it is a function implemented by a web server itself, it is not necessary to introduce special jar packages into the project, but it is necessary to add relevant configurations to some configuration files of the server. Next, take the Tomcat server (the database is MySQL) as an example to describe the use of this method.
Database creation and initialization:
create table test(id INT PRIMARY KEY,name VARCHAR(10),price FLOAT)INSERT INTO test VALUES(1,'English',22.2);INSERT INTO test VALUES(2,'Math',78.9);INSERT INTO test VALUES(3,'History',77.9);
1. Put the data driver mysql-connector-java-5.0.3-bin.jar into the lib in the tomcat directory
2. Modify the context.xml file under tomcat's conf and add support for Resource configuration
<Resource auth="Container" driverClassName="com.mysql.jdbc.Driver" maxActive="100" maxIdle="30" maxWait="10000" name="jdbc/ewsdb" username="root" password="admin" type="javax.sql.DataSource" url="jdbc:mysql://localhost:3306/test1"/>
<Resource attribute description>
1) name: Specify the JNDI name of the Resource.
2)auth: Specifies the Manager that manages Resource, which has two optional values: Container and Application. Container means that the container creates and manages the Resource, and Application means that the web application creates and manages the Resource.
3)type: Specify the Java class name of the Resource.
4) username: Specify the user name to connect to the database.
5) password: Specify 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: Specify the URL to connect to the database. 127.0.0.1 is the IP of the database server to be connected to, 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 that it is not restricted.
9)maxIdle: Specifies the maximum number of database connections in the database connection pool that are idle. The value is 0, indicating that it is not restricted.
10)maxWait: Specifies the maximum time (in milliseconds) for the database connection pool to be idle. After this time, an exception will be thrown. The value is -1, which means that you can wait indefinitely.
maxActive="100"
Indicates the maximum number of connections that can be obtained from the connection pool in concurrency. If the database is not used alone for an application, setting the maxActive parameter can prevent an application from obtaining unlimited connections from 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 simply represents the maximum number of connections that can be obtained concurrently through the connection pool. The acquisition and release of connections are two-way. When the application concurrently requests the connection pool, the connection pool needs to obtain the connection from the database. So, when the application uses the connection and returns the connection to the connection pool, does the connection pool also return the connection to the database at the same time? Obviously the answer is no. If that happens, the connection pool will become unnecessary, which will not only not improve performance, but will instead reduce performance. So how to deal with the connection after it is returned?
maxIdle="30"
If maxActive=100 is reached during concurrency, the connection pool must obtain 100 connections from the database to provide the application for use. When the application closes the connection, since maxIdle=30, not all connections will be returned to the database. 30 connections will be kept in the connection pool, and the status is idle.
minIdle=”2”
The minimum does not take effect by default. Its meaning is 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?
In theory, maxActive should be set to the maximum concurrency number of applications, so that even in the case of maximum concurrency, the application can still obtain connections from the connection pool. However, the difficulty is that it is difficult for us to accurately estimate the maximum concurrency number. Setting it to the maximum concurrency number is an optimal service quality assurance.
The corresponding connections of maxIdle are actually long connections maintained by the connection pool, which is also the part where the connection pool plays its advantages. In theory, maintaining more long connections can respond faster when applying requests, but maintaining too many connections will consume a large amount of resources in the database. Therefore, the larger the maxIdle, the better. In the same example as above, we recommend setting maxIdle to a number close to 50 in 50-100, such as 55. This allows you to maintain fewer database connections while taking into account the maximum concurrency, and in most cases, it can provide the application with the fastest corresponding speed.
3. Open the application's Web.xml file and add the following configuration
<resource-ref><description>DB Connection</description><res-ref-name>jdbc/ewsdb</res-ref-name><res-type>javax.sql.DataSource</res-type><res-auth>Container</res-auth></resource-ref>
<resource-ref> attribute description:
1) description: An explanation 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: Specify the manager that manages the referenced resources, corresponding to the auth attribute in the <Resource> element
4. Write Java code and put it in the tomcat environment, as follows
Create JSP example: MyJsp.jsp
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><%@ page import="java.io.*" %><%@ page import="java.util.*" %><%@ page import="java.sql.*" %><%@ page import="java.sql.*" %><%@ page import="javax.sql.*" %><%@ page import="javax.sql.*" %><%@ page import="javax.sql.*" %><%@ page import="javax.sql.*" %><%@ page import="javax.sql.*" %><%@ page import="javax.naming.*" %><html><head><title>JNDI database connection pool under Tomcat</title></head><body> <% try{ Connection conn; Statement stmt; ResultSet rs; Context ctx = new InitialContext(); DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/ewsdb"); conn = ds.getConnection(); stmt = conn.createStatement(); //Query records rs = stmt.executeQuery("select ID,NAME,PRICE from test"); //Output query result out.println("<table border=1 width=400>"); while (rs.next()){ String col1 = rs.getString(1); String col2 = rs.getString(2); float col3 = rs.getFloat(3); //Print the displayed data out.println("<tr><td>"+col1+"</td><td>"+col2+"</td><td>"+col3+"</td></tr>");} out.println("</table>"); //Close the result set, SQL declaration and database connection rs.close(); stmt.close(); conn.close(); }catch(Exception e){ out.println(e.getMessage()); e.printStackTrace(); } %></body></html>Enter http://localhost:8080/test/MyJsp.jsp in your browser to view the results
Thank you for reading, I hope it can help you. Thank you for your support for this site!