如何将 XxlJob 集成达梦数据库

 1. 前言

在某些情况下，你的项目可能会面临数据库选择的特殊要求，随着国产化的不断推进，达梦数据库是一个常见的选择。本篇博客将教你如何解决 XxlJob 与达梦数据库之间的 SQL 兼容性问题，以便你的任务调度系统能够在这个数据库中正常运行。

 2. 集成 XxlJob  2.1 相关版本

首先，确保你使用的 XxlJob 版本与本文中的示例相匹配。本示例使用 XxlJob-Admin 2.4.0 版本。你可以从官方网站或 GitHub 上下载相应的版本。

Gitee地址
Github地址

 2.2 添加达梦数据库驱动

你需要添加达梦数据库的 JDBC 驱动到项目的 Maven 依赖中。以下是一个示例 Maven 依赖，确保它与你的达梦数据库版本兼容：

com.dmDmJdbcDriver1.8.0

更建议去mvn仓库找寻你需要的链接依赖（或者达梦官方技术文档里也有）

2.3 配置数据库连接

在项目的配置文件中，配置达梦数据库的连接信息，包括 URL、用户名和密码。确保将这些信息配置为你的达梦数据库的实际连接信息。

spring.datasource.url=jdbc:dm://localhost:5236 spring.datasource.username=SYSDBA spring.datasource.password=你的密码 spring.datasource.driver-class-name=dm.jdbc.driver.DmDriver

2.4 初始化数据库表

XxlJob 需要一组特定的数据库表来存储任务信息和调度信息。你可以使用提供的 SQL 脚本初始化这些表。以下是 SQL 脚本的示例：

CREATE TABLE"SHENG"."XXL_JOB_USER"("ID"BIGINT IDENTITY(1,1) NOT NULL,"USERNAME"VARCHAR(50) NOT NULL,"PASSWORD"VARCHAR(50) NOT NULL,"ROLE"NUMBER(4,0) NOT NULL,"PERMISSION"VARCHAR(255) DEFAULT NULL NULL ); CREATE TABLE"SHENG"."XXL_JOB_REGISTRY"("ID"BIGINT IDENTITY(1,1) NOT NULL,"REGISTRY_GROUP"VARCHAR(50) NOT NULL,"REGISTRY_KEY"VARCHAR(255) NOT NULL,"REGISTRY_VALUE"VARCHAR(255) NOT NULL,"UPDATE_TIME"TIMESTAMP(6) NULL ); CREATE TABLE"SHENG"."XXL_JOB_LOGGLUE"("ID"BIGINT IDENTITY(1,1) NOT NULL,"JOB_ID"NUMBER(11,0) NOT NULL,"GLUE_TYPE"VARCHAR(50) DEFAULT NULL NULL,"GLUE_SOURCE"TEXT NULL,"GLUE_REMARK"VARCHAR(128) NOT NULL,"ADD_TIME"TIMESTAMP(6) NULL,"UPDATE_TIME"TIMESTAMP(6) NULL ); CREATE TABLE"SHENG"."XXL_JOB_LOG_REPORT"("ID"BIGINT IDENTITY(1,1) NOT NULL,"TRIGGER_DAY"TIMESTAMP(6) NULL,"RUNNING_COUNT"NUMBER(11,0) DEFAULT '0' NOT NULL,"SUC_COUNT"NUMBER(11,0) DEFAULT '0' NOT NULL,"FAIL_COUNT"NUMBER(11,0) DEFAULT '0' NOT NULL,"UPDATE_TIME"TIMESTAMP(6) NULL ); CREATE TABLE"SHENG"."XXL_JOB_LOG"("ID"BIGINT IDENTITY(1,1) NOT NULL,"JOB_GROUP"NUMBER(11,0) NOT NULL,"JOB_ID"NUMBER(11,0) NOT NULL,"EXECUTOR_ADDRESS"VARCHAR(255) NULL,"EXECUTOR_HANDLER"VARCHAR(255) NULL,"EXECUTOR_PARAM"VARCHAR(512) NULL,"EXECUTOR_SHARDING_PARAM"VARCHAR(20) NULL,"EXECUTOR_FAIL_RETRY_COUNT"NUMBER(11,0) DEFAULT '0' NOT NULL,"TRIGGER_TIME"TIMESTAMP(6) NULL,"TRIGGER_CODE"NUMBER(11,0) NOT NULL,"TRIGGER_MSG"CLOB NULL,"HANDLE_TIME"TIMESTAMP(6) NULL,"HANDLE_CODE"NUMBER(11,0) NOT NULL,"HANDLE_MSG"CLOB NULL,"ALARM_STATUS"NUMBER(4,0) DEFAULT '0' NOT NULL ); CREATE TABLE"SHENG"."XXL_JOB_LOCK"("LOCK_NAME"VARCHAR(50) NOT NULL ); CREATE TABLE"SHENG"."XXL_JOB_INFO"("ID"BIGINT IDENTITY(1,1) NOT NULL,"JOB_GROUP"NUMBER(11,0) NOT NULL,"JOB_DESC"VARCHAR(255) NOT NULL,"ADD_TIME"TIMESTAMP(6) NULL,"UPDATE_TIME"TIMESTAMP(6) NULL,"AUTHOR"VARCHAR(64) NULL,"ALARM_EMAIL"VARCHAR(255) NULL,"SCHEDULE_TYPE"VARCHAR(50) DEFAULT 'NONE' NOT NULL,"SCHEDULE_CONF"VARCHAR(128) NULL,"MISFIRE_STRATEGY"VARCHAR(50) DEFAULT 'DO_NOTHING' NOT NULL,"EXECUTOR_ROUTE_STRATEGY"VARCHAR(50) NULL,"EXECUTOR_HANDLER"VARCHAR(255) NULL,"EXECUTOR_PARAM"VARCHAR(512) NULL,"EXECUTOR_BLOCK_STRATEGY"VARCHAR(50) NULL,"EXECUTOR_TIMEOUT"NUMBER(11,0) DEFAULT '0' NOT NULL,"EXECUTOR_FAIL_RETRY_COUNT"NUMBER(11,0) DEFAULT '0' NOT NULL,"GLUE_TYPE"VARCHAR(50) NOT NULL,"GLUE_SOURCE"TEXT NULL,"GLUE_REMARK"VARCHAR(128) NULL,"GLUE_UPDATETIME"TIMESTAMP(6) NULL,"CHILD_JOBID"VARCHAR(255) NULL,"TRIGGER_STATUS"NUMBER(4,0) DEFAULT '0' NOT NULL,"TRIGGER_LAST_TIME"NUMBER(13,0) DEFAULT '0' NOT NULL,"TRIGGER_NEXT_TIME"NUMBER(13,0) DEFAULT '0' NOT NULL ); CREATE TABLE"SHENG"."XXL_JOB_GROUP"("ID"BIGINT IDENTITY(1,1) NOT NULL,"APP_NAME"VARCHAR(64) NOT NULL,"TITLE"VARCHAR(50) NOT NULL,"ADDRESS_TYPE"NUMBER(4,0) DEFAULT '0' NOT NULL,"ADDRESS_LIST"TEXT NULL,"UPDATE_TIME"TIMESTAMP(6) NULL ); SET IDENTITY_INSERT"SHENG"."XXL_JOB_GROUP"ON; INSERT INTO"SHENG"."XXL_JOB_GROUP"("ID","APP_NAME","TITLE","ADDRESS_TYPE","ADDRESS_LIST","UPDATE_TIME") VALUES(1,'xxl-job-executor-sample','示例执行器',0,null,'2023-11-02 17:28:36.084000'); INSERT INTO"SHENG"."XXL_JOB_GROUP"("ID","APP_NAME","TITLE","ADDRESS_TYPE","ADDRESS_LIST","UPDATE_TIME") VALUES(2,'lpsHandler','lpsHandler',0,null,'2023-11-02 17:28:36.083000'); SET IDENTITY_INSERT"SHENG"."XXL_JOB_GROUP"OFF; SET IDENTITY_INSERT"SHENG"."XXL_JOB_INFO"ON; INSERT INTO"SHENG"."XXL_JOB_INFO"("ID","JOB_GROUP","JOB_DESC","ADD_TIME","UPDATE_TIME","AUTHOR","ALARM_EMAIL","SCHEDULE_TYPE","SCHEDULE_CONF","MISFIRE_STRATEGY","EXECUTOR_ROUTE_STRATEGY","EXECUTOR_HANDLER","EXECUTOR_PARAM","EXECUTOR_BLOCK_STRATEGY","EXECUTOR_TIMEOUT","EXECUTOR_FAIL_RETRY_COUNT","GLUE_TYPE","GLUE_SOURCE","GLUE_REMARK","GLUE_UPDATETIME","CHILD_JOBID","TRIGGER_STATUS","TRIGGER_LAST_TIME","TRIGGER_NEXT_TIME") VALUES(1,1,'测试任务1','2018-11-03 22:21:31.000000','2018-11-03 22:21:31.000000','XXL','','CRON','0 0 0 * * ? *','DO_NOTHING','FIRST','demoJobHandler','','SERIAL_EXECUTION',0,0,'BEAN','','GLUE代码初始化','2018-11-03 22:21:31.000000','',0,0,0); INSERT INTO"SHENG"."XXL_JOB_INFO"("ID","JOB_GROUP","JOB_DESC","ADD_TIME","UPDATE_TIME","AUTHOR","ALARM_EMAIL","SCHEDULE_TYPE","SCHEDULE_CONF","MISFIRE_STRATEGY","EXECUTOR_ROUTE_STRATEGY","EXECUTOR_HANDLER","EXECUTOR_PARAM","EXECUTOR_BLOCK_STRATEGY","EXECUTOR_TIMEOUT","EXECUTOR_FAIL_RETRY_COUNT","GLUE_TYPE","GLUE_SOURCE","GLUE_REMARK","GLUE_UPDATETIME","CHILD_JOBID","TRIGGER_STATUS","TRIGGER_LAST_TIME","TRIGGER_NEXT_TIME") VALUES(3,2,'lpsHandler','2023-11-02 17:19:53.438000','2023-11-02 17:28:25.716000','刘品水','','CRON','0/1 * * * * ? ','DO_NOTHING','FIRST','lpsHandler','','SERIAL_EXECUTION',0,0,'BEAN','','GLUE代码初始化','2023-11-02 17:19:53.438000','',1,1698917841000,1698917842000); SET IDENTITY_INSERT"SHENG"."XXL_JOB_INFO"OFF; INSERT INTO"SHENG"."XXL_JOB_LOCK"("LOCK_NAME") VALUES('schedule_lock'); SET IDENTITY_INSERT"SHENG"."XXL_JOB_LOG"ON; INSERT INTO"SHENG"."XXL_JOB_LOG"("ID","JOB_GROUP","JOB_ID","EXECUTOR_ADDRESS","EXECUTOR_HANDLER","EXECUTOR_PARAM","EXECUTOR_SHARDING_PARAM","EXECUTOR_FAIL_RETRY_COUNT","TRIGGER_TIME","TRIGGER_CODE","TRIGGER_MSG","HANDLE_TIME","HANDLE_CODE","HANDLE_MSG","ALARM_STATUS") VALUES(13,2,3,null,'lpsHandler','',null,0,'2023-11-02 17:20:25.518000',500,'任务触发类型：手动触发
调度机器：192.168.55.2
执行器-注册方式：自动注册
执行器-地址列表：null
路由策略：第一个
阻塞处理策略：单机串行
任务超时时间：0
失败重试次数：0

>>>>>>>>>>>触发调度<<<<<<<<<<<
调度失败：执行器地址为空

',null,0,null,2); SET IDENTITY_INSERT"SHENG"."XXL_JOB_LOG"OFF; SET IDENTITY_INSERT"SHENG"."XXL_JOB_LOG_REPORT"ON; INSERT INTO"SHENG"."XXL_JOB_LOG_REPORT"("ID","TRIGGER_DAY","RUNNING_COUNT","SUC_COUNT","FAIL_COUNT","UPDATE_TIME") VALUES(1,'2023-11-02 00:00:00.000000',0,0,113,null); INSERT INTO"SHENG"."XXL_JOB_LOG_REPORT"("ID","TRIGGER_DAY","RUNNING_COUNT","SUC_COUNT","FAIL_COUNT","UPDATE_TIME") VALUES(2,'2023-11-01 00:00:00.000000',0,0,0,null); INSERT INTO"SHENG"."XXL_JOB_LOG_REPORT"("ID","TRIGGER_DAY","RUNNING_COUNT","SUC_COUNT","FAIL_COUNT","UPDATE_TIME") VALUES(3,'2023-10-31 00:00:00.000000',0,0,0,null); SET IDENTITY_INSERT"SHENG"."XXL_JOB_LOG_REPORT"OFF; SET IDENTITY_INSERT"SHENG"."XXL_JOB_LOGGLUE"ON; SET IDENTITY_INSERT"SHENG"."XXL_JOB_LOGGLUE"OFF; SET IDENTITY_INSERT"SHENG"."XXL_JOB_REGISTRY"ON; SET IDENTITY_INSERT"SHENG"."XXL_JOB_REGISTRY"OFF; SET IDENTITY_INSERT"SHENG"."XXL_JOB_USER"ON; INSERT INTO"SHENG"."XXL_JOB_USER"("ID","USERNAME","PASSWORD","ROLE","PERMISSION") VALUES(1,'admin','e10adc3949ba59abbe56e057f20f883e',1,null); SET IDENTITY_INSERT"SHENG"."XXL_JOB_USER"OFF; ALTER TABLE"SHENG"."XXL_JOB_USER"ADD CONSTRAINT PRIMARY KEY("ID") ; ALTER TABLE"SHENG"."XXL_JOB_REGISTRY"ADD CONSTRAINT PRIMARY KEY("ID") ; ALTER TABLE"SHENG"."XXL_JOB_LOGGLUE"ADD CONSTRAINT PRIMARY KEY("ID") ; ALTER TABLE"SHENG"."XXL_JOB_LOG_REPORT"ADD CONSTRAINT PRIMARY KEY("ID") ; ALTER TABLE"SHENG"."XXL_JOB_LOG_REPORT"ADD CONSTRAINT"I_TRIGGER_DAY"UNIQUE("TRIGGER_DAY") ; ALTER TABLE"SHENG"."XXL_JOB_LOG"ADD CONSTRAINT PRIMARY KEY("ID") ; ALTER TABLE"SHENG"."XXL_JOB_LOCK"ADD CONSTRAINT PRIMARY KEY("LOCK_NAME") ; ALTER TABLE"SHENG"."XXL_JOB_INFO"ADD CONSTRAINT PRIMARY KEY("ID") ; ALTER TABLE"SHENG"."XXL_JOB_GROUP"ADD CONSTRAINT PRIMARY KEY("ID") ; CREATE UNIQUE INDEX"I_USERNAME"ON"SHENG"."XXL_JOB_USER"("USERNAME"); CREATE INDEX"I_G_K_V"ON"SHENG"."XXL_JOB_REGISTRY"("REGISTRY_GROUP","REGISTRY_KEY","REGISTRY_VALUE"); COMMENT ON COLUMN"SHENG"."XXL_JOB_LOGGLUE"."JOB_ID"IS '任务，主键ID'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOGGLUE"."GLUE_TYPE"IS 'GLUE类型'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOGGLUE"."GLUE_SOURCE"IS 'GLUE源代码'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOGGLUE"."GLUE_REMARK"IS 'GLUE备注'; CREATE INDEX"I_TRIGGER_TIME"ON"SHENG"."XXL_JOB_LOG"("TRIGGER_TIME"); CREATE INDEX"I_HANDLE_CODE"ON"SHENG"."XXL_JOB_LOG"("HANDLE_CODE"); COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."ID"IS '主键ID'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."JOB_GROUP"IS '执行器主键ID'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."JOB_ID"IS '任务，主键ID'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."EXECUTOR_ADDRESS"IS '执行器地址，本次执行的地址'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."EXECUTOR_HANDLER"IS '执行器任务handler'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."EXECUTOR_PARAM"IS '执行器任务参数'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."EXECUTOR_SHARDING_PARAM"IS '执行器任务分片参数，格式如 1/2'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."EXECUTOR_FAIL_RETRY_COUNT"IS '失败重试次数'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."TRIGGER_TIME"IS '调度-时间'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."TRIGGER_CODE"IS '调度-结果'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."TRIGGER_MSG"IS '调度-日志'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."HANDLE_TIME"IS '执行-时间'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."HANDLE_CODE"IS '执行-状态'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."HANDLE_MSG"IS '执行-日志'; COMMENT ON COLUMN"SHENG"."XXL_JOB_LOG"."ALARM_STATUS"IS '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."JOB_DESC"IS '任务描述'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."SCHEDULE_TYPE"IS '调度类型'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."SCHEDULE_CONF"IS '调度配置，值含义取决于调度类型'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."MISFIRE_STRATEGY"IS '调度过期策略'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."EXECUTOR_ROUTE_STRATEGY"IS '执行器路由策略'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."EXECUTOR_HANDLER"IS '执行器任务handler'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."EXECUTOR_PARAM"IS '执行器任务参数'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."EXECUTOR_BLOCK_STRATEGY"IS '阻塞处理策略'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."EXECUTOR_TIMEOUT"IS '任务执行超时时间，单位秒'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."EXECUTOR_FAIL_RETRY_COUNT"IS '失败重试次数'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."GLUE_TYPE"IS 'GLUE类型'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."GLUE_SOURCE"IS 'GLUE源代码'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."GLUE_REMARK"IS 'GLUE备注'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."GLUE_UPDATETIME"IS 'GLUE更新时间'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."CHILD_JOBID"IS '子任务ID，多个逗号分隔'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."TRIGGER_STATUS"IS '调度状态：0-停止，1-运行'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."TRIGGER_LAST_TIME"IS '上次调度时间'; COMMENT ON COLUMN"SHENG"."XXL_JOB_INFO"."TRIGGER_NEXT_TIME"IS '下次调度时间'; COMMENT ON COLUMN"SHENG"."XXL_JOB_GROUP"."APP_NAME"IS '执行器AppName'; COMMENT ON COLUMN"SHENG"."XXL_JOB_GROUP"."TITLE"IS '执行器名称'; COMMENT ON COLUMN"SHENG"."XXL_JOB_GROUP"."ADDRESS_TYPE"IS '执行器地址类型：0=自动注册、1=手动录入'; COMMENT ON COLUMN"SHENG"."XXL_JOB_GROUP"."ADDRESS_LIST"IS '执行器地址列表，多地址逗号分隔';

以上 SQL 脚本中包括了创建必需的表和插入默认登录账号。注意，你可以根据自己的需求自定义这些表。

 2.5 更新 XML文件XxlJobLogMapper.xml&&XxlJobRegistryMapper.xml

XxlJob 与达梦数据库之间存在 SQL 兼容性问题，因此需要对 XxlJob 的 SQL 映射进行修改。以下是 XML的示例修改，确保 XxlJob 能够在达梦数据库中正常运行：

XxlJobLogMapper.xml

SELECT ID FROM SHENG.XXL_JOB_LOG WHERE NOT ( (TRIGGER_CODE IN (0, 200) AND HANDLE_CODE = 0) OR (HANDLE_CODE = 200) ) AND ALARM_STATUS = 0 ORDER BY ID ASC LIMIT #{pagesize}

XxlJobRegistryMapper.xml

SELECT t.ID FROM SHENG.XXL_JOB_REGISTRY t WHERE t.UPDATE_TIME < DATEADD(SECOND, -#{timeout} , #{nowTime}) SELECT FROM SHENG.XXL_JOB_REGISTRY t WHERE t.UPDATE_TIME < DATEADD(SECOND, -#{timeout} , #{nowTime})

 2.6 运行 XxlJob

现在，你可以启动 XxlJob 服务，它将连接到达梦数据库并开始正常运行。确保你的配置和数据库初始化都已经完成。

 3. 总结

通过按照上述步骤将 XxlJob 集成到达梦数据库中，你可以解决 SQL 兼容性问题，确保任务调度系统在达梦数据库中正常运行。在选择数据库时，始终考虑到项目需求、性能和兼容性，以便做出明智的选择。

这些步骤应该为你提供了成功集成 XxlJob 和达梦数据库所需的指导。祝你的任务调度系统顺利运行！如果你遇到问题 评论区留言 我看到都会帮着解决的~