JAVA数据库连接池
1. 数据库连接池#
1.1. 原理#
数据库操作的构建连接与释放连接浪费资源，所以应该通过运用共享技术实现数据库连接池（享元模式）
- 降低系统中数据库连接 Connection 对象的数量
- 将数据库服务器的连接响应消耗
- 提高 Connection 获取的响应速度
享元模式：经典设计模式的一种，当一个系统中存在大量相同的对象时，由于这类对象的大量使用，会造成系统内存的浪费，可以使用享元模式来减少系统中对象的数量。
1.2. 基本属性#
- 初始数：初始数据库连接的数量
- 最大数：最大数据库连接的数量
- 增量：用完后增加的数量
- 超时时间：
1.3. C3P0 连接池#
import java.sql.Connection;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Factory1 {
<span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> <span class="hljs-type">ComboPooledDataSource</span> <span class="hljs-variable">dataSource</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">init</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> Exception {
dataSource = <span class="hljs-keyword">new</span> <span class="hljs-title class_">ComboPooledDataSource</span>();
dataSource.setDriverClass( <span class="hljs-string">"com.mysql.jdbc.Driver"</span> );
dataSource.setJdbcUrl( <span class="hljs-string">"jdbc:mysql://localhost:3306/test"</span> );
dataSource.setUser(<span class="hljs-string">"root"</span>);
dataSource.setPassword(<span class="hljs-string">"1234567"</span>);
<span class="hljs-comment">// the settings below are optional -- c3p0 can work with defaults</span>
dataSource.setMinPoolSize(<span class="hljs-number">5</span>);<span class="hljs-comment">//最小元素为5个</span>
dataSource.setAcquireIncrement(<span class="hljs-number">5</span>);<span class="hljs-comment">//增量为5个</span>
dataSource.setMaxPoolSize(<span class="hljs-number">20</span>);<span class="hljs-comment">//最大连接池数量为5个</span>
<span class="hljs-comment">// The DataSource dataSource is now a fully configured and usable pooled DataSource</span>
}
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> Connection <span class="hljs-title function_">getConnection</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> Exception {
<span class="hljs-keyword">if</span>(<span class="hljs-literal">null</span> == dataSource)
{
init();
}
<span class="hljs-keyword">return</span> dataSource.getConnection();
}
}
import java.sql.Connection;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Factory2 {
private static ComboPooledDataSource dataSource = null;
public static void init() throws Exception {
dataSource = new ComboPooledDataSource();
//dataSource 自动加载 c3p0-config.xml 文件
// The DataSource dataSource is now a fully configured and usable pooled DataSource
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
<?xml version="1.0" encoding="UTF-8"?>// 名字必须为 c3p0-config.xml
<c3p0-config>
<default-config> <!-- 默认配置 -->
<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">123456</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
</c3p0-config>
1.4. Druid 连接池#
import java.sql.Connection;
import com.alibaba.druid.pool.DruidDataSource;
public class DruidFactory1 {
private static DruidDataSource dataSource = null;
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">init</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> Exception {
dataSource = <span class="hljs-keyword">new</span> <span class="hljs-title class_">DruidDataSource</span>();
dataSource.setDriverClassName(<span class="hljs-string">"com.mysql.jdbc.Driver"</span>);
dataSource.setUsername(<span class="hljs-string">"root"</span>);
dataSource.setPassword(<span class="hljs-string">"123456"</span>);
dataSource.setUrl(<span class="hljs-string">"jdbc:mysql://127.0.0.1:3306/test"</span>);
dataSource.setInitialSize(<span class="hljs-number">5</span>);
dataSource.setMinIdle(<span class="hljs-number">1</span>);
dataSource.setMaxActive(<span class="hljs-number">10</span>);
<span class="hljs-comment">// 启用监控统计功能 dataSource.setFilters("stat");// </span>
}
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> Connection <span class="hljs-title function_">getConnection</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> Exception {
<span class="hljs-keyword">if</span>(<span class="hljs-literal">null</span> == dataSource)
{
init();
}
<span class="hljs-keyword">return</span> dataSource.getConnection();
}
}
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DruidFactory2 {
private static DruidDataSource dataSource = null;
public static void init() throws Exception {
Properties properties = new Properties();
InputStream in = DruidFactory2.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(in);
dataSource = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
in.close();
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
//druid.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
username=root
password=123456
filters=stat
initialSize=2
maxActive=300
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=false
maxPoolPreparedStatementPerConnectionSize=200
1.5. 测试类#
import java.sql.*;
public class SelectTest {
public static void main(String[] args){
<span class="hljs-type">Connection</span> <span class="hljs-variable">conn</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
<span class="hljs-keyword">try</span> {
<span class="hljs-comment">//从c3p0获取</span>
<span class="hljs-comment">//conn = C3p0Factory1.getConnection();</span>
<span class="hljs-comment">//conn = C3p0Factory2.getConnection();</span>
<span class="hljs-comment">//从Druid获取</span>
<span class="hljs-comment">//conn = DruidFactory1.getConnection();</span>
conn = DruidFactory2.getConnection();
<span class="hljs-comment">//构建数据库执行者</span>
<span class="hljs-type">Statement</span> <span class="hljs-variable">stmt</span> <span class="hljs-operator">=</span> conn.createStatement();
System.out.println(<span class="hljs-string">"创建Statement成功！"</span>);
<span class="hljs-comment">//执行SQL语句并返回结果到ResultSet</span>
<span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> stmt.executeQuery(<span class="hljs-string">"select bookid, bookname, price from t_book order by bookid"</span>);
<span class="hljs-comment">//开始遍历ResultSet数据</span>
<span class="hljs-keyword">while</span>(rs.next())
{
System.out.println(rs.getInt(<span class="hljs-number">1</span>) + <span class="hljs-string">","</span> + rs.getString(<span class="hljs-number">2</span>) + <span class="hljs-string">","</span> + rs.getInt(<span class="hljs-string">"price"</span>));
}
rs.close();
stmt.close();
} <span class="hljs-keyword">catch</span> (Exception e){
e.printStackTrace();
} <span class="hljs-keyword">finally</span> {
<span class="hljs-keyword">try</span> {
<span class="hljs-keyword">if</span>(<span class="hljs-literal">null</span> != conn) {
conn.close();
}
} <span class="hljs-keyword">catch</span> (SQLException e){
e.printStackTrace();
}
}
}
}