JDBC: database connection pool, jdbc database connection pool

JDBC: database connection pool, jdbc database connection pool

Required tools

Commons-pool2-2.3.jar: dependency library implemented by connection pool

Commons-dbcp2-2.0.1.jar: Implementation of Connection Pool

C3p0-0.9.2.1.jar

If Method 'initializationerror' not found occurs when c3p0 is used, opening the test class, and then adding

Mchange-commons-java-0.2.3.4.jar pack

The JDBC driver ojdbc6.jar used

I. Necessity of JDBC database connection pool

When developing database-based web programs, the traditional mode is basically as follows:
-Create a database connection in the main program (such as servlet and beans.
-SQL operations
-Disconnect the database.
This mode of development has the following problems:
The common JDBC database Connection is obtained using DriverManager. Each time a Connection is established to the database, the Connection must be loaded into the memory, and the user name and password must be verified (the cost is 0.05s ~ 1 s ). When you need to connect to the database, you need to ask for one, and then disconnect after the execution is complete. This method will consume a lot of resources and time. The database connection resources are not well reused. if hundreds or even thousands of people are online at the same time, frequent database connection operations will occupy a lot of system resources, which may even cause server crashes.
Each database connection must be closed after use. Otherwise, if the program fails to be closed due to an exception, the memory in the database system will leak and the database will be restarted.
This kind of development cannot control the number of connection objects created, and system resources will be allocated without consideration. If there are too many connections, it may also cause memory leakage and server crash.

Connection pool)

To solve the problem of database connection in traditional development, the database connection pool technology can be used.
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.
The database connection pool is responsible for allocating, managing, and releasing database connections. It allows applications to reuse an existing database connection instead of reestablishing one.
During database connection pool initialization, a certain number of database connections are created in the connection pool.Minimum number of database connectionsSet. Whether or not these database connections are used, the connection pool will always have at least so many connections. Connection PoolMaximum number of database connectionsLimits the maximum number of connections that the connection pool can occupy. When the number of connections requested by the application to the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue.

Working principle of Database Connection Pool

Two open source database connection pools

The JDBC database connection pool uses javax. SQL. dataSource indicates that DataSource is only an interface, which is usually implemented by the server (Weblogic, WebSphere, Tomcat) and implemented by some open source organizations:
DBCP database connection pool
C3P0 database connection pool
DataSource is usually called a data source. It consists of a connection pool and a connection pool management. DataSource is often called a connection pool.

DBCP Data Source

@ Test // OKpublic void testDBCPWithBasicDataSource () throws Exception {BasicDataSource = null; // create a data source instance dataSource = new BasicDataSource (); // set the basic attribute dataSource. setUsername ("scott"); dataSource. setPassword ("qiaolezi"); dataSource. setUrl ("jdbc: oracle: thin :@ localhost: 1521: orcl"); dataSource. setDriverClassName ("oracle. jdbc. driver. oracleDriver "); // other attributes // sets the initial connection count of the database dataSource. setInitialSize (5); // maximum number of idle connections dataSource. setMaxIdle (8); // maximum number of connections dataSource. setMaxTotal (20); // set the minimum number of idle connections in the database and the minimum number of idle connections saved in the database connection pool. dataSource. setMinIdle (2);/* get the maximum number of waiting milliseconds for the connection (if it is set to BlockWhenExhausted for blocking). If it times out, an exception is thrown. If it is less than zero, the uncertain blocking time is displayed, default Value:-1 */dataSource. setMaxWaitMillis (1000*6); // whether to enable the feature. The default value is truedataSource. setLifo (true); // check the validity when obtaining the connection. The default value is falsedataSource. setTestOnBorrow (false); // check validity when idle. The default value is falsedataSource. setTestWhileIdle (false); // The minimum idle time of the eviction connection. The default value is 1800000 milliseconds (30 minutes) dataSource. setMinEvictableIdleTimeMillis (1800000);/* How long will the object be evicted after it is idle? When the idle time is greater than this value and the maximum number of idle connections is greater than the maximum number of idle connections, it is no longer determined by minevictableidletimemill) */dataSource. setSoftMinEvictableIdleTimeMillis (1800000); // The time interval between evicted scans (in milliseconds). If the value is negative, the eviction thread is not run. The default value is-1dataSource. setTimeBetweenEvictionRunsMillis (-1); Connection conn = dataSource. getConnection (); System. out. println (conn );}

PS: the data source and database connection are different. There is no need to create multiple data sources. It is the factory that generates database connections. Therefore, the entire application only needs one data source.
After the database access is complete, the program closes the database connection as before: conn. close (); but the above Code does not close the physical connection of the database. It only releases the database connection and returns it to the database connection pool.

/*** 1. load the dbcp properties configuration file: Key (attribute from BasicDataSource) * 2. call the createDataSource (properties) method of BasicDataSourceFactory to obtain the DataSource instance * 3. obtain the database connection * @ throws Exception */@ Test // okpublic void testdbcpwithperformancefactory () throws Exception {Properties properties = new Properties (); InputStream inStream = JDBC_Tools.class.getClassLoader () from the DataSource instance (). getResourceAsStream ("dbcp. properties "); properties. load (inStream); DataSource dataSource = BasicDataSourceFactory. createDataSource (properties); System. out. println (dataSource. getConnection (); // test BasicDataSource basicDataSource = (BasicDataSource) dataSource; System. out. println (basicDataSource. getMaxWaitMillis ());}

DBCP. properties

username=scottpassword=qiaolezidriverClassName=oracle.jdbc.driver.OracleDriverurl=jdbc:oracle:thin:@localhost:1521:orclinitialSize=5minIdle=5maxWaitMillis=5000

Note that the key must comply with the javabean

C3P0 Data Source

@Testpublic void testc3p0() throws Exception {ComboPooledDataSource cpds = new ComboPooledDataSource();cpds.setDriverClass( "oracle.jdbc.driver.OracleDriver" );          cpds.setJdbcUrl( "jdbc:oracle:thin:@localhost:1521:orcl" );cpds.setUser("scott");                                  cpds.setPassword("qiaolezi"); System.out.println(cpds.getConnection());}

Use ComboPooledDataSource

/*** 1. create A c3p0-config.xml, refer to the c3p0-0.9.2.1 \ doc index.html help document Appendix A: Configuration Properties * 2. create DataSource dataSource = ComboPooledDataSource ("helloc3p0"); instance * 3. obtain the database connection from the DataSource instance * @ throws Exception */@ Test // OKpublic void testC3P0WithConfig () throws Exception {DataSource dataSource = new ComboPooledDataSource ("helloc3p0 "); // xml <named-config name = "helloc3p0"> System. out. println (dataSource); System. out. println (dataSource. getConnection (); ComboPooledDataSource com = (ComboPooledDataSource) dataSource; System. out. println (com. getMaxStatements ());}

C3p0-config.XML

<C3p0-config> <named-config name = "helloc3p0"> <! -- Specify the basic attributes for connecting to the data source --> <property name = "user"> scott </property> <property name = "passwword"> qiaolezi </property> <property name =" driverClass "> oracle. jdbc. driver. oracleDriver </property> <property name = "jdbcUrl"> jdbc: oracle: thin: @ localhost: 1521: orcl </property> <! -- If the number of connections in the database is insufficient, how many connections are requested from the database server at a time --> <property name = "acquireIncrement"> 50 </property> <! -- Number of connections during database connection pool initialization --> <property name = "initialPoolSize"> 100 </property> <! -- Minimum number of database connections in the database connection pool --> <property name = "minPoolSize"> 50 </property> <! -- Maximum number of database connections in the database connection pool --> <property name = "maxPoolSize"> 1000 </property> <! -- Number of Statement that can be maintained by the database connection pool --> <property name = "maxStatements"> 0 </property> <! -- The number of Statement objects that can be used for each connection --> <property name = "maxStatementsPerConnection"> 5 </property> </named-config> </c3p0-config>

Finally, you can reload the getConnection () method in the JDBC_tools tool class. Later Connection. close (). Instead of closing the Connection, you can return the used database Connection to the database Connection pool.