如何将 XxlJob 集成达梦数据库
在某些情况下，你的项目可能会面临数据库选择的特殊要求，随着国产化的不断推进，达梦数据库是一个常见的选择。本篇博客将教你如何解决 XxlJob 与达梦数据库之间的 SQL 兼容性问题，以便你的任务调度系统能够在这个数据库中正常运行。
·
1. 前言
在某些情况下，你的项目可能会面临数据库选择的特殊要求，随着国产化的不断推进，达梦数据库是一个常见的选择。本篇博客将教你如何解决 XxlJob 与达梦数据库之间的 SQL 兼容性问题，以便你的任务调度系统能够在这个数据库中正常运行。
2. 集成 XxlJob
2.1 相关版本
首先，确保你使用的 XxlJob 版本与本文中的示例相匹配。本示例使用 XxlJob-Admin 2.4.0 版本。你可以从官方网站或 GitHub 上下载相应的版本。
2.2 添加达梦数据库驱动
你需要添加达梦数据库的 JDBC 驱动到项目的 Maven 依赖中。以下是一个示例 Maven 依赖，确保它与你的达梦数据库版本兼容：
<dependency>
<groupId>com.dm</groupId>
<artifactId>DmJdbcDriver</artifactId>
<version>1.8.0</version>
</dependency>更建议去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,'任务触发类型：手动触发<br>调度机器：192.168.55.2<br>执行器-注册方式：自动注册<br>执行器-地址列表：null<br>路由策略：第一个<br>阻塞处理策略：单机串行<br>任务超时时间：0<br>失败重试次数：0<br><br><span style="color:#00c0ef;" > >>>>>>>>>>>触发调度<<<<<<<<<<< </span><br>调度失败：执行器地址为空<br><br>',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="findFailJobLogIds" resultType="long" >
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}
</select>
XxlJobRegistryMapper.xml
<select id="findDead" parameterType="java.util.HashMap" resultType="java.lang.Integer" >
SELECT t.ID
FROM SHENG.XXL_JOB_REGISTRY t
WHERE t.UPDATE_TIME < DATEADD(SECOND, -#{timeout} , #{nowTime})
</select>
<select id="findAll" parameterType="java.util.HashMap" resultMap="XxlJobRegistry">
SELECT <include refid="Base_Column_List" />
FROM SHENG.XXL_JOB_REGISTRY t
WHERE t.UPDATE_TIME < DATEADD(SECOND, -#{timeout} , #{nowTime})
</select>
2.6 运行 XxlJob
现在，你可以启动 XxlJob 服务，它将连接到达梦数据库并开始正常运行。确保你的配置和数据库初始化都已经完成。
3. 总结
通过按照上述步骤将 XxlJob 集成到达梦数据库中，你可以解决 SQL 兼容性问题，确保任务调度系统在达梦数据库中正常运行。在选择数据库时，始终考虑到项目需求、性能和兼容性，以便做出明智的选择。
这些步骤应该为你提供了成功集成 XxlJob 和达梦数据库所需的指导。祝你的任务调度系统顺利运行！如果你遇到问题 评论区留言 我看到都会帮着解决的~
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
4
0- 0
已为社区贡献1条内容
所有评论(0)