MySql database connection pool _ MySQL

1. traditional links (as shown below): (1) the number of connections required to connect DriverManager in traditional methods is limited. (2) The traditional method of close () does not reuse the Connection, but only cut off the bridge between the application and the database, that is, no SQL command is sent to the database to execute 1. traditional link (as shown below)

Note:

(1). The traditional method of finding DriverManager to connect is limited.

(2). The traditional method of close () does not reuse the Connection, but only cut off the bridge between the application and the database, that is, no SQL command is sent to the database for execution.

(3) in the project, the Connection pool is used instead of DriverManager.

2. use the connection pool ()

3. open source database connection pool

(1) currently many Web servers (Weblogic, WebSphere, and Tomcat) provide DataSoruce implementation, that is, connection pool implementation. Generally, the implementation of DataSource is called a data source in English. The data source includes the implementation of the database connection pool.
(2) some open source organizations also provide independent implementation of data sources:
DBCP database connection pool (tomcat)
C3P0 database connection pool (hibernate)
(3). in actual applications, you do not need to write database connection code to directly obtain the database connection from the data source. Programmers should also try to use these data sources to improve the database access performance of the program.

4. Use of C3P0:
(1). configure the xml file in the classpath path, that is, under the src file and the file name must be a c3p0-config.xml

File parameters of the original c3p0-config.xml:

 
  
   
   
    
3
   
   
   
    
30
   
   
   
    
1000
   
   
   
    
False
   
   
   
    
Test
   
   
   
    
False
   
   
   
    
100
   
   
   
   
   
    
Null
   
   
   
    
False
   
   
   
    
60
   
   
   
    
3
   
   
   
    
60
   
   
   
    
15
   
   
   
    
100
   
   
   
   
   
    
3
   
   
   
    
Root
   
   
   
    
Password
   
   
   
   
   
    
Select id from test where id = 1
   
   
   
    
300
   
   
   
    
False
   
   
   
    
True
   
   
   
    
Root
   
   
   
    
False
   
   
    
Con_test
   
   
    
30000
   
   
    
30
   
   
    
10
   
   
    
30
   
   
    
25
   
   
    
10
   
   
    
0
   
   
  
  
   
    
200
   
   
    
     
300
    
   
  
 

Practical exercises:
1>. first configure the c3p0-config.xml file

 
 
  
   
    com.mysql.jdbc.Driver
   
   
    root
   
   
    wwh
   
   
    jdbc:mysql://127.0.0.1:3306/jdbctest
   
  
 

2>. code operation connection (TestC3P0. java)

Package cn. wwh. www. java. jdbc. datasource; import java. SQL. connection; import com. mchange. v2.c3p0. the role of the ComboPooledDataSource;/*** class: ***** @ author Yiye banzhou * @ version 1.0 * @ creation time: 12:02:13 * // test the usage of C3P0 in the connection pool. public class TestC3P0 {public static void main (String [] args) throws Exception {long begin = System. currentTimeMillis (); // Create a C3P0 connection pool and load the c3p0-config.xml file combooleddatasource = new ComboPo OledDataSource (); for (int I = 1; I <= 1000000; I ++) {Connection conn = dataSource. getConnection (); if (conn! = Null) {System. out. println ("obtain the connection number" + I + ""); conn. close () ;}long end = System. currentTimeMillis (); System. out. println ("used" + (end-begin)/1000 + "second ");}}

5. Use of DBCP:
(1). use the class loading method. the file name is dbcp. properties.

driverClassName=com.mysql.jdbc.Driverurl=jdbc:mysql://127.0.0.1:3306/jdbctestusername=rootpassword=wwh

(2). Use in code (TestDBCP. java)

Package cn. wwh. www. java. jdbc. datasource; import java. io. inputStream; import java. SQL. connection; import java. util. properties; import javax. SQL. dataSource; import org. apache. commons. dbcp. basicDataSourceFactory;/*** class: uses the DBCP connection pool to obtain the database link, and test his time *** @ author Yiye Binzhou * @ version 1.0 * @ creation time: 09:27:59 * // test the usage of DBCP in the connection pool public class TestDBCP {public static void main (String [] args) throws Exception {l Ong begin = System. currentTimeMillis (); // load the attribute file InputStream is = TestDBCP. class. getClassLoader (). getResourceAsStream ("cn/wwh/www/java/jdbc/config/dbcp. properties "); Properties props = new Properties (); props. load (is); // create DBCP connection pool factory BasicDataSourceFactory = new BasicDataSourceFactory (); // create a data source, that is, the connection pool DataSource ds = factory. createDataSource (props); for (int I = 1; I <= 1000000; I ++) {// Obtain an idle connection object Conn from the connection pool Ection conn = ds. getConnection (); if (conn! = Null) {System. out. println ("Get connection number" + I + ");} // return the connection object to the connection pool conn. close ();} long end = System. currentTimeMillis (); System. out. println ("used" + (end-begin)/1000 + "second ");}}

The first test time is 18 seconds, and the second test time is 13 seconds. The performance of different machines is different, and the test time is neither fast nor fast. However, in theory, the speed of C3P0 should be faster, but the data I tested is the opposite, which is strange.

Summary:

1> DBCP and C3P0 are both open-source Java and must directly or indirectly implement the javax. SQL. DataSource interface.
2> The DBCP connection pool requires the dbcp. properties file and three corresponding jar packages must be added.
3> C3P0 connection pool needs to store WEB-INF files in/c3p0-config.xml/classes/directory, this class ComboPooledDataSource at creation
The system automatically finds the xml file in the specified directory and loads the default settings.

4> tomcat uses c3p0.

6. imported jar packages

Commons-dbcp.jar: DBCP implementation jar to import

Commons-pool.jar: dependency class implemented by connection pool

Commons-collections.jar: collection class implemented by connection pool

C3p0-0.9.1.2.jar: C3P0 implement jar package to import