mysql版本: 8.0.11
java版本: 8
maven版本: 3.6.3
mave依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.6.0</version>
</dependency>
java代码
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.springframework.core.io.ClassPathResource;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.*;
@AllArgsConstructor
@NoArgsConstructor
@Data
@Slf4j
public class DbcpDataSourceService {
private BasicDataSource dataSource;
private String propertiesFile;
private Properties properties;
private String describe;
public DbcpDataSourceService(String propertiesFile) {
this.propertiesFile = propertiesFile;
initDataSource();
}
public void initDataSource() {
try {
long currentTimeMillis = System.currentTimeMillis();
//获取配置文件,转换成流
InputStream in = null;
try {
// 开发环境
URL url = DbcpDataSourceService.class.getClassLoader().getResource(propertiesFile);
File file = new File(url.getFile());
in = new FileInputStream(file);
} catch (Exception e) {
}
if (in == null) {
// 达成jar包部署之后
ClassPathResource classPathResource = new ClassPathResource(propertiesFile);
in = classPathResource.getInputStream();
}
//创建properties对象
properties = new Properties();
//加载流
properties.load(new InputStreamReader(in, StandardCharsets.UTF_8));
//创建dataSource对象
dataSource = BasicDataSourceFactory.createDataSource(properties);
describe = new String(this.properties.getProperty("describe").getBytes(), StandardCharsets.UTF_8);
log.info("【{}】数据库连接池 初始化成功, 耗时:【{}】毫秒", describe, System.currentTimeMillis() - currentTimeMillis);
} catch (Exception e) {
e.printStackTrace();
}
}
public void execute(String sql) {
if (dataSource == null) {
log.warn("数据库连接池实例为空");
return;
}
try (Connection conn = dataSource.getConnection();
Statement statement = conn.createStatement();) {
long startTime = System.currentTimeMillis();
Class.forName(this.properties.getProperty("driverClassName"));
statement.execute(sql);
log.info("执行sql成功,sql:【{}】, 运行时长:【{}】毫秒", sql, System.currentTimeMillis() - startTime);
} catch (Exception e) {
log.error("sql执行失败:【{}】", sql, e);
}
}
public List<List<String>> query(String sql) {
return query(sql, Boolean.FALSE);
}
public List<List<String>> query(String sql, Boolean returnColumnName) {
if (dataSource == null) {
log.warn("数据库连接池实例为空");
return null;
}
long startTime = System.currentTimeMillis();
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();) {
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int column = resultSetMetaData.getColumnCount();
List<List<String>> result = new LinkedList<>();
if (returnColumnName) {
List<String> title = new ArrayList<>();
for (int i = 1; i <= column; i++) {
title.add(resultSetMetaData.getColumnName(i));
}
result.add(title);
}
while (rs.next()) {
List<String> list = new LinkedList<>();
for (int i = 1; i <= column; i++) {
list.add(rs.getString(i));
}
result.add(list);
}
log.info("查询sql:【{}】, 运行时长:【{}】毫秒", sql, System.currentTimeMillis() - startTime);
return result;
} catch (Exception e) {
log.error("sql查询失败:【{}】", sql, e);
return null;
}
}
public void print(String sql) {
print(query(sql, Boolean.TRUE));
}
public void print(List<List<String>> res) {
Map<Integer, Integer> columnMaxLength = new HashMap<>();
for (int i = 0; i < res.size(); i++) {
List<String> strings = res.get(i);
for (int k = 0; k < strings.size(); k++) {
if (!columnMaxLength.containsKey(k)) {
columnMaxLength.put(k, 0);
}
if (strings.get(k) == null) {
continue;
}
int length = strings.get(k).getBytes().length;
if (length > columnMaxLength.get(k)) {
columnMaxLength.put(k, length);
}
}
}
for (int i = 0; i < res.size(); i++) {
List<String> strings = res.get(i);
for (int k = 0; k < strings.size(); k++) {
System.out.format("%-" + (columnMaxLength.get(k) + 5) + "s", strings.get(k));
}
System.out.println();
}
}
@Override
protected void finalize() throws Throwable {
close();
super.finalize();
}
public void close() {
try {
dataSource.close();
log.info("【{}】连接池执行关闭", describe);
} catch (Exception e) {
}
}
}
使用
DbcpDataSourceService ddss = new DbcpDataSourceService("test.properties");
ddss.print("show databases");
ddss.print("show tables");
ddss.close();
test.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3360/test?useUnicode=true&useSSL=false&characterEncoding=utf-8&serverTimezone=GMT%2b8&allowMultiQueries=true&allowPublicKeyRetrieval=true
username=root
password=123456
initialSize=1
maxTotal=3
maxIdle=2
minIdle=1
maxWaitMillis=1000
testOnBorrow=true
validationQuery=select 1
describe=测试
说明
initialSize
初始化连接,连接池启动时创建的初始化连接数量(默认值为0)
maxActive
最大活动连接,连接池中可同时连接的最大的连接数(默认值为8)
minIdle
最小空闲连接,连接池中最小的空闲的连接数,低于这个数量会被创建新的连接(默认为0,该参数越接近maxIdle,性能越好,因为连接的创建和销毁,都是需要消耗资源的;但是不能太大,因为在机器很空闲的时候,也会创建低于minidle个数的连接,类似于jvm参数中的Xmn设置)
maxIdle
最大空闲连接,连接池中最大的空闲的连接数,超过的空闲连接将被释放,如果设置为负数表示不限制(默认为8个,maxIdle不能设置太小,因为假如在高负载的情况下,连接的打开时间比关闭的时间快,会引起连接池中idle的个数上升超过maxIdle,而造成频繁的连接销毁和创建,类似于jvm参数中的Xmx设置)
maxWait
从池中取连接的最大等待时间,单位ms.当没有可用连接时,连接池等待连接释放的最大时间,超过该时间限制会抛出异常,如果设置-1表示无限等待(默认为无限)
validationQuery
验证使用的SQL语句
testWhileIdle
指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除.
testOnBorrow
借出连接时不要测试,否则很影响性能。一定要配置,因为它的默认值是true。false表示每次从连接池中取出连接时,不需要执行validationQuery = "SELECT 1" 中的SQL进行测试。若配置为true,对性能有非常大的影响,性能会下降7-10倍。
timeBetweenEvictionRunsMillis
每30秒运行一次空闲连接回收器,配置timeBetweenEvictionRunsMillis = "30000"后,每30秒运行一次空闲连接回收器(独立线程)。并每次检查3个连接,如果连接空闲时间超过30分钟就销毁。销毁连接后,连接数量就少了,如果小于minIdle数量,就新建连接,维护数量不少于minIdle,过行了新老更替。
minEvictableIdleTimeMillis
池中的连接空闲30分钟后被回收
numTestsPerEvictionRun
在每次空闲连接回收器线程(如果有)运行时检查的连接数量
removeAbandoned
连接泄漏回收参数,当可用连接数少于3个时才执行
removeAbandonedTimeout
连接泄漏回收参数,180秒,泄露的连接可以被删除的超时值