数据统计
后台系统首页中，显示各种统计数据，比如：累计用户数、新增用户数、登录次数等内容。
解决方案
数据库表分析
一、数据采集
需求：
1、探花系统将用户操作日志写入RabbitMQ
2、管理后台获取最新消息，构造日志数据存入数据库
1.搭建RabbitMQ环境
添加依赖
<!--RabbitMQ-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-amqp</artifactId>
</dependency>
在nacos配置管理中添加RabbitMQ地址
spring:
rabbitmq:
host: 192.168.1.8
port: 5672
username: guest
password: guest
2.在探花app系统端发送日志消息到RabbitMQ
例如在登录时把用户登录这行为操作记录发送到RabbitMQ
@Autowired
private AmqpTemplate amqpTemplate;
public void login() {
………
//构造Map集合封装要发送的数据
Map<String, Object> msg = new HashMap<>();
msg.put(“userId”, UserHolder.getUserId().toString());
msg.put(“date",new SimpleDateFormat("yyyy-MM-dd").format(new Date()) );
msg.put("type", "0101",);
String message = JSON.toJSONString(msg);
//发送消息
try {
amqpTemplate.convertSendAndReceive("tanhua.log.exchange",
"log.user",message);
}catch (Exception e) {
e.printStackTrace();
}
………
}
3.在后台系统admin端监听器处理消息,解析数据，存到数据库
package com.tanhua.admin.listener;
import com.alibaba.fastjson.JSON;
import com.tanhua.admin.mapper.LogMapper;
import com.tanhua.model.domain.Log;
import org.springframework.amqp.core.ExchangeTypes;
import org.springframework.amqp.rabbit.annotation.Exchange;
import org.springframework.amqp.rabbit.annotation.Queue;
import org.springframework.amqp.rabbit.annotation.QueueBinding;
import org.springframework.amqp.rabbit.annotation.RabbitListener;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.Map;
@Component
public class Loglistener {
@Autowired
private LogMapper logMapper;
@RabbitListener(bindings = @QueueBinding(
value = @Queue(
value = "tanhua.log.queue",
durable = "true"
),
exchange = @Exchange(
value = "tanhua.log.exchange",
type = ExchangeTypes.TOPIC),
key = "log.*"
))
public void listenerLog(String message){
try {
Map<String, Object> map = JSON.parseObject(message);
//1、获取数据
String userId = (String) map.get("userId");
String date = (String) map.get("logTime");
String type = (String) map.get("type");
System.out.println(userId);
System.out.println(date);
System.out.println(type);
//2.创建对象封装数据，保存到数据库
Log log = new Log(Long.valueOf(userId),date,type);
logMapper.insert(log);
} catch (NumberFormatException e) {
System.out.println("保存到数据库失败");
}
}
}
3.所用到的实体类
Log
package com.tanhua.model.domain;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Log {
/**
* id
*/
private Long id;
/**
* 用户id
*/
private Long userId;
/**
* 操作时间
*/
private String logTime;
/**
* 操作类型,
* 0101为登录，0102为注册，
* 0201为发动态，0202为浏览动态，0203为动态点赞，0204为动态喜欢，0205为评论，0206为动态取消点赞，0207为动态取消喜欢，
* 0301为发小视频，0302为小视频点赞，0303为小视频取消点赞，0304为小视频评论
*/
private String type;
/**
* 登陆地点
*/
private String place;
/**
* 登陆设备
*/
private String equipment;
public Log(Long userId, String logTime, String type) {
this.userId = userId;
this.logTime = logTime;
this.type = type;
}
}
Analysis
package com.tanhua.model.domain;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Analysis{
private Long id;
/**
* 日期
*/
private Date recordDate;
/**
* 新注册用户数
*/
private Integer numRegistered = 0;
/**
* 活跃用户数
*/
private Integer numActive = 0;
/**
* 登陆次数
*/
private Integer numLogin = 0;
/**
* 次日留存用户数
*/
private Integer numRetention1d = 0;
private Date created;
}
4.消息发送工具类
package com.tanhua.server.service;
import com.alibaba.fastjson.JSON;
import org.springframework.amqp.AmqpException;
import org.springframework.amqp.core.AmqpTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
@Service
public class MqMessageService {
@Autowired
private AmqpTemplate amqpTemplate;
//发送日志消息
public void sendLogMessage(Long userId,String type,String key,String busId) {
try {
Map map = new HashMap();
map.put("userId",userId.toString());
map.put("type",type);
map.put("logTime",new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
map.put("busId",busId);
String message = JSON.toJSONString(map);
amqpTemplate.convertAndSend("tanhua.log.exchange",
"log."+key,message);
} catch (AmqpException e) {
e.printStackTrace();
}
}
//发送动态审核消息
public void sendAudiService(String movementId) {
try {
amqpTemplate.convertAndSend("tanhua.audit.exchange",
"audit.movement",movementId);
} catch (AmqpException e) {
e.printStackTrace();
}
}
}
二、定时任务
在实际项目开发中，除了Web应用、SOA服务外，还有一类不可缺少的，那就是定时任务调度。定时任务的场景可以说非常广泛：
- 某些网站会定时发送优惠邮件；
- 银行系统还款日信用卡催收款；
- 某些应用的生日祝福短信等。
那究竟何为定时任务调度，一句话概括就是：基于给定的时间点、给定的时间间隔、自动执行的任务
Spring 3.0以后自带了task 调度工具
入门案例：先在服务启动类用@EnableScheduling注解开启定时任务
package com.tanhua.admin;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.data.mongo.MongoDataAutoConfiguration;
import org.springframework.boot.autoconfigure.mongo.MongoAutoConfiguration;
import org.springframework.scheduling.annotation.EnableScheduling;
@SpringBootApplication(exclude = {MongoAutoConfiguration.class, MongoDataAutoConfiguration.class})
@MapperScan("com.tanhua.admin.mapper")
@EnableScheduling//开启定时任务
public class AdminServerApplication {
public static void main(String[] args) {
SpringApplication.run(AdminServerApplication.class,args);
}
}
2..定义一个类交由容器管理，定义一个无参无返回值的方法做为定时任务
@Component
public class AnalysisTask {
/**
* 配置时间规则
*/
@Scheduled( cron = "0/20 * * * * ? ")
public void analysis() throws ParseException {
//业务逻辑
String time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
System.out.println("当前时间："+time);
}
}
三、定时统计
1. LogMapper
package com.tanhua.admin.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tanhua.model.domain.Log;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
public interface LogMapper extends BaseMapper<Log> {
//可以理解为统计当天注册或者登录的用户
@Select("SELECT COUNT(DISTINCT user_id) FROM tb_log WHERE TYPE=#{type} AND log_time=#{logTime}")
Integer queryByTypeAndLogTime(@Param("type") String type, @Param("logTime") String logTime); //根据操作时间和类型
@Select("SELECT COUNT(DISTINCT user_id) FROM tb_log WHERE log_time=#{logTime}")
Integer queryByLogTime(String logTime); //展示记录时间查询
@Select("SELECT COUNT(DISTINCT user_id) FROM tb_log WHERE log_time=#{today} AND user_id IN (\n " +
" SELECT user_id FROM tb_log WHERE TYPE=\"0102\" AND log_time=#{yestoday} \n " +
")")
Integer queryNumRetention1d(@Param("today") String today,@Param("yestoday") String yestoday); //查询次日留存
}
2.AnalysisService
package com.tanhua.admin.service;
import cn.hutool.core.date.DateUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.tanhua.admin.mapper.AnalysisMapper;
import com.tanhua.admin.mapper.LogMapper;
import com.tanhua.model.domain.Analysis;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
@Service
public class AnalysisService {
@Autowired
private LogMapper logMapper;
@Autowired
private AnalysisMapper analysisMapper;
//这个是定时要执行的方法，也就是要定时统计tb_log中的数据
public void analysisCount() throws ParseException {
//1.构造查询日期
String toDay = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
String yesterDay = DateUtil.yesterday().toString();
//2.统计今日登录人数
Integer logCounts = logMapper.queryByTypeAndLogTime("0101", toDay);
//3.统计今日注册人数
Integer regCounts = logMapper.queryByTypeAndLogTime("0102", toDay);
//4.统计活跃人数
Integer activeCounts = logMapper.queryByLogTime(toDay);
//5.统计次日留存人数，昨天注册、今日活跃的用户
Integer count = logMapper.queryNumRetention1d(toDay, yesterDay);
//6.先查询analysis表看当天是否已经统计过
QueryWrapper<Analysis> qw = new QueryWrapper<>();
qw.eq("record_date", new SimpleDateFormat().parse(toDay));
Analysis analysis = analysisMapper.selectOne(qw);
if(analysis != null){
//此前已经统计过，修改数据
analysis.setNumLogin(logCounts);
analysis.setNumRegistered(regCounts);
analysis.setNumActive(activeCounts);
analysis.setNumRetention1d(count);
analysisMapper.updateById(analysis);
}else {
//为空，保存数据
analysis = new Analysis();
analysis.setNumLogin(logCounts);
analysis.setNumRegistered(regCounts);
analysis.setNumActive(activeCounts);
analysis.setNumRetention1d(count);
analysis.setRecordDate(new SimpleDateFormat().parse(toDay));
analysis.setCreated(new Date());
analysisMapper.insert(analysis);
}
}
}
3.TimerCount
package com.tanhua.admin.task;
import com.tanhua.admin.service.AnalysisService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
@Component
public class TimerCount {
@Autowired
private AnalysisService analysisService;
/**
* 每隔20秒钟查询tb_log表，统计数据；创建对象封装数据
*/
@Scheduled( cron = "0/20 * * * * ? ")
public void printTimer() throws ParseException {
String currentTime =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
System.out.println("当前时间："+currentTime);
analysisService.analysisCount();
}
}