xxl-job集成达梦数据库

1、前言

        由于甲方特殊要求，数据库指定达梦，xxl-job集成达梦的案例，博客上也是万紫千红，但并没有能够单独完美运行的案例，花了很多时间，踩了很多坑，终于把xxl-job集成达梦数据库sql兼容性问题解决。整理不易，望诸君高台贵手，点赞支持。

2、集成xxl-job

2.1 相关版本

本人使用xxl-job-admin版本对应2.4.0，这个可以直接官方网址下载

gitee地址：2.4.0版本

github地址：GitHub - xuxueli/xxl-job at 2.4.0

 xxl-job官方文档地址：分布式任务调度平台XXL-JOB

2.2 达梦数据驱动maven地址

<!-- https://mvnrepository.com/artifact/com.dameng/DmJdbcDriver18 -->
		<dependency>
			<groupId>com.dameng</groupId>
			<artifactId>DmJdbcDriver18</artifactId>
			<version>8.1.2.192</version>
		</dependency>

此为达梦截止到当前时间点，最新官方驱动地址

2.3 修改项目数据库连接方式

  datasource:
    driver-class-name: dm.jdbc.driver.DmDriver
    url: 'jdbc:dm://ip:端口?schema=用户名'
    username: 账号
    password: 密码

连接达梦数据库，如有不明白的，可以自行百度(相关文档很多)

2.4 最重要地方-初始化sql脚本

-- XXL_JOB_GROUP definition

-- Drop table

-- DROP TABLE XXL_JOB_GROUP;

CREATE TABLE XXL_JOB_GROUP (
	ID INT NOT NULL,
	APP_NAME VARCHAR(100) NOT NULL,
	TITLE VARCHAR(100) NOT NULL,
	ADDRESS_TYPE INT DEFAULT 0 NOT NULL,
	ADDRESS_LIST TEXT,
	UPDATE_TIME TIMESTAMP,
	CONSTRAINT CONS134218943 PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX INDEX33555778 ON XXL_JOB_GROUP (ID);


-- XXL_JOB_INFO definition

-- Drop table

-- DROP TABLE XXL_JOB_INFO;

CREATE TABLE XXL_JOB_INFO (
	ID INT NOT NULL,
	JOB_GROUP INT NOT NULL,
	JOB_DESC VARCHAR(255) NOT NULL,
	ADD_TIME TIMESTAMP,
	UPDATE_TIME TIMESTAMP,
	AUTHOR VARCHAR(64),
	ALARM_EMAIL VARCHAR(255),
	SCHEDULE_TYPE VARCHAR(50) DEFAULT 'NONE' NOT NULL,
	SCHEDULE_CONF VARCHAR(128),
	MISFIRE_STRATEGY VARCHAR(50) DEFAULT 'DO_NOTHING' NOT NULL,
	EXECUTOR_ROUTE_STRATEGY VARCHAR(50),
	EXECUTOR_HANDLER VARCHAR(255),
	EXECUTOR_PARAM VARCHAR(512),
	EXECUTOR_BLOCK_STRATEGY VARCHAR(50),
	EXECUTOR_TIMEOUT INT DEFAULT 0 NOT NULL,
	EXECUTOR_FAIL_RETRY_COUNT INT DEFAULT 0 NOT NULL,
	GLUE_TYPE VARCHAR(50) NOT NULL,
	GLUE_SOURCE CLOB,
	GLUE_REMARK VARCHAR(128),
	GLUE_UPDATETIME TIMESTAMP,
	CHILD_JOBID VARCHAR(255),
	TRIGGER_STATUS INT DEFAULT 0 NOT NULL,
	TRIGGER_LAST_TIME BIGINT DEFAULT 0 NOT NULL,
	TRIGGER_NEXT_TIME BIGINT DEFAULT 0 NOT NULL,
	CONSTRAINT CONS134218944 PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX INDEX33555780 ON XXL_JOB_INFO (ID);


-- XXL_JOB_LOCK definition

-- Drop table

-- DROP TABLE XXL_JOB_LOCK;

CREATE TABLE XXL_JOB_LOCK (
	LOCK_NAME VARCHAR(50) NOT NULL,
	CONSTRAINT CONS134218945 PRIMARY KEY (LOCK_NAME)
);
CREATE UNIQUE INDEX INDEX33555782 ON XXL_JOB_LOCK (LOCK_NAME);


-- XXL_JOB_LOG definition

-- Drop table

-- DROP TABLE XXL_JOB_LOG;

CREATE TABLE XXL_JOB_LOG (
	ID BIGINT NOT NULL,
	JOB_GROUP INT NOT NULL,
	JOB_ID INT NOT NULL,
	EXECUTOR_ADDRESS VARCHAR(255),
	EXECUTOR_HANDLER VARCHAR(255),
	EXECUTOR_PARAM VARCHAR(512),
	EXECUTOR_SHARDING_PARAM VARCHAR(20),
	EXECUTOR_FAIL_RETRY_COUNT INT DEFAULT 0 NOT NULL,
	TRIGGER_TIME TIMESTAMP,
	TRIGGER_CODE INT NOT NULL,
	TRIGGER_MSG TEXT,
	HANDLE_TIME TIMESTAMP,
	HANDLE_CODE INT NOT NULL,
	HANDLE_MSG TEXT,
	ALARM_STATUS TINYINT DEFAULT 0 NOT NULL,
	CONSTRAINT CONS134218946 PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX INDEX33555784 ON XXL_JOB_LOG (ID);
CREATE INDEX I_HANDLE_CODE ON XXL_JOB_LOG (HANDLE_CODE);
CREATE INDEX I_TRIGGER_TIME ON XXL_JOB_LOG (TRIGGER_TIME);


-- XXL_JOB_LOG_REPORT definition

-- Drop table

-- DROP TABLE XXL_JOB_LOG_REPORT;

CREATE TABLE XXL_JOB_LOG_REPORT (
	ID INT NOT NULL,
	TRIGGER_DAY TIMESTAMP,
	RUNNING_COUNT INT DEFAULT 0 NOT NULL,
	SUC_COUNT INT DEFAULT 0 NOT NULL,
	FAIL_COUNT INT DEFAULT 0 NOT NULL,
	UPDATE_TIME TIMESTAMP,
	CONSTRAINT CONS134218948 PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX INDEX33555790 ON XXL_JOB_LOG_REPORT (ID);
CREATE UNIQUE INDEX INDEX33555791 ON XXL_JOB_LOG_REPORT (TRIGGER_DAY);


-- XXL_JOB_LOGGLUE definition

-- Drop table

-- DROP TABLE XXL_JOB_LOGGLUE;

CREATE TABLE XXL_JOB_LOGGLUE (
	ID INT NOT NULL,
	JOB_ID INT NOT NULL,
	GLUE_TYPE VARCHAR(50),
	GLUE_SOURCE CLOB,
	GLUE_REMARK VARCHAR(128) NOT NULL,
	ADD_TIME TIMESTAMP,
	UPDATE_TIME TIMESTAMP,
	CONSTRAINT CONS134218947 PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX INDEX33555788 ON XXL_JOB_LOGGLUE (ID);


-- XXL_JOB_REGISTRY definition

-- Drop table

-- DROP TABLE XXL_JOB_REGISTRY;

CREATE TABLE XXL_JOB_REGISTRY (
	ID INT NOT NULL,
	REGISTRY_GROUP VARCHAR(50) NOT NULL,
	REGISTRY_KEY VARCHAR(255) NOT NULL,
	REGISTRY_VALUE VARCHAR(255) NOT NULL,
	UPDATE_TIME TIMESTAMP,
	CONSTRAINT CONS134218950 PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX INDEX33555793 ON XXL_JOB_REGISTRY (ID);
CREATE INDEX I_G_K_V ON XXL_JOB_REGISTRY (REGISTRY_GROUP,REGISTRY_KEY,REGISTRY_VALUE);


-- XXL_JOB_USER definition

-- Drop table

-- DROP TABLE XXL_JOB_USER;

CREATE TABLE XXL_JOB_USER (
	ID INT NOT NULL,
	USERNAME VARCHAR(50) NOT NULL,
	PASSWORD VARCHAR(50) NOT NULL,
	"ROLE" TINYINT NOT NULL,
	PERMISSION VARCHAR(255),
	CONSTRAINT CONS134218951 PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX INDEX33555796 ON XXL_JOB_USER (ID);
CREATE UNIQUE INDEX INDEX33555797 ON XXL_JOB_USER (USERNAME);

INSERT INTO XXL_JOB_USER (USERNAME,PASSWORD,"ROLE",PERMISSION) VALUES ('admin','827ccb0eea8a706c4c34a16891f84e7b',1,'');

因达梦数据库，和mysql的执行脚本，在语法上，大体相同，只是相关字段类型定义上，有出入，当然如果是达梦数据库高手，可以直接通过达梦提供的官方数据迁移工具，直接把官方提供的mysql脚本，迁移到达梦数据库，也是完美的解决方案；

上述sql最后一行，插入xxl-job-admin管理平台默认登录账号密码
账号：admin 密码：123456

2.5 重重重点来了！！

把sql导入到达梦数据库是第一步，此时的xxl-job-admin要想运行起来，还是不行的

因xxl-job-admin，XxlJobRegistryMapper.xml用了一些mysql的语法，在达梦数据库无法兼容，会

导致启动报错，所以XxlJobRegistryMapper可以直接替换为下方代码

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxl.job.admin.dao.XxlJobRegistryDao">
	
	<resultMap id="XxlJobRegistry" type="com.xxl.job.admin.core.model.XxlJobRegistry" >
		<result column="id" property="id" />
	    <result column="registry_group" property="registryGroup" />
	    <result column="registry_key" property="registryKey" />
	    <result column="registry_value" property="registryValue" />
		<result column="update_time" property="updateTime" />
	</resultMap>

	<sql id="Base_Column_List">
		t.id,
		t.registry_group,
		t.registry_key,
		t.registry_value,
		t.update_time
	</sql>

	<select id="findDead" parameterType="java.util.HashMap" resultType="java.lang.Integer" >
		SELECT t.id
		FROM xxl_job_registry AS t
-- 		<!--WHERE t.update_time <![CDATA[ < ]]> DATE_ADD(#{nowTime},INTERVAL SECOND (#{timeout},0))-->
		<!--WHERE t.update_time <![CDATA[ > ]]> DATE_ADD(#{nowTime},numtodsinterval(#{timeout},'SECOND'))-->
		WHERE t.update_time <![CDATA[ < ]]> (#{nowTime}+numtodsinterval(-#{timeout},'SECOND'))

	</select>
	
	<delete id="removeDead" parameterType="java.lang.Integer" >
		DELETE FROM xxl_job_registry
		WHERE id in
		<foreach collection="ids" item="item" open="(" close=")" separator="," >
			#{item}
		</foreach>
	</delete>

	<select id="findAll" parameterType="java.util.HashMap" resultMap="XxlJobRegistry">
		SELECT <include refid="Base_Column_List" />
		FROM xxl_job_registry AS t
<!--WHERE t.update_time <![CDATA[ > ]]> ADD_DAYS(#{nowTime},INTERVAL SECOND (#{timeout},0))-->
		<!--WHERE t.update_time <![CDATA[ <]]> DATE_ADD(#{nowTime},numtodsinterval(#{timeout},'SECOND'))-->
		WHERE t.update_time <![CDATA[ > ]]> (#{nowTime}+numtodsinterval(-#{timeout},'SECOND'))

	</select>

    <update id="registryUpdate" >
        UPDATE xxl_job_registry
        SET update_time = #{updateTime}
        WHERE registry_group = #{registryGroup}
          AND registry_key = #{registryKey}
          AND registry_value = #{registryValue}
    </update>

    <insert id="registrySave" >
        INSERT INTO xxl_job_registry( registry_group , registry_key , registry_value, update_time)
        VALUES( #{registryGroup},#{registryKey},#{registryValue},#{updateTime})
    </insert>

	<delete id="registryDelete" >
		DELETE FROM xxl_job_registry
		WHERE registry_group = #{registryGroup}
			AND registry_key = #{registryKey}
			AND registry_value = #{registryValue}
	</delete>

</mapper>

上述sql，尤其注意，t.update_time <![CDATA[ < ]]> (#{nowTime}+numtodsinterval(-#{timeout},'SECOND'))，这里的写法，这里是导致xxl-job-admin无法发现注册的执行器ip的关键 

3、总结

执行器如果注册到xxl-job-admin 直接参考官方案例

需要注意，一定要把官方执行器demo中XxlJobConfig这个类，复制到自己需要调用xxl-job-admin工程内，该方法，本质上就是开启线程，把当前工程，注册进xxl-job-admin。