MySQL Database connection pool

1. Traditional links (below)

Attention:

(1). The traditional way to find DriverManager to connect, the number is limited.

(2). The traditional way of close (), and did not reuse connection, just cut off the application and Database bridge, that is no send to SQL command to the database side execution

(3). In the project, for connection not to say, do not use the DriverManager directly, and use the connection pooling method.

2. Using connection pooling ()

3. Open Source Database connection pool

(1). Many Web servers (Weblogic, WebSphere, Tomcat) now provide the implementation of the Datasoruce, which is the implementation of the connection pool. Usually we put the implementation of DataSource, according to its English meaning is called the data source, the data source contains the database connection pool implementation.
(2). There are also open source organizations that provide a separate implementation of the data source:
DBCP Database Connection pool (Tomcat)
C3P0 Database connection pool (Hibernate)
(3). The actual application does not need to write the connection database code, directly from the data source to obtain the database connection. Programmers should also use the implementation of these data sources as much as possible to improve the database access performance of the program.

Use of 4.c3p0:
(1). Under the Classpath path, configure the XML file, which is the src file and the file name must be C3p0-config.xml

File parameters of the original c3p0-config.xml:

<c3p0-config><default-config><!--c3p0 The number of connections that are fetched at the same time when the connection in the connection pool is exhausted. Default:3--><property name= "acquireincrement" >3</property><!--defines the number of repeated attempts to obtain a new connection from the database after a failure. default:30--><property name= "acquireretryattempts" >30</property><!--Two-connection interval, in milliseconds. default:1000--><property name= "Acquireretrydelay" >1000</property><!--the connection is closed by default, all uncommitted actions are rolled back. Default:false--><property name= "Autocommitonclose" >false</property><!--c3p0 will build a blank table named Test, and test it with its own query statement. If this parameter is defined, then the attribute preferredtestquery will be ignored. You cannot do anything on this test sheet, it will be used only for C3P0 testing. Default:null--><property name= "automatictesttable" >Test</property><!-- Getting a connection failure will cause any thread that waits for the connection pool to get the connection to throw an exception. However, the data source is still valid and continues to try to get the connection the next time you call Getconnection (). If set to True, the data source will declare broken and permanently shut down after attempting to acquire a connection failure. Default:false--><property name= "Breakafteracquirefailure" >false</property><!-- When the connection pool runs out, the client calls getconnection () to wait for a new connection, and after the timeout, the SqlException is thrown and, if set to 0, waits indefinitely. Unit milliseconds. Default:0--><property name= "CheckOuttimeout ">100</property><!--test the connection by implementing a class of Connectiontester or Queryconnectiontester. The class name needs to be set to the full path. Default:com.mchange.v2.c3p0.impl.defaultconnectiontester--><property name= "ConnectionTesterClassName" > </property><!--Specify the path to the C3P0 libraries, and if (usually it is) locally you can get that without setting, default null is Default:null--><property name= "Factoryclasslocation" >null</property><!--strongly disrecommended. Setting this to true may leads to subtle andbizarre bugs. An attribute--><property name= "Forceignoreunresolvedtransactions" is strongly recommended by the author (original document) >false</property><!- -Check for idle connections in all connection pools every 60 seconds. default:0--><property name= "Idleconnectiontestperiod" >60</property><!--get three connections when initializing, The value should be between Minpoolsize and Maxpoolsize. Default:3--><property name= "initialpoolsize" >3</property><!--maximum idle time, unused in 60 seconds, the connection is discarded. If 0, it will never be discarded. default:0--><property name= "MaxIdleTime" >60</property><!--the maximum number of connections that are kept in the connection pool. Default:15--><property name= "Maxpoolsize" >15</property><!--The standard parameters of JDBC to control the number of preparedstatements loaded within the data source. However, because the pre-cached statements belong to a single connection instead of the entire connection pool. So setting this parameter takes into account a variety of factors. If both maxstatements and maxstatementsperconnection are 0, the cache is closed. Default:0--><property name= "maxstatements" >100</property><!-- Maxstatementsperconnection defines the maximum number of cache statements that a single connection in a connection pool has. Default:0--><property name= "maxstatementsperconnection" ></property><!--c3p0 is asynchronous operation, Slow JDBC operations are done through the help process. Extending these operations can effectively improve performance by implementing multiple operations at the same time through multithreading. Default:3--><property name= "Numhelperthreads" >3</property><!--when user calls getconnection () The root user becomes the user who is going to get the connection. Used primarily when connection pooling is connected to a non-c3p0 data source. Default:null--><property name= "Overridedefaultuser" >root</property><!-- A parameter that corresponds to the Overridedefaultuser parameter. Default:null--><property name= "Overridedefaultpassword" >password</property><!--password. Default:null--><property name= "Password" ></property><!--define test statements that are executed by all connection tests. This one significantly improves the test speed in the case of connection testing. Note: The test table must exist at the time of the initial data source. Default:null--><property name= "PreferrEdtestquery ">select ID from test where id=1</property>< waits up to 300 seconds before the user modifies the system configuration parameters for execution." default:300--><property name= "propertycycle" >300</property><!--because of the high performance consumption please use it only when you need it. If set to true then the validity of each connection submission is officer. We recommend using methods such as Idleconnectiontestperiod or automatictesttable to improve the performance of your connectivity tests. Default:false--><property name= "Testconnectiononcheckout" >false</property><!-- If set to true then the validity of the officer connection is obtained while the connection is made. Default:false--><property name= "Testconnectiononcheckin" >true</property><!--user name. Default:null--><property name= "User" >root</property><!--earlier versions of C3P0 used a dynamic reflection proxy for the JDBC interface. This parameter allows the user to revert to the dynamic reflection agent to resolve an unstable failure in the case of a wide range of uses in earlier versions. The newest non-reflection agents are faster and have started to be widely used, so this parameter may not be useful. The original dynamic reflection is now supported at the same time as the new non-reflection agent, but a possible future version may not support the dynamic reflection proxy. Default:false. --><property name= "usestraditionalreflectiveproxies" >false</property><property name= " Automatictesttable ">con_test</property><property name=" Checkouttimeout ">30000</property> <property name= "IdleconnectiontestPeriod ">30</property><property name=" initialpoolsize ">10</property><property name=" MaxIdleTime ">30</property><property name=" maxpoolsize ">25</property><property name=" Minpoolsize ">10</property><property name=" maxstatements ">0</property><user-overrides User= "Swaldman" ></user-overrides></default-config><named-config name= "DumbTestConfig" >< Property Name= "Maxstatements" >200</property><user-overrides user= "poop" ><property name= " Maxstatements ">300</property></user-overrides></named-config></c3p0-config>

Practical Practice:
1> First Configurec3p0-config.xml File

<?xml version= "1.0" encoding= "UTF-8"? ><c3p0-config><default-config><property name= " Driverclass ">com.mysql.jdbc.driver</property><property name=" user ">root</property>< Property name= "Password" >wwh</property><property name= "Jdbcurl" >jdbc:mysql://127.0.0.1:3306/ Jdbctest</property></default-config></c3p0-config>

2>. Code Operation Connection (Testc3p0.java)

Package Cn.wwh.www.java.jdbc.datasource;import Java.sql.connection;import com.mchange.v2.c3p0.combopooleddatasource;/** * Function of class: * * * @author Skiff * @version 1.0 *@ created: 2014-9-2   12:02:13 *// /test Connection Pool C3P0 usage public class Testc3p0 {public static void main (string[] args) throws Exception {Long begin = System.currentti Memillis ();//create C3P0 connection pool and load C3p0-config.xml file Combopooleddatasource dataSource = new Combopooleddatasource (); for (int i =1;i<=1000000;i++) {Connection conn = datasource.getconnection (); if (conn!=null) {System.out.println ("Get Connection" +i+ " "); Conn.close ();}} Long end = System.currenttimemillis (); System.out.println ("used" + (End-begin)/1000+ "seconds");}}

Use of 5.DBCP:
(1). Using the class loading method, the file name is: dbcp.properties

Driverclassname=com.mysql.jdbc.driverurl=jdbc:mysql://127.0.0.1:3306/jdbctestusername=rootpassword=wwh

(2). Use in code (TESTDBCP.JAVA)

Package Cn.wwh.www.java.jdbc.datasource;import Java.io.inputstream;import Java.sql.connection;import Java.util.properties;import Javax.sql.datasource;import org.apache.commons.dbcp.basicdatasourcefactory;/** * The role of the class: Use the DBCP connection pool to get a link to the database and test his time * * * * @author Skiff * @version 1.0 *@ Create time: 2014-9-1 09:27:59 *///test Connection Pool DBCP usage public cl testdbcp {public static void main (string[] args) throws Exception {long begin = System.currenttimemillis ();//Load Properties file InP Utstream is = TestDBCP.class.getClassLoader (). getResourceAsStream ("cn/wwh/www/java/jdbc/config/dbcp.properties"); Properties props = new properties ();p rops.load (IS);//create DBCP Connection Pool Factory basicdatasourcefactory factory = new Basicdatasourcefactory (); Create a data source, Connection pool DataSource ds = Factory.createdatasource (props); for (int i=1;i<=1000000;i++) {// Get an idle connection object from the connection pool Connection conn = Ds.getconnection (), if (conn!=null) {System.out.println ("Get Connection" +i+ "a");} The connection object is also returned to the connection pool Conn.close ();} Long end = System.currenttimemillis (); System.out.println ("used" + (End-begin)/1000+ "seconds");}} 

The first test time is 18 seconds, the second Test time is 13 seconds, different machine performance is not the same, the test time is not possible. But theoretically c3p0 should be faster, but the data I'm testing is quite the opposite, very strange.

Summarize:

1>DBCP and C3P0, both Java open source, must be implemented directly or indirectly Javax.sql.DataSource interface
2>DBCP connection pool requires a dbcp.properties file, with 3 corresponding jar packages to be added
3>C3P0 connection pooling requires the C3p0-config.xml file to be stored in the/web-inf/classes/directory, which is combopooleddatasource when it is created
The XML file is automatically found in the specified directory and the default settings are loaded

4>tomcat is c3p0.

6. The imported JAR package

COMMONS-DBCP.JAR:DBCP implement the Jar to import

Commons-pool.jar: Dependency classes for connection pooling implementations

Commons-collections.jar: Collection class for connection pooling implementations

C3P0-0.9.1.2.JAR:C3P0 implementing the jar package to be imported

MySQL Database connection pool