Jdbc BASICS (5) connection pool and Data source: Use of DBCP and C3P0

Jdbc BASICS (5) connection pool and Data source: Use of DBCP and C3P0

I. Concepts and usage of Connection Pool

In actual application development, especially in WEB application systems, If JSP, Servlet, or EJB uses JDBC to directly access data in the database, every data access request must go through the steps of establishing a database connection, opening a database, accessing data, and closing a database connection. Connecting to and opening a database is a resource-consuming and time-consuming task, if such database operations occur frequently, the system performance will inevitably drop sharply, and even cause the system to crash. The database connection pool technology is the most commonly used method to solve this problem.

The main operations of the database connection pool are as follows: (1) Create a database connection pool object. (2) create an initial number of database connections (idle connections) based on the specified parameters ). (3) For a database access request, a connection is directly obtained from the connection pool. If there is no idle connection in the database connection pool object and the number of connections does not reach the maximum (that is, the maximum number of active connections), create a new database connection. (4) access the database. (5) Close the database and release all database connections (close the database connection at this time, rather than actually close it, but put it into the idle queue. If the actual number of idle connections is greater than the initial number of idle connections, the connection is released ). (6) release the database connection pool object (during server stop and maintenance, release the database connection pool object and release all connections ). 2. Open-source connection pool projects DBCP and C3P0 1. DBCP (DataBase connection pool). DataBase connection pool is a java connection pool project on apache and a connection pool component used by tomcat. Using dbcp alone requires 2 Packages: commons-dbcp.jar, commons-pool.jar. The latest jar package for commons-dbcp2-2.1 and commons-pool2-2.4.1, support java7 and above 2. C3P0 is an open source JDBC connection pool, it implements the data source and JNDI binding, support JDBC3 specifications and JDBC2 standard extension. Currently, open-source projects such as Hibernate and Spring do not have the function to automatically recycle idle connections. c3p0 has the function to automatically recycle idle connections. 3. Use of dbcp and C3P0. DBCP use ① import commons-dbcp2-2.1 and commons-pool2-2.4.1 into project
② The configuration file is dbcpconfig. properties, and the connection settings are configured as follows:

# Connection setting driverClassName = com. mysql. jdbc. Driverurl = jdbc: mysql: // localhost: 3306/day16username = rootpassword = 123456 # <! -- Initialize connection --> initialSize = 10 # maximum number of connections maxActive = 50 # <! -- Maximum idle connection --> maxIdle = 20 # <! -- Minimum idle connection --> minIdle = 5 # <! -- The timeout wait time is in milliseconds. The unit is 6000 milliseconds/1000 is equal to 60 seconds. --> maxWait = 60000 # The format of the connection attribute that is attached to the JDBC driver when establishing a connection must be as follows: [property name = property;] # Note: the "user" and "password" attributes are passed explicitly, so they are not included here. ConnectionProperties = useUnicode = true; characterEncoding = utf8 # specify the auto-commit status of the connection created by the connection pool. DefaultAutoCommit = true # driver default specifies the read-only status of the connection created by the connection pool. # If this value is not set, the "setReadOnly" method will not be called. (Some drivers do not support read-only mode, such as Informix.) defaultReadOnly = # driver default specifies the transaction level (TransactionIsolation) of the connection pool ). # The available value is one of the following: (for details, see javadoc .) NONE, READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLEdefaultTransactionIsolation = REPEATABLE_READ

③ Write a tool class with functions similar to jdbc BASICS (2) connect to JdbcUtils in the database through the properties configuration file. However, the principle here is to obtain a data source from the Connection pool and obtain the Connection object through the data source. The Code is as follows:

Package com. cream. ice. jdbc; import java. io. inputStream; import java. SQL. connection; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; import java. util. properties; import javax. SQL. dataSource; import org. apache. commons. dbcp2.BasicDataSourceFactory;/*** DBCP tool class * default adapter method used by DBCP. When the Connection object calls the close () method, the Connection object is put back into the Connection pool, in fact, the connection is not closed * through dbcpconfig. properties file configuration database and connection pool parameters * * @ Author ice **/public class DBCPUtils {public static DataSource dataSource; static {try {InputStream in = DBCPUtils. class. getClassLoader (). getResourceAsStream ("dbcpconfig. properties "); Properties properties = new Properties (); properties. load (in); // return the data source object dataSource = BasicDataSourceFactory. createDataSource (properties);} catch (Exception e) {e. printStackTrace () ;}}/*** obtain the data source * @ return data source */public Static DataSource getDataSource () {return dataSource;}/*** obtain the Connection from the Connection pool * @ return */public static Connection getConnection () {try {return dataSource. getConnection ();} catch (SQLException e) {throw new RuntimeException (e);}/*** release resource */public static void releaseResources (ResultSet resultSet, Statement statement, connection connection) {try {if (resultSet! = Null) resultSet. close ();} catch (SQLException e) {e. printStackTrace ();} finally {resultSet = null; try {if (statement! = Null) statement. close () ;}catch (SQLException e) {e. printStackTrace () ;}finally {statement = null; try {if (connection! = Null) connection. close () ;}catch (SQLException e) {e. printStackTrace () ;}finally {connection = null ;}}}}}

2. Use C3P0

Import c3p0-0.9.5.1.jar and mchange-commons-java-0.2.10.jar

The configuration file for the c3p0-config.xml, the content is as follows:

<?xml version="1.0" encoding="UTF-8"?><c3p0-config>  <default-config>    <property name="driverClass">com.mysql.jdbc.Driver</property>    <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>    <property name="user">root</property>    <property name="password">123456</property>        <property name="checkoutTimeout">30000</property>    <property name="initialPoolSize">10</property>    <property name="maxIdleTime">30</property>    <property name="maxPoolSize">100</property>    <property name="minPoolSize">10</property>    <property name="maxStatements">200</property>    <user-overrides user="test-user">      <property name="maxPoolSize">10</property>      <property name="minPoolSize">1</property>      <property name="maxStatements">0</property>    </user-overrides>  </default-config>  <!-- This app is massive! -->  <named-config name="intergalactoApp">     <property name="acquireIncrement">50</property>    <property name="initialPoolSize">100</property>    <property name="minPoolSize">50</property>    <property name="maxPoolSize">1000</property>    <!-- intergalactoApp adopts a different approach to configuring statement caching -->    <property name="maxStatements">0</property>     <property name="maxStatementsPerConnection">5</property>    <!-- he's important, but there's only one of him -->    <user-overrides user="master-of-the-universe">       <property name="acquireIncrement">1</property>      <property name="initialPoolSize">1</property>      <property name="minPoolSize">1</property>      <property name="maxPoolSize">5</property>      <property name="maxStatementsPerConnection">50</property>    </user-overrides>  </named-config></c3p0-config>

③ Compile the tool class with the following code:

Package com. cream. ice. jdbc; import java. SQL. connection; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; import javax. SQL. dataSource; import com. mchange. v2.c3p0. comboPooledDataSource;/*** C3P0 tool class * DBCP uses dynamic proxy. When the Connection object calls the close () method, the Connection object is put back into the Connection pool, actually do not close the connection * configure the database through the c3p0-config.xml file, connection pool parameters * @ author ice **/public class C3P0Utils {/*** data source */public static Combo PooledDataSource cpDataDataSource = new ComboPooledDataSource ();/*** obtain the data source * @ return data source */public static DataSource getDataSource () {return cpDataDataSource ;} /*** obtain the Connection from the Connection pool * @ return */public static Connection getConnection () {try {return cpDataDataSource. getConnection ();} catch (SQLException e) {throw new RuntimeException (e);}/*** release resource */public static void releaseResources (ResultSet resultSet, Statement statement, Connection connection) {try {if (resultSet! = Null) resultSet. close ();} catch (SQLException e) {e. printStackTrace ();} finally {resultSet = null; try {if (statement! = Null) statement. close () ;}catch (SQLException e) {e. printStackTrace () ;}finally {statement = null; try {if (connection! = Null) connection. close () ;}catch (SQLException e) {e. printStackTrace () ;}finally {connection = null ;}}}}}

The usage of DBCPUtils and C3P0Utils is the same as that of JdbcUtils. The difference is that when a resource is released, when the Connection object calls the close () method, it only puts the Connection object back into the Connection pool, but does not actually close the Connection.