The use of c3p0 in JDBC Hibernate spring

Introduction

Database connection pooling: The database connection pool is responsible for allocating, managing, and freeing database connections, allowing applications to reuse an existing database connection instead of re-establishing one, and freeing up database connections that have idle time beyond the maximum idle time to avoid missing database connections due to no database connections being freed. This technology can significantly improve the performance of database operations.

Java connection pooling: Open source database connection pools in Java are as follows (excerpt from Baidu Encyclopedia):
1, C3P0: is an open source JDBC connection pool, which is published in the Lib directory with Hibernate, including statement objects for the connection and datasources pools that implement the JDBC3 and JDBC2 extension specification descriptions.
2. Proxool: Is a Java SQL driver driver that provides a connection pooling package for other types of drivers selected. Can be easily ported to existing code, fully configurable, fast, mature, and robust. You can transparently increase the connection pooling capability for existing JDBC drivers.
3. Jakarta DBCP:DBCP is a database connection pool that relies on the Jakartacommons-pool object pooling mechanism. DBCP can be used directly in the application.
4, Ddconnectionbroker: is a simple, lightweight database connection pool.

5, Dbpool: is an efficient, easy to configure database connection pool. In addition to supporting the functionality that a connection pool should have, it includes an object pool that enables users to develop a database connection pool that meets their needs. 1 Using native JDBC operations

First you need to download the official jar package, the current version is 0.9.5.2,

csdn Download Link: http://download.csdn.net/download/weixin_38187317/10252492

Official website Download Link: https://sourceforge.net/projects/c3p0/files/latest/download?source=files

Download, unzip C3p0-0.9.5.2.jar and Mchange-commons-java-0.2.11.jar to import the project

New Project C3P0TEST project structure as follows Sqljdbc42.jar for SQL Server connection driver, MySQL Similarly, need to add their own additional download, or I sent a compressed package also:

1 in the project SRC under the new name C3p0-config.xml, note that the name cannot be changed

<?xml version= "1.0" encoding= "UTF-8"?> <! DOCTYPE xml> <c3p0-config> <!--default configuration-<default-config> <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> </default-config> <!--Configuring connection pooling 1-<named-config name= "SQL Server" > < Property name= "User" >test</property> <property name= "password" >123</property> <property Name= "Driverclass" >com.microsoft.sqlserver.jdbc.SQLServerDriver</property> <property name= "Jdbcurl" >jdbc:sqlserver://localhost:1433; Databasename=test</property> <!--The user is the database users, password is the password of the database, Driverclass is the database driver of SQL Server, Jdbcurl is the URL of the connection database-<!--when the connection in the connection pool is exhausted c3p0 the number of connections you get simultaneously-<property name= "acquireincrEment ">5</property> <!--get 10 connections when initializing, the value should be between Minpoolsize and Maxpoolsize and <property name=" Initialpoolsize ">10</property> <!--the minimum number of connections left in the connection pool-<property name=" Minpoolsize ">10</ Property> <!--The maximum number of connections that are retained in the connection pool--<property name= "Maxpoolsize" >50</property> <!--the standard parameters of JDBC to control The number of preparedstatements loaded within the data source. 
However, because the pre-cached statements belong to a single connection instead of the entire connection pool. So setting this parameter takes into account a variety of factors. If both maxstatements and maxstatementsperconnection are 0, the cache is closed. default:0--<property name= "maxstatements" >20</property> <!-- Maxstatementsperconnection defines the maximum number of cache statements that a single connection in a connection pool has. default:0--<property name= "maxstatementsperconnection" >5</property> </named-config> <!-- Configure connection Pooling 2 MySQL--<named-config name= "MySQL" > <property name= "Driverclass" >com.mysql.jdbc.driver</ property> <property name= "Jdbcurl" >jdbc:mysql://localhost:3306/CoupleSpace</property> <property Name= "User" &Gt;root</property> <property name= "password" >root</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> <!--below for more detailed configuration, optional--<!--acquireincrement: The link ran out of auto-increment 3. --<property name= "Acquireincrement" >3</property> <!--acquireretryattempts: Try again 30 times after the link has failed. --<property name= "acquireretryattempts" >30</property> <!--acquireretrydelay, 1000 milliseconds between two connections. --<property name= "Acquireretrydelay" >1000</property> <!--autocommitonclose: All uncommitted actions are rolled back by default when the connection is closed. --<property name= "Autocommitonclose" >false</property> <!--automatictesttable:c3p0 Test table, no use. --<property name= "automatictesttable" >Test</property> <!--breakafterAcquirefailure: The data being submitted is not discarded when an error occurs. --<property name= "Breakafteracquirefailure" >false</property> <!-- checkouttimeout:100 milliseconds after the SQL data is not completed will be an error, if set to 0, then will wait indefinitely. --<property name= "Checkouttimeout" >100</property> <!-- Connectiontesterclassname: Test the connection by implementing a class of Connectiontester or Queryconnectiontester. The class name needs to be set to the full path. Default:com.mchange.v2.c3p0.impl.DefaultConnectionTester--<property name= "Connectiontesterclassname" > </property> <!--factoryclasslocation: Specifies the path of the C3P0 libraries, which is not required if (usually) local, and defaults to null. --<property name= "Factoryclasslocation" >null</property> <!-- Forceignoreunresolvedtransactions: Authors strongly recommend a property that is not used. --<property name= "Forceignoreunresolvedtransactions" >false</property> <!-- Idleconnectiontestperiod: Checks for idle connections in all connection pools every 60 seconds. --<property name= "Idleconnectiontestperiod" >60</property> <!--initialpoolsize: Get three connections when initializing, The value should be between Minpoolsize and Maxpoolsize. -<p roperty name= "initialpoolsize" >3</property> <!--maxidletime: Maximum idle time, unused in 60 seconds, the connection is discarded. If 0, it will never be discarded. -<property name= "MaxIdleTime" >60</property> <!--maxpoolsize: The maximum number of connections that are kept in the connection pool. --<property name= "Maxpoolsize" >15</property> <!--maxstatements: Maximum number of links. --<property name= "maxstatements" >100</property> <!-- Maxstatementsperconnection: Defines the maximum number of cache statements that a single connection in a connection pool has. default:0--<property name= "Maxstatementsperconnection" ></property> <!--numhelperthreads: asynchronous operator Performance is performed at the same time through multi-threaded implementations of multiple operations. Default:3--<property name= "Numhelperthreads" >3</property> <!-- Overridedefaultuser: When the user calls getconnection (), the root user becomes the user to get the connection. Used primarily when connection pooling is connected to a non-c3p0 data source. Default:null--<property name= "Overridedefaultuser" >root</property> <!--OVERRIDEDEFAULTPASSW Ord: A parameter that corresponds to the Overridedefaultuser parameter. Default:null--<property name= "Overridedefaultpassword" >password</property> <!--password: password. Default:null--<property name= "password" ></property> <!--preferredtestquery: Define test statements that are executed by all connection tests. This one significantly improves the test speed in the case of connection testing. Note: The test table must exist at the time of the initial data source. Default:null--<property name= "preferredtestquery" >select ID from test where id=1</property> &L t;! --propertycycle: The user waits up to 300 seconds before modifying the system configuration parameters to execute. default:300--<property name= "propertycycle" >300</property> <!-- Testconnectiononcheckout: Please use it only when you need it because of high performance consumption. Default:false--<property name= "Testconnectiononcheckout" >false</property> <!-- Testconnectiononcheckin: If set to true then the validity of the officer connection is obtained while the connection is made. Default:false--<property name= "Testconnectiononcheckin" >true</property> <!--User: username. Default:null--<property name= "user" >root</property> <!-- Usestraditionalreflectiveproxies: Dynamic reflection Agent. Default:false--<property name= "usestraditionalreflectiveproxies" >false</property> </ Named-config> <!--Configuring connection Pooling 3--<!--configuring connection pooling 4--and </c3p0-config> 

Parameter write comments are very detailed, there are many optional parameters, you can configure multiple connection pools at the same time, a connection pool corresponding to a database such as this is MySQL and SQL Server

2 Getting the Connection tool class:

Package test;
Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;

Import java.sql.SQLException;

Import Com.mchange.v2.c3p0.ComboPooledDataSource; public class C3p0utils {//through the identity name to create the appropriate connection pool static combopooleddatasource DataSource = new Combopooleddatasource ("Sqlserv

Er "); 

		Take a connection from the connection pool public static Connection getconnection () throws Exception {try {return datasource.getconnection (); } catch (Exception e) {System.out.println ("Exception in c3p0utils!"
			+ e); throw new Exception ("Database connection Error!")
		+ e);  }}//release connection back to connection pool public static void Close (Connection conn, PreparedStatement PST, ResultSet rs) throws Exception {if (rs! = null)
			{try {rs.close (); } catch (SQLException e) {System.out.println ("Exception in c3p0utils!"
				+ e); throw new Exception ("Database connection shutdown Error!")
			+ e);
			}} if (PST! = null) {try {pst.close (); } catch (SQLException e) {System.out.println ("Exception in c3p0utils!"
				+ e); Throw nEW Exception ("Database connection shutdown Error!")
			+ e);
			}} if (conn! = null) {try {conn.close (); } catch (SQLException e) {System.out.println ("Exception in c3p0utils!"
				+ e); throw new Exception ("Database connection shutdown Error!")
			+ e); }}}//release connection back to connection pool public static void Close (Connection conn,resultset rs) throws Exception {if (rs! = null) {TR
			y {rs.close (); } catch (SQLException e) {System.out.println ("Exception in c3p0utils!"
				+ e); throw new Exception ("Database connection shutdown Error!")
			+ e);
			}} if (conn! = null) {try {conn.close (); } catch (SQLException e) {System.out.println ("Exception in c3p0utils!"
				+ e); throw new Exception ("Database connection shutdown Error!")
			+ e); }
		}
	}
}

3 Test Program:

Package test;
Import java.sql.Connection;
Import Java.sql.ResultSet;
Import java.sql.SQLException;

Import java.sql.Statement;
		public class C3p0test {public static void main (string[] args) {System.out.println ("C3P0 Connection test program");
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
			try {conn = c3p0utils.getconnection ();
			st = Conn.createstatement ();

			rs = St.executequery ("SELECT * from person");
			while (Rs.next ()) {System.out.println ("Database data:" +rs.getstring ("username"));
		}} catch (Exception e) {//TODO auto-generated catch block E.printstacktrace ();
				} finally {if (rs! = null) try {rs.close ();
				} catch (SQLException e) {//TODO auto-generated catch block E.printstacktrace ();
				} if (st! = null) try {st.close ();
				} catch (SQLException E1) {//TODO auto-generated catch block E1.printstacktrace ();
			} try {C3p0utils.close (conn, RS); } catch (Exception e) {//TODO AuTo-generated Catch block E.printstacktrace (); }
		}
	}

}

Operation Result:

2 use of c3p0 in Hibernate

Hibernate has a connection pool built into it, and all database connections are taken from the connection pool. Sessionfactory The connection pool is initialized when loading, Hibernate's built-in connection pool is simpler and less efficient, and is used only for learning and demonstration purposes, preferably using a third-party connection pool, such as C3P0. By overwriting hibernate.connection.pool_size, you can turn off Hibernate's built-in connection pool.

C3p0 in Hibernate lib package, do not need to introduce themselves, as long as the hibernate parameters set hibernate.c3p0.* parameters, Hibernate automatically use C3P0 as a connection pool implementation such as:

Hibernate.cfg.xml

<?xml version= ' 1.0 ' encoding= ' UTF-8 '?> <! DOCTYPE hibernate-configuration Public "-//hibernate/hibernate configuration DTD 3.0//en" "http://www. Hibernate.org/dtd/hibernate-configuration-3.0.dtd "> <!--Generated by MyEclipse hibernate Tools. - 
3 use of c3p0 in spring 
Very simple to use if you use Jdbcdaosupport, you can simply change the implementation class of the data source from Basicdatasource to Combopooleddatasource, for example:

	<!--Configure C3P0 Connection pool:-
	<bean id= "DataSource" class= "Com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method= "Close" >
		<property name= "Driverclass" value= " Com.microsoft.sqlserver.jdbc.SQLServerDriver "/>
		<property name=" Jdbcurl "value=" jdbc:sqlserver:// localhost:1433; Databasename=test "/>
		<property name=" user "value=" test "></property>
		<property name=" Password "value=" 123 "></property>
	</bean>