网站怎么建立视频,关于设计图的网站,互动营销网站,永嘉网站开发公司1. 前言
在某些情况下#xff0c;你的项目可能会面临数据库选择的特殊要求#xff0c;随着国产化的不断推进#xff0c;达梦数据库是一个常见的选择。本篇博客将教你如何解决 XxlJob 与达梦数据库之间的 SQL 兼容性问题#xff0c;以便你的任务调度系统能够在这个数据库中… 1. 前言
在某些情况下你的项目可能会面临数据库选择的特殊要求随着国产化的不断推进达梦数据库是一个常见的选择。本篇博客将教你如何解决 XxlJob 与达梦数据库之间的 SQL 兼容性问题以便你的任务调度系统能够在这个数据库中正常运行。 2. 集成 XxlJob 2.1 相关版本
首先确保你使用的 XxlJob 版本与本文中的示例相匹配。本示例使用 XxlJob-Admin 2.4.0 版本。你可以从官方网站或 GitHub 上下载相应的版本。
Gitee地址Github地址 2.2 添加达梦数据库驱动
你需要添加达梦数据库的 JDBC 驱动到项目的 Maven 依赖中。以下是一个示例 Maven 依赖确保它与你的达梦数据库版本兼容 dependencygroupIdcom.dm/groupIdartifactIdDmJdbcDriver/artifactIdversion1.8.0/version/dependency
更建议去mvn仓库找寻你需要的链接依赖或者达梦官方技术文档里也有
2.3 配置数据库连接
在项目的配置文件中配置达梦数据库的连接信息包括 URL、用户名和密码。确保将这些信息配置为你的达梦数据库的实际连接信息。
spring.datasource.urljdbc:dm://localhost:5236
spring.datasource.usernameSYSDBA
spring.datasource.password你的密码
spring.datasource.driver-class-namedm.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 NULLNULL
);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 NULLNULL,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 0NOT NULL,SUC_COUNT NUMBER(11,0) DEFAULT 0NOT NULL,FAIL_COUNT NUMBER(11,0) DEFAULT 0NOT 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 0NOT 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 0NOT 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 NONENOT NULL,SCHEDULE_CONF VARCHAR(128) NULL,MISFIRE_STRATEGY VARCHAR(50) DEFAULT DO_NOTHINGNOT 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 0NOT NULL,EXECUTOR_FAIL_RETRY_COUNT NUMBER(11,0) DEFAULT 0NOT 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 0NOT NULL,TRIGGER_LAST_TIME NUMBER(13,0) DEFAULT 0NOT NULL,TRIGGER_NEXT_TIME NUMBER(13,0) DEFAULT 0NOT 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 0NOT 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.2br执行器-注册方式自动注册br执行器-地址列表nullbr路由策略第一个br阻塞处理策略单机串行br任务超时时间0br失败重试次数0brbrspan stylecolor:#00c0ef; 触发调度 /spanbr调度失败执行器地址为空brbr,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.xmlXxlJobRegistryMapper.xml
XxlJob 与达梦数据库之间存在 SQL 兼容性问题因此需要对 XxlJob 的 SQL 映射进行修改。以下是 XML的示例修改确保 XxlJob 能够在达梦数据库中正常运行
XxlJobLogMapper.xml select idfindFailJobLogIds resultTypelong SELECT ID FROM SHENG.XXL_JOB_LOGWHERE NOT ((TRIGGER_CODE IN (0, 200) AND HANDLE_CODE 0)OR(HANDLE_CODE 200))AND ALARM_STATUS 0ORDER BY ID ASCLIMIT #{pagesize}/select XxlJobRegistryMapper.xml select idfindDead parameterTypejava.util.HashMap resultTypejava.lang.Integer SELECT t.IDFROM SHENG.XXL_JOB_REGISTRY tWHERE t.UPDATE_TIME lt; DATEADD(SECOND, -#{timeout} , #{nowTime})/selectselect idfindAll parameterTypejava.util.HashMap resultMapXxlJobRegistrySELECT include refidBase_Column_List /FROM SHENG.XXL_JOB_REGISTRY tWHERE t.UPDATE_TIME lt; DATEADD(SECOND, -#{timeout} , #{nowTime})/select 2.6 运行 XxlJob
现在你可以启动 XxlJob 服务它将连接到达梦数据库并开始正常运行。确保你的配置和数据库初始化都已经完成。 3. 总结
通过按照上述步骤将 XxlJob 集成到达梦数据库中你可以解决 SQL 兼容性问题确保任务调度系统在达梦数据库中正常运行。在选择数据库时始终考虑到项目需求、性能和兼容性以便做出明智的选择。
这些步骤应该为你提供了成功集成 XxlJob 和达梦数据库所需的指导。祝你的任务调度系统顺利运行如果你遇到问题 评论区留言 我看到都会帮着解决的~
