Detailed explanation and simple examples of Java database connection pool

Java database connection pool detailed explanation

The principle of the database connection pool is:

The basic idea of ​​the connection pool is to store the database connection as an object in the memory during system initialization. When the user needs to access the database, instead of establishing a new connection, an established idle connection object is taken out of the connection pool. After use, the user does not close the connection, but puts the connection back into the connection pool for 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 uses of each connection, the maximum idle time, etc. by setting the parameters of the connection pool. It can also monitor the number, usage, etc. of database connections 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, the third party that the sping framework relies on uses c3p0 and dbcp; Bonecp is known as the fastest database connection pool. The datasource created and implemented in the JNDI method truly implements javax.sql.datasource (the other three methods are not)

Now we mainly introduce how to use the JNDI method. This method is controlled by the web server (such as : tomcat, weblogic, websphere, tomcat), implements java.sql.datasource. The web server is responsible for initializing the data source, creating connections, allocating, and managing connections. Since the function itself is implemented by the web server, there is no need to introduce a special jar package into the project, but it is necessary to add relevant configurations to certain configuration files of the server. Next, take the Tomcat server (the database is MySQL) as an example to describe the use of this method.

Creation and initialization data of database:

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"
/>

4ab9875a0f359a4a59ecba3fcd462eb8

1)name: Specify 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 resource is created and managed by the container, and Application means that the resource is created and managed by the web application.

3)type: Specify the Java class name of Resource.

4) username: Specify the user name to connect to the database.

5)password: Specify the password to connect to the database.

6) driverClassName: Specify the name of the Driver implementation class in the JDBC driver that connects 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, 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, which means no limit.

9)maxIdle: Specifies the maximum number of idle database connections in the database connection pool. The value is 0, which means there is no limit.

10)maxWait: Specify the maximum time (in milliseconds) for the database connection in the database connection pool to be idle. If this time is exceeded, 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 under concurrent conditions. If the database is not separate and is used by one application, setting the maxActive parameter can prevent an application from obtaining unlimited connections and affecting other applications. If a database is only used to support one application, maxActive can theoretically be set so that the database can support it. The maximum number of connections. maxActive just 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 an application concurrently requests the connection pool, the connection pool needs to obtain the connection from the database. Then, when the application finishes using the connection and returns the connection to the connection pool, will the connection pool also return the connection at the same time? What about the database? Obviously the answer is no. In that case, the connection pool will become unnecessary. Not only will it not improve performance, but it will reduce performance. So, how should the connection pool be handled after the application returns the connection?

maxIdle="30"

If maxActive=100 is reached during concurrency, then the connection pool must obtain 100 connections from the database for use by the application. When the application closes the connection Finally, since maxIdle=30, not all connections will be returned to the database. There will be 30 connections remaining in the connection pool and the status is idle.

minIdle=”2”

Minimum does not take effect by default. It means that when there are few connections in the connection pool with minIdle, the system monitoring thread will start the supplementary function. Under normal circumstances We do not start supplementary threads.

Question: How to set maxActive and maxIdle?

Theoretically, maxActive should be set to the maximum number of concurrencies of the application. In this way, even under maximum concurrency, the application can still obtain connections from the connection pool, but the difficulty is that it is difficult for us to be accurate. Estimating the maximum number of concurrencies and setting it to the maximum number of concurrencies is an optimal service quality guarantee.

maxIdle对应的连接，实际上是连接池保持的长连接，这也是连接池发挥优势的部分，理论上讲保持较多的长连接，在应用请求时可以更快的响应，但是过多的连接保持，反而会消耗数据库大量的资源，因此maxIdle也并不是越大越好，同上例我们建议将 maxIdle设置成50-100中靠近50的数字，例如55。这样就能在兼顾最大并发同时，保持较少的数据库连接，而且在绝大多情况，能够为应用程序提供最快的相应速度。

3．打开应用程序的 Web.xml文件，添加以下配置

<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>

 8f448df49ccb78a46df6973096920559属性说明：

1)description：对所引用的资源的说明。

2)res-ref-name：指定所引用资源的JNDI名字，与41a992d172d4058fa1f494159e00332c元素中的name属性对应。

3)res-type：指定所引用资源的类名，与41a992d172d4058fa1f494159e00332c元素中的type属性对应。

4)res-auth：指定管理所引用资源的Manager，与41a992d172d4058fa1f494159e00332c元素中的auth属性对应

4. 编写使用java代码，并放在tomcat环境下使用，如下

创建JSP范例：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="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<html>
<head>
<title>Tomcat下JNDI数据库连接池</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();
 //查询记录
 rs = stmt.executeQuery("select ID,NAME,PRICE from test");
 //输出查询结果
 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);
  //打印显示的数据
  out.println("<tr><td>"+col1+"</td><td>"+col2+"</td><td>"+col3+"</td></tr>");}
  out.println("</table>");
 
 //关闭结果集、SQL声明和数据库连接
 rs.close();
 stmt.close();
 conn.close();
 }catch(Exception e){
 out.println(e.getMessage());
 e.printStackTrace();
 }
 %>
</body>
</html>

　在浏览器中输入http://localhost:8080/test/MyJsp.jsp，即可查看结果

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

更多Java 数据库连接池详解及简单实例相关文章请关注PHP中文网！