背景
批量插入带时间戳数据 插入后,根据时间戳判定失效数据,然后删除
注意事项
new Date()创建的时间值,会带小数点如2021-01-13 15:07:47.815,保存到mysql数据库时,小数值会四舍五入变成2021-01-13 15:07:48,查询失效数据时进行时间条件判断会有出入。现象如下:
select * from base_station_test WHERE domain= '1' and net_workg= '2g' and create_time = '2021-01-13 15:07:47.815' ;
select * from base_station_test WHERE domain= '1' and net_workg= '2g' and create_time = '2021-01-13 15:07:48' ;
要点示例
import org. apache. commons. lang3. StringUtils;
import org. apache. commons. lang3. time. DateFormatUtils;
import org. apache. commons. lang3. time. DateUtils;
private void updateDate ( List< Map< String, Object> > mapList, String domain, String net_workg) {
try {
Date creatTime = DateUtils. parseDate ( DateFormatUtils. format ( new Date ( ) , "yyyy-MM-dd HH:mm:ss" ) , "yyyy-MM-dd HH:mm:ss" ) ;
List< BaseStation> domainList= mapList2DomainList ( mapList, domain, net_workg, creatTime) ;
for ( int i= 0 , j= 0 , betw= 3000 ; i < domainList. size ( ) ; i+= betw) {
j= i+ betw;
try {
if ( j< domainList. size ( ) ) {
baseStationMapper. insertBatch ( domainList. subList ( i, j) ) ;
} else {
baseStationMapper. insertBatch ( domainList. subList ( i, domainList. size ( ) ) ) ;
break ;
}
} catch ( Exception e) {
log. error ( domainList. get ( i) ) ;
e. printStackTrace ( ) ;
}
}
baseStationMapper. deleteDomainOldData ( domain, net_workg, creatTime) ;
} catch ( ParseException e) {
e. printStackTrace ( ) ;
}
}
void insertBatch ( @Param ( "list" ) List< BaseStation> dataList) ;
< insert id= "insertBatch" >
INSERT INTO base_station_test
(
`enode_bid`, `eci`, `domain`, `net_workg`, `lac`,
`cellid`, `create_time`, `longitude`, `latitude`, `base_station_type`,
`province`, `station_name`, `ne_name`, `city`, `county`,
`address`, `village_longitude`, `village_latitude`, `antenna_angle`, `cover_radius`,
`update_time`
) VALUES
< foreach collection = "list" item= "info" separator= "," >
(
#{ info. enodeBID} , #{ info. eci} , #{ info. domain} , #{ info. network} , #{ info. lac} ,
#{ info. cellid} , #{ info. createTime} , #{ info. longitude} , #{ info. latitude} , #{ info. baseStationType} ,
#{ info. province} , #{ info. stationName} , #{ info. neName} , #{ info. city} , #{ info. county} ,
#{ info. address} , #{ info. villageLongitude} , #{ info. villageLatitude} , #{ info. antennaAngle} , #{ info. coverRadius} ,
#{ info. updateTime}
)
< / foreach>
< / insert>
< delete id= "deleteDomainOldData" >
< if test= "domain != null and net_workg != null and create_time != null" >
delete from base_station_test where domain= #{ domain} and net_workg= #{ net_workg} and create_time != #{ create_time}
< / if >
< / delete>
SELECT * from base_station_test where create_time = str_to_date ( '2021-01-13 10:06:09' , '%Y-%m-%d %H:%i:%s' )
DELETE from base_station_test where create_time != '2021-01-13 10:06:09'
delete FROM base_station_test WHERE domain= '1' and net_workg= '2g' and create_time = '2021-01-13 14:38:29.52' ;