About the simple use of the C3P0 database connection pool

When using the development of a database-based Web program, the traditional pattern is basically the following steps:

Establish a database connection in the main program (such as servlet, beans).

For SQL operations

Disconnect the database.

This pattern of development, the existence of the problem:

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.

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.

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.

2. Database connection pooling (connection pool)

In order to solve the problem of database connection in traditional development, database connection pool technology can be adopted.

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.

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.

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.

3. Advantages of database connection pooling technology

(1) Resource reuse: Due to the reuse of database connections, avoid the frequent creation, release the connection caused by a large number of performance costs. On the basis of reducing the system consumption, on the other hand, it also increases the stability of the system operating environment.

(2) 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 deallocation, thus reducing system response time

(3) New resource allocation means for multiple applications sharing the same database system, the application layer through the database connection pool configuration to achieve an application of the maximum number of available database connections to avoid an application exclusive all database resources.

(4) Unified connection management, to avoid database connection leakage in a more complete database connection pool implementation, according to the pre-occupancy timeout setting, forced to reclaim the occupied connection, thus avoiding the general database connection operation may appear in the resource leakage.

Let me show you how to use the C3P0 connection pool simply

(1) configuration file, located under src c3p0-config.xml, the file name is C3p0-config, will be automatically read. Each configuration specific explanation self-surfing search

<?xml version= "1.0" encoding= "UTF-8"?>
<c3p0-config>
<default-config>
<property name= "User" >root</property>
<property name= "Password" ></property>
<property name= "Driverclass" >com.mysql.jdbc.Driver</property>
<property name= "Jdbcurl" >jdbc:mysql://localhost:3306/test</property>
<property name= "Checkouttimeout" >30000</property>
<property name= "Idleconnectiontestperiod" >30</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>
</default-config>
</c3p0-config>

(2) Connection tool class

Package com.myc3p0.utils;

Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;

Import Com.mchange.v2.c3p0.ComboPooledDataSource;

public class Dbconn {
private static Combopooleddatasource ds = new Combopooleddatasource ();

Get Connected
public static Connection Getconn () {
try {
return Ds.getconnection ();
} catch (SQLException e) {
throw new RuntimeException (e);
}
}

Close the link is actually to recycle the connection
public static void Close (Connection conn) throws SQLException {
IF (conn! = null) {
try {
Conn.close ();
} catch (SQLException e) {
E.printstacktrace ();
Throw e;
}
}
}

public static void Close (PreparedStatement pstate) throws SQLException {
if (pstate! = null) {
Pstate.close ();
}
}

public static void Close (ResultSet rs) throws SQLException {
if (rs! = null) {
Rs.close ();
}
}

}

(3) test

Package com.myc3p0.utils;

Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;

public class Testc3p0 {
public static void Main (string[] args) {
Connection conn = Dbconn.getconn ();
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "SELECT * from temp WHERE id = 1";
try {
pstmt = conn.preparestatement (sql);
rs = Pstmt.executequery ();
Student Student = new Student ();
while (Rs.next ()) {
Student.setid (Rs.getint (1));
Student.setname (rs.getstring (2));
}
SYSTEM.OUT.PRINTLN (student);

} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
try {
Closing the connection is actually recycling the connection
Dbconn.close (RS);
Dbconn.close (PSTMT);
Dbconn.close (conn);
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}

}

}

About the simple use of the C3P0 database connection pool