1--依赖
<!--mysql数据库前题条件-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--德鲁伊连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.20</version>
</dependency>
2--连接参数
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://ip:端口/数据库名?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
username: 账号
password: 密码
# 初始连接数
initialSize: 5
# 最小连接池数量
minIdle: 10
# 最大连接池数量
maxActive: 100
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
# 配置一个连接在池中最大生存的时间,单位是毫秒
maxEvictableIdleTimeMillis: 900000
# 配置检测连接是否有效
validationQuery: SELECT 1 FROM DUAL
#申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
testWhileIdle: true
#配置从连接池获取连接时,是否检查连接有效性,true每次都检查;false不检查。做了这个配置会降低性能。
testOnBorrow: false
#配置向连接池归还连接时,是否检查连接有效性,true每次都检查;false不检查。做了这个配置会降低性能。
testOnReturn: false
#打开PsCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,log4j
#合并多个DruidDatasource的监控数据
useGlobalDataSourceStat: true
#通过connectProperties属性来打开mergesql功能罗慢sQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500;
形式二:
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://82.156.13.174:3306/history?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
username=root
password=Xs123456.
# 初始化连接数量
initialSize=10
minIdle=10
maxActive=20
# 最大等待时间
maxWait=3000
附:
批处理
Connection conn = null;
PreparedStatement preparedStatement = null;
try {
conn = DruidUtil.getConnection();
String sql = "insert into ref_role_menu values(?,?)";
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < menuIdList.size(); i++) {
preparedStatement.setLong(1, roleId);
preparedStatement.setLong(2, menuIdList.get(i));
preparedStatement.addBatch();
if (i % 1000 == 0) {
preparedStatement.executeBatch();
preparedStatement.clearBatch();//清空批处理内容
}
}
preparedStatement.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DruidUtil.close(preparedStatement, conn);
}
3--工具类
public class DruidUtil {
private static DataSource dataSource;
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src/main/java/com/util/druid.properties"));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
getConnection();
}
public static Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
/**
* 释放资源
*/
public static void close(Statement stmt, Connection conn) {
close(null, stmt, conn);
}
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();//归还连接
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取连接池方法
*/
public static DataSource getDataSource() {
return dataSource;
}
}