xxl-job适配达梦数据库并制作镜像、源码部署xxl-job

最新推荐文章于 2024-09-03 14:53:41 发布

五月高高

最新推荐文章于 2024-09-03 14:53:41 发布

阅读量992

点赞数 27

文章标签：达梦 xxl-job Docker

本文链接：https://blog.csdn.net/baidu_35160588/article/details/140729864

版权

背景：因项目需要信创，需将原本的mysql数据库，改成达梦数据库

一、部署达梦数据库

1.1 部署达梦数据库服务

可参考：Docker安装达梦数据库_达梦数据库docker镜像-CSDN博客

PS：部署达梦数据库时，需加上大小写不敏感配置，否则会报无效表明或视图，或无法识别某些列名

1.2 创建xxl-job数据库

--创建表空间
CREATE TABLESPACE "XXLJOB" DATAFILE 'XXLJOB.DBF' SIZE 300 autoextend on maxsize 67108863 CACHE = NORMAL;
--创建用户
CREATE USER "XXLJOB" IDENTIFIED BY "XXLJOB!123" LIMIT PASSWORD_LOCK_TIME 1, PASSWORD_GRACE_TIME 10 DEFAULT TABLESPACE "XXLJOB";
--给用户授权
GRANT "RESOURCE","PUBLIC","DBA","VTI" TO "XXLJOB";

1.3 适配了达梦的xxl-job脚本

--创建表空间
CREATE TABLESPACE  "XXLJOB" DATAFILE 'XXLJOB.DBF' SIZE 300 autoextend on maxsize 67108863 CACHE = NORMAL;
--创建用户
CREATE USER "XXLJOB" IDENTIFIED BY "XXLJOB!123" LIMIT PASSWORD_LOCK_TIME 1, PASSWORD_GRACE_TIME 10 DEFAULT TABLESPACE "XXLJOB";
--给用户授权
GRANT "RESOURCE","PUBLIC","DBA","VTI" TO "XXLJOB";

CREATE TABLE "XXLJOB"."XXL_JOB_GROUP"
(
    "ID" INT IDENTITY(2, 1) NOT NULL,
    "APP_NAME" VARCHAR(64) NOT NULL,
    "TITLE" VARCHAR(12) NOT NULL,
    "ADDRESS_TYPE" INT DEFAULT 0 NOT NULL,
    "ADDRESS_LIST" TEXT,
    "UPDATE_TIME" TIMESTAMP(0),
    NOT CLUSTER PRIMARY KEY("ID")) STORAGE(ON "XXLJOB", CLUSTERBTR) ;

COMMENT ON COLUMN "XXLJOB"."XXL_JOB_GROUP"."ADDRESS_LIST" IS '执行器地址列表，多地址逗号分隔';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_GROUP"."ADDRESS_TYPE" IS '执行器地址类型：0=自动注册、1=手动录入';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_GROUP"."APP_NAME" IS '执行器AppName';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_GROUP"."TITLE" IS '执行器名称';


CREATE TABLE "XXLJOB"."XXL_JOB_INFO"
(
    "ID" INT IDENTITY(2, 1) NOT NULL,
    "JOB_GROUP" INT NOT NULL,
    "JOB_DESC" VARCHAR(255) NOT NULL,
    "ADD_TIME" TIMESTAMP(0),
    "UPDATE_TIME" TIMESTAMP(0),
    "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(0),
    "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,
    NOT CLUSTER PRIMARY KEY("ID")) STORAGE(ON "XXLJOB", CLUSTERBTR) ;

COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."ALARM_EMAIL" IS '报警邮件';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."AUTHOR" IS '作者';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."CHILD_JOBID" IS '子任务ID，多个逗号分隔';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."EXECUTOR_BLOCK_STRATEGY" IS '阻塞处理策略';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."EXECUTOR_FAIL_RETRY_COUNT" IS '失败重试次数';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."EXECUTOR_HANDLER" IS '执行器任务handler';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."EXECUTOR_PARAM" IS '执行器任务参数';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."EXECUTOR_ROUTE_STRATEGY" IS '执行器路由策略';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."EXECUTOR_TIMEOUT" IS '任务执行超时时间，单位秒';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."GLUE_REMARK" IS 'GLUE备注';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."GLUE_SOURCE" IS 'GLUE源代码';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."GLUE_TYPE" IS 'GLUE类型';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."GLUE_UPDATETIME" IS 'GLUE更新时间';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."JOB_GROUP" IS '执行器主键ID';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."MISFIRE_STRATEGY" IS '调度过期策略';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."SCHEDULE_CONF" IS '调度配置，值含义取决于调度类型';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."SCHEDULE_TYPE" IS '调度类型';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."TRIGGER_LAST_TIME" IS '上次调度时间';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."TRIGGER_NEXT_TIME" IS '下次调度时间';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_INFO"."TRIGGER_STATUS" IS '调度状态：0-停止，1-运行';


CREATE TABLE "XXLJOB"."XXL_JOB_LOCK"
(
    "LOCK_NAME" VARCHAR(50) NOT NULL,
    NOT CLUSTER PRIMARY KEY("LOCK_NAME")) STORAGE(ON "XXLJOB", CLUSTERBTR) ;

COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOCK"."LOCK_NAME" IS '锁名称';


CREATE TABLE "XXLJOB"."XXL_JOB_LOG"
(
    "ID" BIGINT IDENTITY(1, 1) 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(0),
    "TRIGGER_CODE" INT NOT NULL,
    "TRIGGER_MSG" TEXT,
    "HANDLE_TIME" TIMESTAMP(0),
    "HANDLE_CODE" INT NOT NULL,
    "HANDLE_MSG" TEXT,
    "ALARM_STATUS" TINYINT DEFAULT 0 NOT NULL,
    NOT CLUSTER PRIMARY KEY("ID")) STORAGE(ON "XXLJOB", CLUSTERBTR) ;

COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."ALARM_STATUS" IS '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."EXECUTOR_ADDRESS" IS '执行器地址，本次执行的地址';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."EXECUTOR_FAIL_RETRY_COUNT" IS '失败重试次数';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."EXECUTOR_HANDLER" IS '执行器任务handler';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."EXECUTOR_PARAM" IS '执行器任务参数';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."EXECUTOR_SHARDING_PARAM" IS '执行器任务分片参数，格式如 1/2';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."HANDLE_CODE" IS '执行-状态';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."HANDLE_MSG" IS '执行-日志';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."HANDLE_TIME" IS '执行-时间';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."JOB_GROUP" IS '执行器主键ID';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."JOB_ID" IS '任务，主键ID';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."TRIGGER_CODE" IS '调度-结果';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."TRIGGER_MSG" IS '调度-日志';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG"."TRIGGER_TIME" IS '调度-时间';


CREATE  INDEX "I_TRIGGER_TIME" ON "XXLJOB"."XXL_JOB_LOG"("TRIGGER_TIME" ASC) STORAGE(ON "XXLJOB", CLUSTERBTR) ;
CREATE  INDEX "I_HANDLE_CODE" ON "XXLJOB"."XXL_JOB_LOG"("HANDLE_CODE" ASC) STORAGE(ON "XXLJOB", CLUSTERBTR) ;

CREATE TABLE "XXLJOB"."XXL_JOB_LOGGLUE"
(
    "ID" INT IDENTITY(1, 1) NOT NULL,
    "JOB_ID" INT NOT NULL,
    "GLUE_TYPE" VARCHAR(50),
    "GLUE_SOURCE" CLOB,
    "GLUE_REMARK" VARCHAR(128) NOT NULL,
    "ADD_TIME" TIMESTAMP(0),
    "UPDATE_TIME" TIMESTAMP(0),
    NOT CLUSTER PRIMARY KEY("ID")) STORAGE(ON "XXLJOB", CLUSTERBTR) ;

COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOGGLUE"."GLUE_REMARK" IS 'GLUE备注';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOGGLUE"."GLUE_SOURCE" IS 'GLUE源代码';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOGGLUE"."GLUE_TYPE" IS 'GLUE类型';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOGGLUE"."JOB_ID" IS '任务，主键ID';


CREATE TABLE "XXLJOB"."XXL_JOB_LOG_REPORT"
(
    "ID" INT IDENTITY(1, 1) NOT NULL,
    "TRIGGER_DAY" TIMESTAMP(0),
    "RUNNING_COUNT" INT DEFAULT 0 NOT NULL,
    "SUC_COUNT" INT DEFAULT 0 NOT NULL,
    "FAIL_COUNT" INT DEFAULT 0 NOT NULL,
    "UPDATE_TIME" TIMESTAMP(0),
    NOT CLUSTER PRIMARY KEY("ID"),
    CONSTRAINT "I_TRIGGER_DAY" UNIQUE("TRIGGER_DAY")) STORAGE(ON "XXLJOB", CLUSTERBTR) ;

COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG_REPORT"."FAIL_COUNT" IS '执行失败-日志数量';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG_REPORT"."RUNNING_COUNT" IS '运行中-日志数量';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG_REPORT"."SUC_COUNT" IS '执行成功-日志数量';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_LOG_REPORT"."TRIGGER_DAY" IS '调度-时间';


CREATE TABLE "XXLJOB"."XXL_JOB_REGISTRY"
(
    "ID" INT 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(0),
    NOT CLUSTER PRIMARY KEY("ID")) STORAGE(ON "XXLJOB", CLUSTERBTR) ;

CREATE  INDEX "I_G_K_V" ON "XXLJOB"."XXL_JOB_REGISTRY"("REGISTRY_GROUP" ASC,"REGISTRY_KEY" ASC,"REGISTRY_VALUE" ASC) STORAGE(ON "XXLJOB", CLUSTERBTR) ;

CREATE TABLE "XXLJOB"."XXL_JOB_USER"
(
    "ID" INT IDENTITY(2, 1) NOT NULL,
    "USERNAME" VARCHAR(50) NOT NULL,
    "PASSWORD" VARCHAR(50) NOT NULL,
    "ROLE" TINYINT NOT NULL,
    "PERMISSION" VARCHAR(255),
    NOT CLUSTER PRIMARY KEY("ID"),
    CONSTRAINT "I_USERNAME" UNIQUE("USERNAME")) STORAGE(ON "XXLJOB", CLUSTERBTR) ;

COMMENT ON COLUMN "XXLJOB"."XXL_JOB_USER"."PASSWORD" IS '密码';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_USER"."PERMISSION" IS '权限：执行器ID列表，多个逗号分割';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_USER"."ROLE" IS '角色：0-普通用户、1-管理员';
COMMENT ON COLUMN "XXLJOB"."XXL_JOB_USER"."USERNAME" IS '账号';

INSERT INTO XXLJOB."xxl_job_group"("app_name", "title", "address_type", "address_list", "update_time") VALUES (1, 'xxl-job-executor-sample', '示例执行器', 0, NULL, '2018-11-03 22:21:31' );
INSERT INTO XXLJOB."xxl_job_info"("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") VALUES (1, 1, '测试任务1', '2018-11-03 22:21:31', '2018-11-03 22:21:31', 'XXL', '', 'CRON', '0 0 0 * * ? *', 'DO_NOTHING', 'FIRST', 'demoJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2018-11-03 22:21:31', '');
INSERT INTO XXLJOB."xxl_job_user"("username", "password", "role", "permission") VALUES (1, 'admin', 'e10adc3949ba59abbe56e057f20f883e', 1, NULL);
INSERT INTO XXLJOB."xxl_job_lock" ( "lock_name") VALUES ( 'schedule_lock');
commit;

二、xxl-job适配达梦数据库

这里以xxl-job-2.3.0为例子

2.1 下载xxl-job-2.3.0源码

可参考：Release XXL-JOB v2.3.0，分布式任务调度平台 · xuxueli/xxl-job · GitHub

2.2 修改XXL-JOB源码以适配达梦数据库

2.2.1 引入达梦驱动依赖

首先在xxl-job-admin的pom.xml中引入达梦数据库依赖

<dependency>
<groupId>com.dameng</groupId>
<artifactId>DmJdbcDriver18</artifactId>
<version>8.1.3.140</version>
</dependency>

2.2.2 修改application.properties文件

spring.datasource.driver-class-name=dm.jdbc.driver.DmDriver
spring.datasource.url=jdbc:dm://192.168.111.129:5236?schema=XXLJOB&characterEncoding=UTF-8&useUnicode=true&serverTimezone=Asia/Shanghai
spring.datasource.username=XXLJOB
spring.datasource.password=XXLJOB!123

2.2.3 修改mybatis-mapper 目录下自定义SQL文件

XxlJobGroupMapper.xml

<?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.XxlJobGroupDao">

	<resultMap id="XxlJobGroup" type="com.xxl.job.admin.core.model.XxlJobGroup" >
		<result column="id" property="id" />
		<result column="app_name" property="appname" />
		<result column="title" property="title" />
		<result column="address_type" property="addressType" />
		<result column="address_list" property="addressList" />
		<result column="update_time" property="updateTime" />
	</resultMap>

	<sql id="Base_Column_List">
		t.id,
		t.app_name,
		t.title,
		t.address_type,
		t.address_list,
		t.update_time
	</sql>

	<select id="findAll" resultMap="XxlJobGroup">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_GROUP" AS t
		ORDER BY t.app_name, t.title, t.id ASC
	</select>

	<select id="findByAddressType" parameterType="java.lang.Integer" resultMap="XxlJobGroup">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_GROUP" AS t
		WHERE t.address_type = #{addressType}
		ORDER BY t.app_name, t.title, t.id ASC
	</select>

	<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobGroup" useGeneratedKeys="true" keyProperty="id" >
		INSERT INTO "XXLJOB"."XXL_JOB_GROUP" ( app_name, title, address_type, address_list, update_time)
		values ( #{appname}, #{title}, #{addressType}, #{addressList}, #{updateTime} );
	</insert>

	<update id="update" parameterType="com.xxl.job.admin.core.model.XxlJobGroup" >
		UPDATE "XXLJOB"."XXL_JOB_GROUP"
		SET app_name = #{appname},
			title = #{title},
			address_type = #{addressType},
			address_list = #{addressList},
			update_time = #{updateTime}
		WHERE id = #{id}
	</update>

	<delete id="remove" parameterType="java.lang.Integer" >
		DELETE FROM "XXLJOB"."XXL_JOB_GROUP"
		WHERE id = #{id}
	</delete>

	<select id="load" parameterType="java.lang.Integer" resultMap="XxlJobGroup">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_GROUP" AS t
		WHERE t.id = #{id}
	</select>

	<select id="pageList" parameterType="java.util.HashMap" resultMap="XxlJobGroup">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_GROUP" AS t
		<trim prefix="WHERE" prefixOverrides="AND | OR" >
			<if test="appname != null and appname != ''">
				AND t.app_name like CONCAT(CONCAT('%', #{appname}), '%')
			</if>
			<if test="title != null and title != ''">
				AND t.title like CONCAT(CONCAT('%', #{title}), '%')
			</if>
		</trim>
		ORDER BY t.app_name, t.title, t.id ASC
		LIMIT #{offset}, #{pagesize}
	</select>

	<select id="pageListCount" parameterType="java.util.HashMap" resultType="int">
		SELECT count(1)
		FROM "XXLJOB"."XXL_JOB_GROUP" AS t
		<trim prefix="WHERE" prefixOverrides="AND | OR" >
			<if test="appname != null and appname != ''">
				AND t.app_name like CONCAT(CONCAT('%', #{appname}), '%')
			</if>
			<if test="title != null and title != ''">
				AND t.title like CONCAT(CONCAT('%', #{title}), '%')
			</if>
		</trim>
	</select>

</mapper>

XxlJobInfoMapper.xml

<?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.XxlJobInfoDao">

	<resultMap id="XxlJobInfo" type="com.xxl.job.admin.core.model.XxlJobInfo" >
		<result column="id" property="id" />

		<result column="job_group" property="jobGroup" />
		<result column="job_desc" property="jobDesc" />

		<result column="add_time" property="addTime" />
		<result column="update_time" property="updateTime" />

		<result column="author" property="author" />
		<result column="alarm_email" property="alarmEmail" />

		<result column="schedule_type" property="scheduleType" />
		<result column="schedule_conf" property="scheduleConf" />
		<result column="misfire_strategy" property="misfireStrategy" />

		<result column="executor_route_strategy" property="executorRouteStrategy" />
		<result column="executor_handler" property="executorHandler" />
		<result column="executor_param" property="executorParam" />
		<result column="executor_block_strategy" property="executorBlockStrategy" />
		<result column="executor_timeout" property="executorTimeout" />
		<result column="executor_fail_retry_count" property="executorFailRetryCount" />

		<result column="glue_type" property="glueType" />
		<result column="glue_source" property="glueSource" />
		<result column="glue_remark" property="glueRemark" />
		<result column="glue_updatetime" property="glueUpdatetime" />

		<result column="child_jobid" property="childJobId" />

		<result column="trigger_status" property="triggerStatus" />
		<result column="trigger_last_time" property="triggerLastTime" />
		<result column="trigger_next_time" property="triggerNextTime" />
	</resultMap>

	<sql id="Base_Column_List">
		t.id,
		t.job_group,
		t.job_desc,
		t.add_time,
		t.update_time,
		t.author,
		t.alarm_email,
		t.schedule_type,
		t.schedule_conf,
		t.misfire_strategy,
		t.executor_route_strategy,
		t.executor_handler,
		t.executor_param,
		t.executor_block_strategy,
		t.executor_timeout,
		t.executor_fail_retry_count,
		t.glue_type,
		t.glue_source,
		t.glue_remark,
		t.glue_updatetime,
		t.child_jobid,
		t.trigger_status,
		t.trigger_last_time,
		t.trigger_next_time
	</sql>

	<select id="pageList" parameterType="java.util.HashMap" resultMap="XxlJobInfo">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_INFO" AS t
		<trim prefix="WHERE" prefixOverrides="AND | OR" >
			<if test="jobGroup gt 0">
				AND t.job_group = #{jobGroup}
			</if>
			<if test="triggerStatus gte 0">
				AND t.trigger_status = #{triggerStatus}
			</if>
			<if test="jobDesc != null and jobDesc != ''">
				AND t.job_desc like CONCAT(CONCAT('%', #{jobDesc}), '%')
			</if>
			<if test="executorHandler != null and executorHandler != ''">
				AND t.executor_handler like CONCAT(CONCAT('%', #{executorHandler}), '%')
			</if>
			<if test="author != null and author != ''">
				AND t.author like CONCAT(CONCAT('%', #{author}), '%')
			</if>
		</trim>
		ORDER BY id DESC
		LIMIT #{offset}, #{pagesize}
	</select>

	<select id="pageListCount" parameterType="java.util.HashMap" resultType="int">
		SELECT count(1)
		FROM "XXLJOB"."XXL_JOB_INFO" AS t
		<trim prefix="WHERE" prefixOverrides="AND | OR" >
			<if test="jobGroup gt 0">
				AND t.job_group = #{jobGroup}
			</if>
			<if test="triggerStatus gte 0">
				AND t.trigger_status = #{triggerStatus}
			</if>
			<if test="jobDesc != null and jobDesc != ''">
				AND t.job_desc like CONCAT(CONCAT('%', #{jobDesc}), '%')
			</if>
			<if test="executorHandler != null and executorHandler != ''">
				AND t.executor_handler like CONCAT(CONCAT('%', #{executorHandler}), '%')
			</if>
			<if test="author != null and author != ''">
				AND t.author like CONCAT(CONCAT('%', #{author}), '%')
			</if>
		</trim>
	</select>

	<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobInfo" useGeneratedKeys="true" keyProperty="id" >
		INSERT INTO "XXLJOB"."XXL_JOB_INFO" (
		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 (
		#{jobGroup},
		#{jobDesc},
		#{addTime},
		#{updateTime},
		#{author},
		#{alarmEmail},
		#{scheduleType},
		#{scheduleConf},
		#{misfireStrategy},
		#{executorRouteStrategy},
		#{executorHandler},
		#{executorParam},
		#{executorBlockStrategy},
		#{executorTimeout},
		#{executorFailRetryCount},
		#{glueType},
		#{glueSource},
		#{glueRemark},
		#{glueUpdatetime},
		#{childJobId},
		#{triggerStatus},
		#{triggerLastTime},
		#{triggerNextTime}
		);
		<!--<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
			SELECT LAST_INSERT_ID()
			/*SELECT @@IDENTITY AS id*/
		</selectKey>-->
	</insert>

	<select id="loadById" parameterType="java.util.HashMap" resultMap="XxlJobInfo">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_INFO" AS t
		WHERE t.id = #{id}
	</select>

	<update id="update" parameterType="com.xxl.job.admin.core.model.XxlJobInfo" >
		UPDATE "XXLJOB"."XXL_JOB_INFO"
		SET
			job_group = #{jobGroup},
			job_desc = #{jobDesc},
			update_time = #{updateTime},
			author = #{author},
			alarm_email = #{alarmEmail},
			schedule_type = #{scheduleType},
			schedule_conf = #{scheduleConf},
			misfire_strategy = #{misfireStrategy},
			executor_route_strategy = #{executorRouteStrategy},
			executor_handler = #{executorHandler},
			executor_param = #{executorParam},
			executor_block_strategy = #{executorBlockStrategy},
			executor_timeout = ${executorTimeout},
			executor_fail_retry_count = ${executorFailRetryCount},
			glue_type = #{glueType},
			glue_source = #{glueSource},
			glue_remark = #{glueRemark},
			glue_updatetime = #{glueUpdatetime},
			child_jobid = #{childJobId},
			trigger_status = #{triggerStatus},
			trigger_last_time = #{triggerLastTime},
			trigger_next_time = #{triggerNextTime}
		WHERE id = #{id}
	</update>

	<delete id="delete" parameterType="java.util.HashMap">
		DELETE
		FROM "XXLJOB"."XXL_JOB_INFO"
		WHERE id = #{id}
	</delete>

	<select id="getJobsByGroup" parameterType="java.util.HashMap" resultMap="XxlJobInfo">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_INFO" AS t
		WHERE t.job_group = #{jobGroup}
	</select>

	<select id="findAllCount" resultType="int">
		SELECT count(1)
		FROM "XXLJOB"."XXL_JOB_INFO"
	</select>


	<select id="scheduleJobQuery" parameterType="java.util.HashMap" resultMap="XxlJobInfo">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_INFO" AS t
		WHERE t.trigger_status = 1
		and t.trigger_next_time <![CDATA[ <= ]]> #{maxNextTime}
		ORDER BY id ASC
		LIMIT #{pagesize}
	</select>

	<update id="scheduleUpdate" parameterType="com.xxl.job.admin.core.model.XxlJobInfo"  >
		UPDATE "XXLJOB"."XXL_JOB_INFO"
		SET
			trigger_last_time = #{triggerLastTime},
			trigger_next_time = #{triggerNextTime},
			trigger_status = #{triggerStatus}
		WHERE id = #{id}
	</update>

</mapper>

XxlJobLogGlueMapper.xml

<?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.XxlJobLogGlueDao">

	<resultMap id="XxlJobLogGlue" type="com.xxl.job.admin.core.model.XxlJobLogGlue" >
		<result column="id" property="id" />
		<result column="job_id" property="jobId" />
		<result column="glue_type" property="glueType" />
		<result column="glue_source" property="glueSource" />
		<result column="glue_remark" property="glueRemark" />
		<result column="add_time" property="addTime" />
		<result column="update_time" property="updateTime" />
	</resultMap>

	<sql id="Base_Column_List">
		t.id,
		t.job_id,
		t.glue_type,
		t.glue_source,
		t.glue_remark,
		t.add_time,
		t.update_time
	</sql>

	<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobLogGlue" useGeneratedKeys="true" keyProperty="id" >
		INSERT INTO "XXLJOB"."XXL_JOB_LOGGLUE" (
		job_id,
		glue_type,
		glue_source,
		glue_remark,
		add_time,
		update_time
		) VALUES (
		#{jobId},
		#{glueType},
		#{glueSource},
		#{glueRemark},
		#{addTime},
		#{updateTime}
		);
		<!--<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
			SELECT LAST_INSERT_ID()
		</selectKey>-->
	</insert>

	<select id="findByJobId" parameterType="java.lang.Integer" resultMap="XxlJobLogGlue">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_LOGGLUE" AS t
		WHERE t.job_id = #{jobId}
		ORDER BY id DESC
	</select>

	<delete id="removeOld" >
		DELETE FROM "XXLJOB"."XXL_JOB_LOGGLUE"
		WHERE id NOT in(
			SELECT id FROM(
							  SELECT id FROM "XXLJOB"."XXL_JOB_LOGGLUE"
							  WHERE job_id = #{jobId}
							  ORDER BY update_time desc
								  LIMIT 0, #{limit}
						  ) t1
		) AND job_id = #{jobId}
	</delete>

	<delete id="deleteByJobId" parameterType="java.lang.Integer" >
		DELETE FROM "XXLJOB"."XXL_JOB_LOGGLUE"
		WHERE job_id = #{jobId}
	</delete>

</mapper>

XxlJobLogMapper.xml

<?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.XxlJobLogDao">

	<resultMap id="XxlJobLog" type="com.xxl.job.admin.core.model.XxlJobLog" >
		<result column="id" property="id" />

		<result column="job_group" property="jobGroup" />
		<result column="job_id" property="jobId" />

		<result column="executor_address" property="executorAddress" />
		<result column="executor_handler" property="executorHandler" />
		<result column="executor_param" property="executorParam" />
		<result column="executor_sharding_param" property="executorShardingParam" />
		<result column="executor_fail_retry_count" property="executorFailRetryCount" />

		<result column="trigger_time" property="triggerTime" />
		<result column="trigger_code" property="triggerCode" />
		<result column="trigger_msg" property="triggerMsg" />

		<result column="handle_time" property="handleTime" />
		<result column="handle_code" property="handleCode" />
		<result column="handle_msg" property="handleMsg" />

		<result column="alarm_status" property="alarmStatus" />
	</resultMap>

	<sql id="Base_Column_List">
		t.id,
		t.job_group,
		t.job_id,
		t.executor_address,
		t.executor_handler,
		t.executor_param,
		t.executor_sharding_param,
		t.executor_fail_retry_count,
		t.trigger_time,
		t.trigger_code,
		t.trigger_msg,
		t.handle_time,
		t.handle_code,
		t.handle_msg,
		t.alarm_status
	</sql>

	<select id="pageList" resultMap="XxlJobLog">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_LOG" AS t
		<trim prefix="WHERE" prefixOverrides="AND | OR" >
			<if test="jobId==0 and jobGroup gt 0">
				AND t.job_group = #{jobGroup}
			</if>
			<if test="jobId gt 0">
				AND t.job_id = #{jobId}
			</if>
			<if test="triggerTimeStart != null">
				AND t.trigger_time <![CDATA[ >= ]]> #{triggerTimeStart}
			</if>
			<if test="triggerTimeEnd != null">
				AND t.trigger_time <![CDATA[ <= ]]> #{triggerTimeEnd}
			</if>
			<if test="logStatus == 1" >
				AND t.handle_code = 200
			</if>
			<if test="logStatus == 2" >
				AND (
				t.trigger_code NOT IN (0, 200) OR
				t.handle_code NOT IN (0, 200)
				)
			</if>
			<if test="logStatus == 3" >
				AND t.trigger_code = 200
				AND t.handle_code = 0
			</if>
		</trim>
		ORDER BY t.trigger_time DESC
		LIMIT #{offset}, #{pagesize}
	</select>

	<select id="pageListCount" resultType="int">
		SELECT count(1)
		FROM "XXLJOB"."XXL_JOB_LOG" AS t
		<trim prefix="WHERE" prefixOverrides="AND | OR" >
			<if test="jobId==0 and jobGroup gt 0">
				AND t.job_group = #{jobGroup}
			</if>
			<if test="jobId gt 0">
				AND t.job_id = #{jobId}
			</if>
			<if test="triggerTimeStart != null">
				AND t.trigger_time <![CDATA[ >= ]]> #{triggerTimeStart}
			</if>
			<if test="triggerTimeEnd != null">
				AND t.trigger_time <![CDATA[ <= ]]> #{triggerTimeEnd}
			</if>
			<if test="logStatus == 1" >
				AND t.handle_code = 200
			</if>
			<if test="logStatus == 2" >
				AND (
				t.trigger_code NOT IN (0, 200) OR
				t.handle_code NOT IN (0, 200)
				)
			</if>
			<if test="logStatus == 3" >
				AND t.trigger_code = 200
				AND t.handle_code = 0
			</if>
		</trim>
	</select>

	<select id="load" parameterType="java.lang.Long" resultMap="XxlJobLog">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_LOG" AS t
		WHERE t.id = #{id}
	</select>


	<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobLog" useGeneratedKeys="true" keyProperty="id" >
		INSERT INTO "XXLJOB"."XXL_JOB_LOG" (
		job_group,
		job_id,
		trigger_time,
		trigger_code,
		handle_code
		) VALUES (
		#{jobGroup},
		#{jobId},
		#{triggerTime},
		#{triggerCode},
		#{handleCode}
		);
		<!--<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
			SELECT LAST_INSERT_ID()
		</selectKey>-->
	</insert>

	<update id="updateTriggerInfo" >
		UPDATE "XXLJOB"."XXL_JOB_LOG"
		SET
			trigger_time= #{triggerTime},
			trigger_code= #{triggerCode},
			trigger_msg= #{triggerMsg},
			executor_address= #{executorAddress},
			executor_handler=#{executorHandler},
			executor_param= #{executorParam},
			executor_sharding_param= #{executorShardingParam},
			executor_fail_retry_count= #{executorFailRetryCount}
		WHERE `id`= #{id}
	</update>

	<update id="updateHandleInfo">
		UPDATE "XXLJOB"."XXL_JOB_LOG"
		SET
			handle_time= #{handleTime},
			handle_code= #{handleCode},
			handle_msg= #{handleMsg}
		WHERE `id`= #{id}
	</update>

	<delete id="delete" >
		delete from "XXLJOB"."XXL_JOB_LOG"
		WHERE job_id = #{jobId}
	</delete>

	<!--<select id="triggerCountByDay" resultType="java.util.Map" >
        SELECT
            DATE_FORMAT(trigger_time,'%Y-%m-%d') triggerDay,
            COUNT(handle_code) triggerDayCount,
            SUM(CASE WHEN (trigger_code in (0, 200) and handle_code = 0) then 1 else 0 end) as triggerDayCountRunning,
            SUM(CASE WHEN handle_code = 200 then 1 else 0 end) as triggerDayCountSuc
        FROM xxl_job_log
        WHERE trigger_time BETWEEN #{from} and #{to}
        GROUP BY triggerDay
        ORDER BY triggerDay
    </select>-->

	<select id="findLogReport" resultType="java.util.Map" >
		SELECT
			COUNT(handle_code) triggerDayCount,
			SUM(CASE WHEN (trigger_code in (0, 200) and handle_code = 0) then 1 else 0 end) as triggerDayCountRunning,
			SUM(CASE WHEN handle_code = 200 then 1 else 0 end) as triggerDayCountSuc
		FROM "XXLJOB"."XXL_JOB_LOG"
		WHERE trigger_time BETWEEN #{from} and #{to}
	</select>

	<select id="findClearLogIds" resultType="long" >
		SELECT id FROM "XXLJOB"."XXL_JOB_LOG"
		<trim prefix="WHERE" prefixOverrides="AND | OR" >
			<if test="jobGroup gt 0">
				AND job_group = #{jobGroup}
			</if>
			<if test="jobId gt 0">
				AND job_id = #{jobId}
			</if>
			<if test="clearBeforeTime != null">
				AND trigger_time <![CDATA[ <= ]]> #{clearBeforeTime}
			</if>
			<if test="clearBeforeNum gt 0">
				AND id NOT in(
				SELECT id FROM(
				SELECT id FROM "XXLJOB"."XXL_JOB_LOG" AS t
				<trim prefix="WHERE" prefixOverrides="AND | OR" >
					<if test="jobGroup gt 0">
						AND t.job_group = #{jobGroup}
					</if>
					<if test="jobId gt 0">
						AND t.job_id = #{jobId}
					</if>
				</trim>
				ORDER BY t.trigger_time desc
				LIMIT 0, #{clearBeforeNum}
				) t1
				)
			</if>
		</trim>
		order by id asc
		LIMIT #{pagesize}
	</select>

	<delete id="clearLog" >
		delete from "XXLJOB"."XXL_JOB_LOG"
		WHERE id in
		<foreach collection="logIds" item="item" open="(" close=")" separator="," >
			#{item}
		</foreach>
	</delete>

	<select id="findFailJobLogIds" resultType="long" >
		SELECT id FROM "XXLJOB"."XXL_JOB_LOG"
		WHERE !(
			(trigger_code in (0, 200) and handle_code = 0)
		   OR
			(handle_code = 200)
			)
		  AND alarm_status = 0
		ORDER BY id ASC
			LIMIT #{pagesize}
	</select>

	<update id="updateAlarmStatus" >
		UPDATE "XXLJOB"."XXL_JOB_LOG"
		SET
			alarm_status = #{newAlarmStatus}
		WHERE id= #{logId} AND alarm_status = #{oldAlarmStatus}
	</update>

	<select id="findLostJobIds" resultType="long" >
		SELECT t.id
		FROM "XXLJOB"."XXL_JOB_LOG" AS t
		WHERE t.trigger_code = 200
		  and t.handle_code = 0
		  and t.trigger_time <![CDATA[ <= ]]> #{losedTime}
		  and t.executor_address not in (
			SELECT t2.registry_value
			FROM "XXLJOB"."XXL_JOB_REGISTRY" AS t2
		)
	</select>

</mapper>

XxlJobLogReportMapper.xml

<?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.XxlJobLogReportDao">

	<resultMap id="XxlJobLogReport" type="com.xxl.job.admin.core.model.XxlJobLogReport" >
		<result column="id" property="id" />
		<result column="trigger_day" property="triggerDay" />
		<result column="running_count" property="runningCount" />
		<result column="suc_count" property="sucCount" />
		<result column="fail_count" property="failCount" />
	</resultMap>

	<sql id="Base_Column_List">
		t.id,
		t.trigger_day,
		t.running_count,
		t.suc_count,
		t.fail_count
	</sql>

	<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobLogReport" useGeneratedKeys="true" keyProperty="id" >
		INSERT INTO "XXLJOB"."XXL_JOB_LOG_REPORT" (
		trigger_day,
		running_count,
		suc_count,
		fail_count
		) VALUES (
		#{triggerDay},
		#{runningCount},
		#{sucCount},
		#{failCount}
		);
		<!--<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
			SELECT LAST_INSERT_ID()
		</selectKey>-->
	</insert>

	<update id="update" >
		UPDATE "XXLJOB"."XXL_JOB_LOG_REPORT"
		SET running_count = #{runningCount},
			suc_count = #{sucCount},
			fail_count = #{failCount}
		WHERE trigger_day = #{triggerDay}
	</update>

	<select id="queryLogReport" resultMap="XxlJobLogReport">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_LOG_REPORT" AS t
		WHERE t.trigger_day between #{triggerDayFrom} and #{triggerDayTo}
		ORDER BY t.trigger_day ASC
	</select>

	<select id="queryLogReportTotal" resultMap="XxlJobLogReport">
		SELECT
			SUM(running_count) running_count,
			SUM(suc_count) suc_count,
			SUM(fail_count) fail_count
		FROM "XXLJOB"."XXL_JOB_LOG_REPORT" AS t
	</select>

</mapper>

XxlJobRegistryMapper.xml

<?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 "XXLJOB"."XXL_JOB_REGISTRY" AS t
		WHERE t.update_time <![CDATA[ < ]]> DATEADD(SECOND, -#{timeout}, #{nowTime})
	</select>

	<delete id="removeDead" parameterType="java.lang.Integer" >
		DELETE FROM "XXLJOB"."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 "XXLJOB"."XXL_JOB_REGISTRY" AS t
		WHERE t.update_time <![CDATA[ > ]]> DATEADD(SECOND,-#{timeout},#{nowTime})
	</select>

	<update id="registryUpdate" >
		UPDATE "XXLJOB"."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 "XXLJOB"."XXL_JOB_REGISTRY"( registry_group , registry_key , registry_value, update_time)
		VALUES( #{registryGroup}  , #{registryKey} , #{registryValue}, #{updateTime})
	</insert>

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

</mapper>

XxlJobUserMapper.xml

<?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.XxlJobUserDao">

	<resultMap id="XxlJobUser" type="com.xxl.job.admin.core.model.XxlJobUser" >
		<result column="id" property="id" />
		<result column="username" property="username" />
		<result column="password" property="password" />
		<result column="role" property="role" />
		<result column="permission" property="permission" />
	</resultMap>

	<sql id="Base_Column_List">
		t.id,
		t.username,
		t.password,
		t.role,
		t.permission
	</sql>

	<select id="pageList" parameterType="java.util.HashMap" resultMap="XxlJobUser">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_USER" AS t
		<trim prefix="WHERE" prefixOverrides="AND | OR" >
			<if test="username != null and username != ''">
				AND t.username like CONCAT(CONCAT('%', #{username}), '%')
			</if>
			<if test="role gt -1">
				AND t.role = #{role}
			</if>
		</trim>
		ORDER BY username ASC
		LIMIT #{offset}, #{pagesize}
	</select>

	<select id="pageListCount" parameterType="java.util.HashMap" resultType="int">
		SELECT count(1)
		FROM "XXLJOB"."XXL_JOB_USER" AS t
		<trim prefix="WHERE" prefixOverrides="AND | OR" >
			<if test="username != null and username != ''">
				AND t.username like CONCAT(CONCAT('%', #{username}), '%')
			</if>
			<if test="role gt -1">
				AND t.role = #{role}
			</if>
		</trim>
	</select>

	<select id="loadByUserName" parameterType="java.util.HashMap" resultMap="XxlJobUser">
		SELECT <include refid="Base_Column_List" />
		FROM "XXLJOB"."XXL_JOB_USER" AS t
		WHERE t.username = #{username}
	</select>

	<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobUser" useGeneratedKeys="true" keyProperty="id" >
		INSERT INTO "XXLJOB"."XXL_JOB_USER" (
			username,
			password,
			role,
			permission
		) VALUES (
					 #{username},
					 #{password},
					 #{role},
					 #{permission}
				 );
	</insert>

	<update id="update" parameterType="com.xxl.job.admin.core.model.XxlJobUser" >
		UPDATE "XXLJOB"."XXL_JOB_USER"
		SET
		<if test="password != null and password != ''">
			password = #{password},
		</if>
		role = #{role},
		permission = #{permission}
		WHERE id = #{id}
	</update>

	<delete id="delete" parameterType="java.util.HashMap">
		DELETE
		FROM "XXLJOB"."XXL_JOB_USER"
		WHERE id = #{id}
	</delete>

</mapper>

三、制作适配过达梦数据库的xxl-job-dm.tar镜像

3.1 上传适配了达梦数据库的xxl-job-admin-2.3.0-SNAPSHOT.jar包

3.2 编写Dockerfile文件

vi Dockerfile

FROM java:8

MAINTAINER wux

ENV JAVA_HOME=/jdk1.8.0_261

ENV PATH=$PATH:$JAVA_HOME/bin

ENV LC_ALL=en_US.utf8

ENV LANG=en_US.utf8

ENV LANGUAGE=en_US.utf8

#修改Docker时区-否则里面的时间会慢8小时
#通过文件链接的方式修改时区
ENV TZ=Asia/Shanghai
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone

ADD xxl-job-admin-2.3.0-SNAPSHOT.jar app.jar

EXPOSE 8088

ENTRYPOINT ["java", "-Dfile.encoding=utf-8","-jar", "app.jar"]

PS：如果想使用外置配置文件，添加如下内容：

# 设置外部配置文件路径（必须，否则无法加载到此配置文件）
ENV JAVA_OPTS="-Dspring.config.location=/xxl-job/application.properties"
# 复制配置文件到容器中
COPY ./application.properties /xxl-job/application.properties

PS：上面的基础镜像java:8 可参考：Docker 制作java8镜像-CSDN博客

3.3 制作镜像

docker build -t xxl-job-dm:2.3.0 .

出现上图表示镜像制作成功

四、运行镜像并访问

4.1 编写启动脚本

vi docker_xxl-job-dm_start.sh

app_name='xxl-job-dm'
docker stop ${app_name}
echo '--------stop container-------------'

docker rm ${app_name}
echo '--------rm container---------------'

docker run -p 8088:8088 --name ${app_name} \
-m 2g \
--privileged=true \
-e TZ="Asia/Shanghai" \
--restart always \
-v /etc/localtime:/etc/localtime \
-v /home/data/develop/xxl-job/:/data/applogs \
# 启动容器时指定使用外置配置文件

-v /home/data/develop/xxl-job/application.properties:/application.properties \
-e PARAMS='--spring.config.location=/application.properties' \
-d ${app_name}:2.3.0

echo '---------start container---------'

4.2 执行脚本

sh docker_xxl-job-dm_start.sh

4.3 开通防火墙和外部访问

firewall-cmd --zone=public --add-port=8088/tcp --permanent

firewall-cmd --reload

在浏览器输入：http://192.168.111.129:8088/xxl-job-admin

用户名/密码：admin/123456

五、保存和加载tar镜像

5.1 保存tar镜像

docker save -o xxl-job-dm.tar xxl-job-dm:2.3.0

5.2 加载tar镜像

docker load -i xxl-job-dm.tar

六、源码部署xxl-job

6.1 上传jar包

在/home/data/jars/xxl-job-admin目录下上传xxl-job-admin-2.4.0.jar

6.2 编写application.properties文件

### web
server.port=8080
server.servlet.context-path=/xxl-job-admin

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

### xxl-job, email
# spring.mail.host=smtp.qq.com
# spring.mail.port=25
# spring.mail.username=xxx@qq.com
# spring.mail.from=xxx@qq.com
# spring.mail.password=xxx
# spring.mail.properties.mail.smtp.auth=true
# spring.mail.properties.mail.smtp.starttls.enable=true
# spring.mail.properties.mail.smtp.starttls.required=true
# spring.mail.properties.mail.smtp.socketFactory.class=javax.net.ssl.SSLSocketFactory

### xxl-job, access token
xxl.job.accessToken=

### xxl-job, i18n (default is zh_CN, and you can choose "zh_CN", "zh_TC" and "en")
xxl.job.i18n=zh_CN

## xxl-job, triggerpool max size
xxl.job.triggerpool.fast.max=50
xxl.job.triggerpool.slow.max=20

### xxl-job, log retention days
xxl.job.logretentiondays=30

6.3 编写启动脚本start.sh

#!/bin/bash

APPHOME=/home/data/jars/xxl-job-admin
APPHOMELOG=/home/data/monitorlog/xxl-job-admin

# kill all the xxj-job apps
pids=$(ps aux | egrep 'xxl-job-admin.*' | grep 'java' | awk '{print $2}')
if [ -n "$pids" ]; then
echo "killing xxl-job-admin with pid $pids"
kill $pids
sleep 4
fi

# start webapp
cd $APPHOME/
echo "apphome=" $APPHOME
source /etc/profile
source /root/.bash_profile

nohup java -jar /home/data/jars/xxl-job-admin/xxl-job-admin-2.4.0.jar --server.port=8100 &>> $APPHOMELOG/`date +%F`.log &

echo "xxl-job-admin started..."
# show all ps
ps aux | egrep '(xxl-job.*)'