SpringBoot实战：手把手教你实现mysql数据报表统计并定时推送

通过一个小的业务点出发，搭建一个可以实例使用的项目工程，将各种知识点串联起来; 实战演练专题中，每一个项目都是可以独立运行的，包含若干知识点，甚至可以不做修改直接应用于生产项目；

今天的实战项目主要解决的业务需求为：每日新增用户统计，生成报表，并邮件发送给相关人

本项目将包含以下知识点：

• 基于 MySql 的每日新增用户报表统计（如何统计每日新增用户，若日期不连续如何自动补 0？）
• 定时执行报表统计任务
• MyBatis MySql 数据操作
• 邮件发送
• Thymeleaf 引擎实现报表模板渲染

I. 需求拆解

需要相对来说属于比较明确的了，目的就是实现一个自动报表统计的任务，查询出每日的用户新增情况，然后推送给指定的用户

因此我们将很清晰的知道，我们需要干的事情

定时任务

这里重点放在如何来支持这个任务的定时执行，通常来说定时任务会区分为固定时刻执行 间隔时长执行两种（注意这种区分主要是为了方便理解，如每天五点执行的任务，也可以理解为每隔 24h 执行一次）

前者常见于一次性任务，如本文中的每天统计一次，这种就是相对典型的固定时刻执行的任务；

后者常见于轮询式任务，如常见的应用探活(每隔 30s 发一个 ping 消息，判断服务是否健在）

定时任务的方案非常多，有兴趣的小伙伴可以关注一波“一灰灰 blog”公众号，蹲守一个后续

本文将直接采用 Spring 的定时任务实现需求场景，对这块不熟悉的小伙伴可以看一下我之前的分享的博文

180801-Spring 之定时任务基本使用篇 - 一灰灰 Blog[2] 180803-Spring 定时任务高级使用篇 - 一灰灰 Blog[3]

每日新增用户统计

每日新增用户统计，实现方式挺多的，比如举几个简单的实现思路

• 基于 redis 的计数器：一天一个 key，当天有新用户时，同步的实现计数器 1
• 基于数据库，新增一个统计表，包含如日期 新增用户数 活跃用户数 等字段
  • 有新用户注册时，对应日期的新增用户数，活跃用户数 1
  • 老用户今日首次使用时，活跃用户数 1

上面两个方案都需要借助额外的库表来辅助支持，本文则采用直接统计用户表，根据注册时间来聚合统计每日的新增用户数

• 优点：简单，无额外要求，适用于数据量小的场景（比如用户量小于百万的）
• 缺点：用户量大时，数据库压力大

关于如何使用 mysql 进行统计每日新增用户，不熟悉的小伙伴，推荐参考博主之前的分享文章

• 220707-MySql 按时、天、周、月进行数据统计 - 一灰灰 Blog[4]

报表生成&推送用户

接下来就是将上面统计的数据，生成报表然后推送给用户；首先是如何将数据生成报表？其次则是如何推送给指定用户？

将数据组装成报表的方式通常取决于你选择的推送方式，如飞书、钉钉之类的，有对应的开发 api，可以直接推送富文本；

本文的实现姿势则选择的是通过邮件的方式进行发送，why?

• 飞书、钉钉、微信之类的，需要授权，对于不使用这些作为办公软件的小伙伴没什么意义
• 短信需要钱....

对于邮件，大家应该都有，无论是 qq 邮箱，还是工作邮箱；基本上对于想要直接跑本文的小伙伴来说，没有什么额外的门槛

关于 java/spring 如何使用邮箱，对此不太熟悉的小伙伴，可以参考博主之前的分享文章

• 【中间件】SpringBoot 系列之邮件发送姿势介绍 | 一灰灰 Blog[5]

上面文章中介绍的是 FreeMaker 来实现模板渲染，本文则介绍另外一个知识点，借助 Thymleaf 来实现数据报表的生成 （一篇文章获取这么多知识点，就问你开不开心 O(∩_∩)O）

II. 分布实现

1. 项目搭建

首选搭建一个基本的 SpringBoot 应用，相信这一步大家都很熟悉了；若有不懂的小伙伴，请点赞、评论加博主好友，手把手教你，不收费

最终的项目依赖如下

代码语言：javascript复制

<dependencies>
  <!-- 邮件发送的核心依赖 -->
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-mail</artifactId>
  </dependency>

  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>

  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-thymeleaf</artifactId>
  </dependency>


  <dependency>
    <groupId>com.google.guava</groupId>
    <artifactId>guava</artifactId>
    <version>31.1-jre</version>
  </dependency>

  <dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
  </dependency>
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
  </dependency>
</dependencies>

别看上面好像依赖了不少包，实际上各有用处

• spring-boot-starter-web: 提供 web 服务
• spring-boot-starter-mail: 发邮件就靠它
• mybatis-spring-boot-starter: 数据库操作

我们的用户存在 mysql 中，这里使用 mybatis 来实现 db 操作（又一个知识点来了，收好不谢）

2. 数据准备

文末的源码包含库表结构，初始化数据，可以直接使用

既然模拟的是从数据库中读取每日新增用户，所以我们准备了一张表

代码语言：javascript复制

CREATE TABLE `u1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT 'name',
  `email` varchar(512) NOT NULL DEFAULT '' COMMENT 'email',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='u1测试';

接下来准备写入一些数据；为了模拟某些天没有新增用户，贴心的一灰灰博主给大家提供基于 python 的数据生成脚本，源码如下 (python3 ，对 python 不熟的小伙伴，可以到博主的站点进补一下，超链[6])

代码语言：javascript复制

import datetime

def create_day_time(n):
    now = datetime.datetime.now()
    now = now - datetime.timedelta(days = n)
    return now.strftime("%Y-%m-%d %H:%S:%M")

vals = []
for i in range(0, 100):
    if (i % 32 % 6) == 0:
        # 模拟某一天没有用户的场景
        continue
    vals.append(f"('{i}_灰灰', '{i}hui@email.com', '{create_day_time(i % 32)}', '{create_day_time(i % 32)}')")

values = ',nt'.join(vals)
sqls = f"INSERT INTO story.u1 (name, email, create_time, update_time) VALUES n{values};"
print(sqls)

3. 全局配置

数据准备完毕之后，接下来配置一下 db、email 相关的参数

resources/application.yml 文件内容如下

代码语言：javascript复制

spring:
  #邮箱配置
  mail:
    host: smtp.163.com
    from: xhhuiblog@163.com
    # 使用自己的发送方用户名   授权码填充
    username:
    password:
    default-encoding: UTF-8
    properties:
      mail:
        smtp:
          auth: true
          starttls:
            enable: true
            required: true

  datasource:
    url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password:


  thymeleaf:
    mode: HTML
    encoding: UTF-8
    servlet:
      content-type: text/html
    cache: false

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.git.hui.demo.report.dao.po

上面的配置分为三类

• 数据库相关：连接信息，用户名密码， mybatis 配置
• thymleaf：模板渲染相关
• email: 邮箱配置相关，请注意若使用博主的源码，在本地运行时，请按照前面介绍的邮箱博文中手把手的教程，获取您自己的邮箱授权信息，填在上面的 username, password 中

4. 数据报表统计实现

接下来就正式进入大家喜闻乐见的编码实现环节，我们直接使用 mybaits 来实现数据库操作，定义一个统计的接口

代码语言：javascript复制

/**
 * @author YiHui
 */
public interface UserStatisticMapper {
    /**
     * 统计最近多少天内的新增用户数
     *
     * @param days 统计的天数，从当前这一天开始
     * @return
     */
    List<UserStatisticPo> statisticUserCnt(int days);
}

接口中定义了一个 PO 对象，就是我们希望返回的数据，其定义就非常清晰简单了，时间 数量

代码语言：javascript复制

@Data
public class UserStatisticPo {
    private String day;
    private Integer count;
}

上面定义的知识接口，具体首先，当然是放在 mybatis 的传统 xml 文件中，根据前面 application.yml 配置，我们的 xml 文件需要放在 resources/mapper 目录下，具体实现如下

代码语言：javascript复制

<?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.git.hui.demo.report.dao.UserStatisticMapper">

    <resultMap id="countMap" type="com.git.hui.demo.report.dao.po.UserStatisticPo">
        <result column="day" property="day"/>
        <result column="count" property="count"/>
    </resultMap>

    <!-- 统计用户新增  -->
    <select id="statisticUserCnt" resultMap="countMap">
        SELECT date_table.day as `day`, IFNULL(data.cnt, 0) as `count`
        from
        (select DATE_FORMAT(create_time, '%Y-%m-%d') day, count(id) cnt from u1 GROUP BY day) data
            right join
        (SELECT @date := DATE_ADD(@date, interval - 1 day) day from (SELECT @date := DATE_ADD(CURDATE(), interval 1 day) from u1) days limit #{days}) date_table
        on date_table.day = data.day
    </select>
</mapper>

重点看一下上面的 sql 实现，为什么会一个 join 逻辑？

那我们稍稍思考，若我们直接通过日期进行 format 之后，再 group 一下统计计数，会有什么问题？给大家 3s 的思考时间

• 1s
• 2s
• 3s

好的 3s 时间到，现在公布答案，当某一天一个新增用户都没有的时候，会发生什么事情？会出现这一天的数据空缺，即返回的列表中，少了一天，不连续了，如果前段的小伙伴基于这个列表数据进行绘图，很有可能出现异常

所以出于系统的健壮性考虑（即传说中的鲁棒性），我们希望若某一天没有数据，则对应的计数设置为 0

具体的 sql 说明就不展开了，请查看博文获取更多：MySql 按时、天、周、月进行数据统计[7]

5. 报表生成实现

数据统计出来之后，接下来就是基于这些数据来生成我们报表，我们借助 Thymleaf 来实现，因此先写一个 html 模板，resources/templates/report.html

代码语言：javascript复制

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title th:text="${vo.htmlTitle}">每日用户统计</title>
</head>
<style>
    .title22 {
        font: 16px/24px bold;
        position: relative;
        display: block;
        padding: 0 6px;
        margin-left: -6px;
        margin-bottom: 12px;
        font-size: 22px;
        font-weight: 550;
    }

    .container {
        background: #fff;
        overflow: auto;
        padding: 6px;
        margin: 6px;
        font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', '微软雅黑', SimSun, '宋体';
    }

    .content {
        overflow: auto;
        padding: 6px 12px;
        margin: 6px;
    }

    table {
        border: none;
        border-collapse: collapse;
        table-layout: fixed;
    }

    .thead {
        font: 14px/20px bold;
        font-weight: 550;
        background: #eaeaea;
        line-height: 1.5em;
    }

    .tbody {
        font: 15px/20px normal;
        font-weight: 540;
        background: #fff;
    }

    tr > td {
        padding: 6px 12px;
        border: 1px solid #d8d8d8;
        max-width: 600px;
    }
</style>
<body>
<div class="container">
    <div class="content">
        <div class="title22" style="color: red;" th:text="${vo.tableTitle}">统计标题</div>
        <table>
            <thead class="thead">
            <tr>
                <td class="thead" style="background:#eaeaea;">日期</td>
                <td style="min-width: 50px; color: #4040e1">新增用户</td>
            </tr>
            </thead>
            <tbody class="tbody">
            <tr th:each="item: ${vo.list}">
                <td class="thead" style="background:#eaeaea;" th:text="${item.day}">2022-08-01</td>
                <td style="min-width: 50px; color: #4040e1" th:text="${item.count}">1</td>
            </tr>
            </tbody>
        </table>
    </div>
</div>
</body>
</html>

一个非常简单的 table 模板，需要接收三个数据，与之对应的 vo 对象，我们定义如下

代码语言：javascript复制

@Data
public class StatisticVo {
    // 表格数据项，即日期   数量的列表
    private List<UserStatisticPo> list;
    // 网页的标题
    private String htmlTitle;
    // 表格标题
    private String tableTitle;
}

接下来就是拿到数据之后，将它与模板渲染得到我们希望的数据，这里主要借助的是org.thymeleaf.spring5.SpringTemplateEngine

核心实现如下

代码语言：javascript复制

@Service
public class StatisticAndReportService {
    @Autowired
    private UserStatisticMapper userStatisticMapper;

    @Autowired
    private JavaMailSender javaMailSender;

    @Autowired
    private Environment environment;

    @Autowired
    private SpringTemplateEngine templateEngine;


    public StatisticVo statisticAddUserReport() {
        List<UserStatisticPo> list = userStatisticMapper.statisticUserCnt(30);
        StatisticVo vo = new StatisticVo();
        vo.setHtmlTitle("每日新增用户统计");
        vo.setTableTitle(String.format("【%s】新增用户报表", LocalDate.now()));
        vo.setList(list);
        return vo;
    }

    public String renderReport(StatisticVo vo) {
        Context context = new Context();
        context.setVariable("vo", vo);
        String content = templateEngine.process("report", context);
        return content;
    }
}

模板渲染就一行templateEngine.process("report", context)，第一个参数为模板名，就是上面的 html 文件名（对于模板文件、静态资源怎么放，放在那儿，这个知识点当然也可以在一灰灰的站点获取，超链[8]）

第二个参数用于封装上下文，传递模板需要使用的参数

5. 邮件发送

报表生成之后，就是将它推送给用户，我们这里选定的是邮箱方式，具体实现也比较简单，但是在最终部署到生产环境（如阿里云服务器时，可能会遇到坑，同样明显的知识点，博主会没有分享么？当然不会没有了，Email 生产环境发送排雷指南，你值得拥有[9]）

代码语言：javascript复制

/**
 * 发送邮件的逻辑
 *
 * @param title
 * @param content
 * @throws MessagingException
 */
public void sendMail(String title, String content) throws MessagingException {
    MimeMessage mimeMailMessage = javaMailSender.createMimeMessage();
    MimeMessageHelper mimeMessageHelper = new MimeMessageHelper(mimeMailMessage, true);
    //邮件发送人，从前面的配置参数中拿，若没有配置，则使用默认的xhhuiblog@163.com
    mimeMessageHelper.setFrom(environment.getProperty("spring.mail.from", "xhhuiblog@163.com"));
    //邮件接收人，可以是多个
    mimeMessageHelper.setTo("bangzewu@126.com");
    //邮件主题
    mimeMessageHelper.setSubject(title);
    //邮件内容
    mimeMessageHelper.setText(content, true);

    // 解决linux上发送邮件时，抛出异常 JavaMailSender no object DCH for MIME type multipart/mixed
    Thread.currentThread().setContextClassLoader(javax.mail.Message.class.getClassLoader());
    javaMailSender.send(mimeMailMessage);
}

上面的实现，直接写死了收件人邮箱，即我本人的邮箱，各位大佬在使用的时候，请记得替换一下啊

上面的实现除了发送邮件这个知识点之外，还有一个隐藏的获取配置参数的知识点，即environment#getProperty()，有兴趣的小伙伴翻博主的站点吧

6. 定时任务

上面几部基本上就把我们的整个任务功能都实现了，从数据库中统计出每日新增用户，然后借助 Thymleaf 来渲染模板生成报告，然后借助 email 进行发送

最后的一步，就是任务的定时执行，直接借助 Spring 的 Schedule 来完成我们的目标，这里我们希望每天 4:15 分执行这个任务，如下配置即可

代码语言：javascript复制

// 定时发送，每天4:15分统计一次，发送邮件
@Scheduled(cron = "0 15 4 * * ?")
//    下上面这个是每分钟执行一次，用于本地测试
//    @Scheduled(cron = "0/1 * * * * ?")
public void autoCalculateUserStatisticAndSendEmail() throws MessagingException {
        StatisticVo vo = statisticAddUserReport();
        String content = renderReport(vo);
        sendMail("新增用户报告", content);
}

7. 测试

最后测试演练一下，启动方法如下，除了基本的启动注解之外，还指定了 mapper 接口位置，开启定时任务；感兴趣的小伙伴可以试一下干掉这两个注解会怎样，评论给出你的实测结果吧

代码语言：javascript复制

@EnableScheduling
@MapperScan(basePackages = "com.git.hui.demo.report.dao")
@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
}

当然我再实际测试的时候，不可能真等到早上四点多来看是否执行，大晚上还是要睡觉的；因此本地测试的时候，可以将上面定时任务改一下，换成每隔一分钟执行一次

接一个 debug 的中间图

打开的内容展示

此外，源码除了实现了定时推送之外，也提供了一个 web 接口，访问之后直接可以查看报表内容，方便大家调样式，实现如下

代码语言：javascript复制

@Controller
public class StatisticReportRest {

    @Autowired
    private StatisticAndReportService statisticAndReportSchedule;

    @GetMapping(path = "report")
    public String view(Model model) {
        StatisticVo vo = statisticAndReportSchedule.statisticAddUserReport();
        model.addAttribute("vo", vo);
        return "report";
    }
}

8.一灰灰的干货总结

最后进入一灰灰的保留环节，这么“大”一个项目坐下来的，当然是得好好盘一盘它的知识点了，前面的各小节内容中有穿插的指出相应的知识点，接下来如雨的知识点将迎面袭来，不要眨眼

• Spring 定时任务@Schedule
  • 怎么用？-> 180801-Spring 之定时任务基本使用篇 - 一灰灰 Blog[10]
  • 多个任务串行并行，是否会相互影响？自定义线程池怎么整？一个异常会影响其他么？-> Spring 定时任务高级使用篇 - 一灰灰 Blog[11]
• 数据库统计每日新增
  • mysql 直接统计日新增，sql 怎么写？时间不连续，如何规避？-> MySql 按时、天、周、月进行数据统计 - 一灰灰 Blog[12]
  • mybatis 操作 db 怎么玩？-> Mybatis 系列教程[13]
• 模板渲染
  • 数据报表生成，直接字符串拼接？还是模板引擎的渲染？
  • 更多的 spring web 知识点 -> 一灰灰的 SpringWeb 专栏 | 免费[14]
• 邮件发送
  • 怎么发邮件？-> SpringBoot 无障碍使用邮箱服务[15]
  • 如何避免上线不采坑 -> Email 生产环境发送排雷指南，你值得拥有[16]

除了上面比较突出的知识点之外，当然还有其他的，如 Spring 如何读取配置参数，SpringMVC 如何向模板中传递上下文，模板语法，静态资源怎么放等等

原文链接： https://mp.weixin.qq.com/s/kH5Y6b4lNoPlfyy7IkbfZA

如果感觉本文对你有帮助，点赞关注支持一下

java 数据库 spring 微服务Java 编程 程序员 面试 架构

0 人点赞