Java database connection pool details and simple examples

  • 2020-05-26 08:35:32
  • OfStack

Java database connection pool details

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, he or she will take an established free connection object out of the connection pool. Instead of closing the connection after use, the user places the connection back into the connection pool for the next request to access. Connection creation and disconnection are managed by the connection pool itself. At the same time, you can control the initial number of connections in the connection pool, the upper and lower limits of connections, the maximum number of times each connection is used, the maximum idle time, and so on by setting the parameters of the connection pool. You can also monitor the number of database connections, usage, and so on through its own management mechanism.

Common database connection pools:

Commonly used database connection pool has JNDI C3p0, Apache Jakarta and DBCPBoneCP, sping framework depends on the third party USES the c3p0 and dbcp two ways; bonecp claims to be the fastest database connection pool. JNDI way to create an datasource implementation is to actually implement javax.sql.datasource (none of the other 3 ways)

Now we mainly introduce how to use JNDI way, this way, by web server (for example: tomcat, weblogic websphere, tomcat), realized the java. sql. datasource. The web server is responsible for initializing the data source, creating connection, allocating and managing connection. Since the functionality itself is implemented by the web server, there is no need to introduce a special jar package into the project, but there is a need to add related configuration to some of the server's configuration files. Let's take the Tomcat server (database MySQL) as an example to illustrate the use of this approach.

Database creation and initialization data:


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-driven mysql-connector-java-5.0.3-bin.jar into lib under the tomcat directory

2. Modify the context.xml file under conf of tomcat to add support for the configuration of Resource


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

1)name: specify the name JNDI of Resource.

2)auth: specify Manager to manage Resource, which has two optional values: Container and Application. Container means that Resource is created and managed by the container, and Application means that Resource is created and managed by web applications.

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 drive connecting to the database.

7)url: specify URL to connect to the database, 127.0.0.1 is ip of the database server to connect to, 3306 is the database server port, BookDB is the database name.

8)maxActive: specify the maximum number of database connections in the active state in the database connection pool, with value of 0, indicating unrestricted.

9)maxIdle: specify the maximum number of database connections in the idle state in the database connection pool, with the value of 0, indicating that there is no restriction.

10)maxWait: specifies the maximum time (in milliseconds) that a database connection in the database connection pool will be idle, after which an exception will be thrown. A value of minus 1 means you can wait indefinitely.

maxActive="100"

Represents the maximum number of connections that can be retrieved from the connection pool in the case of concurrency. If the database is not a separate database for one application, maxActive parameter can be set to avoid the impact of one application's unrestricted access to connections on other applications. If a database is only used to support one 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. Connection acquisition and release are bidirectional. When the application concurrently requests the connection pool, the connection pool needs to get the connection from the database. Does the connection pool also return the connection to the database when the application finishes using the connection and returns the connection to the connection pool? Obviously, the answer is no. If that happens, the connection pool will become more like this. Instead of improving performance, it will degrade performance.

maxIdle="30"

If maxActive=100 is reached at concurrency, the connection pool must obtain 100 connections from the database for the application to use. When the application closes the connection, not all connections will be returned to the database due to maxIdle=30, and 30 connections will remain in the connection pool and idle.

minIdle = "2"

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

Question: how do I set up maxActive and maxIdle?

Theoretically, maxActive should be set to the maximum number of concurrence of the application, so that the application can still get the connection from the connection pool even under the maximum number of concurrence. However, it is difficult to accurately estimate the maximum number of concurrence. Setting the maximum number of concurrence is an optimal quality of service guarantee.

maxIdle corresponding connection, is actually a long connection, a connection pool to keep the connection pool advantage part, theoretically keep more long connection, the application requests can be faster response, but too much connection to keep, but consumes large amounts of resources database, therefore maxIdle also is not the bigger the better, same as above case we suggest that will be close to 50 maxIdle set to 50-100 number, such as 55. This allows for maximum concurrency while maintaining fewer database connections and, in most cases, the fastest corresponding speed for the application.

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 > Property description:

1)description: description of the referenced resource.

2) res-ref-name: specify the name of JNDI of the referenced resource, and < Resource > The name attribute in the element corresponds.

3) res-type: specify the class name of the referenced resource, and < Resource > The type attribute in the element corresponds.

4) res-auth: specify Manager, and < Resource > The auth attribute in the element corresponds

4. Write code using java and use 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="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<html>
<head>
<title>Tomcat Under the JNDI Database connection pool </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 log 
 rs = stmt.executeQuery("select ID,NAME,PRICE from test");
 // Output query results 
 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>

In the browser input http: / / localhost: 8080 / test MyJsp jsp, can view the results

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


Related articles: