xxl-job 的集成及 Mysql 转 PostgresSql

git clone https://gitee.com/xuxueli0323/xxl-job.git

# 进入容器
docker exec -it 容器名称或容器id /bin/bash

# 切换用户
su - postgres

# 执行 sql
psql

# 创建一个 xxl_job 用户，密码是 123456
CREATE USER xxl_job WITH PASSWORD '123456';

# 创建一个 xxl_job 数据库，属于 xxl_o=job 用户
CREATE DATABASE xxl_job OWNER xxl_job;

# 将 xxl_job 数据库授权给 xxl_job 用户
GRANT ALL PRIVILEGES ON DATABASE xxl_job TO xxl_job;

CREATE SEQUENCE xxl_job_user_id_seq START 1;
CREATE SEQUENCE xxl_job_info_id_seq START 1;
CREATE SEQUENCE xxl_job_log_id_seq START 1;
CREATE SEQUENCE xxl_job_log_report_id_seq START 1;
CREATE SEQUENCE xxl_job_logglue_id_seq START 1;
CREATE SEQUENCE xxl_job_registry_id_seq START 1;
CREATE SEQUENCE xxl_job_group_id_seq START 1;

ALTER TABLE "public"."xxl_job_user" alter column ID set default nextval('xxl_job_user_id_seq'::regclass);
ALTER TABLE "public"."xxl_job_info" alter column ID set default nextval('xxl_job_info_id_seq'::regclass);
ALTER TABLE "public"."xxl_job_log" alter column ID set default nextval('xxl_job_log_id_seq'::regclass);
ALTER TABLE "public"."xxl_job_log_report" alter column ID set default nextval('xxl_job_log_report_id_seq'::regclass);
ALTER TABLE "public"."xxl_job_logglue" alter column ID set default nextval('xxl_job_logglue_id_seq'::regclass);
ALTER TABLE "public"."xxl_job_registry" alter column ID set default nextval('xxl_job_registry_id_seq'::regclass);
ALTER TABLE "public"."xxl_job_group" alter column ID set default nextval('xxl_job_group_id_seq'::regclass);

<!-- mysql -->
<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <version>${mysql-connector-j.version}</version>
</dependency>

<!-- postgressql -->
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.7.4</version>
</dependency>

### xxl-job, datasource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/xxl_job?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=root_pwd
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

替换为

### xxl-job, datasource
spring.datasource.url=jdbc:postgresql://127.0.0.1:5432/xxl_job
spring.datasource.username=xxl_job
spring.datasource.password=123456
spring.datasource.driver-class-name=org.postgresql.Driver

• 去掉转义符 ` ，可以用空格替换。
  • 注意 findLogReport 的语句需要修改为 as "xx" 。
• 修改 LIMIT #{offset}, #{pagesize}  为  LIMIT #{pagesize} OFFSET #{offset} 。
• 修改DATE_ADD(#{nowTime},INTERVAL - #{timeout} SECOND)  为  ((select NOW())-INTERVAL '${timeout} S') 。
• 修改 WHERE !(  为  WHERE not ( 。

11.运行代码检查

<dependency>
    <groupId>com.xuxueli</groupId>
    <artifactId>xxl-job-core</artifactId>
    <version>2.4.0</version>
</dependency>

# xll-job 配置项
xxl:
  job:
    enabled: true
    admin:
      addresses: http://127.0.0.1:8080/xxl-job-admin # 调度中心地址（如集群部署存在多个地址则用逗号分隔）执行器将会使用该地址进行"执行器心跳注册"和"任务结果回调"；为空则关闭自动注册；
    executor:
      appname: ${spring.application.name} # 执行器 AppName
      ip: # 执行器IP：默认为空表示自动获取IP，多网卡时可手动设置指定IP，该IP不会绑定Host仅作为通讯实用；地址信息用于 "执行器注册" 和 "调度中心请求并触发任务"；
      port: 9999 # 执行器端口号：小于等于0则自动获取；默认端口为 9999，单机部署多个执行器时，注意要配置不同执行器端口；
      logpath: /akim-boot-server/logs/xxl-job/${spring.application.name} # 执行器运行日志文件存储磁盘路径 ：需要对该路径拥有读写权限；为空则使用默认路径；
      logretentiondays: -1 # 调度中心日志表数据保存天数 [选填]：过期日志自动清理；限制大于等于7时生效，否则, 如-1，关闭自动清理功能；
    accessToken: default_token # 调度中心通讯TOKEN [选填]：非空时启用

@ConfigurationProperties("xxl.job")
@Validated
@Data
public class XxlJobProperties {
    /**
     * 是否开启，默认为 true 关闭
     */
    private Boolean enabled = true;
    /**
     * 访问令牌
     */
    private String accessToken;
    /**
     * 控制器配置
     */
    @NotNull(message = "控制器配置不能为空")
    private AdminProperties admin;
    /**
     * 执行器配置
     */
    @NotNull(message = "执行器配置不能为空")
    private ExecutorProperties executor;

    /**
     * XXL-Job 调度器配置类
     */
    @Data
    @Valid
    public static class AdminProperties {
        /**
         * 调度器地址
         */
        @NotEmpty(message = "调度器地址不能为空")
        private String addresses;
    }

    /**
     * XXL-Job 执行器配置类
     */
    @Data
    @Valid
    public static class ExecutorProperties {
        /**
         * 应用名
         */
        @NotEmpty(message = "应用名不能为空")
        private String appName;
        /**
         * 执行器的 IP
         */
        private String ip;
        /**
         * 执行器的 Port
         */
        private Integer port = -1;
        /**
         * 日志地址
         */
        @NotEmpty(message = "日志地址不能为空")
        private String logPath;
        /**
         * 日志保留天数
         */
        private Integer logRetentionDays = 30;
    }
}

@AutoConfiguration
@ConditionalOnClass(XxlJobSpringExecutor.class)
@ConditionalOnProperty(prefix = "xxl.job", name = "enabled", havingValue = "true", matchIfMissing = true)
@EnableConfigurationProperties({XxlJobProperties.class})
@EnableScheduling // 开启 Spring 自带的定时任务
@Slf4j
public class AkimXxlJobAutoConfiguration {
    @Bean
    @ConditionalOnMissingBean
    public XxlJobExecutor xxlJobExecutor(XxlJobProperties properties) {
        log.info("[xxlJobExecutor][初始化 XXL-Job 执行器的配置]");
        XxlJobProperties.AdminProperties admin = properties.getAdmin();
        XxlJobProperties.ExecutorProperties executor = properties.getExecutor();

        // 初始化执行器
        XxlJobExecutor xxlJobExecutor = new XxlJobSpringExecutor();
        xxlJobExecutor.setIp(executor.getIp());
        xxlJobExecutor.setPort(executor.getPort());
        xxlJobExecutor.setAppname(executor.getAppName());
        xxlJobExecutor.setLogPath(executor.getLogPath());
        xxlJobExecutor.setLogRetentionDays(executor.getLogRetentionDays());
        xxlJobExecutor.setAdminAddresses(admin.getAddresses());
        xxlJobExecutor.setAccessToken(properties.getAccessToken());
        return xxlJobExecutor;
    }
}

@Component
@Slf4j
public class XxlJobDemo {
    @XxlJob("test-hhh")
    public void testHhh() {
        log.error("xxl-job-demo");
    }
}