JDBC Basic Learning (vi)-database connection pooling

I. Database connection pool INTRODUCTION 1. Reasons for database connection pooling

For a simple database application, the access to the database is not very frequent. You can simply create a new connection when you need to access the database, and then close it when you're done with it, without any noticeable performance overhead. But for a complex database application, the situation is completely different. Frequent building and closing of connections can greatly reduce the performance of the system, as the use of connections becomes a bottleneck for system performance.

Connection multiplexing. By establishing a database connection pool and a set of connection usage management policies, a database connection can be reused efficiently and safely, thus avoiding the overhead of frequent database connection establishment and shutdown.

For shared resources, there is a well-known design pattern: a resource pool. This model is to solve the problem caused by the frequent allocation and release of resources. The application of this mode to database connection management is to establish a database connection pool, provide a set of efficient connection allocation, use policy, the ultimate goal is to achieve the efficient and safe reuse of the connection.

2. Principles of database connection pooling

Connection pooling The basic idea is that when the system is initialized, the database connection is stored as an object in memory, and when the user needs to access the database, instead of establishing a new connection, it pulls out an established idle connection object from the connection pool. After use, the user does not close the connection, but instead puts the connection back into the connection pool for use by the next request. Connection pooling is managed by the connection pool itself, while connections are established and disconnected. You can also control the number of initial connections in the connection pool, the upper and lower limits of connections, the maximum number of uses per connection, maximum idle time, and so on, by setting parameters for the connection pool. It is also possible to monitor the number, usage, etc. of database connections through its own management mechanism.

3. Advantages of database connection pooling

(1) Resource reuse

Due to the reuse of database connections, the high performance overhead caused by frequent creation and release of connections is avoided. On the other hand, it improves the smoothness of the system running environment (reduce the memory fragmentation and the number of temporary database processes/threads) on the basis of reducing system consumption.

(2) Faster system response speed

Database connection pooling during initialization, it is often that several database connections have been created to be placed in the 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 release, thus reducing overall system response time.

(3) Unified connection management to avoid database connection leakage

In a more complete database connection pool implementation, it is possible to forcibly reclaim the occupied connection based on the pre-connection occupancy timeout setting. This avoids resource leaks that may occur in regular database connection operations.

Second, C3P0 connection pool

The JDBC database connection pool uses Javax.sql.DataSource to represent that DataSource is just an interface that is typically implemented by servers (Weblogic, WebSphere, Tomcat), and some open source organizations provide implementations:
(1) DBCP Database Connection Pool
(2) C3P0 Database Connection Pool

Now because the most used is the C3P0 connection pool, the following describes its configuration.

C3p0-config.xml

<?xml version= "1.0" encoding= "UTF-8"?><c3p0-config><!--Specify the basic properties of the connection data source--><named-config name= " C3p0test "><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><!--if the number of database connections is insufficient, the number of connections requested to the server--><property name= " Acquireincrement ">5</property><!--number of connections when initializing database connection pooling--><property name=" Initialpoolsize ">10 </property><!--The minimum number of connections in a database connection pool--><property name= "Minpoolsize" >5</property><!-- Maximum number of connections in a database connection pool--><property name= "Maxpoolsize" >20</property><!--the number of statement that a database connection pool can maintain-- <property name= "maxstatements" >20</property><!--the number of statement objects that can be used at the same time for each connection--><property name= "Maxstatementsperconnection" >5</property></named-config></c3p0-config>

Jdbctools.java

public class Jdbctools{private static Combopooleddatasource DataSource; Private Jdbctools () {} static{try{//Register driver Class.forName ("COM.MYSQL.JDBC.        Driver ");        }catch (ClassNotFoundException e) {throw new Exceptionininitializererror (e);    }/* * Initialize database connection pool */static{DataSource = new Combopooleddatasource ("C3p0test"); }/* * GET connection */public static Connection getconnection () throws sqlexception{return datasource.g    Etconnection (); }/* * FREE resources */public static void Releaseresource (Connection con,statement st,resultset rs) {try            {if (rs! = null) {rs.close ();        }}catch (SQLException e) {e.printstacktrace ();                    }finally{try{if (st! = null) {try{st.close ();              }catch (SQLException e) {          E.printstacktrace (); }}}finally{if (con! = null) {try{//number                    It is not true that the connection object is closed by the library connection.                    Instead, it is returned to the database connection word con.close ();                    }catch (SQLException e) {e.printstacktrace (); }}}}}/* * General additions and Deletions method * Execute SQL statement using PREPAREDSTATEMNT * @param SQL Band  PLACEHOLDER SQL statement * @param args fill in the variable parameters of the SQL placeholder */public static void update (String sql,object...args) {Connection con    = NULL;    PreparedStatement PS = null;        ResultSet rs = null;    try{con = jdbctools.getconnection ();        PS = con.preparestatement (SQL);        for (int i = 0;i < args.length;i++) {Ps.setobject (i + 1,args[i]);}        Ps.execute ();    }catch (Exception e) {e.printstacktrace ();}    finally{Jdbctools.releaseresource (CON,PS,RS); }} public StatIC void Update (Connection con,string Sql,object...args) {PreparedStatement PS = null;        ResultSet rs = null;        try{PS = con.preparestatement (SQL);    for (int i = 0;i < args.length;i++) {Ps.setobject (i + 1,args[i]);        } ps.execute ();    }catch (Exception e) {e.printstacktrace ();    } finally{Jdbctools.releaseresource (NULL,PS,RS); }/* * OPEN transaction */public static void Begintx (Connection con) {if (con! = null) {Try{con.setautoco Mmit (false);}    catch (SQLException e) {e.printstacktrace ();} }/* * COMMIT TRANSACTION */public static void Committx (Connection con) {if (con! = null) {try{Con.comm    It ();    }catch (SQLException e) {e.printstacktrace (); }}}/* ROLLBACK TRANSACTION */public static void Rollbacktx (Connection con) {if (con! = null) {try{C    On.rollback ();    }catch (SQLException e) {e.printstacktrace (); }    }    }}

JDBC Basic Learning (vi)-database connection pooling