JDBC database connection pool and jdbc database connection pool
Necessary text description:
Necessity of JDBC database connection pool:
When developing database-based web programs, the traditional mode is basically as follows:
Create a database connection in the main program (such as servlet and beans.
SQL operations
Disconnect the database.
This mode of development has the following problems:
The common JDBC database connection is obtained using DriverManager.
The Connection is loaded into the memory, and then the user name and password are verified (the cost is 0.05s ~ 1 s ). Data required
When the database is connected, one request is sent to the database, and the connection is closed after the execution is complete. This method will consume a lot
Amount of resources and time. Database Connection resources are not well reused. If there are hundreds or even several
Thousands of online users, frequent database connection operations will occupy a lot of system resources, and even cause server crashes.
Each database connection must be closed after use. Otherwise, if the program fails to be closed due to an exception, it will cause the database
Memory leakage in the system will eventually cause database restart.
This kind of development cannot control the number of connection objects created, and system resources will be allocated without consideration, such as too many connections,
It may also cause memory leakage and server crash.
Database Connection Pool:
To solve the problem of database connection in traditional development, the database connection pool technology can be used.
The basic idea of the database connection pool is to create a "buffer pool" for the database connection ". In advance
Add a certain number of connections to the buffer pool. When you need to establish a database connection, you only need"
.
The database connection pool is responsible for allocating, managing, and releasing database connections. It allows repeated applications
Use an existing database connection instead of a new one.
During database connection pool initialization, a certain number of database connections are created in the connection pool.
The number of database connections is set by the minimum number of database connections. Whether these database connections are
Otherwise, the connection pool will always ensure that there are at least so many connections. Maximum connection pool
The number of database connections limits the maximum number of connections that the connection pool can occupy.
When the number of connections requested by the pool exceeds the maximum number of connections, these requests will be added to the waiting queue.
Advantages of database connection pool technology:
Resource Reuse:
Database connections are reused to avoid frequent creation and release of connections, resulting in a large amount of performance overhead.
On the basis of reducing system consumption, the stability of the system runtime environment is also increased.
Faster system response speed:
During database connection pool initialization, several database connections have been created and placed in the connection pool.
. The connection Initialization is complete. For service request processing
Use existing available connections to avoid the time overhead during database connection initialization and release, thus reducing
Less system response time
New Resource allocation methods:
For systems where multiple applications share the same database, you can configure the database connection pool at the application layer,
Limit the maximum number of available database connections of an application to prevent an application from occupying all database resources exclusively.
Unified connection management to avoid database connection leaks
In the more comprehensive implementation of the database connection pool, you can set the pre-occupation timeout to forcibly recycle the occupied
To avoid resource leakage during conventional database connection operations.
The following describes two open-source database connection pools: DBCP C3P0
The JDBC database connection pool uses javax. SQL. DataSource to indicate that DataSource is only an interface,
This interface is usually implemented by the server (Weblogic, WebSphere, Tomcat), and some open-source organizations provide implementation:
DBCP database connection pool
C3P0 database connection pool
DataSource is usually called a data source. It consists of a connection pool and a connection pool.
DataSource is called a connection pool.
DBCP is an open-source connection pool implementation under the Apache Software Foundation. The connection pool depends on another open-source system under the organization:
Common-pool. To use this connection pool, add the following two jar files to the system:
Commons-dbcp.jar: Implementation of Connection Pool
Commons-pool.jar: dependency library implemented by connection pool
Tomcat's connection pool is implemented using this connection pool. The database connection pool can be integrated with the application server or used independently by the application.
Example of DBCP Data source:
Different data sources and databases have different connections, so there is no need to create multiple data sources. It is the factory that generates database connections. Therefore, the entire application only needs one
Data source.
After the database access is complete, the program closes the database connection as before: conn. close (); but the above Code does not close the physical connection of the database,
It only releases the database connection and returns it to the database connection pool.
DBCP simple code case Database Connection Pool connection method:
/** File name: DbcpTest. java * Copyright: Copyright by www.w.wei.com * Description: * modifier: Cuigaochong * modification time: * tracking Ticket No.: * modification Ticket No.: * modification content: */package com. jdbc. dbcp; import java. io. IOException; import java. io. inputStream; import java. SQL. connection; import java. SQL. SQLException; import java. util. properties; import org. apache. commons. dbcp. basicDataSource; import org. apache. commons. dbcp. basicDataSourceFactory; import org. junit. Test;/*** <one-sentence function description> <function description> ** @ author name ID * @ version [version number,] * @ see [related classes/methods] * @ since [product/module version] */public class DbcpTest {@ Test public void test01 () throws SQLException {BasicDataSource = null; dataSource = new BasicDataSource (); // The attribute dataSource required for data source connection. setUsername ("scott"); dataSource. setPassword ("tiger"); dataSource. setUrl ("jdbc: oracle: thin: @ localhost: 1521: CMDL"); dat ASource. setDriverClassName ("oracle. jdbc. driver. oracleDriver "); // attributes of the database connection pool // specify the number of connections initialized in the data connection pool dataSource. setInitialSize (10); // specify the maximum number of connections in the database connection pool dataSource. setMaxActive (50); // specify the minimum number of connections and the minimum number of idle connections saved in the database connection pool dataSource. setMinIdle (5); // wait for the maximum connection time of the database connection pool to be connected. If the maximum unit is ms, an exception dataSource is thrown. setMaxWait (5*1000); Connection conn = dataSource. getConnection (); System. out. println (conn);}/*** <one-sentence function description> method 2 relative to method 1 Better because all attributes can be stored in jdbc. * In the properyies file, and developers do not need to care about the implementation of DataSource, you only need to obtain <function description> ** @ see [Class, Class # method, class # member] */@ Test public void test02 () from the factory class () {Properties properties = new Properties (); InputStream is = null; BasicDataSource = null; Connection conn = null; try {is = getClass (). getClassLoader (). getResourceAsStream ("jdbc. properties "); properties. load (is); dataSource = (BasicDataSource) BasicDataS OurceFactory. createDataSource (properties); conn = dataSource. getConnection (); // you can obtain jdbc directly through dataSource. the Configuration value in properties is System. out. println (dataSource. getMaxActive ();} catch (Exception e) {e. printStackTrace ();} finally {if (null! = Conn) {try {conn. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (null! = Is) {try {is. close () ;}catch (IOException e) {e. printStackTrace ();}}}}}
C3P0 Data Source System add c3p0-0.9.1.2.jar
Step: 1: Create the c3p0-config.xml as follows
<C3p0-config>
<! -- This app is massive! -->
<Named-config name = "intergalactoApp">
<Property name = "user"> scott </property>
<Property name = "password"> tiger </property>
<Property name = "driverClass"> oracle. jdbc. driver. OracleDriver </property>
<Property name = "jdbcUrl"> jdbc: oracle: thin: @ localhost: 1521: CMDL </property>
<Property name = "acquireIncrement"> 5 </property>
<Property name = "initialPoolSize"> 10 </property>
<Property name = "minPoolSize"> 5 </property>
<Property name = "maxPoolSize"> 10 </property>
<! -- Number of statement that can be maintained by c3p0 -->
<Property name = "maxStatements"> 0 </property>
<! -- Number of statement available for each connection -->
<Property name = "maxStatementsPerConnection"> 5 </property>
</Named-config>
C3p0-config>
2: Create a ComboPooledDataSource instance,
ComboPooledDataSource CPPS = new ComboPooledDataSource ("intergalactoApp ");
3: Get the connection
Connection conn = CPPS. getConnection ();
Connection to get case code:
/** File name: C3p0Test. java * Copyright: Copyright by www.w.wei.com * Description: * modifier: Cuigaochong * modification time: * tracking Ticket No.: * modification Ticket No.: * modification content: */package com. jdbc. dbcp; import java. beans. propertyVetoException; import java. SQL. connection; import java. SQL. SQLException; import org. junit. test; import com. mchange. v2.c3p0. comboPooledDataSource;/*** <one-sentence function description> <function description> ** @ author name ID * @ version [version number,] * @ see [related classes/methods] * @ since [product/module version] */public class C3p0Test {@ Test public void test00 () throws PropertyVetoException, SQLException {ComboPooledDataSource CPPS = new ComboPooledDataSource (); CPPS. setDriverClass ("oracle. jdbc. driver. oracleDriver "); CPPS. setJdbcUrl ("jdbc: oracle: thin: @ localhost: 1521: l"); CPPS. setUser ("scott"); CPPS. setPassword ("tiger"); Connection conn = CPPS. getConnection (); System. out. println (conn);} @ Test public void test01 () throws SQLException {// intergalactoApp is the <named-config name = "intergalactoApp"> // the configuration file can be xml or properties. xml is recommended, the xml name can only be the c3p0-config.xml ComboPooledDataSource cpd= new ComboPooledDataSource ("intergalactoApp"); Connection conn = CPPS. getConnection (); System. out. println (conn); // obtain the attribute System in the configuration file. out. println (CPPS. getMaxPoolSize ());}}
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.
