【没有测开，只有测试干开发】，爆肝一个快照功能

软件测试君

发布于 2024-05-27 20:53:37

1030

发布于 2024-05-27 20:53:37

文章被收录于专栏：测试人生测试人生

写在前面

手写sql果然还是很难，果然还是岁数大了，或者说今天心不静吧，尝试3次才能理解写出来。

但值得说的是，我真的理解了，也掌握了sql中的一些函数，真是感受到了，在变强呀！哈哈

对应SQL

insert into ebook_snapshot (ebook_id, date, view_count, vote_count, view_increase, vote_increase)
SELECT t1.id, curdate(), 0, 0, 0, 0
from ebook t1
where not exists(select 1 from ebook_snapshot t2 where t1.id = t2.ebook_id and t2.date = curdate());


update ebook_snapshot t1, ebook t2
set t1.vote_count=t2.vote_count,
    t1.view_count=t2.view_count
where t1.date = curdate()
  and t1.ebook_id = t2.id;


select t1.ebook_id, t1.vote_count, t1.view_count
from ebook_snapshot t1
where date = date_sub(curdate(), interval 1 day);


update ebook_snapshot t1 left join (select ebook_id, vote_count, view_count
                                    from ebook_snapshot
                                    where date = date_sub(curdate(), interval 1 day)) t2
    on t1.ebook_id = t2.ebook_id
set t1.view_increase=t1.view_count - ifnull(t2.view_count, 0),
    t1.vote_increase=t1.vote_count - ifnull(t2.view_count, 0)
where t1.date = curdate()

电子书快照功能

完全是后端，自定义sql的使用，这是核心，示例如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.jiawa.wiki.mapper.EbookSnapshotMapperCust" >

    <!--
    # 方案一（ID不连续）：
    #   删除今天的数据
    #   为所有的电子书生成一条今天的记录
    #   更新总阅读数、总点赞数
    #   更新今日阅读数、今日点赞数
    # 方案二（ID连续）：
    #   为所有的电子书生成一条今天的记录，如果还没有
    #   更新总阅读数、总点赞数
    #   更新今日阅读数、今日点赞数
    -->
    <update id="genSnapshot">
        insert into ebook_snapshot(ebook_id, date, view_count, vote_count, view_increase, vote_increase)
        select t1.id, curdate(), 0, 0, 0, 0
        from ebook t1
        where not exists(select 1
                         from ebook_snapshot t2
                         where t1.id = t2.ebook_id
                           and t2.date = curdate());

        update ebook_snapshot t1, ebook t2
        set t1.view_count = t2.view_count,
            t1.vote_count = t2.vote_count
        where t1.date = curdate()
          and t1.ebook_id = t2.id;

        update ebook_snapshot t1 left join (select ebook_id, view_count, vote_count
            from ebook_snapshot
            where date = date_sub(curdate(), interval 1 day)) t2
        on t1.ebook_id = t2.ebook_id
            set t1.view_increase = (t1.view_count - ifnull(t2.view_count, 0)),
                t1.vote_increase = (t1.vote_count - ifnull(t2.vote_count, 0))
        where t1.date = curdate();
    </update>

</mapper>

定时任务，负责收集数据

import com.jiawa.wiki.service.EbookSnapshotService;
import com.jiawa.wiki.util.SnowFlake;
import jakarta.annotation.Resource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.slf4j.MDC;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;


@Component
public class EbookSnapshotJob {

    private static final Logger LOG = LoggerFactory.getLogger(EbookSnapshotJob.class);

    @Resource
    private EbookSnapshotService ebookSnapshotService;

    @Resource
    private SnowFlake snowFlake;

    /**
     * 自定义cron表达式跑批
     * 只有等上一次执行完成，下一次才会在下一个时间点执行，错过就错过
     */
    @Scheduled(cron = "0/5 * * * * ?")
    public void doSnapshot() {
        // 增加日志流水号
        MDC.put("LOG_ID", String.valueOf(snowFlake.nextId()));
        LOG.info("生成今日电子书快照开始");
        Long start = System.currentTimeMillis();
        ebookSnapshotService.genSnapshot();
        LOG.info("生成今日电子书快照结束，耗时：{}毫秒", System.currentTimeMillis() - start);
    }

}

public interface EbookSnapshotMapperCust {

    public void genSnapshot();
}

import com.jiawa.wiki.mapper.EbookSnapshotMapperCust;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Service;


@Service
public class EbookSnapshotService {

    @Resource
    private EbookSnapshotMapperCust ebookSnapshotMapperCust;

    public void genSnapshot() {
        ebookSnapshotMapperCust.genSnapshot();
    }

}

配置文件添加如下内容：

rocketmq:
  name-server: 127.0.0.1:9876 # mq地址
  producer:
    group: default # 必须指定group
    send-message-timeout: 3000 # 消息发送超时时长，默认3s
    retry-times-when-send-failed: 3 # 同步发送消息失败重试次数，默认2
    retry-times-when-send-async-failed: 3 # 异步发送消息失败重试次数，默认2
    customized-trace-topic: TEST__TOPIC

知识点：