一、背景:
SpringDataJPA的API是没有高效批量操作的,它的saveAll底层是循环操作并且先读取数据库检查数据是否存在再去插入数据,性能效率很低。 写过了好多增删改查的代码,功能也实现了很多,也是因为没有遇到大批量的MySQL数据库操作业务,就没有去找高效的数据库操作方法,之前的代码都是循环操作数据库。 系统版本升级在今天差不多已经完结,迁移完数据库后,就顺便抽了时间找找怎么样能在SpringBoot整合的SpringDataJPA项目中高效的批量操作数据库,以便后期优化之前的代码,接下来言归正。 SpringDataJPA实战项目很好用,在第六标题有个人观点。
二、BatchService.java
service实现层通过@PersistenceContext注解注入EntityManager接口。 批量写入调用persist方法(参数为实体对象),再调用flush刷新到数据库,后clear。 更新数据调用merge(参数为实体对象),同样调用flush刷新到数据库,后clear。
@Transactional
@Service
public class BatchService {
@PersistenceContext
private EntityManager entityManager;
public < T > void batchInsert ( List < T > list) {
if ( ! ObjectUtils . isEmpty ( list) ) {
for ( int i = 0 ; i < list. size ( ) ; i++ ) {
entityManager. persist ( list. get ( i) ) ;
if ( i % 50 == 0 ) {
entityManager. flush ( ) ;
entityManager. clear ( ) ;
}
}
entityManager. flush ( ) ;
entityManager. clear ( ) ;
}
}
public < T > void batchUpdate ( List < T > list) {
if ( ! ObjectUtils . isEmpty ( list) ) {
for ( int i = 0 ; i < list. size ( ) ; i++ ) {
entityManager. merge ( list. get ( i) ) ;
if ( i % 50 == 0 ) {
entityManager. flush ( ) ;
entityManager. clear ( ) ;
}
}
entityManager. flush ( ) ;
entityManager. clear ( ) ;
}
}
}
三、测试
@Slf4j
@RunWith ( SpringRunner . class )
@SpringBootTest
public class DatabaseTest {
@Value ( "${indexCode}" )
private String indexCode;
@Resource
private StockRepository stockRepository;
@Resource
private BatchService batchService;
@Test
public void forTest ( ) {
List < String > indexCodeArr = Arrays . asList ( indexCode. split ( "," ) ) ;
Set < String > indexCodeSet = new HashSet < > ( indexCodeArr) ;
log. info ( "indexCodeSet:{}" , JSONObject . toJSONString ( indexCodeSet) ) ;
log. info ( "indexCodeSe.size():{}" , indexCodeSet. size ( ) ) ;
long timeIdStart = System . currentTimeMillis ( ) ;
String time = TimeUtil . FORMAT. get ( ) . format ( timeIdStart) ;
int record = 0 ;
for ( String indexCode : indexCodeSet) {
record += stockRepository. updateIndexCalculated ( indexCode, time) ;
}
log. info ( "record:{}" , record ) ;
log. info ( "运行时间,time:{}秒" , ( System . currentTimeMillis ( ) - timeIdStart) / 1000.0 ) ;
}
@Test
public void batchTest ( ) {
List < String > indexCodeArr = Arrays . asList ( indexCode. split ( "," ) ) ;
Set < String > indexCodeSet = new HashSet < > ( indexCodeArr) ;
log. info ( "indexCodeSet:{}" , JSONObject . toJSONString ( indexCodeSet) ) ;
log. info ( "indexCodeSe.size():{}" , indexCodeSet. size ( ) ) ;
long timeIdStart = System . currentTimeMillis ( ) ;
String time = TimeUtil . FORMAT. get ( ) . format ( timeIdStart) ;
List < IndexCalculated > list = new ArrayList < > ( ) ;
for ( String indexCode : indexCodeSet) {
IndexCalculated indexCalculated = new IndexCalculated ( ) ;
indexCalculated. setIndexCode ( indexCode) ;
indexCalculated. setUpdateTime ( time) ;
list. add ( indexCalculated) ;
}
batchService. batchInsert ( list) ;
log. info ( "运行时间,time:{}秒" , ( System . currentTimeMillis ( ) - timeIdStart) / 1000.0 ) ;
}
}
四、测试结果
循环写入74条记录用时12.342秒。 批量写入74条记录用时2.139秒。 体验感受:循环写入方式操作几十条数据需要十多秒,而通过EntityManager批量操作则可以将时间减少到两秒,简直不要太爽!!
五、 实体类(附加)
@Table ( name = "index_calculated" )
@Entity
public class IndexCalculated implements Serializable {
@Id
@Column ( name = "index_code" )
private String indexCode;
@Column ( name = "update_time" )
private String updateTime;
public String getIndexCode ( ) {
return indexCode;
}
public void setIndexCode ( String indexCode) {
this . indexCode = indexCode;
}
public String getUpdateTime ( ) {
return updateTime;
}
public void setUpdateTime ( String updateTime) {
this . updateTime = updateTime;
}
}
六、Repository.java(附加,个人觉得JPA很方便实用)
nativeQuery属性设置为true,可以在value里写原生sql即数据库能直接运行的sql,避开JPA的API这样就很灵活,便于sql优化。 就第一点来说再加上SpringDataJPA与SpringBoot的方便整合,SpringDataJPA确实很方便好用,省去了Mybatis的xml配置。
@Repository
public interface StockRepository extends JpaRepository < IndexCalculated , String > {
@Query ( nativeQuery = true , value = "SELECT `stock_code` AS `stockCode`,`stock_name` AS `stockName`,`stock_display_name` AS `stockDisplayName` FROM `stock_security`" )
List < Map < String , Object > > stockIndexInfoOfStock ( ) ;
@Query ( nativeQuery = true , value = "SELECT `index_code` AS `stockCode`,`index_name` AS `stockName`,`index_display_name` AS `stockDisplayName` FROM `index_info` WHERE `index_code` IN (SELECT `index_code` FROM `index_calculated`)" )
List < Map < String , Object > > stockIndexInfoOfIndex ( ) ;
@Query ( nativeQuery = true , value = "SELECT `stock_code` FROM `sector_stock` WHERE `sector_code` IN ?1" )
List < Object > stockOfSector ( String [ ] sectorCodeArr) ;
@Query ( nativeQuery = true , value = "SELECT `name` AS `sectorName`,`stock_code` AS `stockCode` FROM `industry_sector`,`sector_stock` WHERE industry_sector.`code`=sector_stock.`sector_code`" )
List < Map < String , Object > > industryOfStock ( ) ;
@Query ( nativeQuery = true , value = "SELECT `index_code` AS `code`,`index_name` AS `name`,`index_display_name` AS `displayName` FROM `index_info` WHERE `index_code` IN (SELECT `index_code` FROM `index_calculated`)" )
List < Map < String , Object > > allIndexInfoOfCalculated ( ) ;
@Transactional
@Modifying
@Query ( nativeQuery = true , value = "INSERT INTO `index_calculated`(`index_code`,`update_time`) VALUES (?1,?2)" )
int updateIndexCalculated ( String indexCode, String updateTime) ;
七、application.yml(附加)
server :
port : 9116
spring :
datasource :
url : jdbc: mysql: //
username :
password :
driver-class-name : com.mysql.jdbc.Driver
hikari :
read-only : false
connection-timeout : 60000
idle-timeout : 60000
validation-timeout : 3000
max-lifetime : 60000
login-timeout : 5
maximum-pool-size : 60
minimum-idle : 10
jpa :
generate-ddl : false
show-sql : false
hibernate :
ddl-auto : none
database : mysql
open-in-view : true
redis :
host :
port :
password :
elasticsearch :
jest :
uris : http: //
indexCode : "399695.XSHE,399005.XSHE,399001.XSHE,000003.XSHG,000002.XSHG,000001.XSHG,399006.XSHE,000016.XSHG,000033.XSHG,000034.XSHG,000036.XSHG,000039.XSHG,000040.XSHG,000041.XSHG,000042.XSHG,000055.XSHG,000056.XSHG,000062.XSHG,000097.XSHG,000122.XSHG,000134.XSHG,000158.XSHG,000159.XSHG,000160.XSHG,000161.XSHG,000162.XSHG,000300.XSHG,000812.XSHG,000813.XSHG,000819.XSHG,000941.XSHG,000943.XSHG,000944.XSHG,000945.XSHG,000949.XSHG,399001.XSHE,399005.XSHE,399006.XSHE,399300.XSHE,399355.XSHE,399356.XSHE,399368.XSHE,399380.XSHE,399393.XSHE,399394.XSHE,399395.XSHE,399396.XSHE,399417.XSHE,399418.XSHE,399419.XSHE,399420.XSHE,399429.XSHE,399431.XSHE,399432.XSHE,399433.XSHE,399434.XSHE,399436.XSHE,399438.XSHE,399439.XSHE,399440.XSHE,399441.XSHE,399678.XSHE,399687.XSHE,399688.XSHE,399693.XSHE,399695.XSHE,399803.XSHE,399804.XSHE,399805.XSHE,399806.XSHE,399808.XSHE,399928.XSHE,399929.XSHE,399960.XSHE,399991.XSHE,399994.XSHE,399996.XSHE,399106.XSHE"