使用mysql-binlog-connector-java技术（0.29.2）解析binlog实现实时数据大屏。
一、项目环境
该项目主要为一个数据大屏，采用了mysql-binlog-connector-java技术，利用他，实现自动获取并解析Mysql的binlog，来实时把对应的数据解析出来用于大屏展示。
<!-- https://mvnrepository.com/artifact/com.zendesk/mysql-binlog-connector-java -->
<dependency>
<groupId>com.zendesk</groupId>
<artifactId>mysql-binlog-connector-java</artifactId>
<version>0.29.2</version>
</dependency>我会把利用到mysql-binlog-connector-java技术放到单独一个叫做sdk包目录下的。后面文章下面介绍，同时这篇文章借鉴了
https://blog.csdn.net/weixin_39854288/article/details/110725664【java监听mysql数据表变化_[Spring cloud 一步步实现广告系统] 15. 使用开源组件监听Binlog 实现增量索引准备】
https://lzhcode.blog.csdn.net/article/details/108697491【基于SpringCloud+BinLog的广告系统设计总结】
我在里面加上了线程池，然后对各个数据进行处理。
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.cqsym</groupId>
<artifactId>nbigscreen</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>2.6.15</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<!--lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</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>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-json</artifactId>
</dependency>
<!-- jackson-datatype-jsr310 -->
<dependency>
<groupId>com.fasterxml.jackson.datatype</groupId>
<artifactId>jackson-datatype-jsr310</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
<!-- optional=true,依赖不会传递，该项目依赖devtools；之后依赖myboot项目的项目如果想要使用devtools，需要重新引入 -->
<scope>true</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-jdbc -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-redis -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/com.google.guava/guava -->
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>32.1.3-jre</version>
</dependency>
<!-- https://mvnrepository.com/artifact/cn.hutool/hutool-all -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.10</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.83</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba.fastjson2/fastjson2 -->
<dependency>
<groupId>com.alibaba.fastjson2</groupId>
<artifactId>fastjson2</artifactId>
<version>2.0.47</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.zendesk/mysql-binlog-connector-java -->
<dependency>
<groupId>com.zendesk</groupId>
<artifactId>mysql-binlog-connector-java</artifactId>
<version>0.29.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.12.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-codec/commons-codec -->
<dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
<version>1.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.13.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-collections/commons-collections -->
<dependency>
<groupId>commons-collections</groupId>
<artifactId>commons-collections</artifactId>
<version>3.2.2</version>
</dependency>
<!-- httpclient -->
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
<version>4.5.5</version>
</dependency>
<!-- com.squareup.okhttp3/okhttp -->
<dependency>
<groupId>com.squareup.okhttp3</groupId>
<artifactId>okhttp</artifactId>
<version>4.12.0</version>
</dependency>
</dependencies>
<build>
<finalName>nbigscreen</finalName>
<resources>
<resource>
<directory>src/main/resources</directory>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>2.6.15</version>
<configuration>
<fork>true</fork>
<includeSystemScope>true</includeSystemScope>
<!--fork : 如果没有该项配置，肯呢个devtools不会起作用，即应用不会restart -->
<!--这里写上main方法所在类的路径-->
<mainClass>com.cqsym.nbigscreen.Application</mainClass>
</configuration>
<executions>
<execution>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.22.2</version>
<configuration>
<skip>true</skip>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.11.0</version>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-resources-plugin</artifactId>
<version>3.3.1</version>
<configuration>
<encoding>UTF-8</encoding>
<!-- 过滤后缀为pem、pfx的证书文件 -->
<nonFilteredFileExtensions>
<nonFilteredFileExtension>p12</nonFilteredFileExtension>
<nonFilteredFileExtension>cer</nonFilteredFileExtension>
<nonFilteredFileExtension>pem</nonFilteredFileExtension>
<nonFilteredFileExtension>pfx</nonFilteredFileExtension>
</nonFilteredFileExtensions>
</configuration>
</plugin>
</plugins>
</build>
</project>application-dev.properties
#-----------------自定义信息配置---------------------
com.imddy.layuiadmin.title=BOOT
com.imddy.layuiadmin.description=学习一下BOOT是什么
#-----------------自定义信息配置---------------------
executeTask=2
#----------数据库基础配置--------------------
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#spring.datasource.url=jdbc:mysql://127.0.0.1:3306/layuiadmin2?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=UTC
#spring.datasource.url=jdbc:mysql://127.0.0.1:3306/layuiadmin2?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull
#spring.datasource.url=jdbc:mysql://127.0.0.1:3306/twmshelp?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
#spring.datasource.username=root
#spring.datasource.password=root__
#spring.datasource.name=HikaraPool-100
#----------数据库连接池基础配置--------------------
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
# 指定连接池的名称 - 默认自动生成
spring.datasource.hikari.pool-name=HikaraPool-1
# 如果你的驱动程序支持JDBC4，强烈建议不要设置此属性。
#spring.datasource.hikari.connection-test-query=select 1
# 连接超时时间 - 默认值：30秒。
spring.datasource.hikari.connection-timeout=30000
# 连接池中允许闲置的最长时间 - 默认值：10分钟
spring.datasource.hikari.idle-timeout=600000
# 一个连接生命时长（毫秒），超时而没被使用则被释放 - 默认值：30分钟
spring.datasource.hikari.max-lifetime=1800000
# 连接池中允许的最大连接数，包括闲置和使用中的连接 - 默认值：10
spring.datasource.hikari.maximum-pool-size=100
# 连接池中允许的最小空闲连接数 - 默认值：10。
spring.datasource.hikari.minimum-idle=10
# 连接被测试活动的最长时间 - 默认值：5秒。
spring.datasource.hikari.validation-timeout=5000
spring.jpa.show-sql=true
spring.jpa.open-in-view=false
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.primary.url=jdbc:mysql://127.0.0.1:3306/nbigscreen?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.primary.username=root
spring.datasource.primary.password=root__
# 指定为HikariDataSource
spring.datasource.primary.type=com.zaxxer.hikari.HikariDataSource
# hikari连接池配置 对应 HikariConfig 配置属性类
spring.datasource.primary.hikari.pool-name=HikariCP-Primary
#最小空闲连接数
spring.datasource.primary.hikari.minimum-idle=5
# 空闲连接存活最大时间，默认10分钟
spring.datasource.primary.hikari.idle-timeout=600000
# 连接池最大连接数，默认是10
spring.datasource.primary.hikari.maximum-pool-size=10
# 此属性控制从池返回的连接的默认自动提交行为,默认值：true
spring.datasource.primary.hikari.auto-commit=true
# 此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认30分钟
spring.datasource.primary.hikari.max-lifetime=1800000
# 数据库连接超时时间,默认30秒
spring.datasource.primary.hikari.connection-timeout=30000
# 连接测试query,如果你的驱动程序支持JDBC4，强烈建议不要设置此属性。
#spring.datasource.primary.hikari.connection-test-query=SELECT 1
spring.datasource.second.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.second.url=jdbc:mysql://192.168.203.150:3306/twms?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.second.username=aliyun_root
spring.datasource.second.password=root__
spring.datasource.second.host=192.168.203.150
spring.datasource.second.port=3306
# 指定为HikariDataSource
spring.datasource.second.type=com.zaxxer.hikari.HikariDataSource
# hikari连接池配置 对应 HikariConfig 配置属性类
spring.datasource.second.hikari.pool-name=HikariCP-Second
#最小空闲连接数
spring.datasource.second.hikari.minimum-idle=5
# 空闲连接存活最大时间，默认10分钟
spring.datasource.second.hikari.idle-timeout=600000
# 连接池最大连接数，默认是10
spring.datasource.second.hikari.maximum-pool-size=10
# 此属性控制从池返回的连接的默认自动提交行为,默认值：true
spring.datasource.second.hikari.auto-commit=true
# 此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认30分钟
spring.datasource.second.hikari.max-lifetime=1800000
# 数据库连接超时时间,默认30秒
spring.datasource.second.hikari.connection-timeout=30000
# 连接测试query,如果你的驱动程序支持JDBC4，强烈建议不要设置此属性。
#spring.datasource.second.hikari.connection-test-query=SELECT 1
## redis 配置
spring.redis.host=127.0.0.1
spring.redis.port=6379
spring.redis.database=0
spring.redis.timeout=10s
spring.redis.lettuce.pool.min-idle=0
spring.redis.lettuce.pool.max-idle=8
spring.redis.lettuce.pool.max-active=8
spring.redis.lettuce.pool.max-wait=-1ms这里配置了redis，但实际项目中没有使用。
NbigscreenController.java
package com.cqsym.nbigscreen.controller;
import com.cqsym.nbigscreen.primary.entity.TOwnerBase;
import com.cqsym.nbigscreen.service.BinaryLogService;
import com.cqsym.nbigscreen.service.TOwnerBaseService;
import com.cqsym.nbigscreen.service.XianluMingchengService;
import com.cqsym.nbigscreen.service.ZaituDingdanNumSerivce;
import com.cqsym.nbigscreen.service.ZuoyeNumService;
import com.cqsym.nbigscreen.utils.AjaxResult;
import com.cqsym.nbigscreen.vo.DaquNumVo;
import com.cqsym.nbigscreen.vo.OwnerBaseNumVo;
import com.cqsym.nbigscreen.vo.XianluMingchengVo;
import com.cqsym.nbigscreen.vo.ZaituDingdanVo;
import com.cqsym.nbigscreen.vo.ZhanbiVo;
import com.cqsym.nbigscreen.vo.ZuoyeShishiQingkuangVo;
import com.fasterxml.jackson.core.JsonProcessingException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.List;
import java.util.Map;
@Controller
@RequestMapping("/nbigscreen")
public class NbigscreenController {
private static final Logger log = LoggerFactory.getLogger(NbigscreenController.class);
@Autowired
private BinaryLogService binaryLogService;
@Autowired
private ZuoyeNumService zuoyeNumService;
@Autowired
private XianluMingchengService xianluMingchengService;
@Autowired
private ZaituDingdanNumSerivce zaituDingdanNumSerivce;
@Autowired
private TOwnerBaseService tOwnerBaseService;
@ResponseBody
@RequestMapping("/index")
public AjaxResult index() {
log.info("index... ");
return AjaxResult.success("index");
}
@ResponseBody
@RequestMapping("/on")
public AjaxResult on() {
log.info("on... ");
try {
binaryLogService.start();
} catch (Exception exception) {
return AjaxResult.error(exception.getMessage());
}
return AjaxResult.success();
}
@ResponseBody
@RequestMapping("/off")
public AjaxResult off() {
log.info("off... ");
try {
binaryLogService.stop();
} catch (Exception exception) {
return AjaxResult.error(exception.getMessage());
}
return AjaxResult.success();
}
@ResponseBody
@RequestMapping("/getZuoyeNum")
public AjaxResult getZuoyeNum() {
log.info("getZuoyeNum... ");
Map map = zuoyeNumService.getZuoyeNum();
return AjaxResult.success(map);
}
@ResponseBody
@RequestMapping("/getBanshichuZuoyeNum")
public AjaxResult getBanshichuZuoyeNum() {
log.info("getBanshichuZuoyeNum... ");
List<OwnerBaseNumVo> list = zuoyeNumService.getBanshichuZuoyeNum();
return AjaxResult.success(list);
}
@ResponseBody
@RequestMapping("/getDaquZuoyeNum")
public AjaxResult getDaquZuoyeNum() {
log.info("getDaquZuoyeNum... ");
List<DaquNumVo> list = zuoyeNumService.getDaquZuoyeNum();
return AjaxResult.success(list);
}
@ResponseBody
@RequestMapping("/getShishiZuoyeQingkuang")
public AjaxResult getShishiZuoyeQingkuang() {
log.info("getShishiZuoyeQingkuang... ");
List<ZuoyeShishiQingkuangVo> list = zuoyeNumService.getShishiZuoyeQingkuang();
return AjaxResult.success(list);
}
@ResponseBody
@RequestMapping("/getXianluMingcheng")
public AjaxResult getXianluMingcheng() {
log.info("getXianluMingcheng... ");
List<XianluMingchengVo> list = xianluMingchengService.getXianluMingcheng();
return AjaxResult.success(list);
}
@ResponseBody
@RequestMapping("/getZaituDingdanNumForEveryDay")
public AjaxResult getZaituDingdanNumForEveryDay() throws JsonProcessingException {
log.info("getZaituDingdanNumForEveryDay... ");
List<ZaituDingdanVo> list = zaituDingdanNumSerivce.getZaituDingdanNumForEveryDay();
return AjaxResult.success(list);
}
@ResponseBody
@RequestMapping("/getTOwnerBaseAll")
public AjaxResult getTOwnerBaseAll() {
log.info("getTOwnerBaseAll... ");
List<TOwnerBase> list = tOwnerBaseService.findAll();
return AjaxResult.success(list);
}
@ResponseBody
@RequestMapping("/getZuoyeZhanbi")
public AjaxResult getZuoyeZhanbi() {
log.info("getZuoyeZhanbi... ");
List<ZhanbiVo> list = zuoyeNumService.getZuoyeZhanbi();
return AjaxResult.success(list);
}
}二、各种Service
ZuoyeNumService.java
package com.cqsym.nbigscreen.service;
import com.cqsym.nbigscreen.config.DataSourceRepository;
import com.cqsym.nbigscreen.constant.ZuoyeNumConstants;
import com.cqsym.nbigscreen.enums.OperateTypeEnum;
import com.cqsym.nbigscreen.vo.DaquNumVo;
import com.cqsym.nbigscreen.vo.DaquOwnerBaseNumVo;
import com.cqsym.nbigscreen.vo.OwnerBaseNumVo;
import com.cqsym.nbigscreen.vo.ZhanbiVo;
import com.cqsym.nbigscreen.vo.ZuoyeShishiQingkuangVo;
import com.cqsym.nbigscreen.vo.ZuoyeShishiQingkuangVo1;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import static com.cqsym.nbigscreen.cache.AppMap.BanshichuDaquCacheMap;
import static com.cqsym.nbigscreen.cache.AppMap.BanshichuZuoyeNumCacheMap;
import static com.cqsym.nbigscreen.cache.AppMap.OwnerBaseIdNameCacheName;
import static com.cqsym.nbigscreen.cache.AppMap.ZuoyeNumCacheMap;
import static com.cqsym.nbigscreen.cache.AppMap.ZuoyeShishiQingkuang;
@Service
public class ZuoyeNumService {
private static final Logger log = LoggerFactory.getLogger(ZuoyeNumService.class);
@Autowired
@Qualifier(value="secondDataSourceRepository")
private DataSourceRepository secondDataSourceRepository;
@Autowired
private ObjectMapper objectMapper;
public void init() {
log.info("ZuoyeNumService init() 开始 ... ");
log.info("initZuoyeNumCacheMap ... ");
initZuoyeNumCacheMap();
log.info("getDayZuoyeNum ... ");
getDayZuoyeNum();
log.info("getMonthZuoyeNum ... ");
getMonthZuoyeNum();
log.info("getYearZuoyeNum ... ");
getYearZuoyeNum();
log.info("getGongsiYearNum ... ");
getGongsiYearNum();
log.info("getGongsiMonthNum ... ");
getGongsiMonthNum();
log.info("getGongsiDayNum ... ");
getGongsiDayNum();
log.info("ZuoyeNumService init() complate完成 ... ");
}
public void initZuoyeNumCacheMap() {
ZuoyeNumCacheMap.clear();
ZuoyeNumCacheMap.put(ZuoyeNumConstants.GongsiYearTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.GongsiMonthTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.GongsiDayTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JiecheduanboYearTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JiecheduanboMonthTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JiecheduanboDayTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.ZhuangcheYearTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.ZhuangcheMonthTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.ZhuangcheDayTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.XiecheYearTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.XiecheMonthTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.XiecheDayTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.PeisongYearTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.PeisongMonthTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.PeisongDayTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.GongluzhifaYearTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.GongluzhifaMonthTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.GongluzhifaDayTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.YikuduanboYearTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.YikuduanboMonthTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.YikuduanboDayTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JiaguYearTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JiaguMonthTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JiaguDayTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JieguYearTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JieguMonthTotal, 0L);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JieguDayTotal, 0L);
}
public void getYearZuoyeNum() {
DateTimeFormatter datetimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime yearStart = LocalDateTime.now().withDayOfYear(1).withHour(0).withMinute(0).withSecond(0).withNano(0);
String yearStartStr = yearStart.format(datetimeFormatter);
List<Map<String, Object>> list = getZuoyeNum(yearStartStr);
if (list != null && list.size() > 0) {
for (Map<String, Object> map : list) {
switch (map.get("tddOperationType").toString()) {
case "1":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JiecheduanboYearTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "2":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.ZhuangcheYearTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "3":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.XiecheYearTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "4":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JiaguYearTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "5":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JieguYearTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "10":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.PeisongYearTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "11":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.YikuduanboYearTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "15":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.GongluzhifaYearTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
}
}
}
}
public void getMonthZuoyeNum() {
DateTimeFormatter datetimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime monthStart = LocalDateTime.now().with(TemporalAdjusters.firstDayOfMonth()).withHour(0).withMinute(0).withSecond(0).withNano(0);
String monthStartStr = monthStart.format(datetimeFormatter);
List<Map<String, Object>> list = getZuoyeNum(monthStartStr);
if (list != null && list.size() > 0) {
for (Map<String, Object> map : list) {
switch (map.get("tddOperationType").toString()) {
case "1":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JiecheduanboMonthTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "2":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.ZhuangcheMonthTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "3":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.XiecheMonthTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "4":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JiaguMonthTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "5":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JieguMonthTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "10":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.PeisongMonthTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "11":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.YikuduanboMonthTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "15":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.GongluzhifaMonthTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
}
}
}
}
public void getDayZuoyeNum() {
DateTimeFormatter datetimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime dayStart = LocalDateTime.now().withHour(0).withMinute(0).withSecond(0).withNano(0);
String dayStartStr = dayStart.format(datetimeFormatter);
List<Map<String, Object>> list = getZuoyeNum(dayStartStr);
if (list != null && list.size() > 0) {
for (Map<String, Object> map : list) {
switch (map.get("tddOperationType").toString()) {
case "1":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JiecheduanboDayTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "2":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.ZhuangcheDayTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "3":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.XiecheDayTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "4":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JiaguDayTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "5":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.JieguDayTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "10":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.PeisongDayTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "11":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.YikuduanboDayTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
case "15":
ZuoyeNumCacheMap.put(ZuoyeNumConstants.GongluzhifaDayTotal, Long.valueOf(String.valueOf(map.get("operationNum"))));
break;
}
}
}
}
private List<Map<String, Object>> getZuoyeNum(String zuoyeshijian) {
try {
String sql = "SELECT tdd_operation_type AS tddOperationType, COUNT(1) AS operationNum " +
"FROM t_dispatch_detail " +
"WHERE tdd_status = 2 " +
"AND tdd_operation_end_time >= ? " +
"AND dr = 0 " +
"GROUP BY tdd_operation_type ";
List<Map<String, Object>> list = secondDataSourceRepository.queryForListMap(sql, zuoyeshijian);
return list;
} catch (Exception e) {
log.error("getZuoyeNum error", e);
return null;
}
}
public void getGongsiYearNum() {
DateTimeFormatter datetimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime yearStart = LocalDateTime.now().withDayOfYear(1).withHour(0).withMinute(0).withSecond(0).withNano(0);
String yearStartStr = yearStart.format(datetimeFormatter);
Long gongsiYearNum = getGongsiNum(yearStartStr);
log.info("gongsiYearNum:{}", gongsiYearNum);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.GongsiYearTotal, gongsiYearNum);
}
public void getGongsiMonthNum() {
DateTimeFormatter datetimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime monthStart = LocalDateTime.now().with(TemporalAdjusters.firstDayOfMonth()).withHour(0).withMinute(0).withSecond(0).withNano(0);
String monthStartStr = monthStart.format(datetimeFormatter);
Long gongsiMonthNum = getGongsiNum(monthStartStr);
log.info("gongsiMonthNum:{}", gongsiMonthNum);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.GongsiMonthTotal, gongsiMonthNum);
}
public void getGongsiDayNum() {
DateTimeFormatter datetimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime dayStart = LocalDateTime.now().withHour(0).withMinute(0).withSecond(0).withNano(0);
String dayStartStr = dayStart.format(datetimeFormatter);
Long gongsiDayNum = getGongsiNum(dayStartStr);
log.info("gongsiDayNum:{}", gongsiDayNum);
ZuoyeNumCacheMap.put(ZuoyeNumConstants.GongsiDayTotal, gongsiDayNum);
}
private Long getGongsiNum(String zuoyeshijian) {
String sql = "SELECT COUNT(1) AS operationNum " +
"FROM t_dispatch_detail " +
"WHERE tdd_status = 2 " +
"AND tdd_operation_end_time >= ? " +
"AND dr = 0";
Map map = secondDataSourceRepository.queryForMap(sql, zuoyeshijian);
if (null != map ) {
return (Long) map.getOrDefault("operationNum", 0L);
};
return 0L;
}
public void initBanshichuZuoyeNum() {
DateTimeFormatter datetimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime yearStart = LocalDateTime.now().withDayOfYear(1).withHour(0).withMinute(0).withSecond(0).withNano(0);
String yearStartStr = yearStart.format(datetimeFormatter);
String sql = "SELECT tdd_owner_base AS tddOwnerBase, COUNT(*) AS totalNum " +
"FROM t_dispatch_detail " +
"WHERE tdd_status = 2 " +
"AND tdd_operation_end_time >= ? " +
"AND dr = 0 " +
"GROUP BY tdd_owner_base ";
List<OwnerBaseNumVo> ownerBaseNumVoList =secondDataSourceRepository.queryForList(sql, OwnerBaseNumVo.class, yearStartStr);
Map<Integer, Long> ownerBaseNumVoMap = new HashMap<>();
if (ownerBaseNumVoList != null && ownerBaseNumVoList.size() > 0) {
ownerBaseNumVoList.forEach(ownerBaseNumVo -> {
ownerBaseNumVoMap.put(ownerBaseNumVo.getTddOwnerBase(), ownerBaseNumVo.getTotalNum());
});
}
log.info("ownerBaseNumVoMap:{}", objectMapper.valueToTree(ownerBaseNumVoMap));
try {
log.info("ownerBaseNumVoMap: " + objectMapper.writeValueAsString(ownerBaseNumVoMap));
} catch (JsonProcessingException e) {
log.error("ownerBaseNumVoMap error", e.getMessage());
}
BanshichuZuoyeNumCacheMap.putAll(ownerBaseNumVoMap);
}
public List<OwnerBaseNumVo> getBanshichuZuoyeNum() {
Map<Integer, Long> ownerBaseNumVoMap = BanshichuZuoyeNumCacheMap.asMap();
List<OwnerBaseNumVo> ownerBaseNumVoList = new ArrayList<>();
if (ownerBaseNumVoMap != null && ownerBaseNumVoMap.size() > 0) {
ownerBaseNumVoMap.forEach((key, value) -> {
OwnerBaseNumVo ownerBaseNumVo = new OwnerBaseNumVo();
ownerBaseNumVo.setTddOwnerBase(key);
ownerBaseNumVo.setTotalNum(value);
String ownerBaseName = OwnerBaseIdNameCacheName.get(String.valueOf(key));
ownerBaseNumVo.setTddOwnerBaseName(ownerBaseName);
ownerBaseNumVoList.add(ownerBaseNumVo);
});
}
List<OwnerBaseNumVo> ownerBaseNumVoList1 = ownerBaseNumVoList.stream().sorted(Comparator.comparing(OwnerBaseNumVo::getTotalNum).reversed()).collect(Collectors.toList());
return ownerBaseNumVoList1;
}
public List<DaquNumVo> getDaquZuoyeNum() {
Map<Integer, Long> ownerBaseNumVoMap = BanshichuZuoyeNumCacheMap.asMap();
List<DaquOwnerBaseNumVo> daquOwnerBaseNumVoList = new ArrayList<>();
if (ownerBaseNumVoMap != null && ownerBaseNumVoMap.size() > 0) {
ownerBaseNumVoMap.forEach((key, value) -> {
DaquOwnerBaseNumVo daquOwnerBaseNumVo = new DaquOwnerBaseNumVo();
daquOwnerBaseNumVo.setTddOwnerBase(key);
daquOwnerBaseNumVo.setTotalNum(value);
String ownerBaseName = OwnerBaseIdNameCacheName.get(String.valueOf(key));
daquOwnerBaseNumVo.setTddOwnerBaseName(ownerBaseName);
String daquName = (String) BanshichuDaquCacheMap.get(ownerBaseName);
daquOwnerBaseNumVo.setDaquName(daquName);
daquOwnerBaseNumVoList.add(daquOwnerBaseNumVo);
});
}
Map<String,List<DaquOwnerBaseNumVo>> map = daquOwnerBaseNumVoList.stream().filter(daquOwnerBaseNumVo -> daquOwnerBaseNumVo.getDaquName() != null).collect(Collectors.groupingBy(daquOwnerBaseNumVo -> daquOwnerBaseNumVo.getDaquName()));
List<DaquNumVo> daquNumVoList = new ArrayList<>();
map.entrySet().forEach(entry -> {
DaquNumVo daquNumVo = new DaquNumVo();
daquNumVo.setDaquName(entry.getKey());
daquNumVo.setTotalNum(entry.getValue().stream().mapToLong(DaquOwnerBaseNumVo::getTotalNum).sum());
daquNumVoList.add(daquNumVo);
});
List<DaquNumVo> daquNumVoList1 = daquNumVoList.stream().sorted(Comparator.comparing(DaquNumVo::getTotalNum).reversed()).collect(Collectors.toList());
return daquNumVoList1;
}
public Map getZuoyeNum() {
Map map = new HashMap();
map = ZuoyeNumCacheMap.asMap();
return map;
}
public List<ZuoyeShishiQingkuangVo> getShishiZuoyeQingkuang() {
List<ZuoyeShishiQingkuangVo> zuoyeShishiQingkuangVoList = new ArrayList<>();
ZuoyeShishiQingkuang.iterator().forEachRemaining(zuoyeShishiQingkuangVo -> {
zuoyeShishiQingkuangVoList.add(zuoyeShishiQingkuangVo);
});
return zuoyeShishiQingkuangVoList;
}
public void initShishiZuoyeQingkuang() {
String sql = "SELECT tdd_owner_base, tdd_operation_type, tdd_vin, tdd_operation_end_time " +
"FROM t_dispatch_detail " +
"WHERE tdd_status = 2 " +
"AND dr = 0 " +
"ORDER BY tdd_id DESC " +
"LIMIT 50 ";
List<ZuoyeShishiQingkuangVo1> zuoyeShishiQingkuangVo1List = secondDataSourceRepository.queryForList(sql, ZuoyeShishiQingkuangVo1.class);
if (zuoyeShishiQingkuangVo1List != null && zuoyeShishiQingkuangVo1List.size() > 0) {
zuoyeShishiQingkuangVo1List.forEach(zuoyeShishiQingkuangVo1 -> {
ZuoyeShishiQingkuangVo zuoyeShishiQingkuangVo = new ZuoyeShishiQingkuangVo();
String ownerBaseName = OwnerBaseIdNameCacheName.get(String.valueOf(zuoyeShishiQingkuangVo1.getTddOwnerBase()));
zuoyeShishiQingkuangVo.setOwnerBaseName(ownerBaseName);
OperateTypeEnum operateTypeEnum = OperateTypeEnum.fromValue(zuoyeShishiQingkuangVo1.getTddOperationType());
zuoyeShishiQingkuangVo.setOperateType(operateTypeEnum);
zuoyeShishiQingkuangVo.setOperateTime(zuoyeShishiQingkuangVo1.getTddOperationEndTime());
zuoyeShishiQingkuangVo.setVin(zuoyeShishiQingkuangVo1.getTddVin());
ZuoyeShishiQingkuang.offer(zuoyeShishiQingkuangVo);
});
}
}
public List<ZhanbiVo> getZuoyeZhanbi() {
List<ZhanbiVo> zhanbiVoList = new ArrayList<>();
List<String> nameList = new ArrayList<>();
nameList.add("接车短驳");
nameList.add("装车");
nameList.add("卸车");
nameList.add("配送");
nameList.add("公路直发");
nameList.add("移库");
nameList.add("加固");
nameList.add("解固");
nameList.add("其他");
ZhanbiVo zhanbiVo0 = new ZhanbiVo();
zhanbiVo0.setName("接车短驳");
zhanbiVo0.setValue(ZuoyeNumCacheMap.get(ZuoyeNumConstants.JiecheduanboYearTotal));
zhanbiVoList.add(zhanbiVo0);
ZhanbiVo zhanbiVo1 = new ZhanbiVo();
zhanbiVo1.setName("装车");
zhanbiVo1.setValue(ZuoyeNumCacheMap.get(ZuoyeNumConstants.ZhuangcheYearTotal));
zhanbiVoList.add(zhanbiVo1);
ZhanbiVo zhanbiVo2 = new ZhanbiVo();
zhanbiVo2.setName("卸车");
zhanbiVo2.setValue(ZuoyeNumCacheMap.get(ZuoyeNumConstants.XiecheYearTotal));
zhanbiVoList.add(zhanbiVo2);
ZhanbiVo zhanbiVo3 = new ZhanbiVo();
zhanbiVo3.setName("配送");
zhanbiVo3.setValue(ZuoyeNumCacheMap.get(ZuoyeNumConstants.PeisongYearTotal));
zhanbiVoList.add(zhanbiVo3);
ZhanbiVo zhanbiVo4 = new ZhanbiVo();
zhanbiVo4.setName("公路直发");
zhanbiVo4.setValue(ZuoyeNumCacheMap.get(ZuoyeNumConstants.GongluzhifaYearTotal));
zhanbiVoList.add(zhanbiVo4);
ZhanbiVo zhanbiVo5 = new ZhanbiVo();
zhanbiVo5.setName("移库");
zhanbiVo5.setValue(ZuoyeNumCacheMap.get(ZuoyeNumConstants.YikuduanboYearTotal));
zhanbiVoList.add(zhanbiVo5);
ZhanbiVo zhanbiVo6 = new ZhanbiVo();
zhanbiVo6.setName("加固");
zhanbiVo6.setValue(ZuoyeNumCacheMap.get(ZuoyeNumConstants.JiaguYearTotal));
zhanbiVoList.add(zhanbiVo6);
ZhanbiVo zhanbiVo7 = new ZhanbiVo();
zhanbiVo7.setName("解固");
zhanbiVo7.setValue(ZuoyeNumCacheMap.get(ZuoyeNumConstants.JieguYearTotal));
zhanbiVoList.add(zhanbiVo7);
ZhanbiVo zhanbiVo8 = new ZhanbiVo();
zhanbiVo8.setName("其他");
long a1 = ZuoyeNumCacheMap.get(ZuoyeNumConstants.GongsiYearTotal);
zhanbiVo8.setValue(a1 - zhanbiVo0.getValue() - zhanbiVo1.getValue() - zhanbiVo2.getValue() - zhanbiVo3.getValue() - zhanbiVo4.getValue() - zhanbiVo5.getValue() - zhanbiVo6.getValue() - zhanbiVo7.getValue());
zhanbiVoList.add(zhanbiVo8);
String json = "";
try {
json = objectMapper.writeValueAsString(zhanbiVoList);
} catch (JsonProcessingException e) {
log.error("json转换异常:" + e.getMessage());
throw new RuntimeException(e);
}
log.info("zhanbiVoList: " + json);
return zhanbiVoList;
}
}ZaituDingdanNumSerivce.java
package com.cqsym.nbigscreen.service;
import com.cqsym.nbigscreen.config.DataSourceRepository;
import com.cqsym.nbigscreen.vo.ZaituDingdanVo;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import static com.cqsym.nbigscreen.cache.AppMap.ZaituDingdanNumForEveryDayCacheList;
@Service
public class ZaituDingdanNumSerivce {
private static final Logger log = LoggerFactory.getLogger(ZaituDingdanNumSerivce.class);
@Autowired
@Qualifier(value="secondDataSourceRepository")
private DataSourceRepository secondDataSourceRepository;
@Autowired
private ObjectMapper objectMapper;
public void initZaituDingdanNumForEveryDay() {
log.info("initZaituDingdanNumForEveryDay... ");
DateTimeFormatter datetimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime yearStart = LocalDateTime.now().withDayOfYear(1).withHour(0).withMinute(0).withSecond(0).withNano(0);
String yearStartStr = yearStart.format(datetimeFormatter);
String sql = "SELECT to_start_province, to_delivery_province , COUNT(*) AS count_num " +
"FROM t_order " +
"WHERE to_status = 3 " +
"AND to_order_date >= ? " +
"AND to_start_province != to_delivery_province " +
"GROUP BY to_start_province, to_delivery_province " +
"ORDER BY count_num DESC ";
List<ZaituDingdanVo> zaituDingdanVoList =secondDataSourceRepository.queryForList(sql, ZaituDingdanVo.class, yearStartStr);
String json = "";
try {
json = objectMapper.writeValueAsString(zaituDingdanVoList);
} catch (JsonProcessingException e) {
e.printStackTrace();
}
log.info("zaituDingdanVoList: " + json);
ZaituDingdanNumForEveryDayCacheList.clear();
ZaituDingdanNumForEveryDayCacheList.addAll(zaituDingdanVoList);
log.info("zaituDingdanVoList: " + json);
}
public List<ZaituDingdanVo> getZaituDingdanNumForEveryDay() throws JsonProcessingException {
log.info("getZaituDingdanNumForEveryDay... ");
List<ZaituDingdanVo> zaituDingdanVoListResult = new ArrayList<>();
List<ZaituDingdanVo> zaituDingdanVoList = ZaituDingdanNumForEveryDayCacheList;
zaituDingdanVoList.stream().forEach(zaituDingdanVo -> {
ZaituDingdanVo zaituDingdanVo1 = new ZaituDingdanVo();
zaituDingdanVo1.setCountNum(zaituDingdanVo.getCountNum());
String tosp = zaituDingdanVo.getToStartProvince().replace("省", "").replace("市", "").replace("自治区", "").replace("维吾尔", "").replace("壮族", "").replace("回族", "");
String todp = zaituDingdanVo.getToDeliveryProvince().replace("省", "").replace("市", "").replace("自治区", "").replace("维吾尔", "").replace("壮族", "").replace("回族", "");
zaituDingdanVo1.setToStartProvince(tosp);
zaituDingdanVo1.setToDeliveryProvince(todp);
zaituDingdanVoListResult.add(zaituDingdanVo1);
});
log.info("zaituDingdanVoList: " + objectMapper.writeValueAsString(zaituDingdanVoList));
return zaituDingdanVoListResult;
}
}XianluMingchengService.java
package com.cqsym.nbigscreen.service;
import com.cqsym.nbigscreen.config.DataSourceRepository;
import com.cqsym.nbigscreen.vo.XianluMingchengVo;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import static com.cqsym.nbigscreen.cache.AppMap.XianluMingchengCacheMap;
@Service
public class XianluMingchengService {
private static final Logger log = LoggerFactory.getLogger(XianluMingchengService.class);
@Autowired
private ObjectMapper objectMapper;
@Autowired
@Qualifier(value="secondDataSourceRepository")
private DataSourceRepository secondDataSourceRepository;
public void init() {
log.info("XianluMingchengService init() ... ");
initXianluMingcheng();
}
public void initXianluMingcheng() {
DateTimeFormatter datetimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime yearStart = LocalDateTime.now().withDayOfYear(1).withHour(0).withMinute(0).withSecond(0).withNano(0);
String yearStartStr = yearStart.format(datetimeFormatter);
String sql = "SELECT to_start_province AS toStartProvince, to_delivery_province AS toDeliveryProvince, COUNT(*) AS toTotalNum " +
"FROM t_order " +
"WHERE dr = 0 " +
"AND to_status != 5 " +
"AND createtime >= ? " +
"AND to_start_province IS NOT NULL " +
"AND to_delivery_province IS NOT NULL " +
"GROUP BY to_start_province,to_delivery_province " +
"ORDER BY toTotalNum DESC ";
List<XianluMingchengVo> list = secondDataSourceRepository.queryForList(sql, XianluMingchengVo.class, yearStartStr);
if (null != list && list.size() > 0) {
for (XianluMingchengVo xianluMingchengVo : list) {
log.info(xianluMingchengVo.getToStartProvince() + "--" + xianluMingchengVo.getToDeliveryProvince() + "--" + xianluMingchengVo.getToTotalNum());
XianluMingchengCacheMap.put(xianluMingchengVo.getToStartProvince() + "--" + xianluMingchengVo.getToDeliveryProvince(), xianluMingchengVo.getToTotalNum());
}
}
String mapStr = "";
try {
mapStr = objectMapper.writeValueAsString(XianluMingchengCacheMap);
} catch (Exception exception) {
exception.printStackTrace();
log.error("XianluMingchengService getXianluMingcheng() 发生了异常！ " + exception.getMessage());
}
log.info("XianluMingchengCacheMap: {}", mapStr );
}
public List<XianluMingchengVo> getXianluMingcheng() {
Map<String, Long> map = XianluMingchengCacheMap.asMap();
List<XianluMingchengVo> list = new ArrayList<>();
map.entrySet().forEach(entry -> {
XianluMingchengVo xianluMingchengVo = new XianluMingchengVo();
String key1 = entry.getKey().replace("省", "").replace("市", "").replace("自治区", "").replace("维吾尔", "").replace("壮族", "").replace("回族", "");
log.info("key:{},value:{}", key1, entry.getValue());
xianluMingchengVo.setXianluMingcheng(key1);
xianluMingchengVo.setToTotalNum(entry.getValue());
list.add(xianluMingchengVo);
});
List<XianluMingchengVo> list1 = list.stream().sorted((o1, o2) -> o2.getToTotalNum().compareTo(o1.getToTotalNum())).collect(Collectors.toList());
log.info("list1:{}", list1);
return list1;
}
}TOwnerBaseService.java
package com.cqsym.nbigscreen.service;
import com.cqsym.nbigscreen.primary.entity.TOwnerBase;
import com.cqsym.nbigscreen.primary.repository.TOwnerBaseRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.List;
@Service
public class TOwnerBaseService {
private static final Logger log = LoggerFactory.getLogger(TOwnerBaseService.class);
@Autowired
private TOwnerBaseRepository tOwnerBaseRepository;
public List<TOwnerBase> findAll() {
return tOwnerBaseRepository.findAll();
}
}BinaryLogService.java
package com.cqsym.nbigscreen.service;
import com.cqsym.nbigscreen.listener.TDispatchDetailListener;
import com.cqsym.nbigscreen.listener.TOrderListener;
import com.cqsym.nbigscreen.sdk.AggregationListener;
import com.cqsym.nbigscreen.sdk.TemplateHolder;
import com.github.shyiko.mysql.binlog.BinaryLogClient;
import com.github.shyiko.mysql.binlog.event.deserialization.EventDeserializer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;
import javax.sql.DataSource;
import java.io.IOException;
@Service
public class BinaryLogService {
private static final Logger log = LoggerFactory.getLogger(BinaryLogService.class);
@Autowired
@Qualifier(value="primaryDataSource")
private DataSource primaryDataSource;
@Autowired
@Qualifier(value="secondDataSource")
private DataSource secondDataSource;
@Autowired
@Qualifier(value="primaryJdbcTemplate")
private JdbcTemplate primaryJdbcTemplate;
@Autowired
@Qualifier(value="secondJdbcTemplate")
private JdbcTemplate secondJdbcTemplate;
@Value("${spring.datasource.second.host}")
private String host;
@Value("${spring.datasource.second.port}")
private Integer port;
@Value("${spring.datasource.second.username}")
private String username;
@Value("${spring.datasource.second.password}")
private String password;
@Autowired
private TOrderListener tOrderListener;
@Autowired
private TDispatchDetailListener tDispatchDetailListener;
@Autowired
@Qualifier(value="aggregationListenerThreadPoolTaskExecutor")
private ThreadPoolTaskExecutor aggregationListenerThreadPoolTaskExecutor;
private BinaryLogClient client;
public void start() {
client = new BinaryLogClient(host, port, username, password);
// 反序列化配置
EventDeserializer eventDeserializer = new EventDeserializer();
eventDeserializer.setCompatibilityMode(EventDeserializer.CompatibilityMode.DATE_AND_TIME_AS_LONG
// EventDeserializer.CompatibilityMode.CHAR_AND_BINARY_AS_BYTE_ARRAY
);
// 设置反序列化配置
client.setEventDeserializer(eventDeserializer);
// 设置自己的client作为服务器的id
client.setServerId(30);
// 可选，设置start fileName+position
// client.setBinlogFilename("master-bin.000080");
// client.setBinlogPosition(219);
TemplateHolder templateHolder = new TemplateHolder(secondJdbcTemplate);
templateHolder.init();
AggregationListener aggregationListener = new AggregationListener(templateHolder);
aggregationListener.register("twms", "t_order", tOrderListener);
aggregationListener.register("twms", "t_dispatch_detail", tDispatchDetailListener);
client.registerEventListener(aggregationListener);
try {
client.connect();
} catch (IOException e) {
e.printStackTrace();
}
log.info("BinaryLogClient start()完成。 ");
}
public void disconnect() {
try {
client.disconnect();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public void stop() {
try {
client.disconnect();
client = null;
} catch (IOException e) {
throw new RuntimeException(e);
}
log.info("BinaryLogClient stop()完成。 ");
}
}三、这里有2个业务相关多RUNNABLE
TOrderListenerTaskRunnable.java
package com.cqsym.nbigscreen.listener;
import com.cqsym.nbigscreen.cache.AppMap;
import com.cqsym.nbigscreen.sdk.BinlogRowData;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule;
import com.github.shyiko.mysql.binlog.event.EventType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import java.util.List;
import java.util.Map;
public class TOrderListenerTaskRunnable implements Runnable {
private static final Logger log = LoggerFactory.getLogger(TOrderListenerTaskRunnable.class);
private ObjectMapper objectMapper = new ObjectMapper().registerModule(new JavaTimeModule());
private BinlogRowData eventData;
public TOrderListenerTaskRunnable(BinlogRowData eventData) {
this.eventData = eventData;
}
@Override
public void run() {
log.info("--------TOrderListenerTaskRunnable---run------");
log.info(eventData.toString());
try {
log.info(objectMapper.writeValueAsString(eventData));
} catch (JsonProcessingException e) {
throw new RuntimeException(e);
}
handleBinlogRowData(eventData);
}
private void handleBinlogRowData(BinlogRowData eventData) {
if (eventData.getEventType().equals(EventType.WRITE_ROWS)) {
List<Map<String, String>> afterList = eventData.getAfter();
if (CollectionUtils.isEmpty(afterList)) {
return ;
}
log.info("after List长度:{}", afterList.size());
afterList.forEach(row -> {
Long num = 0L;
log.info("after-row:{}", row.toString());
log.info("to_start_province:{}", row.get("to_start_province").toString());
log.info("to_delivery_province:{}", row.get("to_delivery_province").toString());
num = (Long) AppMap.XianluMingchengCacheMap.get(row.get("to_start_province").toString()+"--"+row.get("to_delivery_province").toString());
if (num == null && num == 0) {
num = AppMap.XianluMingchengCacheMap.addAndGet(row.get("to_start_province").toString()+"--"+row.get("to_delivery_province").toString(), 1L);
log.info("新增了一条线路： 【{}--{}】 数量为{}。 ", row.get("to_start_province").toString(), row.get("to_delivery_province").toString(), num);
} else {
num = AppMap.XianluMingchengCacheMap.incrementAndGet(row.get("to_start_province").toString()+"--"+row.get("to_delivery_province").toString());
log.info("线路： 【{}--{}】 数量为{}。 ", row.get("to_start_province").toString(), row.get("to_delivery_province").toString(), num);
}
});
} else if (EventType.UPDATE_ROWS.equals(eventData.getEventType())) {
} else if (EventType.DELETE_ROWS.equals(eventData.getEventType())) {
}
}
}TDispatchDetailListenerTaskRunnable.java
package com.cqsym.nbigscreen.listener;
import com.cqsym.nbigscreen.cache.AppMap;
import com.cqsym.nbigscreen.constant.AppConstants;
import com.cqsym.nbigscreen.constant.ZuoyeNumConstants;
import com.cqsym.nbigscreen.enums.OperateTypeEnum;
import com.cqsym.nbigscreen.sdk.BinlogRowData;
import com.cqsym.nbigscreen.vo.ZuoyeShishiQingkuangVo;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule;
import com.github.shyiko.mysql.binlog.event.EventType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.List;
import java.util.Map;
public class TDispatchDetailListenerTaskRunnable implements Runnable {
private static final Logger log = LoggerFactory.getLogger(TDispatchDetailListenerTaskRunnable.class);
private ObjectMapper objectMapper = new ObjectMapper().registerModule(new JavaTimeModule());
private BinlogRowData eventData;
public TDispatchDetailListenerTaskRunnable(BinlogRowData eventData) {
this.eventData = eventData;
}
@Override
public void run() {
log.info("--------TDispatchDetailListenerTaskRunnable---run------");
log.info(eventData.toString());
try {
log.info(objectMapper.writeValueAsString(eventData));
} catch (JsonProcessingException e) {
throw new RuntimeException(e);
}
handleBinlogRowData(eventData);
}
private void handleBinlogRowData(BinlogRowData eventData) {
if (EventType.UPDATE_ROWS.equals(eventData.getEventType()) || EventType.WRITE_ROWS.equals(eventData.getEventType())) {
List<Map<String, String>> afterList = eventData.getAfter();
if (CollectionUtils.isEmpty(afterList)) {
return ;
}
log.info("after List长度:{}", afterList.size());
afterList.forEach(row -> {
log.info("after row:{}", row.toString());
if (row.get("tdd_status").equals("2") ) {
log.info("作业tdd_operation_type:{}", row.get("tdd_operation_type").toString());
/** 完成作业数据量的更新 */
handleZuoyeNum(row);
/** 添加作业实时情况 */
handleZuoyeShishiQiankuang(row);
/** 添加作业到办事处 */
handleZuoyeNumAddToOwnerBase(row);
}
});
} else if (EventType.DELETE_ROWS.equals(eventData.getEventType())) {
}
}
/** 完成作业数据量的更新 */
public void handleZuoyeNum(Map<String, String> row) {
switch (row.get("tdd_operation_type").toString()) {
case "1":
handleZuoyeCacheNum(ZuoyeNumConstants.JiecheduanboDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.JiecheduanboMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.JiecheduanboYearTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiYearTotal);
break;
case "2":
handleZuoyeCacheNum(ZuoyeNumConstants.ZhuangcheDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.ZhuangcheMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.ZhuangcheYearTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiYearTotal);
break;
case "3":
handleZuoyeCacheNum(ZuoyeNumConstants.XiecheDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.XiecheMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.XiecheYearTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiYearTotal);
break;
case "4":
handleZuoyeCacheNum(ZuoyeNumConstants.JiaguDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.JiaguMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.JiaguYearTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiYearTotal);
break;
case "5":
handleZuoyeCacheNum(ZuoyeNumConstants.JieguDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.JieguMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.JieguYearTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiYearTotal);
break;
case "10":
handleZuoyeCacheNum(ZuoyeNumConstants.PeisongDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.PeisongMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.PeisongYearTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiYearTotal);
break;
case "11":
handleZuoyeCacheNum(ZuoyeNumConstants.YikuduanboDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.YikuduanboMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.YikuduanboYearTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiYearTotal);
break;
case "15":
handleZuoyeCacheNum(ZuoyeNumConstants.GongluzhifaDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongluzhifaMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongluzhifaYearTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiYearTotal);
break;
default:
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiDayTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiMonthTotal);
handleZuoyeCacheNum(ZuoyeNumConstants.GongsiYearTotal);
//break;
}
}
private void handleZuoyeCacheNum(String str) {
AppMap.ZuoyeNumCacheMap.incrementAndGet(str);
}
/** 添加作业实时情况 */
private void handleZuoyeShishiQiankuang(Map<String, String> row) {
/** 添加作业实时情况 */
log.info("作业实时情况after row:{}", row.toString());
// log.info("作业实时情况tdd_operation_type: " + row.get("tdd_operation_type").toString());
// log.info("作业实时情况tdd_owner_base: " + row.get("tdd_owner_base").toString());
// log.info("作业实时情况tdd_vin: " + row.get("tdd_vin").toString());
// log.info("作业实时情况tdd_operation_end_time: " + row.get("tdd_operation_end_time").toString());
// 28,800 8小时，要减去
String a1 = row.get("tdd_owner_base").toString();
String a2 = row.get("tdd_operation_type").toString();
String a3 = row.get("tdd_vin").toString();
Long a40 = Long.valueOf(row.get("tdd_operation_end_time").toString())-Long.valueOf(28800000);
LocalDateTime localDateTimea4 = LocalDateTime.ofInstant(Instant.ofEpochMilli(Long.valueOf(row.get("tdd_operation_end_time").toString())), ZoneOffset.of("+0"));
// log.info("作业实时情况a1: " + a1);
// log.info("作业实时情况a2: " + a2);
// log.info("作业实时情况a3: " + a3);
// log.info("作业实时情况a4: " + localDateTimea4);
// log.info("作业实时情况now: " + LocalDateTime.now());
// Long l1 = localDateTimea4.toEpochSecond(ZoneOffset.of("+0"));
// Long l2 = LocalDateTime.now().toEpochSecond(ZoneOffset.of("+0"));
// Long l3 = Math.abs(l2 - l1);
// log.info("作业实时情况l2-l1时间差异: " + l3);
// if (l3 >= AppConstants.ZuoyeShijianCha) {
// log.info("作业实时情况4：" + "时间差异太大，不要了");
// return;
// }
// log.info("作业实时情况3：" + "算正常情况");
ZuoyeShishiQingkuangVo zuoyeShishiQingkuangVo = new ZuoyeShishiQingkuangVo();
String ownerBaseNamea1 = "";
ownerBaseNamea1 = AppMap.OwnerBaseIdNameCacheName.get(a1);
zuoyeShishiQingkuangVo.setOwnerBaseName(ownerBaseNamea1);
zuoyeShishiQingkuangVo.setOperateType(OperateTypeEnum.fromValue(Integer.valueOf(a2)));
zuoyeShishiQingkuangVo.setVin(a3);
//zuoyeShishiQingkuangVo.setOperateTime(localDateTimea4);
zuoyeShishiQingkuangVo.setOperateTime(LocalDateTime.now());
log.info("作业实时情况1：" + zuoyeShishiQingkuangVo);
String temp00 = "";
try {
temp00 = objectMapper.writeValueAsString(zuoyeShishiQingkuangVo);
} catch (JsonProcessingException e) {
log.info("作业实时情况2报错：报错信息" + e.getMessage());
}
log.info("作业实时情况2：" + temp00);
AppMap.ZuoyeShishiQingkuang.offer(zuoyeShishiQingkuangVo);
}
/** 添加作业到办事处 */
private void handleZuoyeNumAddToOwnerBase(Map<String, String> row) {
/** 添加作业到办事处 */
log.info("after row:{}", row.toString());
if (row.get("tdd_owner_base") != null) {
handleZuoyeNumAddToOwnerBase(Integer.valueOf(row.get("tdd_owner_base").toString()));
}
}
private void handleZuoyeNumAddToOwnerBase(Integer str) {
AppMap.BanshichuZuoyeNumCacheMap.incrementAndGet(str);
}
}下面2个listener主要用于启动多线程来处理调用上面的Runnable来处理数据
TDispatchDetailListener.java
package com.cqsym.nbigscreen.listener;
import com.cqsym.nbigscreen.sdk.BinlogRowData;
import com.cqsym.nbigscreen.sdk.Ilistener;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Component;
@Component
public class TDispatchDetailListener implements Ilistener {
private static final Logger log = LoggerFactory.getLogger(TDispatchDetailListener.class);
@Autowired
private ObjectMapper objectMapper;
@Autowired
@Qualifier("tDispatchDetailListenerThreadPoolTaskExecutor")
private ThreadPoolTaskExecutor tDispatchDetailListenerThreadPoolTaskExecutor;
@Override
public void onEvent(BinlogRowData eventData) {
log.info("--------TDispatchDetailListener---onEvent------");
log.info(eventData.toString());
try {
log.info(objectMapper.writeValueAsString(eventData));
} catch (JsonProcessingException e) {
throw new RuntimeException(e);
}
tDispatchDetailListenerThreadPoolTaskExecutor.submit(new TDispatchDetailListenerTaskRunnable(eventData));
}
}TOrderListener.java
package com.cqsym.nbigscreen.listener;
import com.cqsym.nbigscreen.sdk.BinlogRowData;
import com.cqsym.nbigscreen.sdk.Ilistener;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Component;
@Component
public class TOrderListener implements Ilistener {
private static final Logger log = LoggerFactory.getLogger(TOrderListener.class);
@Autowired
private ObjectMapper objectMapper;
@Autowired
@Qualifier("tOrderListenerThreadPoolTaskExecutor")
private ThreadPoolTaskExecutor tOrderListenerThreadPoolTaskExecutor;
@Override
public void onEvent(BinlogRowData eventData) {
log.info("--------TOrderListener---onEvent------");
log.info(eventData.toString());
try {
log.info(objectMapper.writeValueAsString(eventData));
} catch (JsonProcessingException e) {
throw new RuntimeException(e);
}
tOrderListenerThreadPoolTaskExecutor.execute(new TOrderListenerTaskRunnable(eventData));
}
}这里有2个Factory类没在该项目中没有使用
TDispatchDetailListenerTaskFactory.java
package com.cqsym.nbigscreen.listener;
import com.cqsym.nbigscreen.sdk.BinlogRowData;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
@Component
public class TDispatchDetailListenerTaskFactory {
private static final Logger log = LoggerFactory.getLogger(TDispatchDetailListenerTaskFactory.class);
public Runnable createTask(BinlogRowData eventData) {
Runnable runnable = new TDispatchDetailListenerTaskRunnable(eventData);
return runnable;
}
}TOrderListenerTaskFactory.java
package com.cqsym.nbigscreen.listener;
import com.cqsym.nbigscreen.sdk.BinlogRowData;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
@Component
public class TOrderListenerTaskFactory {
private static final Logger log = LoggerFactory.getLogger(TOrderListenerTaskFactory.class);
public Runnable createTask(BinlogRowData eventData) {
Runnable runnable = new TOrderListenerTaskRunnable(eventData);
return runnable;
}
}
四、这里是mysql-binlog-connector-java技术，我把所有用到的都放到了SDK包里了。
sdk包目录图片
AggregationListener.java
处理mysql-binlog-connector-java技术的接收到mysql-binlog后的处理，继承BinaryLogClient.EventListener，但我我这里把他当成一个聚合了各个子处理的聚合Listener。把上个章节的业务Listener通过register注册到该聚合Listener中来。
package com.cqsym.nbigscreen.sdk;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule;
import com.github.shyiko.mysql.binlog.BinaryLogClient;
import com.github.shyiko.mysql.binlog.event.DeleteRowsEventData;
import com.github.shyiko.mysql.binlog.event.Event;
import com.github.shyiko.mysql.binlog.event.EventData;
import com.github.shyiko.mysql.binlog.event.EventType;
import com.github.shyiko.mysql.binlog.event.TableMapEventData;
import com.github.shyiko.mysql.binlog.event.UpdateRowsEventData;
import com.github.shyiko.mysql.binlog.event.WriteRowsEventData;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
public class AggregationListener implements BinaryLogClient.EventListener {
private static final Logger log = LoggerFactory.getLogger(AggregationListener.class);
private String dbName;
private String tbName;
private Map<String, Ilistener> listenerMap = new HashMap<>();
private final TemplateHolder templateHolder;
public AggregationListener(TemplateHolder templateHolder) {
this.templateHolder = templateHolder;
}
private String genKey(String dbName, String tableName) {
return dbName + ":" + tableName;
}
public void register(String _dbName, String _tableName,
Ilistener ilistener) {
log.info("register : {}-{}", _dbName, _tableName);
this.listenerMap.put(genKey(_dbName, _tableName), ilistener);
}
@Override
public void onEvent(Event event) {
EventType type = event.getHeader().getEventType();
EventData data0 = event.getData();
log.info("Event type: {}", type);
log.info("Event data: {}", data0);
//数据库增删改之前，肯定有一个table_map event 的binlog
if (EventType.TABLE_MAP.equals(type)) {
TableMapEventData data = event.getData();
this.tbName = data.getTable();
this.dbName = data.getDatabase();
return;
}
//EXT_UPDATE_ROWS,EXT_WRITE_ROWS,EXT_DELETE_ROWS 是Mysql 8以上的type
if (!(EventType.UPDATE_ROWS.equals(type))
&& !(EventType.WRITE_ROWS.equals(type))
&& !(EventType.DELETE_ROWS.equals(type))
&& !(EventType.EXT_UPDATE_ROWS.equals(type))
&& !(EventType.EXT_WRITE_ROWS.equals(type))
&& !(EventType.EXT_DELETE_ROWS.equals(type))
) {
return;
}
// 检查表名和数据库名是否已经正确填充
if (StringUtils.isEmpty(dbName) || StringUtils.isEmpty(tbName)) {
log.error("Meta data got error. tablename:{},database:{}", tbName, dbName);
return;
}
//找出对应数据表敏感的监听器
String key = genKey(this.dbName, this.tbName);
Ilistener ilistener = this.listenerMap.get(key);
if (null == ilistener) {
log.debug("skip {}", key);
}
log.info("trigger-event1:{}", type.name());
log.info("trigger-event2-dbName--tbName:{}--{}", dbName, tbName);
//log.info("trigger-event3-EventHeader--type:{}", event.getHeader().getEventType());
//log.info("trigger-event4-EventData: {}", event.getData());
//log.info("trigger-event4-EventData: {}", data0);
try {
BinlogRowData rowData = convertEventData2BinlogRowData(event.getData());
if (null == rowData) {
return;
}
rowData.setEventType(type);
ilistener.onEvent(rowData);
} catch (Exception e) {
e.printStackTrace();
log.error(e.getMessage());
} finally {
this.dbName = "";
this.tbName = "";
}
}
/**
* 解析Binlog数据到Java实体对象的映射
*
* @param data binlog
* @return java 对象
*/
private BinlogRowData convertEventData2BinlogRowData(EventData data) {
TableTemplate tableTemplate = templateHolder.getTable(tbName);
if (null == tableTemplate) {
log.warn("table {} not found.", tbName);
return null;
}
List<Map<String, String>> afterMapList = new ArrayList<>();
for (Serializable[] after : getAfterValues(data)) {
Map<String, String> afterMap = new HashMap<>();
int columnLength = after.length;
for (int i = 0; i < columnLength; ++i) {
//取出当前位置对应的列名
String colName = tableTemplate.getPosMap().get(i);
//如果没有，则说明不需要该列
if (null == colName) {
log.debug("ignore position: {}", i);
continue;
}
String colValue = after[i].toString();
afterMap.put(colName, colValue);
}
afterMapList.add(afterMap);
}
BinlogRowData binlogRowData = new BinlogRowData();
binlogRowData.setAfter(afterMapList);
binlogRowData.setTableTemplate(tableTemplate);
return binlogRowData;
}
/**
* 获取不同事件的变更后数据
* Add & Delete变更前数据假定为空
*/
private List<Serializable[]> getAfterValues(EventData eventData) {
if (eventData instanceof WriteRowsEventData) {
return ((WriteRowsEventData) eventData).getRows();
}
if (eventData instanceof UpdateRowsEventData) {
return ((UpdateRowsEventData) eventData).getRows()
.stream()
.map(Map.Entry::getValue)
.collect(Collectors.toList()
);
}
if (eventData instanceof DeleteRowsEventData) {
return ((DeleteRowsEventData) eventData).getRows();
}
return Collections.emptyList();
}
}template.json
该文件在resources资源目录下，为该sdk配置需要获取的数据的配置文件。
{
"database": "twms",
"tableList": [
{
"tableName": "t_order",
"level": 2,
"insert": [
{
"columnName": "to_id"
},
{
"columnName": "to_num"
},
{
"columnName": "to_source"
},
{
"columnName": "to_owner_division_name"
},
{
"columnName": "to_owner_division"
},
{
"columnName": "to_order_date"
},
{
"columnName": "to_status"
},
{
"columnName": "to_start_province"
},
{
"columnName": "to_delivery_province"
},
{
"columnName": "updatetime"
}
],
"update": [
{
"columnName": "to_id"
},
{
"columnName": "to_num"
},
{
"columnName": "to_source"
},
{
"columnName": "to_owner_division_name"
},
{
"columnName": "to_owner_division"
},
{
"columnName": "to_order_date"
},
{
"columnName": "to_status"
},
{
"columnName": "to_start_province"
},
{
"columnName": "to_delivery_province"
},
{
"columnName": "updatetime"
}
],
"delete": [
{
"columnName": "to_id"
}
]
},
{
"tableName": "t_dispatch_detail",
"level": 3,
"insert": [
{
"columnName": "tdd_id"
},
{
"columnName": "tdd_customer_order_num"
},
{
"columnName": "tdd_vin"
},
{
"columnName": "tdd_vehicle_brand"
},
{
"columnName": "tdd_status"
},
{
"columnName": "tdd_owner_base"
},
{
"columnName": "tdd_operation_type"
},
{
"columnName": "tdd_operation_end_time"
},
{
"columnName": "updatetime"
}
],
"update": [
{
"columnName": "tdd_id"
},
{
"columnName": "tdd_customer_order_num"
},
{
"columnName": "tdd_vin"
},
{
"columnName": "tdd_vehicle_brand"
},
{
"columnName": "tdd_status"
},
{
"columnName": "tdd_owner_base"
},
{
"columnName": "tdd_operation_type"
},
{
"columnName": "tdd_operation_end_time"
},
{
"columnName": "updatetime"
}
],
"delete": [
{
"columnName": "tdd_id"
}
]
}
]
}BinlogTemplate.java
定义模版文件对应的实体
package com.cqsym.nbigscreen.sdk;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
/**
* 定义模版文件对应的实体
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class BinlogTemplate {
//单数据库对应
private String database;
//多表
private List<JsonTable> tableList;
}Ilistener.java
Ilistener for 为了后续扩展不同的实现
package com.cqsym.nbigscreen.sdk;
/**
* Ilistener for 为了后续扩展不同的实现
*/
public interface Ilistener {
void onEvent(BinlogRowData eventData);
}BinlogRowData.java
解析后的binlogRowData封装
package com.cqsym.nbigscreen.sdk;
import com.github.shyiko.mysql.binlog.event.EventType;
import lombok.Data;
import java.util.List;
import java.util.Map;
@Data
public class BinlogRowData {
private TableTemplate tableTemplate;
private EventType eventType;
private List<Map<String, String>> before;
private List<Map<String, String>> after;
}JsonTable.java
JsonTable for 用于表示template.json中对应的表信息
package com.cqsym.nbigscreen.sdk;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
/**
* JsonTable for 用于表示template.json中对应的表信息
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class JsonTable {
private String tableName;
private Integer level;
private List<Column> insert;
private List<Column> update;
private List<Column> delete;
@Data
@AllArgsConstructor
@NoArgsConstructor
public static class Column {
private String columnName;
}
}OperationTypeEnum.java
package com.cqsym.nbigscreen.sdk;
import com.github.shyiko.mysql.binlog.event.EventType;
public enum OperationTypeEnum {
ADD(1),
UPDATE(2),
DELETE(3),
OTHER(4);
private int value;
OperationTypeEnum(int value) {
this.value = value;
}
public int getValue() {
return value;
}
public static OperationTypeEnum convert(EventType type) {
switch (type) {
case EXT_WRITE_ROWS:
return ADD;
case EXT_UPDATE_ROWS:
return UPDATE;
case EXT_DELETE_ROWS:
return DELETE;
default:
return OTHER;
}
}
}ParseCustomTemplate.java
解析模板文件到java对象
package com.cqsym.nbigscreen.sdk;
import lombok.Data;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Supplier;
/**
* 解析模板文件到java对象
*/
@Data
public class ParseCustomTemplate {
private String database;
/**
* key -> TableName
* value -> {@link TableTemplate}
*/
private Map<String, TableTemplate> tableTemplateMap = new HashMap<>();
public static ParseCustomTemplate parse(BinlogTemplate _template) {
ParseCustomTemplate template = new ParseCustomTemplate();
template.setDatabase(_template.getDatabase());
for (JsonTable jsonTable : _template.getTableList()) {
String name = jsonTable.getTableName();
Integer level = jsonTable.getLevel();
TableTemplate tableTemplate = new TableTemplate();
tableTemplate.setTableName(name);
tableTemplate.setLevel(level.toString());
template.tableTemplateMap.put(name, tableTemplate);
//遍历操作类型对应的列信息
Map<OperationTypeEnum, List<String>> operationTypeListMap = tableTemplate.getOpTypeFieldSetMap();
for (JsonTable.Column column : jsonTable.getInsert()) {
getAndCreateIfNeed(
OperationTypeEnum.ADD,
operationTypeListMap,
ArrayList::new
).add(column.getColumnName());
}
for (JsonTable.Column column : jsonTable.getUpdate()) {
getAndCreateIfNeed(
OperationTypeEnum.UPDATE,
operationTypeListMap,
ArrayList::new
).add(column.getColumnName());
}
for (JsonTable.Column column : jsonTable.getDelete()) {
getAndCreateIfNeed(
OperationTypeEnum.DELETE,
operationTypeListMap,
ArrayList::new
).add(column.getColumnName());
}
}
return template;
}
/**
* 从Map中获取对象，如果不存在，创建一个
*/
private static <T, R> R getAndCreateIfNeed(T key, Map<T, R> map, Supplier<R> factory) {
return map.computeIfAbsent(key, k -> factory.get());
}
}TableTemplate.java
Binlog日志中 字段索引 -> 字段名称 的一个转换映射
因为binlog中不会显示更新的列名是什么，它只会展示字段的索引，因此我们需要实现一次转换
package com.cqsym.nbigscreen.sdk;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TableTemplate {
private String tableName;
private String level;
//操作类型 -> 多列
private Map<OperationTypeEnum, List<String>> opTypeFieldSetMap = new HashMap<>();
/**
* Binlog日志中 字段索引 -> 字段名称 的一个转换映射
* 因为binlog中不会显示更新的列名是什么，它只会展示字段的索引，因此我们需要实现一次转换
*/
private Map<Integer, String> posMap = new HashMap<>();
}TemplateHolder.java
该文件主要用于传入一个jdbctemplate后用于查询给定的数据库名和表名后把字段索引与给定的列元素进行对应起来。
package com.cqsym.nbigscreen.sdk;
import com.alibaba.fastjson.JSON;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.annotation.PostConstruct;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.Charset;
import java.util.List;
import java.util.Map;
@Slf4j
public class TemplateHolder {
private ParseCustomTemplate template;
private final JdbcTemplate jdbcTemplate;
private ObjectMapper objectMapper = new ObjectMapper();
private String SQL_SCHEMA = "SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION FROM information_schema.COLUMNS " +
"WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
public TemplateHolder(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
/**
* 需要在容器加载的时候，就载入数据信息
*/
@PostConstruct
public void init() {
log.info("TemplateHolder的init() ... ");
loadJSON("template.json");
}
/**
* 对外提供加载服务
*/
public TableTemplate getTable(String tableName) {
return template.getTableTemplateMap().get(tableName);
}
/**
* 加载需要监听的binlog json文件
*/
private void loadJSON(String path) {
log.info("加载需要监听的binlog json文件...");
ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
InputStream inputStream = classLoader.getResourceAsStream(path);
try {
BinlogTemplate binlogTemplate = JSON.parseObject(
inputStream,
Charset.defaultCharset(),
BinlogTemplate.class
);
this.template = ParseCustomTemplate.parse(binlogTemplate);
loadMeta();
} catch (IOException ex) {
log.error((ex.getMessage()));
throw new RuntimeException("fail to parse json file");
}
}
/**
* 加载元信息
* 使用表索引到列名称的映射关系
*/
private void loadMeta() {
log.info("加载元信息 使用表索引到列名称的映射关系 ...");
for (Map.Entry<String, TableTemplate> entry : template.getTableTemplateMap().entrySet()) {
TableTemplate table = entry.getValue();
log.info("tableName: " + table.getTableName());
try {
log.info("tableName: " + objectMapper.writeValueAsString(table));
} catch (JsonProcessingException e) {
throw new RuntimeException(e);
}
List<String> insertFields = table.getOpTypeFieldSetMap().get(
OperationTypeEnum.ADD
);
List<String> updateFields = table.getOpTypeFieldSetMap().get(
OperationTypeEnum.UPDATE
);
List<String> deleteFields = table.getOpTypeFieldSetMap().get(
OperationTypeEnum.DELETE
);
log.info("insertFields: " + insertFields);
log.info("updateFields: " + updateFields);
log.info("deleteFields: " + deleteFields);
jdbcTemplate.query(SQL_SCHEMA, new Object[]{
template.getDatabase(), table.getTableName()
}, (rs, i) -> {
int pos = rs.getInt("ORDINAL_POSITION");
String colName = rs.getString("COLUMN_NAME");
if ((null != updateFields && updateFields.contains(colName))
|| (null != insertFields && insertFields.contains(colName))
|| (null != deleteFields && deleteFields.contains(colName))) {
table.getPosMap().put(pos - 1, colName);
}
return null;
}
);
log.info("getPosMap: " + table.getPosMap());
}
}
}五、mysql-binlog-connector-java技术封装的sdk使用
前面的SDK包的调用还是主要由binaryLogService该类的start方法启动。
六、介绍下用到的3个缓存工具
AppCacheMap.java
好像这个项目这里没有使用到。
package com.cqsym.nbigscreen.cache;
import java.util.HashMap;
import java.util.Map;
public class AppCacheMap {
public static Map cache = new HashMap();
public static Object get(Object key) {
return cache.get(key);
}
public static void put(Object key, Object value) {
cache.put(key, value);
}
}AppMap.java
package com.cqsym.nbigscreen.cache;
import com.cqsym.nbigscreen.vo.ZaituDingdanVo;
import com.cqsym.nbigscreen.vo.ZuoyeShishiQingkuangVo;
import com.google.common.util.concurrent.AtomicLongMap;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class AppMap {
public static Map<String, String> BanshichuDaquCacheMap = new HashMap();
public static final AtomicLongMap<Integer> BanshichuZuoyeNumCacheMap = AtomicLongMap.create();
public static final AtomicLongMap<String> ZuoyeNumCacheMap = AtomicLongMap.create();
public static final AtomicLongMap<String> XianluMingchengCacheMap = AtomicLongMap.create();
public static final FixedSizeConcurrentQueue<ZuoyeShishiQingkuangVo> ZuoyeShishiQingkuang = new FixedSizeConcurrentQueue<ZuoyeShishiQingkuangVo>(50);
public static Map<String, String> OwnerBaseIdNameCacheName = new HashMap();
public static List<ZaituDingdanVo> ZaituDingdanNumForEveryDayCacheList = new ArrayList<>();
static {
initBanshichuDAquCacheMap();
}
private static void initBanshichuDAquCacheMap() {
BanshichuDaquCacheMap.put("包头作业点", "华北");
BanshichuDaquCacheMap.put("保定办事处", "华北");
BanshichuDaquCacheMap.put("北京办事处", "华北");
BanshichuDaquCacheMap.put("成都办事处", "西南");
BanshichuDaquCacheMap.put("定州办事处", "华北");
BanshichuDaquCacheMap.put("福州作业点", "华南");
BanshichuDaquCacheMap.put("格尔木作业点", "西北");
}
}FixedSizeConcurrentQueue.java
FixedSizeConcurrentQueues是基于ConcurrentLinkedQueue封装的固定长度的，可以用于多线程环境的一个队列（Queue）。
package com.cqsym.nbigscreen.cache;
import java.util.Collection;
import java.util.Iterator;
import java.util.concurrent.ConcurrentLinkedQueue;
public class FixedSizeConcurrentQueue<T> {
private final ConcurrentLinkedQueue<T> queue;
private final int maxSize;
public FixedSizeConcurrentQueue(int maxSize) {
this.maxSize = maxSize;
this.queue = new ConcurrentLinkedQueue<>();
}
public boolean add(T element) {
while (queue.size() >= maxSize) {
// 队列已满，移除队头元素
T polled = queue.poll();
if (polled == null) {
// poll() 返回 null 表示队列为空，这不应该发生，因为我们已经检查了队列大小
throw new IllegalStateException("Queue unexpectedly empty");
}
}
return queue.add(element);
}
public boolean offer(T element) {
while (queue.size() >= maxSize) {
// 队列已满，移除队头元素
T polled = queue.poll();
if (polled == null) {
// poll() 返回 null 表示队列为空，这不应该发生，因为我们已经检查了队列大小
throw new IllegalStateException("Queue unexpectedly empty");
}
}
// 添加新元素到队列
return queue.offer(element);
}
public T poll() {
return queue.poll();
}
public T peek() {
return queue.peek();
}
public int size() {
return queue.size();
}
public boolean isEmpty() {
return queue.isEmpty();
}
public boolean contains(Object o) {
return queue.contains(o);
}
public boolean remove(Object o) {
return queue.remove(o);
}
public boolean addAll(Collection<? extends T> c) {
return queue.addAll(c);
}
public Iterator<T> iterator() {
return queue.iterator();
}
public Object[] toArray() {
return queue.toArray();
}
public <T> T[] toArray(T[] a) {
return queue.toArray(a);
}
@Override
public String toString() {
return queue.toString();
}
}下一篇介绍flink-cdc来实现一摸一样的的功能。
