JDBC Foundation (v) connection pooling and data sources: DBCP and C3P0 use

first, the concept and use of the connection pool

In practical application development, especially in Web applications, if a JSP, servlet, or EJB uses JDBC to directly access data in a database, every data access request must undergo steps such as establishing a database connection, opening a database, accessing data, and shutting down a database connection. While connecting and opening the database is a resource consuming and time consuming work, if this kind of database operation occurs frequently, the performance of the system will inevitably drop sharply, even cause the system to crash. database connection Pooling technology is the most common method to solve this problem.

The main operations of the database connection pool are as follows:(1) Establish database connection pool object. (2) Create the initial number of database connections (that is, the number of idle connections) according to the parameters specified beforehand. (3) for a database access request, get a connection directly from the connection pool. Create a new database connection 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). (4) access the database. (5) Close the database, release all database connections (at this point, close the database connection, not really shut down, but put it in an idle queue.) Release the connection if the number of actual idle connections is greater than the number of initial idle connections). (6) Release the database connection pool object (during server stop, maintenance, release the database connection pool object, and release all connections). Two, open source connection pool project 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. The use of DBCP alone requires 2 packages:Commons-dbcp.jar,commons-pool.jar. The latest jar packages are commons-dbcp2-2.1 and commons-pool2-2.4.1, supporting more than Java7 2. c3p0 is an open source JDBC connection pool that implements the data source and Jndi bindings, and supports the standard extensions of the JDBC3 specification and JDBC2. The open source projects that currently use it are hibernate,spring , etc.DBCP does not automatically reclaim idle connections, C3P0 has the ability to automatically reclaim idle connections Iii. use of DBCP and c3p01.DBCP Use① will commons-dbcp2-2.1 and Span style= "margin:0px; padding:0px; Color:rgb (255,153,0) ">commons-pool2-2.4.1 Import project Span style= "margin:0px; padding:0px; Color:rgb (0,0,0) ">
The ② configuration file is dbcpconfig.properties, and the connection settings are self-configuring with the following contents :

#连接设置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 #<!--Timeout Wait time in milliseconds of 6000 milliseconds/1000 equals 60 seconds-->maxwait=60000#jdbc the Connection property property that accompanies the driver when the connection is established must be in such a format: [property name =property;] #注意: "User" The two attributes with "password" are explicitly passed, so there is no need to include them here. connectionproperties=useunicode=true;characterencoding=utf8# Specifies the auto-commit (Auto-commit) state of the connection created by the connection pool. Defaultautocommit=true#driver default Specifies the read-only (read-only) state of the connection created by the connection pool. #如果没有设置该值, 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 created by the connection pool. #可用值为下列之一: (Details visible Javadoc. ) none,read_uncommitted, read_committed, Repeatable_read, Serializabledefaulttransactionisolation=repeatable_read

③ Write a tool class that functions like the JDBC Foundation (ii) Connect to the database through the properties configuration filein theJdbcutils, but the principle here is to get a data source from the connection pool and get 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 * DBCP The default adapter method used, when the connection object calls the Close () method, Put the connection object back into the connection pool and do not actually close the connection * configuration database via dbcpconfig.properties file, connection pool parameters * * @author ice * */public class Dbcputils {public S Tatic DataSource datasource;static {try {inputstream in = DBCPUtils.class.getClassLoader (). getResourceAsStream (" Dbcpconfig.properties "); Properties Properties = new properties ();p roperties.load (in);//Return Data source Object DataSource = Basicdatasourcefactory.createdatasource (properties);} catch (Exception e) {e.printstacktrace ();}} /** * Get Data source * @return Data source */public static DataSource Getdatasource () {return DataSource;} /** * Get connection from Connection pool * @return */public static Connection getconnection () {try {return datasource.getconnection ();} catch (SQLE Xception e) {throw new RuntimeException (e);}} /** * Release resources */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 ();} cat CH (SQLException e) {e.printstacktrace ();} finally {statement = null;try {if (connection! = null) Connection.close ();} CATC H (SQLException e) {e.printstacktrace ();} finally {connection = null;}}}}

2.c3p0 use

① importing C3p0-0.9.5.1.jar and Mchange-commons-java-0.2.10.jar

② configuration file is c3p0-config.xml Span style= "margin:0px; padding:0px; Color:rgb (0,0,0) ", the contents are 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= "initial Poolsize ">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> &LT;/DEFAULT-CONFIG&GT <!--this app is massive! --<named-config name= "Intergalactoapp" > <property name= "acquireincrement" >50</property> &lt ;p roperty name= "initialpoolsize" >100</property> <property name= "Minpoolsize" >50</property> & Lt;property name= "Maxpoolsize" >1000</property> <!--Intergalactoapp adopts a different approach to Configur ING statement caching--<property name= "maxstatements" >0</property> <property name= "Maxstatemen  Tsperconnection ">5</property> <!--He's important, but there's only one of him--and <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> &Lt;/user-overrides> </named-config></c3p0-config> 

③ Write 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, puts the connection object back into the connection pool and does not actually close the connection * configuration database via C3p0-config.xml file, connection pool parameters * @ Author Ice * */public class C3p0utils {/** * data source */public static Combopooleddatasource Cpdatadatasource = new Combopooledd Atasource ();/** * Get Data source * @return Data source */public static DataSource Getdatasource () {return cpdatadatasource;}  /** * Get connection from Connection pool * @return */public static Connection getconnection () {try {return cpdatadatasource.getconnection ();} catch (SQLException e) {throw new RuntimeException (e);}} /** * Release resources */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 ();} C Atch (SQLException e) {e.printstacktrace ();} finally {connection = null;}}}}

dbcputilsand thec3p0utilswith thejdbcutilsusage indistinguishable, the difference is simply to release the resource when the connection object calls the Close () method, just put the connection object back into the connection pool, and actually does not close the connection.

JDBC Foundation (v) connection pooling and data sources: DBCP and c3p0 use