Java database connection pool Learning

The principle of the database connection pool is:

The basic idea of the connection pool is to store database connections as objects in the memory during system initialization. When users need to access the database, they do not create a new connection, instead, a created idle connection object is retrieved from the connection pool. After use, the user does not close the connection, but puts the connection back in the connection pool for access by the next request. The connection pool manages connection establishment and disconnection. You can also set connection pool parameters to control the initial connection count, upper and lower limit, maximum usage, 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 Jakarta, and dbcpbonecp. The sping Framework relies on third parties to use c3p0 and DBCP. bonecp claims to be the fastest database connection pool. The datasource created by using the JNDI method is actually implemented by javax. SQL. datasource (none of the other three methods)

Now we will mainly introduce how to use the JNDI method, which is implemented by Java. SQL. datasource on the Web server (such as Tomcat, WebLogic, websphere, and tomcat. The Web server initializes the data source, creates a connection, allocates, and manages the connection. Because it is implemented by the Web server, you do not need to introduce a special jar package in the project, but you need to add related configurations to some configuration files on the server. The following uses the Tomcat server (MySQL database) as an example to describe how to use this method.

Database creation and initialization data:

 Create   Table Test (IDInt   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 In the Lib under the Tomcat directory

2. Modify the context. xml file in the conf file of Tomcat to add resource configuration support.

 <  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: Specifies the JNDI name of the resource.

2) Auth: Specifies the manager for resource management. It has two optional values: container and application. Container indicates that resources are created and managed by containers, and application indicates that resources are created and managed by web applications.

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

4) Username: Specifies the username used to connect to the database.

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

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

7) URL: Specify the URL to connect to the database. 127.0.0.1 is the IP address 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 no restriction.

9) maxidle: specifies the maximum number of idle database connections in the database connection pool. The value is 0, indicating no restriction.

10) maxwait: specifies the maximum time (in milliseconds) for idle database connections in the database connection pool. If this time is exceeded, an exception is thrown. The value is-1, indicating that you can wait for an indefinite period of time.

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 by an application separately, you can set the maxactive parameter to avoid the impact of an application's unrestricted access to 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 applicationProgramWhen concurrent requests are sent to the connection pool, the connection pool needs to obtain the connection from the database. But when the application uses the connection pool and returns the connection to the connection pool, will the connection pool also return the connection to the database? Obviously, the answer is no. If that is the case, the connection pool will become more redundant, not only to improve performance, but to reduce performance. But after the application returns the connection pool, how can the connection pool be handled?

Maxidle = "30"

If maxactive = 100 is reached at the time of concurrent transmission, the connection pool must obtain 100 connections from the database for use by the application. When the application closes the connection, maxidle = 30, therefore, not all connections will be returned to the database, and 30 connections will be kept in the connection pool and the status will be idle.

Minidle = "2"

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

Q: How do I set maxactive and maxidle?

In theory, maxactive should be set to the maximum number of concurrent applications. In this way, even with the maximum concurrency, the application can still obtain connections from the connection pool, however, it is difficult to accurately estimate the maximum number of concurrent jobs. setting the maximum number of concurrent jobs is an optimal service quality guarantee.

The connection corresponding to maxidle is actually a persistent connection maintained by the connection pool. This is also part of the advantage of the connection pool. Theoretically, a large number of persistent connections can be maintained to respond faster when application requests are made, however, too many connections will consume a large amount of database resources, so maxidle is not as large as possible. In the same example, we recommend that you set maxidle to a number close to 50 in 50-, for example 55. In this way, we can maintain a small number of database connections while taking into account the maximum concurrency. In most cases, we can provide the fastest corresponding speed for applications.

3. Open the Web. xml file of the application 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: Description of the referenced resource.

2) RES-ref-Name: Specify the JNDI name of the referenced resource, which corresponds to the name attribute in the <resource> element.

3) RES-type: Specifies the Class Name of the referenced resource, which corresponds to the type attribute in the <resource> element.

4) RES-auth: Specify the manager for managing referenced resources, which corresponds to the auth attribute in the <resource> element.

4. Write and use JavaCodeAnd put it in the Tomcat environment, as follows:

Create a 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>  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.exe cutequery ("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>" );  // Disable result set, SQL statement, and database connection  Rs. Close (); stmt. Close (); Conn. Close ();}  Catch  (Exception e) {out. println (E. getmessage (); E. printstacktrace ();} %> </Body> 
 
Enter http: // localhost: 8080/test/myjsp. jsp in the browser to view the result.