The database connection pool is generally indispensable to the development of actual projects. Here are two most commonly used open source database connection pool components: DBCP component and C3P0 component.
1.Why use database connection pooling
The known method is to make a database connection once when the database needs to be accessed, and then release the connection after the database operation is complete. Often the business has obvious drawbacks:
Users need to get a link to the database for each request, and creating a connection to the database usually takes relatively large resources and takes a long time.Assuming a site has 100,000 visits a day, the database server needs to create 100,000 connections, which wastes a lot of database resources, and can easily cause database server memory overflow and extension.
Database connection is a key, limited and expensive resource, especially in multi-user web applications. The management of database connection can significantly affect the scalability and robustness of the entire application and the performance indicators of the program. Database connection pools have been formally raised to address this issue.The database connection pool is responsible for allocating, managing, and releasing database connections, allowing applications to reuse an existing database connection instead of re-establishing it.
The database connection pool initially creates a certain number of database connections into the connection pool, which is set by the minimum number of database connections. Whether these database connections are used or not, the connection pool will always guarantee at least that many connections. The maximum database for the connection poolThe number of connections limits the maximum number of connections that this pool can hold. When applications request more connections to the pool than the maximum number of connections, these requests are queued.
On the web servers in java, DataSoruce is provided, that is, connection pool implementation. sun company stipulates that if you write your own connection pool, you implement the javax.sql.DataSource interface
The most frequently used open source database connection pools are:
- DBCP database connection pool (tomcat)
- C3P0 database connection pool (hibernate)
There is no need to write connection database code in practical application, and the connection to the database is obtained directly from the data source.Programmers should also use these techniques whenever possible to improve their database access performance
2.DBCP configuration and use
1. Import related jar packages
- commons-dbcp.jar: implementation of connection pool
- commons-pool.jar: Dependent library for connection pool implementation
Note: Tomcat's connection pool is implemented using this connection pool, which can be used in combination with the application server or independently by the application using the second configuration.
It is important to note that the key in the configuration file is consistent with the properties in BaseDataSouce.(Remove set and lower case first letter)
(2) There are two methods of use, one is hard-coded, which is not recommended because of poor maintenance. The second method is recommended.
The second configuration implementation requires attention to the fact that the key in the configuration file is consistent with the properties in BaseDataSouce.(Remove set and lower case first letter)
Code example:
package dao;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
* Test how DBCP data sources are used
* Created by cenyu on 16-12-18.
*/
public class testDBCP {
//1. Hard-coded connection pooling
@Test
public void test1() throws SQLException {
//DBCP Connection Pool Core Class
BasicDataSource dataSource = new BasicDataSource();
//Connection pool parameter configuration: initialization parameters, maximum number of connections/connection string, driver, username, password, etc.
dataSource.setUrl("jdbc:mysql://localhost:3306/db");
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUsername("root");
dataSource.setPassword("root");
dataSource.setInitialSize(3);//Number of Initial Connections
dataSource.setMaxActive(6);//Maximum number of connections
dataSource.setMaxIdle(3000);//Maximum idle time, beyond which it will be released
//Get Connections
Connection con = dataSource.getConnection();
con.prepareStatement("DELETE FROM Admin WHERE id =2").executeUpdate();
//Close
con.close();
}
//2. [Recommended] Configuration implements connection pooling for easy maintenance
@Test
public void test2() throws Exception {
//Load prop profile
Properties prop = new Properties();
//Get File Stream
InputStream inputStream=testDBCP.class.getClassLoader().getResourceAsStream("db.properties");
//Load Property Profile
prop.load(inputStream);
//Create data source objects directly according to prop configuration
DataSource dataSource = BasicDataSourceFactory.createDataSource(prop);
//Get Connections
Connection con = dataSource.getConnection();
con.prepareStatement("DELETE FROM Admin WHERE id =4").executeUpdate();
//Close
con.close();
}
}DBCP Profiledb.properties
#connections setting up driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/day14 username=root password=abc #<!--Initialize connection--> initialSize=10 #Maximum number of connections maxActive=50 #<!--Maximum idle connection--> maxIdle=20 #<!--Minimum idle connection--> minIdle=5 #<!--Timeout wait time in milliseconds 60000ms/1000 equals 60 seconds--> maxWait=60000 #The JDBC driver must format the connection property attributes attached to the connection as follows: [Property name=property;] #Note that the attributes "user" and "password" are explicitly passed, so they do not need to be included here. connectionProperties=useUnicode=true;characterEncoding=utf8 #Specifies the auto-commit state of the connection created by the connection pool. defaultAutoCommit=true #driver default specifies the read-only state 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 created by the connection pool. #The available values are one of the following: (javadoc is available for details.)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=REPEATABLE_READ
http://www.cnblogs.com/cenyu/p/6195467.html
3.C3P0 Configuration and Use
C3P0 Connection Pool Component is the most common connection pool technology, and Spring Framework supports C3P0 Connection Pool technology by default.
Core Class of C3P0 Connection Pool:
CombopooledDataSource ;
1. Introducing jar files:
c3p0-0.9.1.2.jar
(2) Create connections using connection pools
- a. Hard coding methods
- b. Configuration (xml configuration)
- c. Configuration (properties configuration) Reference http://blog.csdn.net/u012050416/article/details/50738892
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.ConcurrentNavigableMap;
/**
* Two Methods of Using C3P0 Connection Pool Technology
* Created by cenyu on 16-12-18.
*/
public class testC3P0 {
//1. Hard-coded, use c3p0 connection pool to manage connections
@Test
public void testCode() throws Exception {
//Create Connection Pool Core Tool Class
ComboPooledDataSource dataSource=new ComboPooledDataSource();
//Set connection parameters: url, driver, user password, initial number of connections, maximum number of connections
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/db");
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setUser("root");
dataSource.setPassword("root");
dataSource.setInitialPoolSize(3);
dataSource.setMaxPoolSize(6);
dataSource.setMaxIdleTime(1000);
//Get the connection object from the connection pool object
Connection conn = dataSource.getConnection();
//Execution and New
conn.prepareStatement("DELETE FROM Admin WHERE id=1").executeUpdate();
//Close
conn.close();
}
//2.xml configuration, use c3p0 connection pool to manage connections
@Test
public void testXML() throws Exception{
//Create C3P0 Connection Core Tool Class
//Automatically load the configuration file for c3p0 under src [c3p0-config.xml]
ComboPooledDataSource dataSource = new ComboPooledDataSource("mysql");//Configuration using mysql
//Get Connections
Connection conn = dataSource.getConnection();
//Execute Connection
conn.prepareStatement("delete from Admin where id=3").executeUpdate();
//Close
conn.close();
}
}
The c3p0-config.xml configuration file is modified and placed in the root directory of the project src. After compiling, it will be compiled into the WEB-INF/classes
Common configuration files are as follows:
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- This is default config! -->
<default-config>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
<!-- This is my config for mysql-->
<named-config name="mysql">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password"></property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</named-config>
<!-- This is my config for oracle -->
<named-config name="oracle">
<property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
<property name="jdbcUrl">jdbc:oracle:thin:@localhost:1521:orcl</property>
<property name="user">scott</property>
<property name="password">liang</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</named-config>
</c3p0-config> Reference: http://blog.csdn.net/u012050416/article/details/50738892
http://www.cnblogs.com/cenyu/p/6195467.html
http://www.cnblogs.com/wang-meng/p/5463020.html