Java--JDBC Learning--Database connection pooling

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:

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

This pattern of development, the existence of the problem:

1. The normal JDBC database connection is obtained using DriverManager, which loads the Connection into memory each time a connection is made to the database, and then validates the user name and password (which takes 0.05s~1s time). When a database connection is required, a request is made to the database and then disconnected after execution is complete. Such a way would consume a lot of resources and time. Database connection resources are not well reused. If there are hundreds of or even thousands of people online, frequent database connection operations will consume a lot of system resources, which can even cause the server to crash.
2. For each database connection, you will have to disconnect after you have finished using it. Otherwise, if the program fails to close, it will cause a memory leak in the database system and will eventually cause the database to be restarted.
3. 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 pools (connection pool)

1. In order to solve the problem of database connection in traditional development, database connection pool technology can be adopted.
2. The basic idea of a database connection pool is to establish a "buffer pool" for database connections. A certain number of connections are pre-placed in the buffer pool, and when a database connection needs to be established, simply take one out of the buffer pool and put it back when you are finished.
3. 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.
4. When the database connection pool is initialized, a certain number of database connections are created in the connection pool, and the number of these database connections is set by the minimum number of database connections. Regardless of whether these database connections are being used, the connection pool will always be guaranteed to have at least so many connections. The maximum number of database connections for a connection pool limits the maximum number of connections that this pool can occupy, and these requests are added to the wait queue when the number of connections requested by the application to the connection pool exceeds the maximum number of connections.

How database connection pooling works

Benefits of database connection pooling technology

Resource reuse:
Due to the reuse of database connections, there is a high performance overhead associated with frequent creation and release of connections. On the basis of reducing the system consumption, on the other hand, it also increases the stability of the system operating environment.
Faster system response Speed
Database connection pooling during initialization, several database connections are often created to be placed in the connection pool for backup. The initialization of the connection is now complete. For business request processing, direct utilization of existing available connections avoids the time overhead of database connection initialization and release, thus reducing system response time
New means of resource allocation
For multiple applications sharing the same database system, the application layer can be configured through the database connection pool, the application of a maximum number of available database connections limit, to avoid an application exclusive all database resources.
Unified connection management to avoid database connection leaks
In a more complete database connection pool implementation, it is possible to forcibly reclaim the occupied connection based on the pre-occupancy timeout setting, thus avoiding the potential resource leaks in regular database connection operations.

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:

1. DBCP Database Connection Pool
2. 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

DBCP is an open source connection pool implementation under the Apache Software Foundation, which relies on another open source system under the organization: Common-pool. To use this connection pool implementation, you should add the following two jar files to the system:

1. Commons-dbcp.jar: Implementation of connection pooling
2. Commons-pool.jar: Dependency libraries for connection pooling implementations

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.

DBCP Data Source Usage Example:

Unlike data sources and database connections, there is no need to create multiple data sources, which are factories that generate database connections, so the entire application needs only one data source.
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

You can also read from a configuration file:

DBCP:

Dbcp.properties

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

Read the file for database connection pool configuration

    /*** 1. Load DBCP's 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. */@Test Public voidTestdbcpwithdatasourcefactory ()throwsexception{ Properties Properties = new Properties (); InputStream instream = JDBCTest.class.getClassLoader (). getResourceAsStream ("dbcp.properties"); Properties.load (instream); DataSource DataSource = Basicdatasourcefactory.createdatasource (properties);         System.out.println (Datasource.getconnection ()); //Basicdatasource Basicdatasource =//(Basicdatasource) DataSource;//        //System.out.println (basicdatasource.getmaxwait ());}

C3P0:

C3p0-config.xml

<?XML version= "1.0" encoding= "UTF-8"?><C3p0-config>    <Named-configname= "Helloc3p0">                <!--specifying the basic properties of a connected data source -        < Propertyname= "User">Root</ Property>        < Propertyname= "Password">1230</ Property>        < Propertyname= "Driverclass">Com.mysql.jdbc.Driver</ Property>        < Propertyname= "Jdbcurl">Jdbc:mysql:///soyoungboy</ Property>                <!--How many connections are requested to the database server at a time if the number of connections in the database is insufficient -        < Propertyname= "Acquireincrement">5</ Property>        <!--number of connections when initializing a database connection pool -        < Propertyname= "Initialpoolsize">5</ Property>        <!--minimum number of database connections in the database connection pool -        < Propertyname= "Minpoolsize">5</ Property>        <!--Maximum number of database connections in a database connection pool -        < Propertyname= "Maxpoolsize">10</ Property>        <!--c3p0 The number of Statement that a database connection pool can maintain -        < Propertyname= "Maxstatements">20</ Property>        <!--The number of Statement objects that can be used at the same time for each connection -        < Propertyname= "Maxstatementsperconnection">5</ Property>        </Named-config>        </C3p0-config>

Use:

    /*** 1. Create a C3p0-config.xml file, * Refer to the contents of appendix B:configuation files in the Help document * 2. Create Combopooleddatasource       Example: * DataSource DataSource = * New Combopooleddatasource ("helloc3p0"); * 3.      Gets the database connection from the DataSource instance. */@Test Public voidTestc3powithconfigfile ()throwsexception{ DataSource DataSource = new Combopooleddatasource ("Helloc3p0");                 System.out.println (Datasource.getconnection ()); Combopooleddatasource Combopooleddatasource = (Combopooleddatasource) DataSource;     System.out.println (Combopooleddatasource.getmaxstatements ()); }

Java--JDBC Learning--Database connection pooling