MySql database connection pool and mysql database connection

MySql database connection pool and mysql database connection

1.Traditional links(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:

<C3p0-config> <default-config> <! -- The number of connections that c3p0 obtains at the same time when connections in the connection pool are exhausted. Default: 3 --> <property name = "acquireIncrement"> 3 </property> <! -- Defines the number of repeated attempts after a new connection fails to be obtained from the database. Default: 30 --> <property name = "acquireRetryAttempts"> 30 </property> <! -- Interval between two connections, in milliseconds. Default: 1000 --> <property name = "acquireRetryDelay"> 1000 </property> <! -- When the connection is closed, all uncommitted operations are rolled back by default. Default: false --> <property name = "autoCommitOnClose"> false </property> <! -- C3p0 creates an empty table named Test and uses its own query statement for testing. If this parameter is defined, preferredTestQuery is ignored. You cannot perform any operation on this Test table. It will only be used for c3p0 testing. Default: null --> <property name = "automaticTestTable"> Test </property> <! -- Failed to obtain the connection will cause all threads waiting for the connection pool to obtain the connection to throw an exception. However, the data source is still valid, and the next call to getConnection () will continue to get the connection. If it is set to true, the data source will be declared disconnected and permanently closed after the connection fails to be obtained. Default: false --> <property name = "breakAfterAcquireFailure"> false </property> <! -- When the connection pool is used up, the client calls getConnection () and waits for the time to obtain the new connection. After the timeout, SQLException will be thrown. If it is set to 0, the client waits indefinitely. Unit: milliseconds. Default: 0 --> <property name = "checkoutTimeout"> 100 </property> <! -- Test the connection by implementing the ConnectionTester or QueryConnectionTester class. The full path must be specified for the class name. Default: com. mchange. v2.c3p0. impl. DefaultConnectionTester --> <property name = "connectionTesterClassName"> </property> <! -- Specify the path of c3p0 libraries. If (usually like this) can be obtained locally, you do not need to set it. The Default value is null and can be Default: null --> <property name = "factoryClassLocation"> null </property> <! -- Stronugly disrecommended. setting this to true may lead to subtle andbizarre bugs. <property name = "forceIgnoreUnresolvedTransactions"> false </property> <! -- Check all idle connections in the connection pool every 60 seconds. Default: 0 --> <property name = "idleConnectionTestPeriod"> 60 </property> <! -- Three connections are obtained during initialization. The value must be between minPoolSize and maxPoolSize. Default: 3 --> <property name = "initialPoolSize"> 3 </property> <! -- Maximum idle time. connections are dropped if they are not used within 60 seconds. If it is 0, it will never be discarded. Default: 0 --> <property name = "maxIdleTime"> 60 </property> <! -- The maximum number of connections retained in the connection pool. Default: 15 --> <property name = "maxPoolSize"> 15 </property> <! -- JDBC standard parameter, used to control the number of PreparedStatements loaded in the data source. However, the pre-cached statements belong to a single connection rather than the entire connection pool. Therefore, you need to consider many factors when setting this parameter. If both maxStatements and maxStatementsPerConnection are 0, the cache is disabled. Default: 0 --> <property name = "maxStatements"> 100 </property> <! -- MaxStatementsPerConnection defines the maximum number of statements cached for a single connection in the connection pool. Default: 0 --> <property name = "maxStatementsPerConnection"> </property> <! -- C3p0 is asynchronous, and slow JDBC operations are completed by helping the process. These operations can effectively improve performance. Multiple operations can be executed simultaneously through multiple threads. Default: 3 --> <property name = "numHelperThreads"> 3 </property> <! -- When the user calls getConnection (), the root user becomes the user who gets the connection. It is mainly used when the connection pool is connected to a data source other than c3p0. Default: null --> <property name = "overrideDefaultUser"> root </property> <! -- This parameter corresponds to the overrideDefaultUser parameter. Default: null --> <property name = "overrideDefaultPassword"> password </property> <! -- Password. Default: null --> <property name = "password"> </property> <! -- Define the test statement executed for all connection tests. This significantly increases the testing speed when connection tests are used. Note: The test table must exist at the initial data source. Default: null --> <property name = "preferredTestQuery"> select id from test where id = 1 </property> <! -- The user can wait up to 300 seconds before modifying system configuration parameters. Default: 300 --> <property name = "propertyCycle"> 300 </property> <! -- Because of high performance consumption, use it only when needed. If it is set to true, the validity of each connection is verified when it is submitted. We recommend that you use idleConnectionTestPeriod or automaticTestTable to improve the connection test performance. Default: false --> <property name = "testconnectioncheckout"> false </property> <! -- If it is set to true, the connection validity will be verified when the connection is obtained. Default: false --> <property name = "testConnectionOnCheckin"> true </property> <! -- User name. Default: null --> <property name = "user"> root </property> <! -- Early versions of c3p0 used dynamic reflection proxy for JDBC interfaces. This parameter allows the user to recover to the dynamic reflection proxy for a wide range of purposes in earlier versions to solve unstable faults. The latest non-reflection proxy is faster and has been widely used, so this parameter may not be useful. Currently, the original dynamic reflection and new non-reflection proxy are supported at the same time. However, in future versions, it may not support dynamic reflection proxy. Default: false. --> <Property name = "usesTraditionalReflectiveProxies"> false </property> <property name = "automaticTestTable"> con_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"> 25 </property> <property name = "minPoolSize"> 10 </property> <property name = "maxStatements"> 0 </property> <user-overrides user = "swaldman"> </user-overrides> </default-config> <named-config name = "dumbTestConfig"> <property name = "maxStatements"> 200 </property> <user-overrides user = "poop"> <property name = "maxStatements"> 300 </property> </user-overrides> </named-config> <c3p0-config>

Practical exercises:
1>. First configure the c3p0-config.xml File

<?xml version="1.0" encoding="UTF-8"?><c3p0-config><default-config><property name="driverClass">com.mysql.jdbc.Driver</property><property name="user">root</property><property name="password">wwh</property><property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/jdbctest</property></default-config></c3p0-config>

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

Mysql database connection pool

Change the url to url = "jdbc: mysql: // localhost: 3306/mydb? UseUnicode = true & characterEncoding = GB2312"

How to Create a mysql database connection pool

Directly use its connection function mysql_connect
Can be connected.