JDBC Connection Pool

Database connection pool (connection pool) the need for a JDBC database connection pool

When using the development of a database-based Web program, the traditional pattern is basically the following steps:

Establish a database connection in the main program (such as servlet, beans).
For SQL operations
Disconnect the database.

This pattern of development, the existence of the problem:

　　　　the database's connection resources are not being reused very well. For each database connection, you will have to disconnect after you have finished using it.

This development does not control the number of connection objects being created, and system resources are allocated without consideration, such as excessive connections, which can also lead to memory leaks and server crashes.

Database Connection Pool

The basic idea of a database connection pool is to establish a "buffer pool" for database connections.

　　Database connection pooling is responsible for allocating, managing, and freeing database connections, which allows applications to reuse an existing database connection instead of re-establishing one.

How database connection pooling works

　　

Benefits of database connection pooling technology

Resource reuse;  Faster system response speed;  New means of resource allocation; Unified connection management to avoid database connection leaks.

Two open-source database connection pools

The JDBC database connection pool uses Javax.sql.DataSource to represent that DataSource is just an interface that is typically implemented by servers (Weblogic, WebSphere, Tomcat), and some open source organizations provide implementations:
DBCP Database Connection Pool
C3P0 Database Connection Pool

DataSource is often referred to as a data source, which contains two parts of connection pooling and connection pooling management, and it is customary to often call DataSource a connection pool

DBCP Data source

The connection pool for Tomcat is implemented with this connection pool. The database connection pool can be used either in combination with the application server or independently by the application.

Using the DBCP database connection pool
* 1. Add a jar package (2 jar packages).  Commons-dbcp.jar: The implementation of the connection pool; Commons-pool.jar: Dependency libraries for connection pooling implementations
* 2. Create a database connection pool
* 3. Specify the required properties for the data source instance
* 4. Get a database connection from the data source

public void Testdbcp () throws sqlexception{final basicdatasource DataSource = new Basicdatasource ();//2. Specify the required properties for the data source instance, Datasource.setusername ("root");d Atasource.setpassword ("1230");d Atasource.seturl ("jdbc:mysql:/// Atguigu ");d atasource.setdriverclassname (" Com.mysql.jdbc.Driver ");//3. Specifies some optional properties of the data source.//1). Specifies the number of initialized connections in the database connection pool datasource.setinitialsize (5);//2). Specify the maximum number of connections: the number of connections that can be requested simultaneously to the database at the same time datasource.setmaxactive (5);//3). Specify the number of small connections: the minimum number of idle connections saved in the database connection pool Datasource.setminidle (2);//4). The maximum time to wait for the database connection pool to allocate connections. The unit is in milliseconds. An exception will be thrown beyond that time. Datasource.setmaxwait (1000 * 5);//4. Get the database connection from the data source connection connection = Datasource.getconnection ();        System.out.println (Connection.getclass ()); }    

/** * 1. Load DBCP Properties Profile: The keys in the configuration file require attributes from Basicdatasource *. * 2. Call Basicdatasourcefactory's CreateDataSource method to create the DataSource * instance * 3. Gets the database connection from the DataSource instance.  */@Testpublic void Testdbcpwithdatasourcefactory () throws exception{properties Properties = new properties (); Nputstream instream = JDBCTest.class.getClassLoader (). getResourceAsStream ("Dbcp.properties");p roperties.load ( instream);D Atasource DataSource = Basicdatasourcefactory.createdatasource (properties); System.out.println (Datasource.getconnection ()); }

Dbcp.properties

Username=rootpassword=1230driverclassname=com.mysql.jdbc.driverurl=jdbc:mysql:///atguiguinitialsize= 10maxactive=50minidle=5maxwait=5000

When the database access is finished, the program closes the database connection as before: Conn.close (); But the code above does not close the physical connection to the database, it simply frees the database connection and returns it to the database connection pool.

C3P0 Data source

@Testpublic void Testc3p0 () throws Exception{combopooleddatasource CPDs = new Combopooleddatasource (); Cpds.setdriverclass ("Com.mysql.jdbc.Driver"); Loads the JDBC driver            cpds.setjdbcurl ("Jdbc:mysql:///atguigu"); Cpds.setuser ("root");                                  Cpds.setpassword ("1230");   System.out.println (Cpds.getconnection ()); }

/** * 1. Create a C3p0-config.xml file,  * Refer to the contents of appendix B:configuation files in the Help document * 2. Create a Combopooleddatasource instance; * DataSource dat Asource =  *new combopooleddatasource ("helloc3p0");   * 3. Gets the database connection from the DataSource instance.  */@Testpublic void Testc3powithconfigfile () throws Exception{datasource DataSource = new Combopooleddatasource (" Helloc3p0 ");  System.out.println (Datasource.getconnection ()); Combopooleddatasource Combopooleddatasource = (combopooleddatasource) DataSource; System.out.println (Combopooleddatasource.getmaxstatements ()); }

 c3p0-config.xml

<?xml version= "1.0" encoding= "UTF-8"? ><c3p0-config><named-config name= "Helloc3p0" ><!-- Specify the basic properties of the connection data source--><property name= "user" >root</property><property name= "password" >1230</ Property><property name= "Driverclass" >com.mysql.jdbc.driver</property><property name= "JdbcUrl" >jdbc:mysql:///atguigu</property><!--How many connections are requested to the database server at a time if the number of connections in the database is insufficient--><property name= " Acquireincrement ">5</property><!--the number of connections when initializing a database connection pool--><property name=" Initialpoolsize ">5< /property><!--The minimum number of database connections in a database connection pool--><property name= "Minpoolsize" >5</property><!-- Maximum number of database connections in a database connection pool--><property name= "maxpoolsize" >10</property><!--c3p0 the number of Statement that a database connection pool can maintain --><property name= "maxstatements" >20</property><!--the number of Statement objects that can be used at the same time for each connection-->< Property Name= "Maxstatementsperconnection" >5</property></named-config></c3p0-config>

JDBC Connection Pool