Java Datasource, database connection pool

There are multiple ways to create a database connection pool: JNDI,DBCP,C3P0, etc.

Why connection pooling is required:

Using the Java API drivermanager.getconnection () to create a database connection, consuming memory and time, low-real-time, the acquisition of connection need to manually shut down, otherwise it will consume a lot of memory resources, for frequent database operations, This approach can result in low performance, especially in Web applications

Database connection pooling Capabilities:

Responsible for creating, managing, and assigning database connections. When a database connection pool is initialized, a certain number of database connection objects are created and stored in the database connection pool. When a connection is requested to a database, the connection pool allocates a connection that is idle, the connection pool is reclaimed when the database connection is not used, and is set to idle, and the connection pool frees the connection when the connection idle time is greater than the connection idle time set in the initiating connection pool.

Database Connection Pool Description:

1. JNDI

2, C3P0

3, Apache Jakarta DBCP

4, BONECP

Of these, the sping framework relies on the third party to use the C3P0 and dbcp two ways, while BONECP claims to be the fastest database connection pool. The DataSource of the Jndi creation implementation is the real implementation of the javax.sql.DataSource; the other three ways are not. The following list shows the differences and differences in several ways:

Serial number	Connection Pool Name	Dependent jar Packages	Implementation of the DataSource class	Note
1	Jndi	The data source is initialized, created, and managed by the appropriate Web server (for example: Tomcat,weblogic,websphere). There is no need to introduce a special jar package in the program.	Javax.sql.datasource
2	C3p0	C3p0-0.9.xxx.jar	Com.mchange.v2.c3p0.ComboPooledDataSource
3	DBCP	Commons-dbcp.jar,commons-pool.jar	Org.apache.commons.dbcp.BasicDataSource
4	Bonecp	Bonecp-0.6.5.jar · Google-collections-1.0.jar · Slf4j-api-1.5.11.jar · Slf4j-log4j12-1.5.11.jar Log4j-1.2.15.jar	Bonecpdatasource

Note: The configuration parameters of database connection pool of the above methods are similar, slightly different, the configuration of the parameters can be configured either by configuration file or by hard coding.

1, using Jndi mode

In this way, the Java.sql.datasource is implemented by the Web server. It is the responsibility of the Web server to initialize the data source, create connection, assign, and manage connection. Because of the functionality that is implemented by the Web server itself, there is no need to introduce a special jar package in project projects, but you need to add the relevant configuration to some of the server's configuration files. Below, take the Tomcat server as an example to describe the use of this approach.

(1), modify the Context.xml file under Tomcat conf, increase the support of resource configuration.

(2), because the data source is created by Tomcat, the required JDBC driver should be placed under Tomcat's Lib path.

(3), write the use of Java code, and placed in the Tomcat environment for use, as follows:

Public void jnditest () {

TODO auto-generated Method stub

Try {

Context initcontext=New initialcontext ();

Context context= (context) Initcontext.lookup ("java:comp/env");

DataSource datasource= (DataSource) context.lookup ("Jdbc/editortest");

Connection cn=datasource.getconnection ();

Statement st=cn.createstatement ();

String sql= "SELECT * from artical where id=1";

ResultSet rs=st.executequery (SQL);

while (Rs.next ()) {

System. out. println ("1:" +rs.getstring (1));

System. out. println ("2:" +rs.getstring (2));

System. out. println ("3:" +rs.getstring (3));

System. out. println ("4:" +rs.getstring (4));

}

} catch (Namingexception e) {

TODO auto-generated Catch block

E.printstacktrace ();

} catch (SQLException e) {

TODO auto-generated Catch block

E.printstacktrace ();

}

}

(4), details refer to Jndisql. Java files, and index.jsp.

Note: This test cannot be tested in the main method; You can write a JSP to test in the Tomcat environment . Because the Java unit environment is the JDK, and the JSP environment is Tomcat, the data connection pool is configured in Tomcat, so it works, but the Java test environment only JDK, so when the reference data connection pool when the environment can not find the error.

Usage environment: When using advanced Web servers such as WebLogic or WebSphere, consider using this approach to improve performance.

3. Using C3p0 method

C3P0 is an open source database connectivity component that supports the creation of database connection pools and the management of connection functions. When you use this method to make a database connection, you need to import C3p0-0.9.1.2.jar.

At the same time, about the database connection specific parameters, such as: Url,username,password, the minimum number of connections, the maximum number of connections ..... Such information can be either configured in an XML configuration file or created programmatically by program encoding. Spring supports the C3P0 database connection pooling approach, so it is supported in the Applicationcontext.xml file when used in a spring environment. Also, because database connection pooling is singleton for a database throughout project, it is guaranteed to be single-instance attributes even if created by encoding. If there is more than one, it inevitably leads to poor performance.

The following is a list of ways to use the C3P0 database connection pool by program encoding.

Combopooleddatasource ds = new combopooleddatasource ();

Try {

Ds.setdriverclass ("Com.mysql.jdbc.Driver");

Ds.setjdbcurl ("Jdbc:mysql://localhost:3306/editortest");

Ds.setuser ("root");

Ds.setpassword ("123456");

Ds.setmaxpoolsize (20);

Ds.setinitialpoolsize (10);

Ds.setmaxidletime (2000);

Connection cn=ds.getconnection ();

Statement st=cn.createstatement ();

String sql= "SELECT * from artical where id=1";

ResultSet rs=st.executequery (SQL);

while (Rs.next ()) {

System. out. println ("1:" +rs.getstring (1));

System. out. println ("2:" +rs.getstring (2));

System. out. println ("3:" +rs.getstring (3));

System. out. println ("4:" +rs.getstring (4));

}

} catch (Propertyvetoexception e) {

TODO auto-generated Catch block

E.printstacktrace ();

} catch (SQLException e) {

TODO auto-generated Catch block

E.printstacktrace ();

}

Note: Typically used in a configuration file configuration, this hard-coded approach is rarely used . Here, just a brief introduction to how C3P0 is used. For details, refer to C3p0test.java.

4. Using DBCP method

DBCP mode, is the data source connection pool method provided by Apache, supports the database connection pool creation, manages the connection and so on function. With the environment, you need to import Commons-dbcp.jar and Commons-pool.jar two jar packages. The above-mentioned Jndi method, which is essentially practical is the DBCP data source; The Web server is responsible for creating the data source by making the configuration on the Web server.

Similarly, the DBCP data source supports both XML configuration files and hard-coded methods. Typically used in a configuration file configuration, almost no hard-coded approach is used. The following is a brief introduction to the encoding of the DBCP method:

Basicdatasource ds = new basicdatasource ();

Ds.setdriverclassname ("Com.mysql.jdbc.Driver");

Ds.seturl ("Jdbc:mysql://localhost:3306/editortest");

Ds.setusername ("root");

Ds.setpassword ("123456");

Ds.setmaxidle (20);

Ds.setinitialsize (10);

Ds.setmaxactive (2000);

Try {

Connection cn=ds.getconnection ();

Statement st=cn.createstatement ();

String sql= "SELECT * from artical where id=1";

ResultSet rs=st.executequery (SQL);

while (Rs.next ()) {

System. out. println ("1:" +rs.getstring (1));

System. out. println ("2:" +rs.getstring (2));

System. out. println ("3:" +rs.getstring (3));

System. out. println ("4:" +rs.getstring (4));

}

} catch (SQLException e) {

TODO auto-generated Catch block

E.printstacktrace ();

}

5, use BONECP way.

BONECP is a fast and efficient database connection pool component, which is said to be the best in performance today, 25 times times faster than C3p0 and DBCP. Using this component, You need to import Bonecp-0.6.5.jar,google-collections-1.0.jar,slf4j-api-1.5.11.jar,slf4j-log4j12-1.5.11.jar,log4j-1.2.15.jar.

Below, simply list the use of coding methods, do a simple understanding.

Bonecpdatasource ds = new bonecpdatasource ();

Ds.setdriverclass ("Com.mysql.jdbc.Driver");

Ds.setjdbcurl ("Jdbc:mysql://localhost:3306/editortest");

Ds.setusername ("root");

Ds.setpassword ("123456");

Try {

Connection cn = Ds.getconnection ();

Statement st = Cn.createstatement ();

String sql = "SELECT * from artical where id=1";

ResultSet rs = st.executequery (SQL);

while (Rs.next ()) {

System. out. println ("1:" + rs.getstring (1));

System. out. println ("2:" + rs.getstring (2));

System. out. println ("3:" + rs.getstring (3));

System. out. println ("4:" + rs.getstring (4));

}

} catch (SQLException e) {

TODO auto-generated Catch block

E.printstacktrace ();

}

Summary: Above, introduced several commonly used data source connection pool, these kinds of connection pool in the use process, namely supports the hard-coded way, also supports the configuration file configuration method, in the formal practical time, should use the configuration way, facilitates the maintenance and the management. Hard-coded ways that can be used for testing. At the same time, the spring framework, in his own way, integrates the above-mentioned data sources, which are theoretically supported. Each data source connection pool has some public properties, because they are inherited from javax.sql.DataSource and have the same concepts as the maximum number of connections and the number of connections initialized. At the same time, they have their own different attributes, have been expanded. Here is a simple introduction, in the actual use, want to achieve high-performance database connection pool management, but also need to delve into each mode of connection property configuration, for example: according to the actual needs, set the appropriate minimum number of connections and the maximum number of connections, wait time and so on.

Note: Database connection pooling to ensure a single instance of the connection pool

Java Datasource, database connection pool