JDBC p5 数据库连接池 - ToB企服应用市场:ToB评测及商务社交产业平台

package com.hspedu.jdbc.datasource;
import com.hspedu.jdbc.utils.JDBCUtils;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
public class ConQuestion {
//代码连接mysql 5000次
@Test
public void testCon(){
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++){
//使用传统jdbc连接方式
Connection connection = JDBCUtils.getConnection();
//可能会抛出too many connections的异常
//再看看，如果得到连接就立即关闭，总共会耗时多久
JDBCUtils.close(null, null, connection);
}
long end = System.currentTimeMillis();
System.out.println("耗时 = " + (end - start));//耗时 = 6598
}
}

复制代码

package com.hspedu.jdbc.datasource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.jupiter.api.Test;
import java.beans.PropertyVetoException;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class C3P0_ {
//方式1：相关参数，在程序中指定user, url, password
@Test
public void testC3P0_01() throws IOException, PropertyVetoException, SQLException {
//1. 创建一个数据源对象
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
//2. 通过配置文件获取相关连接信息
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
//给数据源（数据源负责连接，连接池） comboPooledDataSource 设置参数
comboPooledDataSource.setDriverClass(driver);
comboPooledDataSource.setJdbcUrl(url);
comboPooledDataSource.setUser(user);
comboPooledDataSource.setPassword(password);
//设置初始化连接数
comboPooledDataSource.setInitialPoolSize(10);
//最大连接数
comboPooledDataSource.setMaxPoolSize(50);
//获取连接
//测试连接池的效率，连接5000次
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection();//这个方法就是从DateSource接口实现的
// System.out.println("连接OK");
//关闭
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("c3p0 5000次连接mysql 耗时 = " + (end - start));//c3p0 5000次连接mysql 耗时 = 280，之前传统的耗时 = 6598
}
//第二种方式，使用配置文件的模板来完成
//1. 将c3p0 提供的 c3p0.config.xml 拷贝到src目录
//2. 该文件指定了连接数据库和连接池的相关参数
@Test
public void testC3P0_02() throws SQLException {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hsp_edu");//填入数据源名称
long start = System.currentTimeMillis();
for (int i = 0; i < 500000; i++) {
Connection connection = comboPooledDataSource.getConnection();//这个方法就是从DateSource接口实现的
// System.out.println("连接OK");
//关闭
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("c3p0 第二种方式500000次连接mysql 耗时 = " + (end - start));//c3p0 第二种方式500000次连接mysql 耗时 = 1452
}
}

复制代码

<c3p0-config>
<named-config name="hsp_edu">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc_learning</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxStatementPerConnection">2</property>
</named-config>
</c3p0-config>

复制代码

package com.hspedu.jdbc.datasource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.jupiter.api.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;
public class Druid_ {
@Test
public void testDruid() throws Exception {
//1. 加入 Druid jar包
//2. 加入配置文件 druid.properties，将该文件拷贝到项目的src目录
//3. 创建 Properties 对象
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
//4. 创建一个指定参数的数据库连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
long start = System.currentTimeMillis();
for (int i = 0; i < 500000; i++) {
Connection connection = dataSource.getConnection();
// System.out.println("连接成功");
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("druid连接池，连接500000次耗时 = " + (end - start));//druid连接池，连接500000次耗时 = 549
}
}

复制代码

#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_learning?rewriteBatchedStatements=true
username=root
password=root
#initial connection size
initialSize=10
#min idle connection size
minIdle=5
#max active connection size
maxActive=50
#max wait time (5000 mil seconds)
maxWait=5000

复制代码