XXL-JOB-2.4.0适配Oracle数据库
XXL-JOB是我们比较常用的定时任务框架，但是官方只适配了MySQL数据库，生产过程中我们可能用到Oracle数据，下面我们就对2.4.0版本进行改造。
修改XXL-JOB数据表初始化SQL
tables_xxl_job_oracle.sql
--drop table XXL_JOB_GROUP;
create table XXL_JOB_GROUP
(
ID NUMBER not null,
APP_NAME VARCHAR2(150) not null,
TITLE VARCHAR2(50) not null,
ADDRESS_TYPE NUMBER not null,
ADDRESS_LIST CLOB,
UPDATE_TIME DATE
);
comment on table XXL_JOB_GROUP
is '执行器信息表';
comment on column XXL_JOB_GROUP.ID
is 'id';
comment on column XXL_JOB_GROUP.APP_NAME
is '执行器AppName';
comment on column XXL_JOB_GROUP.TITLE
is '执行器名称';
comment on column XXL_JOB_GROUP.ADDRESS_TYPE
is '执行器地址类型：0=自动注册、1=手动录入';
comment on column XXL_JOB_GROUP.ADDRESS_LIST
is '执行器地址列表，多地址逗号分隔';
comment on column XXL_JOB_GROUP.UPDATE_TIME
is '';
create index IDX_JOB_GROUP_APP_NAME on XXL_JOB_GROUP(APP_NAME) ;
alter table XXL_JOB_GROUP add constraint PK_XXL_JOB_GROUP primary key (ID) ;
--drop table XXL_JOB_INFO;
create table XXL_JOB_INFO
(
ID NUMBER not null,
JOB_GROUP NUMBER not null,
JOB_DESC VARCHAR2(500) not null,
ADD_TIME DATE,
UPDATE_TIME DATE,
AUTHOR VARCHAR2(500),
ALARM_EMAIL VARCHAR2(500),
SCHEDULE_TYPE VARCHAR2(50),
SCHEDULE_CONF VARCHAR2(128),
MISFIRE_STRATEGY VARCHAR2(50),
EXECUTOR_ROUTE_STRATEGY VARCHAR2(150),
EXECUTOR_HANDLER VARCHAR2(500),
EXECUTOR_PARAM VARCHAR2(1000),
EXECUTOR_BLOCK_STRATEGY VARCHAR2(150),
EXECUTOR_TIMEOUT NUMBER not null,
EXECUTOR_FAIL_RETRY_COUNT NUMBER not null,
GLUE_TYPE VARCHAR2(150) not null,
GLUE_SOURCE CLOB,
GLUE_REMARK VARCHAR2(128),
GLUE_UPDATETIME DATE,
CHILD_JOBID VARCHAR2(500),
TRIGGER_STATUS NUMBER not null,
TRIGGER_LAST_TIME NUMBER not null,
TRIGGER_NEXT_TIME NUMBER not null
) ;
comment on table XXL_JOB_INFO
is '调度信息表';
comment on column XXL_JOB_INFO.SCHEDULE_CONF
is '调度配置，值含义取决于调度类型';
comment on column XXL_JOB_INFO.EXECUTOR_HANDLER
is '执行器任务handler';
comment on column XXL_JOB_INFO.EXECUTOR_PARAM
is '执行器任务参数';
comment on column XXL_JOB_INFO.EXECUTOR_BLOCK_STRATEGY
is '阻塞处理策略';
comment on column XXL_JOB_INFO.EXECUTOR_TIMEOUT
is '任务执行超时时间，单位秒';
comment on column XXL_JOB_INFO.EXECUTOR_FAIL_RETRY_COUNT
is '失败重试次数';
comment on column XXL_JOB_INFO.GLUE_TYPE
is 'GLUE类型';
comment on column XXL_JOB_INFO.GLUE_SOURCE
is 'GLUE源代码';
comment on column XXL_JOB_INFO.GLUE_REMARK
is 'GLUE备注';
comment on column XXL_JOB_INFO.GLUE_UPDATETIME
is 'GLUE更新时间';
comment on column XXL_JOB_INFO.CHILD_JOBID
is '子任务ID，多个逗号分隔';
comment on column XXL_JOB_INFO.TRIGGER_STATUS
is '调度状态：0-停止，1-运行';
comment on column XXL_JOB_INFO.TRIGGER_LAST_TIME
is '上次调度时间';
comment on column XXL_JOB_INFO.TRIGGER_NEXT_TIME
is '下次调度时间';
comment on column XXL_JOB_INFO.MISFIRE_STRATEGY
is '调度过期策略';
comment on column XXL_JOB_INFO.SCHEDULE_TYPE
is '调度类型';
comment on column XXL_JOB_INFO.EXECUTOR_ROUTE_STRATEGY
is '执行器路由策略';
comment on column XXL_JOB_INFO.AUTHOR
is '作者';
comment on column XXL_JOB_INFO.ALARM_EMAIL
is '报警邮件';
comment on column XXL_JOB_INFO.ID
is '主键';
comment on column XXL_JOB_INFO.JOB_GROUP
is '执行器主键ID';
comment on column XXL_JOB_INFO.JOB_DESC
is '任务描述';
comment on column XXL_JOB_INFO.ADD_TIME
is '添加时间';
comment on column XXL_JOB_INFO.UPDATE_TIME
is '更新时间';
create index IDX_JOB_AUTHOR on XXL_JOB_INFO(AUTHOR) ;
create index IDX_JOB_EXECUTOR_HANDLER on XXL_JOB_INFO(EXECUTOR_HANDLER) ;
create index IDX_JOB_JOB_GROUP on XXL_JOB_INFO(JOB_GROUP) ;
create index IDX_JOB_TRIGGER_STATUS on XXL_JOB_INFO(TRIGGER_STATUS) ;
alter table XXL_JOB_INFO add constraint PK_XXL_JOB_INFO primary key (ID) ;
--drop table XXL_JOB_LOCK;
create table XXL_JOB_LOCK(
LOCK_NAME VARCHAR2(150) not null
) ;
comment on table XXL_JOB_LOCK
is '锁信息';
comment on column XXL_JOB_LOCK.LOCK_NAME
is '锁名称';
alter table XXL_JOB_LOCK add constraint PK_XXL_JOB_LOCK primary key (LOCK_NAME) ;
--drop table XXL_JOB_LOG;
create table XXL_JOB_LOG
(
ID NUMBER not null,
JOB_GROUP NUMBER not null,
JOB_ID NUMBER not null,
EXECUTOR_ADDRESS VARCHAR2(500),
EXECUTOR_HANDLER VARCHAR2(500),
EXECUTOR_PARAM VARCHAR2(1000),
EXECUTOR_SHARDING_PARAM VARCHAR2(50),
EXECUTOR_FAIL_RETRY_COUNT NUMBER not null,
TRIGGER_TIME DATE,
TRIGGER_CODE NUMBER not null,
TRIGGER_MSG CLOB,
HANDLE_TIME DATE,
HANDLE_CODE NUMBER not null,
HANDLE_MSG CLOB,
ALARM_STATUS NUMBER not null
) ;
comment on table XXL_JOB_LOG
is '任务日志信息';
comment on column XXL_JOB_LOG.JOB_ID
is '任务，主键ID';
comment on column XXL_JOB_LOG.EXECUTOR_ADDRESS
is '执行器地址，本次执行的地址';
comment on column XXL_JOB_LOG.EXECUTOR_HANDLER
is '执行器任务handler';
comment on column XXL_JOB_LOG.EXECUTOR_PARAM
is '执行器任务参数';
comment on column XXL_JOB_LOG.EXECUTOR_SHARDING_PARAM
is '执行器任务分片参数，格式如 1/2';
comment on column XXL_JOB_LOG.EXECUTOR_FAIL_RETRY_COUNT
is '失败重试次数';
comment on column XXL_JOB_LOG.TRIGGER_TIME
is '调度-时间';
comment on column XXL_JOB_LOG.TRIGGER_CODE
is '调度-结果';
comment on column XXL_JOB_LOG.TRIGGER_MSG
is '调度-日志';
comment on column XXL_JOB_LOG.HANDLE_TIME
is '执行-时间';
comment on column XXL_JOB_LOG.HANDLE_CODE
is '执行-状态';
comment on column XXL_JOB_LOG.HANDLE_MSG
is '执行-日志';
comment on column XXL_JOB_LOG.ALARM_STATUS
is '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败';
comment on column XXL_JOB_LOG.JOB_GROUP
is '执行器主键ID';
comment on column XXL_JOB_LOG.ID
is '主键ID';
create index IDX_JOB_LOG_TRIGGER_CODE on XXL_JOB_LOG(TRIGGER_CODE) ;
create index IDX_JOB_LOG_TRIGGER_TIME on XXL_JOB_LOG(TRIGGER_TIME) ;
alter table XXL_JOB_LOG add constraint PK_XXL_JOB_LOG primary key (ID) ;
create index IDX_JOB_LOG_HANDLE_CODE on XXL_JOB_LOG(HANDLE_CODE) ;
create index IDX_JOB_LOG_HANDLE_TIME on XXL_JOB_LOG(HANDLE_TIME) ;
create index IDX_JOB_LOG_JOB_GROUP on XXL_JOB_LOG(JOB_GROUP) ;
create index IDX_JOB_LOG_JOB_ID on XXL_JOB_LOG(JOB_ID) ;
ALTER TABLE XXL_JOB_LOG MODIFY EXECUTOR_FAIL_RETRY_COUNT DEFAULT 0
ALTER TABLE XXL_JOB_LOG MODIFY ALARM_STATUS DEFAULT 0
--drop table XXL_JOB_LOGGLUE;
create table XXL_JOB_LOGGLUE
(
ID NUMBER not null,
JOB_ID NUMBER not null,
GLUE_TYPE VARCHAR2(150),
GLUE_SOURCE CLOB,
GLUE_REMARK VARCHAR2(256) not null,
ADD_TIME DATE,
UPDATE_TIME DATE
) ;
comment on table XXL_JOB_LOGGLUE
is '任务GLUE日志';
comment on column XXL_JOB_LOGGLUE.GLUE_SOURCE
is 'GLUE源代码';
comment on column XXL_JOB_LOGGLUE.GLUE_REMARK
is 'GLUE备注';
comment on column XXL_JOB_LOGGLUE.ADD_TIME
is '添加时间';
comment on column XXL_JOB_LOGGLUE.UPDATE_TIME
is '更新时间';
comment on column XXL_JOB_LOGGLUE.ID
is '主键ID';
comment on column XXL_JOB_LOGGLUE.JOB_ID
is '任务，主键ID';
comment on column XXL_JOB_LOGGLUE.GLUE_TYPE
is 'GLUE类型';
create index IDX_JOB_LOGGLUE_JOB_ID on XXL_JOB_LOGGLUE(JOB_ID) ;
alter table XXL_JOB_LOGGLUE add constraint PK_XXL_JOB_LOGGLUE primary key (ID) ;
--drop table XXL_JOB_LOG_REPORT;
create table XXL_JOB_LOG_REPORT
(
ID NUMBER(11) not null,
TRIGGER_DAY DATE,
RUNNING_COUNT NUMBER(11) not null,
SUC_COUNT NUMBER(11) not null,
FAIL_COUNT NUMBER(11) not null,
UPDATE_TIME DATE
) ;
comment on table XXL_JOB_LOG_REPORT
is '日志报表';
comment on column XXL_JOB_LOG_REPORT.ID
is 'id';
comment on column XXL_JOB_LOG_REPORT.TRIGGER_DAY
is '调度-时间';
comment on column XXL_JOB_LOG_REPORT.RUNNING_COUNT
is '运行中-日志数量';
comment on column XXL_JOB_LOG_REPORT.SUC_COUNT
is '执行成功-日志数量';
comment on column XXL_JOB_LOG_REPORT.FAIL_COUNT
is '执行失败-日志数量';
comment on column XXL_JOB_LOG_REPORT.UPDATE_TIME
is '更新时间';
alter table XXL_JOB_LOG_REPORT add constraint PK_XXL_JOB_LOG_REPORT primary key (ID) ;
--drop table XXL_JOB_REGISTRY;
create table XXL_JOB_REGISTRY
(
ID NUMBER not null,
REGISTRY_GROUP VARCHAR2(500) not null,
REGISTRY_KEY VARCHAR2(500) not null,
REGISTRY_VALUE VARCHAR2(500) not null,
UPDATE_TIME DATE
) ;
comment on table XXL_JOB_REGISTRY
is '执行器注册表';
comment on column XXL_JOB_REGISTRY.ID
is '主键ID';
comment on column XXL_JOB_REGISTRY.REGISTRY_GROUP
is '注册组';
comment on column XXL_JOB_REGISTRY.REGISTRY_KEY
is '注册key';
comment on column XXL_JOB_REGISTRY.REGISTRY_VALUE
is '注册value';
comment on column XXL_JOB_REGISTRY.UPDATE_TIME
is '修改时间';
create index IDX_JOB_REGISTRY_GROUP on XXL_JOB_REGISTRY(REGISTRY_GROUP) ;
create index IDX_JOB_REGISTRY_UPDATE_TIME on XXL_JOB_REGISTRY(UPDATE_TIME) ;
alter table XXL_JOB_REGISTRY add constraint PK_XXL_JOB_REGISTRY primary key (ID) ;
--drop table XXL_JOB_USER;
create table XXL_JOB_USER
(
ID NUMBER not null,
USERNAME VARCHAR2(150) not null,
PASSWORD VARCHAR2(150) not null,
ROLE NUMBER not null,
PERMISSION VARCHAR2(500)
) ;
comment on table XXL_JOB_USER
is '登录用户信息';
comment on column XXL_JOB_USER.ID
is '主键ID';
comment on column XXL_JOB_USER.USERNAME
is '账号';
comment on column XXL_JOB_USER.PASSWORD
is '密码';
comment on column XXL_JOB_USER.ROLE
is '角色：0-普通用户、1-管理员';
comment on column XXL_JOB_USER.PERMISSION
is '权限：执行器ID列表，多个逗号分割';
alter table XXL_JOB_USER add constraint PK_XXL_JOB_USER primary key (ID) ;
-- Create sequence
create sequence XXL_JOB_GROUP_ID
minvalue 1
maxvalue 999999999999
start with 2
increment by 1
cache 20
cycle;
-- Create sequence
create sequence XXL_JOB_INFO_ID
minvalue 1
maxvalue 999999999999
start with 2
increment by 1
cache 20
cycle;
-- Create sequence
create sequence XXL_JOB_LOGGLUE_ID
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 20
cycle;
-- Create sequence
create sequence XXL_JOB_LOG_ID
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 20
cycle;
-- Create sequence
create sequence XXL_JOB_LOG_REPORT_ID
minvalue 1
maxvalue 999999999999
start with 2
increment by 1
cache 20
cycle;
-- Create sequence
create sequence XXL_JOB_REGISTRY_ID
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 20
cycle;
-- Create sequence
create sequence XXL_JOB_USER_ID
minvalue 1
maxvalue 999999999999
start with 2
increment by 1
cache 20
cycle;
INSERT INTO xxl_job_group(id, app_name, title, address_type, address_list) VALUES (1, 'xxl-job-executor-sample', '示例执行器', 0, NULL);
INSERT INTO xxl_job_user(id, username, password, role, permission) VALUES (1, 'admin', 'e10adc3949ba59abbe56e057f20f883e', 1, NULL);
INSERT INTO xxl_job_lock ( lock_name) VALUES ( 'schedule_lock');
POM添加Oracle驱动
在xxl-job-admin 模块中的pom.xml 引入Oracle的驱动
<!--Oracle数据库依赖-->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>19.3.0.0</version>
</dependency>
<!--解决Oracle字符集问题-->
<dependency>
<groupId>cn.easyproject</groupId>
<artifactId>orai18n</artifactId>
<version>12.1.0.2.0</version>
</dependency>
修改YAML文件
driver-class-name: oracle.jdbc.OracleDriver
connection-test-query: SELECT 1 FROM DUAL
数据库url,username,password 换成自己的就行了
修改Mapper 中的语句
问题：
- 表别名 AS t 需要替换成 t
- MySQL 转义字符 ` 替换成空格
- findLogReport 中的包含小写字母别名的字段，需要用双引号转义，保证 SQL 语句正确
- 有的 update 语句中，为字段设置 jdbcType 属性
- 所有的 insert 语句中的自增 ID，插入列加 ID，值为序列值
改造完后的mapper
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>
<!--//TODO oracle支持调整 取序列 -->
<sql id='XXL_JOB_GROUP_ID'>XXL_JOB_GROUP_ID.NEXTVAL</sql>
<select id="findAll" resultMap="XxlJobGroup">
SELECT <include refid="Base_Column_List" />
FROM xxl_job_group 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 xxl_job_group 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" >
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select <include refid="XXL_JOB_GROUP_ID" /> from dual
</selectKey>
INSERT INTO xxl_job_group (id, app_name, title, address_type, address_list, update_time)
values ( #{id}, #{appname,jdbcType=VARCHAR}, #{title,jdbcType=VARCHAR}, #{addressType,jdbcType=VARCHAR}, #{addressList,jdbcType=CLOB}, #{updateTime} )
</insert>
<update id="update" parameterType="com.xxl.job.admin.core.model.XxlJobGroup" >
UPDATE xxl_job_group
SET app_name = #{appname,jdbcType=VARCHAR},
title = #{title,jdbcType=VARCHAR},
address_type = #{addressType,jdbcType=VARCHAR},
address_list = #{addressList,jdbcType=CLOB},
update_time = #{updateTime,jdbcType=DATE}
WHERE id = #{id}
</update>
<delete id="remove" parameterType="java.lang.Integer" >
DELETE FROM xxl_job_group
WHERE id = #{id}
</delete>
<select id="load" parameterType="java.lang.Integer" resultMap="XxlJobGroup">
SELECT <include refid="Base_Column_List" />
FROM xxl_job_group t
WHERE t.id = #{id}
</select>
<!--//TODO oracle支持调整 分页修改为oracle三层分页 -->
<select id="pageList" parameterType="java.util.HashMap" resultMap="XxlJobGroup">
select *
from (select *
from (select A.*, rownum rn
from (
SELECT <include refid="Base_Column_List" />
FROM xxl_job_group 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) A)
where rn > #{offset}) B
<![CDATA[ where rn <= #{offset}+ #{pagesize} ]]>
</select>
<select id="pageListCount" parameterType="java.util.HashMap" resultType="int">
SELECT count(1)
FROM xxl_job_group 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" jdbcType="DATE"/>
<result column="update_time" property="updateTime" jdbcType="DATE"/>
<result column="author" property="author" jdbcType="VARCHAR"/>
<result column="alarm_email" property="alarmEmail" jdbcType="VARCHAR"/>
<result column="schedule_type" property="scheduleType" jdbcType="VARCHAR"/>
<result column="schedule_conf" property="scheduleConf" jdbcType="VARCHAR"/>
<result column="misfire_strategy" property="misfireStrategy" jdbcType="VARCHAR"/>
<result column="executor_route_strategy" property="executorRouteStrategy" jdbcType="VARCHAR"/>
<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" jdbcType="CLOB"/>
<result column="glue_remark" property="glueRemark" />
<result column="glue_updatetime" property="glueUpdatetime" jdbcType="DATE"/>
<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>
<!--//TODO oracle支持调整 取序列 -->
<sql id='XXL_JOB_INFO_ID'>XXL_JOB_INFO_ID.NEXTVAL</sql>
<!--//TODO oracle支持调整 分页修改为oracle三层分页 -->
<select id="pageList" parameterType="java.util.HashMap" resultMap="XxlJobInfo">
select *
from (select *
from (select A.*, rownum rn
from (
SELECT <include refid="Base_Column_List" />
FROM xxl_job_info 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) A)
where rn > #{offset}) B
<![CDATA[ where rn <= #{offset}+ #{pagesize} ]]>
</select>
<select id="pageListCount" parameterType="java.util.HashMap" resultType="int">
SELECT count(1)
FROM xxl_job_info 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" >
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select <include refid="XXL_JOB_INFO_ID" /> from dual
</selectKey>
INSERT INTO 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 (
#{id},
#{jobGroup},
#{jobDesc},
#{addTime},
#{updateTime},
#{author},
#{alarmEmail},
#{scheduleType},
#{scheduleConf},
#{misfireStrategy},
#{executorRouteStrategy},
#{executorHandler},
#{executorParam},
#{executorBlockStrategy},
#{executorTimeout},
#{executorFailRetryCount},
#{glueType},
#{glueSource,jdbcType=CLOB},
#{glueRemark},
#{glueUpdatetime},
#{childJobId},
#{triggerStatus},
#{triggerLastTime},
#{triggerNextTime}
)
</insert>
<select id="loadById" parameterType="java.util.HashMap" resultMap="XxlJobInfo">
SELECT <include refid="Base_Column_List" />
FROM xxl_job_info t
WHERE t.id = #{id}
</select>
<update id="update" parameterType="com.xxl.job.admin.core.model.XxlJobInfo" >
UPDATE xxl_job_info
SET
job_group = #{jobGroup},
job_desc = #{jobDesc,jdbcType=VARCHAR},
update_time = #{updateTime,jdbcType=DATE},
author = #{author,jdbcType=VARCHAR},
alarm_email = #{alarmEmail,jdbcType=VARCHAR},
schedule_type = #{scheduleType,jdbcType=VARCHAR},
schedule_conf = #{scheduleConf,jdbcType=VARCHAR},
misfire_strategy = #{misfireStrategy,jdbcType=VARCHAR},
executor_route_strategy = #{executorRouteStrategy,jdbcType=VARCHAR},
executor_handler = #{executorHandler,jdbcType=VARCHAR},
executor_param = #{executorParam,jdbcType=VARCHAR},
executor_block_strategy = #{executorBlockStrategy,jdbcType=VARCHAR},
executor_timeout = ${executorTimeout},
executor_fail_retry_count = ${executorFailRetryCount},
glue_type = #{glueType,jdbcType=VARCHAR},
glue_source = #{glueSource,jdbcType=CLOB},
glue_remark = #{glueRemark,jdbcType=VARCHAR},
glue_updatetime = #{glueUpdatetime,jdbcType=DATE},
child_jobid = #{childJobId,jdbcType=VARCHAR},
trigger_status = #{triggerStatus,jdbcType=VARCHAR},
trigger_last_time = #{triggerLastTime},
trigger_next_time = #{triggerNextTime}
WHERE id = #{id}
</update>
<delete id="delete" parameterType="java.util.HashMap">
DELETE
FROM xxl_job_info
WHERE id = #{id}
</delete>
<select id="getJobsByGroup" parameterType="java.util.HashMap" resultMap="XxlJobInfo">
SELECT <include refid="Base_Column_List" />
FROM xxl_job_info t
WHERE t.job_group = #{jobGroup}
</select>
<select id="findAllCount" resultType="int">
SELECT count(1)
FROM xxl_job_info
</select>
<!--//TODO oracle支持调整 分页修改为oracle分页 -->
<select id="scheduleJobQuery" parameterType="java.util.HashMap" resultMap="XxlJobInfo">
select <include refid="Base_Column_List" /> from (
SELECT *
FROM xxl_job_info a
WHERE a.trigger_status = 1
and a.trigger_next_time <![CDATA[ <= ]]> #{maxNextTime,jdbcType=NUMERIC}
ORDER BY id ASC
) t where <![CDATA[ rownum < #{pagesize} ]]>
</select>
<update id="scheduleUpdate" parameterType="com.xxl.job.admin.core.model.XxlJobInfo" >
UPDATE xxl_job_info
SET
trigger_last_time = #{triggerLastTime,jdbcType=NUMERIC},
trigger_next_time = #{triggerNextTime,jdbcType=NUMERIC},
trigger_status = #{triggerStatus,jdbcType=NUMERIC}
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>
<!--//TODO oracle支持调整 取序列 -->
<sql id='XXL_JOB_LOGGLUE_ID'>XXL_JOB_LOGGLUE_ID.NEXTVAL</sql>
<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobLogGlue" useGeneratedKeys="true" keyProperty="id" >
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select <include refid="XXL_JOB_LOGGLUE_ID" /> from dual
</selectKey>
INSERT INTO xxl_job_logglue (
id,
job_id,
glue_type,
glue_source,
glue_remark,
add_time,
update_time
) VALUES (
#{id},
#{jobId},
#{glueType},
#{glueSource},
#{glueRemark},
#{addTime},
#{updateTime}
)
</insert>
<select id="findByJobId" parameterType="java.lang.Integer" resultMap="XxlJobLogGlue">
SELECT <include refid="Base_Column_List" />
FROM xxl_job_logglue t
WHERE t.job_id = #{jobId}
ORDER BY id DESC
</select>
<!--//TODO oracle支持调整 分页修改为oracle分页 -->
<delete id="removeOld" >
DELETE FROM xxl_job_logglue
WHERE id NOT in(
SELECT id FROM(
select id from (
SELECT id FROM xxl_job_logglue
WHERE job_id = #{jobId}
ORDER BY update_time desc) a
<![CDATA[where rownum < #{limit} ]]>
) t1
) AND job_id = #{jobId}
</delete>
<delete id="deleteByJobId" parameterType="java.lang.Integer" >
DELETE FROM 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>
<!--//TODO oracle支持调整 取序列 -->
<sql id='XXL_JOB_LOG_ID'>XXL_JOB_LOG_ID.NEXTVAL</sql>
<!--//TODO oracle支持调整 分页修改为oracle三层分页 -->
<select id="pageList" resultMap="XxlJobLog">
select *
from (select *
from (select A.*, rownum rn
from (
SELECT <include refid="Base_Column_List" />
FROM xxl_job_log 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) A)
where rn > #{offset}) B
<![CDATA[ where rn <= #{offset}+ #{pagesize} ]]>
</select>
<select id="pageListCount" resultType="int">
SELECT count(1)
FROM xxl_job_log 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 xxl_job_log t
WHERE t.id = #{id}
</select>
<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobLog" useGeneratedKeys="true" keyProperty="id" >
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select <include refid="XXL_JOB_LOG_ID" /> from dual
</selectKey>
INSERT INTO xxl_job_log (
id,
job_group,
job_id,
trigger_time,
trigger_code,
handle_code
) VALUES (
#{id},
#{jobGroup},
#{jobId},
#{triggerTime},
#{triggerCode},
#{handleCode}
)
<!--<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
SELECT LAST_INSERT_ID()
</selectKey>-->
</insert>
<update id="updateTriggerInfo" >
UPDATE xxl_job_log
SET
trigger_time= #{triggerTime,jdbcType=DATE},
trigger_code= #{triggerCode,jdbcType=VARCHAR},
trigger_msg= #{triggerMsg,jdbcType=VARCHAR},
executor_address= #{executorAddress,jdbcType=VARCHAR},
executor_handler=#{executorHandler,jdbcType=VARCHAR},
executor_param= #{executorParam,jdbcType=VARCHAR},
executor_sharding_param= #{executorShardingParam,jdbcType=VARCHAR},
executor_fail_retry_count= #{executorFailRetryCount,jdbcType=VARCHAR}
WHERE id= #{id,jdbcType=VARCHAR}
</update>
<update id="updateHandleInfo">
UPDATE xxl_job_log
SET
handle_time= #{handleTime,jdbcType=DATE},
handle_code= #{handleCode,jdbcType=VARCHAR},
handle_msg= #{handleMsg,jdbcType=CLOB}
WHERE id= #{id}
</update>
<delete id="delete" >
delete from xxl_job_log
WHERE job_id = #{jobId}
</delete>
<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 xxl_job_log
WHERE trigger_time BETWEEN #{from} and #{to}
</select>
<!--//TODO oracle支持调整 分页修改为oracle三层分页 -->
<select id="findClearLogIds" resultType="long" >
select id from
(select id,rownum rn from (
SELECT id FROM 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 xxl_job_log 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
) t1 where where <![CDATA[ rownum < #{clearBeforeNum} ]]>
)
</if>
</trim>
order by id asc
) )
where <![CDATA[ rn <= #{pagesize} ]]>
</select>
<delete id="clearLog" >
delete from xxl_job_log
WHERE id in
<foreach collection="logIds" item="item" open="(" close=")" separator="," >
#{item}
</foreach>
</delete>
<!--//TODO oracle支持调整 分页修改为oracle分页 -->
<select id="findFailJobLogIds" resultType="long" >
select id from (
SELECT id
FROM xxl_job_log
WHERE 1 = 1
and (trigger_code not in (0, 200) and handle_code != 0)
OR (handle_code != 200)
AND alarm_status = 0
ORDER BY id ASC
) where <![CDATA[ rownum < #{pagesize} ]]>
</select>
<update id="updateAlarmStatus" >
UPDATE xxl_job_log
SET
alarm_status = #{newAlarmStatus}
WHERE id= #{logId} AND alarm_status = #{oldAlarmStatus}
</update>
<select id="findLostJobIds" resultType="long" >
SELECT
t.id
FROM
xxl_job_log t
LEFT JOIN xxl_job_registry t2 ON t.executor_address = t2.registry_value
WHERE
t.trigger_code = 200
AND t.handle_code = 0
AND t.trigger_time <![CDATA[ <= ]]> #{losedTime}
AND t2.id IS NULL
</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>
<!--//TODO oracle支持调整 取序列 -->
<sql id='XXL_JOB_LOG_REPORT_ID'>XXL_JOB_LOG_REPORT_ID.NEXTVAL</sql>
<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobLogReport" useGeneratedKeys="true" keyProperty="id" >
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select <include refid="XXL_JOB_LOG_REPORT_ID" /> from dual
</selectKey>
INSERT INTO xxl_job_log_report (
id,
trigger_day,
running_count,
suc_count,
fail_count
) VALUES (
#{id},
#{triggerDay},
#{runningCount},
#{sucCount},
#{failCount}
)
</insert>
<update id="update" >
UPDATE 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 xxl_job_log_report 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 xxl_job_log_report 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>
<!--//TODO oracle支持调整 取序列 -->
<sql id='XXL_JOB_REGISTRY_ID'>XXL_JOB_REGISTRY_ID.NEXTVAL</sql>
<!--//TODO oracle支持调整 取时间间隔 DATE_ADD(#{nowTime},INTERVAL -#{timeout} SECOND)修改为(#{nowTime}-1/(24*60*60)*#{timeout}) -->
<select id="findDead" parameterType="java.util.HashMap" resultType="java.lang.Integer" >
SELECT t.id
FROM xxl_job_registry t
WHERE t.update_time <![CDATA[ < ]]> (#{nowTime}-1/(24*60*60)*#{timeout})
</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 t
WHERE t.update_time <![CDATA[ > ]]> (#{nowTime}-1/(24*60*60)*#{timeout})
</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" >
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select <include refid="XXL_JOB_REGISTRY_ID" /> from dual
</selectKey>
INSERT INTO xxl_job_registry( id ,registry_group , registry_key , registry_value, update_time)
VALUES( #{id} , #{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>
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>
<!--//TODO oracle支持调整 取序列 -->
<sql id='XXL_JOB_USER_ID'>XXL_JOB_USER_ID.NEXTVAL</sql>
<!--//TODO oracle支持调整 分页修改为oracle三层分页 -->
<select id="pageList" parameterType="java.util.HashMap" resultMap="XxlJobUser">
select *
from (select *
from (select A.*, rownum rn
from (
SELECT <include refid="Base_Column_List" />
FROM xxl_job_user 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) A)
where rn > #{offset}) B
<![CDATA[ where rn <= #{offset}+ #{pagesize} ]]>
</select>
<select id="pageListCount" parameterType="java.util.HashMap" resultType="int">
SELECT count(1)
FROM xxl_job_user 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 xxl_job_user t
WHERE t.username = #{username}
</select>
<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobUser" useGeneratedKeys="true" keyProperty="id" >
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select <include refid="XXL_JOB_USER_ID" /> from dual
</selectKey>
INSERT INTO xxl_job_user (
id,
username,
password,
role,
permission
) VALUES (
#{id},
#{username},
#{password},
#{role},
#{permission}
)
</insert>
<update id="update" parameterType="com.xxl.job.admin.core.model.XxlJobUser" >
UPDATE xxl_job_user
SET
<if test="password != null and password != ''">
password = #{password},
</if>
role = #{role}
WHERE id = #{id}
</update>
<delete id="delete" parameterType="java.util.HashMap">
DELETE
FROM xxl_job_user
WHERE id = #{id}
</delete>
</mapper>
5944
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
