JDBC database connection pool

Today I mainly talked about three methods. The working principle of the Connection Pool's database connection pool (connectionpool) is 1. The basic concepts and principles can be seen from the above analysis, the root cause of the problem is the inefficient management of database connection resources. We know that there is a well-known design pattern for shared resources: resource pool ).

Today I mainly talked about three methods. The working principle of the Connection Pool's database connection pool (connectionpool) is 1. The basic concepts and principles can be seen from the above analysis, the root cause of the problem is the inefficient management of database connection resources. We know that there is a well-known design pattern for shared resources: resource pool ).

Today we mainly talk about three methods:

How database connection pool works
1. Basic Concepts and Principles
From the above analysis, we can see that the root cause of the problem is the inefficient management of database connection resources. We know that,
There is a well-known design pattern for shared resources: Resource Pool ). This mode is designed to solve the problems caused by frequent resource allocation and release. To solve the above problems, you can use the database connection pool technology. The basic idea of the database connection pool is to create a "buffer pool" for the database connection ". A certain number of connections are put in the buffer pool in advance. When you need to establish a database connection, you only need to extract one from the "buffer pool" and put it back after use. We can set the maximum number of connections in the connection pool to prevent endless connections to the database. More importantly, we can monitor the number and usage of database connections through the connection pool management mechanism to provide a basis for system development, testing and performance adjustment.

Basic working principle of Connection Pool
2. Connection Pool provided by the server
The jdbc api does not provide the connection pool method. Some large WEB application servers such as BEA's WebLogic and IBM WebSphere provide connection pool mechanisms, but they must have third-party dedicated class methods to support connection pool usage.

Write a database connection pool:

First, write the database connection pool to implement java and SQL. dataSource interface. The DataSource interface defines two overloaded getConnection () methods. After the DataSource interface is implemented, the constructor creates connections to the database in batches, add the created connection to the listlist object to implement the getConnection method, so that a connection is obtained from the listlist every time this method is called to the user, when you use connection to call the close method, make sure that the Collection object returns itself to the linked list instead of returning the connection to the database. First, let's look at how to get the database connection and store it in the linked list.

Let's take a look at how to get the database connection and store it in the linked list:

Package com. csdn. tool;

Import java. io. IOException;

Import java. io. InputStream;

Import java. io. OutputStream;

Import java. io. PrintWriter;

Import java. lang. reflect. InvocationHandler;

Import java. lang. reflect. Method;

Import java. lang. reflect. Proxy;

Import java. SQL. Connection;

Import java. SQL. DriverManager;

Import java. SQL. SQLException;

Import java. util. Collections list;

Import java. util. Properties;

Import javax. SQL. DataSource;

Public class MyJdbcDemo implements DataSource {

Private static upload list List = new external list ();

Private static String driver;

Private static String url;

Private static String username;

Private static String password;

Static {

InputStream in = MyJdbcDemo. class. getClassLoader (). getResourceAsStream ("db. properties ");

Properties prop = new Properties ();

Try {

Prop. load (in );

Driver = prop. getProperty ("driver ");

Url = prop. getProperty ("url ");

Username = prop. getProperty ("username ");

Password = prop. getProperty ("password ");

// Register the driver

Class. forName (driver );

// Get a batch of Connection sets through DriverManager;

For (int I = 0; I <10; I ++ ){

Connection conn = DriverManager. getConnection (url, username, password );

System. out. println ("added to the pool" + conn );

List. add (conn); // add to set;

}

} Catch (IOException e ){

// TODO Auto-generated catch block

E. printStackTrace ();

} Catch (ClassNotFoundException e ){

// TODO Auto-generated catch block

E. printStackTrace ();

} Catch (SQLException e ){

// TODO Auto-generated catch block

E. printStackTrace ();

}

}

@ Override

Public Connection getConnection () throws SQLException {

If (list. size ()> 0 ){

Final Connection conn = list. removeFirst (); // an implementation of mysql

System. out. println ("User removed from the pool:" + conn );

System. out. println ("pool size:" + list. size ());

Return (Connection) Proxy. newProxyInstance (MyJdbcDemo. class. getClassLoader (), new Class [] {Connection. class}, new InvocationHandler (){

@ Override

Public Object invoke (Object proxy, Method method, Object [] args)

Throws Throwable {

If (! Method. getName (). inclusignorecase ("close ")){

Return method. invoke (conn, args );

}

System. out. println (conn + "canceled ");

List. add (conn );

System. out. println ("pool size:" + list. size ());

Return null;

}

});

} Else {

Throw new RuntimeException ("Please wait ");

}

}

Dbcp method:

DBCP is the open source connection pool implementation under the Apache Software Foundation, using the DBCP data source, the application should add the following two jar files in the system: Commons-dbcp.jar Commons-pool.jar

Tomcat's connection pool is implemented using this connection pool. The database connection pool can be integrated with the application server or used independently by the application.

Write the configuration file first to solve the garbled problem. Change the tool to gbk.

# Connection setting driverClassName = com. mysql. jdbc. Driverurl = jdbc: mysql: // localhost: 3306/javawebusername = rootpassword = root #
 InitialSize = 10 # maximum number of connections maxActive = 50 #
 MaxIdle = 20 #
 MinIdle = 5 #
 MaxWait = 60000 # The format of the connection property attributes attached to the JDBC driver when establishing a connection must be as follows: [property name = property;] # Note: the attributes "user" and "password" are passed explicitly, so they are not included here. ConnectionProperties = useUnicode = true; characterEncoding = gbk # 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, serializabledefadefatransactionisolation = READ_UNCOMMITTED // write class public class DBManager_dbcp {private static DataSource ds; static {try {InputStream in = DBManager. class. getClassLoader (). getResourceAsStream ("dbcpconfig. properties "); Properties prop = new Properties (); prop. load (in); BasicDataSourceFactory factory = new BasicDataSourceFactory (); ds = factory. createDataSource (prop);} catch (Exception e) {e. printStackTrace () ;}} public static Connection getConnection () throws SQLException {return ds. getConnection () ;}} C3p0 configuration file:
 
 
  
   
    
Com. mysql. jdbc. Driver
   
   
    
Jdbc: mysql: /// localhost: 3306/javaweb
   
   
    
Root
   
   
    
Root
   
   
    
5
   
   
    
10
   
   
    
5
   
   
    
20
   
  
  
   
    
Com. mysql. jdbc. Driver
   
   
    
Jdbc: mysql: /// localhost: 3306/javaweb
   
   
    
Root
   
   
    
Root
   
   
    
5
   
   
    
10
   
   
    
5
   
   
    
20
   
  
 File class: package com. csdn. tool; import java. beans. propertyVetoException; import java. SQL. connection; import java. SQL. SQLException; import com. mchange. v2.c3p0. comboPooledDataSource; public class C3p0Demo1 {private static combooleddatasource ds = null; static {ds = new ComboPooledDataSource (); try {ds. setDriverClass ("com. mysql. jdbc. driver "); ds. setJdbcUrl ("jdbc: mysql: // localhost: 3306/javaweb"); ds. setUser ("root"); ds. setPassword ("root"); ds. setInitialPoolSize (10); ds. setMaxPoolSize (40); ds. setMinPoolSize (5);} catch (PropertyVetoException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}} public static Connection getConnection () throws SQLException {return ds. getConnection ();}}