从excel中分析xxl-job任务的执行频率
本项目基于xxl-admin项目进行开发
需要的pom依赖
<!--引入poi依赖--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency>
代码部分
package com.xxl.job.admin;
import com.xxl.bean.TempParam;
import com.xxl.job.admin.core.cron.CronExpression;
import java.io.FileInputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
/**
* @Description:
* @Author: tutu-qiuxie
* @Create: 2024/2/5 19:06
*/
public class Test002 {
private static final int ONE_MINUTE_MILLIS = 60000;
private static final int MINUTE_BUCKET_NUM = 1440;
private static final int SECOND_BUCKET_NUM = 86400;
private static final int ONE_SECOND_MILLIS = 1000;
public static void main(String[] args) throws Exception {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
List<TempParam> tempParamList=new ArrayList<>(16);
Date statDate = format.parse("2024-03-05 00:00:00");
Date endDate = format.parse("2024-03-06 00:00:00");
List<Map<String, Object>> mapList = new ArrayList<>(10);
FileInputStream inputStream = new FileInputStream("C:\\Users\\13738\\Documents\\WeChat Files\\wxid_vggrup6sfx7522\\FileStorage\\File\\2024-03\\job.xlsx");
List<Map<String, Object>> list = new ArrayList<>();
ExcelDealTest.dealTimeTask(mapList, inputStream, list);
int[] minuteBucket = new int[SECOND_BUCKET_NUM];
for (int i = 0; i < minuteBucket.length; i++) {
minuteBucket[i] = 0;
}
Date printStatDate = format.parse("2024-03-05 00:00:00");
Date printEndDate = format.parse("2024-03-06 00:00:00");
for (Map<String, Object> cronStr : mapList) {
CronExpression cronExpression = null;
try {
String scheduleConf = String.valueOf(cronStr.get("schedule_conf"));
if (scheduleConf.contains("60")) {
continue;
}
cronExpression = new CronExpression(scheduleConf);
} catch (ParseException e) {
System.out.println("Parsing error: " + cronStr);
continue;
}
for (Date nextTime = cronExpression.getNextValidTimeAfter(statDate); nextTime.before(endDate); nextTime = cronExpression.getNextValidTimeAfter(nextTime)) {
int minuteInDay = (int) ((nextTime.getTime() - statDate.getTime()) / ONE_SECOND_MILLIS);
// System.out.println("minuteInDay="+minuteInDay);
// System.out.println("cronStr="+cronStr);
tempParamList.add(TempParam.builder().minuteInDay(minuteInDay).cronStr(cronStr).build());
minuteBucket[minuteInDay] ++;
}
}
// System.out.println("minuteBucket.length="+minuteBucket.length);
// tempParamList.forEach(a->{
// System.out.println(a);
// });
List<TempParam> tempParamList1=new ArrayList<>(16);
for (int i = 0; i < minuteBucket.length; i++) {
Date date = new Date(statDate.getTime() + i * ONE_SECOND_MILLIS);
if (date.after(printStatDate) && date.before(printEndDate)) {
String dateStr = format.format(date);
if (minuteBucket[i] > 1) {
// System.out.println(dateStr + "\t" + minuteBucket[i]+ "\t" + i);
int finalI = i;
List<TempParam> tempParams = tempParamList.stream().filter(tempParam -> tempParam.getMinuteInDay() == finalI).collect(Collectors.toList());
tempParamList1.addAll(tempParams);
// tempParams.forEach(b->{
// System.out.println(b);
// });
}
}
}
List<Map<String, Object>> maps = tempParamList.stream().
map(TempParam::getCronStr)
.collect(Collectors.toList());
// maps.forEach(c->{
// System.out.println(c);
// });
// 统计具有相同app_name和job_desc的记录数量
Map<String, Integer> countMap = new HashMap<>();
for (Map<String, Object> tempParam : maps) {
String key = tempParam.get("app_name") + "--" + tempParam.get("job_desc");
countMap.put(key, countMap.getOrDefault(key, 0) + 1);
}
// 按照统计数量从高到低排序
List<Map.Entry<String, Integer>> sortedEntries = countMap.entrySet().stream()
.sorted(Map.Entry.comparingByValue(Comparator.reverseOrder()))
.collect(Collectors.toList());
// 打印排序后的统计结果
for (Map.Entry<String, Integer> entry : sortedEntries) {
String key = entry.getKey();
String[] split = key.split("--");
String appNmae=split[0];
String jobDesc=split[1];
List<Map<String, Object>> mapList1 = maps.stream()
.filter(job -> appNmae.equals(job.get("app_name")) && jobDesc.equals(job.get("job_desc")))
.collect(Collectors.toList());
int count = entry.getValue();
// System.out.println("Record: " + key + ","+printStatDate+"~"+printEndDate+"时间范围内执行的次数"+ + count);
System.out.println(format.format(printStatDate)+"~"+format.format(printEndDate)+"时间范围内执行的次数："+ + count);
// mapList1.forEach(d->{
// System.out.println(d);
// });
}
}
// Custom class to store execution records
static class ExecutionRecord {
private final String time;
private final int count;
public ExecutionRecord(String time, int count) {
this.time = time;
this.count = count;
}
public String getTime() {
return time;
}
public int getCount() {
return count;
}
}
}
package com.xxl.job.admin;
import com.xxl.job.admin.core.cron.CronExpression;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
/**
* @Description:
* @Author: 喵星人
* @Create: 2024/1/29 13:36
*/
public class ExcelDealTest {
public static int count=0;
public static void dealTimeTask(List<Map<String, Object>> mapList, FileInputStream inputStream, List<Map<String, Object>> list) throws IOException {
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
//获取工作表对象
XSSFSheet sheet = wb.getSheetAt(0);
//得到行的迭代器
Iterator<Row> iterator = sheet.iterator();
int rowNum = 0;
int cellNum = 0;
while (iterator.hasNext()) {
Row row = iterator.next();
//跳过标题行
if (rowNum == 0) {
rowNum++;
continue;
}
//遍历,把每一行数据存到Object数组中
Map<String, Object> map = new HashMap<>(16);
for (int i = 0; i < 5; i++) {
// 获取到单元格内的数据，方法见下
Cell cell = row.getCell(i);
if (cell == null) {
continue;
}
if (i == 0) {
cell.setCellType(CellType.STRING);
map.put("job_desc", cell.getStringCellValue());
continue;
} else if (i == 1) {
cell.setCellType(CellType.STRING);
map.put("schedule_conf", cell.getStringCellValue());
continue;
} else if (i == 2) {
cell.setCellType(CellType.STRING);
map.put("executor_fail_retry_count", cell.getStringCellValue());
continue;
} else if (i == 3) {
cell.setCellType(CellType.STRING);
map.put("app_name", cell.getStringCellValue());
continue;
} else {
cell.setCellType(CellType.STRING);
map.put("title", cell.getStringCellValue());
continue;
}
}
mapList.add(map);
}
}
private static void printNextTenFireTimes(String cronExpression,Map<String, Object> a,int num) throws ParseException {
CronExpression cron = new CronExpression(cronExpression);
Date now = new Date();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
for (int i = 0; i < num; i++) {
now = cron.getNextValidTimeAfter(now);
a.put("nextTime",format.format(now));
}
}
// 按照时间排序字符串列表的方法
private static void sortListByTime(List<String> timeStrings) {
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
// 将时间字符串解析为LocalDateTime对象
List<LocalDateTime> dateTimes = new ArrayList<>();
for (String timeString : timeStrings) {
LocalDateTime dateTime = LocalDateTime.parse(timeString, formatter);
dateTimes.add(dateTime);
}
// 对LocalDateTime对象进行排序
Collections.sort(dateTimes);
// 将排序后的LocalDateTime对象转换回时间字符串
for (int i = 0; i < timeStrings.size(); i++) {
timeStrings.set(i, dateTimes.get(i).format(formatter));
}
}
}
下面说下使用方法
请使用如下sql语句操作，它的作用是导出正在运行的任务，同时可以看到该任务对应的执行器
SELECT xxj.job_desc,schedule_conf ,xxj.executor_fail_retry_count,xxg.app_name,xxg.`title` FROM xxl_job_info xxj INNER JOIN xxl_job_group xxg ON xxj.`job_group`=xxg.id WHERE trigger_status=1
mysql建议使用5.7以上版本，jdk使用的是1.8
最后附上admin项目pom的完整信息
<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> <parent> <groupId>com.xuxueli</groupId> <artifactId>xxl-job</artifactId> <version>2.3.0</version> </parent> <artifactId>xxl-job-admin</artifactId> <packaging>jar</packaging> <dependencyManagement> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>${spring-boot.version}</version> <type>pom</type> <scope>import</scope> </dependency> </dependencies> </dependencyManagement> <dependencies> <!-- starter-web：spring-webmvc + autoconfigure + logback + yaml + tomcat --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-web</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-expression</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jcl</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context-support</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>${spring.version}</version> </dependency> <!-- starter-test：junit + spring-test + mockito --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- SpringCloud Alibaba Nacos Config --> <dependency> <groupId>com.alibaba.cloud</groupId> <artifactId>spring-cloud-starter-alibaba-nacos-config</artifactId> <version>2021.1</version> </dependency> <!-- freemarker-starter --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-freemarker</artifactId> </dependency> <!-- mail-starter --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-mail</artifactId> </dependency> <!-- starter-actuator --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-actuator</artifactId> </dependency> <!-- mybatis-starter：mybatis + mybatis-spring + hikari（default） --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>${mybatis-spring-boot-starter.version}</version> </dependency> <!-- mysql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>${mysql-connector-java.version}</version> </dependency> <!-- xxl-job-core --> <dependency> <groupId>com.xuxueli</groupId> <artifactId>xxl-job-core</artifactId> <version>${project.parent.version}</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.6</version> </dependency> <!--引入druid-替换默认数据库连接池--> <!-- <dependency>--> <!-- <groupId>com.alibaba</groupId>--> <!-- <artifactId>druid-spring-boot-starter</artifactId>--> <!-- <version>1.2.15</version>--> <!-- </dependency>--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency> <!--springboot整合aop--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId> </dependency> <!--引入poi依赖--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <!--lombok依赖--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.26</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <version>${spring-boot.version}</version> <executions> <execution> <goals> <goal>repackage</goal> </goals> </execution> </executions> </plugin> <!-- docker --> <plugin> <groupId>com.spotify</groupId> <artifactId>docker-maven-plugin</artifactId> <version>0.4.13</version> <configuration> <!-- made of '[a-z0-9-_.]' --> <imageName>${project.artifactId}:${project.version}</imageName> <dockerDirectory>${project.basedir}</dockerDirectory> <resources> <resource> <targetPath>/</targetPath> <directory>${project.build.directory}</directory> <include>${project.build.finalName}.jar</include> </resource> </resources> </configuration> </plugin> </plugins> </build> </project>
