1、添加依赖:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.2</version>
</dependency>
2、配置application.yml,重点数据库的url,开启批量插入属性配置:rewriteBatchedStatements=true
注:MySql的JDBC连接的url中要加rewriteBatchedStatements参数,并保证5.1.13以上版本的驱动,才能实现性能的批量插入。
spring:
datasource:
# 主数据源
shop:
url: jdbc:mysql://61.162.219.30:3309/touzi?rewriteBatchedStatements=true&autoReconnect=true&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false
username: root
password: 1678697149
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
# 连接池设置
druid:
initial-size: 5
min-idle: 5
max-active: 20
# 配置获取连接等待超时的时间
max-wait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
time-between-eviction-runs-millis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
min-evictable-idle-time-millis: 300000
# Oracle请使用select 1 from dual
validation-query: SELECT 'x'
test-while-idle: true
test-on-borrow: false
test-on-return: false
# 打开PSCache,并且指定每个连接上PSCache的大小
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
use-global-data-source-stat: true
2、可直接在service层使用:
import org.springframework.transaction.annotation.Transactional;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
/**
* @description 数据权限管理的业务类
*/
@Service
public class DatapermissionService{
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 根据userid添加,数据权限表中数据,JDBC插入
* @param userId 如:1
* @param split 如:[1,2,3,4]
*/
@Transactional
public int userPower(int userid, String[] split) {
//如果split为空,则不添加
if(split[0] == "" || split[0] == null){
return 0;
}
jdbc(Arrays.asList(split),userid);
return 1;
}
public void jdbc(List<String> list,int userid){
int[] updatedCountArray=jdbcTemplate.batchUpdate("INSERT INTO t_datapermission (dataperid,userid) VALUES (?,?);", new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
preparedStatement.setString(1,list.get(i));
preparedStatement.setString(2,userid+"");
}
@Override
public int getBatchSize() {
return list.size();
}
});
}
}
3、在Druid中的表现:插入数据3779条,用时145ms(0.1秒)。