从excel中分析xxl-job任务的执行频率

本项目基于xxl-admin项目进行开发

需要的pom依赖

?
1
2
3
4
5
6
7
8
9
10
11
12
<!--引入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>

　　代码部分

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
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;
        }
    }
}

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
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语句操作，它的作用是导出正在运行的任务，同时可以看到该任务对应的执行器

?
1
2
3
4
5
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>
View Code
复制代码

 

posted @   不忘初心2021  阅读(48)  评论(0)  编辑  收藏  举报
相关博文：
·  mybatis自动分表和JdbcTemplate自动建表
·  玩转SpringBoot之定时任务详解
·  单机版 Xxl-Job 分布式定时任务调度的部署和使用
·  xxl-job
·  springboot项目集成xxl-job
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· C#/.NET/.NET Core技术前沿周刊 | 第 29 期（2025年3.1-3.9）
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异
点击右上角即可分享
微信分享提示
SQL AI 助手