[SpringBoot actual combat] Data report statistics and regularly push the user's hands-on tutorial

This article is excerpted from the "Special Subject of Practical Exercises"

[Actual Combat Series] Data report statistics and regularly push the user's hands-on tutorial

Starting from a small business point, build a project project that can be used as an example, and connect various knowledge points in series; in the actual combat exercise topic, each project can be run independently, including several knowledge points, even without modification Directly applied to production projects;

The main business needs of today's actual combat project are: 每日新增用户统计，生成报表，并邮件发送给相关人

This project will include the following knowledge points:

• Daily new user report statistics based on MySql (how to count daily new users, and how to automatically add 0 if the date is not consecutive?)
• Execute report statistics tasks regularly
• MyBatis + MySql data manipulation
• Mail sending
• Thymeleaf engine implements report template rendering

<!-- more -->

I. Demand disassembly

It needs to be relatively clear. The purpose is to realize the task of automatic report statistics, query the daily increase of users, and then push them to the specified users.

So we will know very clearly what we need to do

timed task

The focus here is on how to support the timing execution of this task. Generally speaking, timing tasks are divided into two types: fixed time execution + interval execution (note that this distinction is mainly for the convenience of understanding, such as tasks executed at five o’clock every day, also It can be understood as executing every 24h)

The former is commonly seen in 一次性任务 , such as the statistics once a day in this article, this is a relatively typical task performed at a fixed time;

The latter is commonly seen in 轮询式任务 , such as common application detection (send a ping message every 30s to determine whether the service is still alive)

There are many plans for timed tasks. Interested friends can pay attention to a wave of "One Ash Blog" public account and keep a follow-up.

This article will directly use Spring's timed tasks to realize the demand scenario. For those who are not familiar with this piece, you can take a look at the blog post I shared before.

• 180801-Basic Use of Spring's Timing Tasks - A Gray Blog
• 180803-Advanced Use of Spring Scheduled Tasks-One Gray Blog

Daily new user statistics

There are many ways to implement new user statistics every day, such as a few simple implementation ideas

• Redis-based counter: one key per day, when there is a new user on that day, the synchronized implementation counter +1

• Based on the database, a new statistical table is added, including fields such as date + number of new users + number of active users

  • When a new user is registered, the number of new users on the corresponding date, the number of active users + 1
  • When old users use it for the first time today, the number of active users + 1

Both of the above two schemes need to be supported by additional database tables. In this paper, the direct statistics user table is used, and the daily number of new users is aggregated and counted according to the registration time.

• Advantages: simple, no additional requirements, suitable for scenarios with a small amount of data (such as users with less than one million users)
• Disadvantage: When the number of users is large, the database pressure is high

About how to use mysql for statistics to add new users every day, unfamiliar friends, it is recommended to refer to the blogger's previous sharing articles

• • 220707-MySql conducts data statistics on time, day, week, and month - a gray blog

Report generation & push user

The next step is to generate the above statistical data, generate a report and then push it to the user; the first is how to generate a report from the data? The second is how to push to the specified user?

The way to assemble data into reports usually depends on the push method you choose, such as Feishu, DingTalk, etc. There are corresponding development APIs that can directly push rich text;

The implementation posture of this article is to send it by email, why?

• Feishu, DingTalk, WeChat, etc. require authorization, and it is meaningless for small partners who do not use these as office software.
• Texting needs money....

For email, everyone should have it, whether it is a qq mailbox or a work mailbox; basically, there is no additional threshold for small partners who want to run this article directly

For those who are not familiar with how java/spring uses mailboxes, you can refer to the blogger's previous sharing articles

• • [Middleware] Introduction to the Mail Sending Posture of the SpringBoot Series | A Gray Blog

The above article introduces FreeMaker to realize template rendering. This article introduces another knowledge point, using Thymleaf to realize the generation of data reports. O)

II. Distributed Implementation

1. Project construction

The first choice is to build a basic SpringBoot application, I believe everyone is familiar with this step; if you have any friends who don't understand, please like, comment and add blogger friends, and teach you hands-on, free of charge

The final project dependencies are as follows

 <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>

Don't look at the above, it seems to depend on a lot of packages, in fact, each is useful

• spring-boot-starter-web : provide web service
• spring-boot-starter-mail : rely on it to send emails
• mybatis-spring-boot-starter : database operations

Our users exist in mysql, and mybatis is used here to implement db operations (another knowledge point is here, thank you for keeping it)

2. Data Preparation

The source code at the end of the article contains the library table structure, initialization data, and can be used directly

Since the simulation is to read daily new users from the database, we have prepared a table

 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测试';

Next, I'm going to write some data; in order to simulate that there are no new users in some days, the considerate blogger provides a python-based data generation script. The source code is as follows (python3+, for those who are not familiar with python, you can go to The blogger's site supplements it, hyperlink )

 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}[e-mail]', '{create_day_time(i % 32)}', '{create_day_time(i % 32)}')")

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

3. Global configuration

After the data is prepared, configure the parameters related to db and email.

resources/application.yml The content of the file is as follows

 spring:
  #邮箱配置
  mail:
    host: smtp.163.com
    from: [e-mail]
    # 使用自己的发送方用户名 + 授权码填充
    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

The above configuration is divided into three categories

• Database related: connection information, username and password, mybatis configuration
• thymleaf: template rendering related
• email: Email configuration is related, please note that if you use the blogger's source code, when running locally, please follow the step-by-step tutorial in the email blog post introduced earlier to obtain your own email authorization information, and fill in the username and password above.

4. Data report statistics realization

Next, we will officially enter the coding implementation link that everyone likes to hear. We directly use mybaits to implement database operations and define a statistical interface.

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

A PO object is defined in the interface, which is the data we want to return. Its definition is very clear and simple, time + quantity

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

The knowledge interface defined above is, of course, placed in the traditional xml file of mybatis. According to the previous application.yml configuration, our xml file needs to be placed in the resources/mapper directory. The specific implementation is as follows

 <?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>

Focus on the above sql implementation, why is there a join logic?

Then let's think about it a little bit. If we directly format the date and then group the count, what will be the problem? Give everyone 3s of thinking time

• 1s
• 2s
• 3s

The good 3s time is up, and now the answer is announced. When there is no new user one day, what will happen? There will be a data vacancy for this day, that is, in the returned list, there is one day less, and it is discontinuous. If the friends in the previous paragraph draw based on this list data, it is very likely that an abnormality will occur.

So for the sake of the robustness of the system (that is, the robustness of the legend), we hope that if there is no data on a certain day, the corresponding count is set to 0

The specific sql description will not be expanded. Please check the blog post for more information: MySql conducts data statistics on time, day, week and month

5. Report generation implementation

After the data is counted, the next step is to generate our report based on these data. We use Thymleaf to achieve it, so first write an html template, resources/templates/report.html

 <!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>

A very simple table template needs to receive three data, and the corresponding vo object, we define as follows

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

The next step is to get the data and render it with the template to get the data we want. The main help here is org.thymeleaf.spring5.SpringTemplateEngine

The core implementation is as follows

 @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;
    }
}

The template rendering is just one line templateEngine.process("report", context) , the first parameter is the template name, which is the html file name above (how to put template files and static resources, put them there, of course, this knowledge point can also be grayed out site acquisition, hyperlink )

The second parameter is used to encapsulate the context, passing the parameters that the template needs to use

5. Email sending

After the report is generated, it is pushed to the user. We chose the mailbox method here, and the specific implementation is relatively simple, but when it is finally deployed to the production environment (such as Alibaba Cloud server, it may encounter pits, and the same obvious knowledge Point, will the blogger not share it? Of course not, Email production environment to send mine clearance guide, you deserve it )

 /**
 * 发送邮件的逻辑
 *
 * @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);
    //邮件发送人，从前面的配置参数中拿，若没有配置，则使用默认的[e-mail]
    mimeMessageHelper.setFrom(environment.getProperty("spring.mail.from", "[e-mail]"));
    //邮件接收人，可以是多个
    mimeMessageHelper.setTo("[e-mail]");
    //邮件主题
    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);
}

The above implementation directly writes the recipient's mailbox, that is, my own mailbox. When you use it, please remember to replace it.

In addition to the knowledge point of sending emails, the above implementation also has a hidden knowledge point for obtaining configuration parameters, namely environment#getProperty() , interested friends, please visit the blogger's site.

6. Scheduled tasks

The above parts basically implement our entire task function, count daily new users from the database, and then use Thymleaf to render templates to generate reports, and then send them by email.

The last step is the scheduled execution of the task. We directly use Spring's Schedule to complete our goal. Here we hope to execute this task at 4:15 every day. The following configuration can be used.

 // 定时发送，每天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. Test

Finally, let's test and practice. The startup method is as follows. In addition to the basic startup annotations, the location of the mapper interface is also specified to enable scheduled tasks; interested friends can try to kill these two annotations. The comments give your actual measurement the result

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

Of course, when I actually test, I can't really wait until four o'clock in the morning to see if it is executed, and I still have to sleep at night; so when testing locally, I can change the above scheduled task to execute it every minute.

The intermediate image of one debug

open content display

In addition, the source code not only realizes the regular push, but also provides a web interface. After accessing, you can directly view the report content, which is convenient for everyone to adjust the style. The implementation is as follows

 @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. A summary of dry goods

Finally, we enter the grey and grey retention session. To sit down on such a "big" project, of course, you must have a good share of its knowledge points. The content of the previous sections is interspersed to point out the corresponding knowledge points. The following is as follows: The knowledge point of the rain is coming, don't blink

• Spring Scheduled Task @Schedule

  • how to use? -> 180801-Basic Use of Spring's Timing Tasks - A Gray Blog
  • If multiple tasks are run in parallel, will they affect each other? How to adjust the custom thread pool? Does one exception affect the others? -> Advanced Use of Spring Scheduled Tasks - A Grey Blog

• Database statistics are added daily

  • MySQL directly adds statistics to the day, how to write sql? Time is not continuous, how to avoid it? -> MySql conducts data statistics by time, day, week and month - a gray blog
  • How does mybatis operate db? -> Mybatis series of tutorials

• Template rendering

  • Data report generation, direct string splicing? Or the rendering of the template engine?
  • More spring web knowledge points -> a gray SpringWeb column | Free

• Mail sending

  • How to send email? -> SpringBoot accessibility email service
  • How to avoid going online without mining pits -> Email production environment to send mine clearance guide, you deserve it

In addition to the more prominent knowledge points above, of course there are others, such as how Spring reads configuration parameters, how SpringMVC passes context to templates, template syntax, how to put static resources, etc.

I am stunned when I write this, there are so many knowledge points in an article, what is there to hesitate about, just one key and three consecutive walks, I am a gray ash, this may be the last time I have this holiday. The actual combat is dry, and the school is about to start. After the wife and children return, the follow-up updates will be maintained by Cui Geng of all readers.

All the knowledge points in this article can be obtained on my personal site, welcome to follow: https://hhui.top/

Note: It is recommended to view the original text for all hyperlinks in this article.

III. Source code and related knowledge points that cannot be missed

0. Project

• Engineering: https://github.com/liuyueyi/spring-boot-demo
• Source code: https://github.com/liuyueyi/spring-boot-demo/tree/master/spring-case/431-mail-report

1. WeChat public account: Yihuihui Blog

It is not as good as a letter. The above content is purely from the family. Due to limited personal ability, it is inevitable that there will be omissions and mistakes. If you find bugs or have better suggestions, you are welcome to criticize and correct them. Thank you

The following is a gray personal blog, recording all blog posts in study and work, welcome everyone to visit

• A Huihui Blog Personal blog https://blog.hhui.top
• A gray Blog-Spring special blog http://spring.hhui.top