MyBatis调用存储备份过期数据

最新推荐文章于 2024-06-18 12:55:53 发布

翁正存

最新推荐文章于 2024-06-18 12:55:53 发布

阅读量510

点赞数

分类专栏： MyBatis使用指南文章标签： MyBatis调用存储备份过期数据

本文链接：https://blog.csdn.net/Wengzhengcun/article/details/89021054

版权

MyBatis使用指南专栏收录该内容

8 篇文章 0 订阅

订阅专栏

创建存储过程：

delimiter #
create procedure sps.backup_coupon_history_data(IN beginIndex INT, IN endIndex INT)
begin
INSERT INTO sps_active_rp_detail_his_2019 select * from sps_active_rp_detail where id >= beginIndex and id < endIndex and (TO_DAYS(NOW()) - TO_DAYS(invalid_time) >= 60 or (TO_DAYS(NOW()) - TO_DAYS(add_time) >= 30 and status = 1));
delete from sps_active_rp_detail where id >= beginIndex and id < endIndex and (TO_DAYS(NOW()) - TO_DAYS(invalid_time) >= 60 or (TO_DAYS(NOW()) - TO_DAYS(add_time) >= 30 and status = 1));
end #
delimiter ;

配置XML：

<select id="backUpCouponHistoryData" parameterMap="backUpHistoryDataMap" statementType="CALLABLE">
  CALL sps.backup_coupon_history_data(?,?)
</select>

<parameterMap type="java.util.Map" id="backUpHistoryDataMap">
  <parameter property="beginIndex" mode="IN" jdbcType="INTEGER"/>
  <parameter property="endIndex" mode="IN" jdbcType="INTEGER"/>
</parameterMap>

代码逻辑：

public void backupHistoryDataCron(){

    try {
        //获取Redis锁
        if(!jedisTemplate.acquireLock(RedisConstants.BACKUP_COUPON_HISTORY_DATA_LOCK, RedisConstants.THREE_IN_MIN)){
            logger.info("[ActiveCouponDetailServiceImpl.backupHistoryDataCron] lock is unaccessible");
            return;
        }
        //获取redis中存储的上次处理完毕的最大id
        Integer index= jedisTemplate.getAsInt(RedisConstants.BACKUP_COUPON_HISTORY_DATA_INDEX);
        if(index == null) {
            index = 100019994;
        }
        if(index > 620000000) {
            logger.info("[ActiveCouponDetailServiceImpl.backupHistoryDataCron] index达到处理上限");
            return;
        }
        HashMap<String, Integer> param = new HashMap<>();
        param.put("beginIndex", index);
        param.put("endIndex", index + 20000);

        //执行存储过程
        iActiveCouponDetailDao.backUpCouponHistoryData(param);

        //更新id
        jedisTemplate.set(RedisConstants.BACKUP_COUPON_HISTORY_DATA_INDEX, String.valueOf(index + 20000));
    }catch (Exception e){
        logger.error("[ActiveCouponDetailServiceImpl.backupHistoryDataCron] 备份数据异常,exception:{}", e);
    } finally {
        //释放redis锁
        jedisTemplate.releaseLock(RedisConstants.BACKUP_COUPON_HISTORY_DATA_LOCK);
    }
}