前言
由于工作的需要,要求项目组使用xxljob调度平台做调度任务。本来这个事情也没什么的,但是上级又提了一个小小的要求,让我们使用适配oracle12c,表示一下小小的心塞吧。
好了回到正题吧。xxljob本身开发出来以后是只适配了MySQL数据库的,而我们这边就需要想办法兼容oracle数据库了。经常使用oracle数据库的朋友都知道,该数据库是区分大小写的,而mysql数据库不区分。
1、运行环境
Centos7.6
JDK8
Springboot2.3
Xxljob2.3.0
Oracle12c
Navicat Premium 15
2、导入oracle依赖包
<dependency>
<groupId>com.github.noraui</groupId>
<artifactId>ojdbc7</artifactId>
<version>12.1.0.2</version>
</dependency>
3、application.properties 配置文件修改
### oracle数据库连接
spring.datasource.url=jdbc:oracle:thin:@192.168.1.101:1521:orcl
spring.datasource.username=oracle
spring.datasource.password=oracle
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
4、创建oracle数据库表
我把修改后的数据库表整理了一份,贴出来供大家学习参考,不过在适配oracle的过程中可能会出现字段类型不一致的问题,这个需要注意一下。
CREATE TABLE xxl_job_info (
id number(11) NOT NULL primary key,
job_group number(11) NOT NULL,
job_desc varchar2(255) NOT NULL,
add_time timestamp DEFAULT NULL,
update_time timestamp DEFAULT NULL,
author varchar2(64) DEFAULT NULL,
alarm_email varchar2(255) DEFAULT NULL,
schedule_type varchar2(50) DEFAULT 'NONE' NOT NULL,
schedule_conf varchar2(128) DEFAULT NULL,
misfire_strategy varchar2(50) DEFAULT 'DO_NOTHING' NOT NULL,
executor_route_strategy varchar2(50) DEFAULT NULL,
executor_handler varchar2(255) DEFAULT NULL,
executor_param varchar2(512) DEFAULT NULL,
executor_block_strategy varchar2(50) DEFAULT NULL,
executor_timeout number(11) DEFAULT '0' NOT NULL,
executor_fail_retry_count number(11) DEFAULT '0' NOT NULL,
glue_type varchar2(50) NOT NULL,
glue_source clob,
glue_remark varchar2(128) DEFAULT NULL,
glue_updatetime timestamp DEFAULT NULL,
child_jobid varchar2(255) DEFAULT NULL,
trigger_status number(4) DEFAULT '0' NOT NULL,
trigger_last_time number(13) DEFAULT '0' NOT NULL,
trigger_next_time number(13) DEFAULT '0' NOT NULL
)
comment on column xxl_job_info.job_group is '执行器主键ID';
comment on column xxl_job_info.author is '作者';
comment on column xxl_job_info.alarm_email is '报警邮件';
comment on column xxl_job_info.schedule_type is '调度类型';
comment on column xxl_job_info.schedule_conf is '调度配置,值含义取决于调度类型';
comment on column xxl_job_info.misfire_strategy is '调度过期策略';
comment on column xxl_job_info.executor_route_strategy 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 '下次调度时间';
CREATE TABLE xxl_job_log (
id number(20) NOT NULL primary key,
job_group number(11) NOT NULL,
job_id number(11) NOT NULL,
executor_address varchar2(255) DEFAULT NULL,
executor_handler varchar2(255) DEFAULT NULL,
executor_param varchar2(512) DEFAULT NULL,
executor_sharding_param varchar2(20) DEFAULT NULL,
executor_fail_retry_count number(11) DEFAULT '0' NOT NULL,
trigger_time timestamp DEFAULT NULL,
trigger_code number(11) NOT NULL,
trigger_msg varchar2(1000),
handle_time timestamp DEFAULT NULL,
handle_code number(11) NOT NULL,
handle_msg varchar2(1000),
alarm_status number(4) DEFAULT '0' NOT NULL
)
comment on column xxl_job_log.job_group is '执行器主键ID';
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-告警失败';
CREATE TABLE xxl_job_log_report (
id number(11) NOT NULL primary key,
trigger_day timestamp DEFAULT NULL,
running_count number(11) DEFAULT '0' NOT NULL,
suc_count number(11) DEFAULT '0' NOT NULL,
fail_count number(11) DEFAULT '0' NOT NULL,
update_time timestamp DEFAULT NULL
)
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 '执行失败-日志数量';
CREATE TABLE xxl_job_logglue (
id number(11) NOT NULL primary key,
job_id number(11) NOT NULL,
glue_type varchar2(50) DEFAULT NULL,
glue_source clob,
glue_remark varchar2(128) NOT NULL,
add_time timestamp DEFAULT NULL,
update_time timestamp DEFAULT NULL
)
comment on column xxl_job_logglue.job_id is '任务,主键ID';
comment on column xxl_job_logglue.glue_type is 'GLUE类型';
comment on column xxl_job_logglue.glue_source is 'GLUE源代码';
comment on column xxl_job_logglue.glue_remark is 'GLUE备注';
CREATE TABLE xxl_job_registry (
id number(11) NOT NULL primary key,
registry_group varchar2(50) NOT NULL,
registry_key varchar2(255) NOT NULL,
registry_value varchar2(255) NOT NULL,
update_time timestamp DEFAULT NULL
)
CREATE TABLE xxl_job_group (
id number(11) NOT NULL primary key,
app_name varchar2(64) NOT NULL,
title varchar2(12) NOT NULL,
address_type number(4) DEFAULT '0' NOT NULL,
address_list varchar2(100),
update_time timestamp DEFAULT NULL
)
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 '执行器地址列表,多地址逗号分隔';
CREATE TABLE xxl_job_user (
id number(11) NOT NULL primary key,
username varchar2(50) NOT NULL,
password varchar2(50) NOT NULL,
role number(4) NOT NULL,
permission varchar2(255) DEFAULT NULL
)
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列表,多个逗号分割';
CREATE TABLE xxl_job_lock (
lock_name varchar2(50) NOT NULL primary key
)
comment on column xxl_job_lock.lock_name is '锁名称';
comment on table xxl_job_lock is '任务调度锁表';
comment on table xxl_job_group is '执行器信息表';
comment on table xxl_job_info is '调度扩展信息表';
comment on table xxl_job_log is '调度日志表';
comment on table xxl_job_log_report is '调度日志报表';
comment on table xxl_job_logglue is '任务GLUE日志';
comment on table xxl_job_registry is '执行器注册表';
comment on table xxl_job_user is '系统用户表';
上述sql语句导入oracle数据库中会自动变为大写,这个是正常的,因为oracle本身就是识别大写字段和数据库表名称的。
5、创建序列
mysql数据库是创建表的时候设置主键字段自动增长的,但是oracle数据库需要设置序列的同时再设置自动增长值和步长。
// 创建序列 所有的表都需要创建序列
CREATE SEQUENCE xxl_job_log_report_seq
INCREMENT BY 1 #增长值
START WITH 1 #开始值
NOMAXvalue #没有最大值
NOCYCLE; #不循环
CREATE SEQUENCE xxl_job_log_seq
INCREMENT BY 1
START WITH 1
NOMAXvalue
NOCYCLE;
CREATE SEQUENCE xxl_job_registry_seq
INCREMENT BY 1
START WITH 1
NOMAXvalue
NOCYCLE;
CREATE SEQUENCE xxl_job_info_seq
INCREMENT BY 1
START WITH 1
NOMAXvalue
NOCYCLE;
CREATE SEQUENCE xxl_job_group_seq
INCREMENT BY 1
START WITH 1
NOMAXvalue
NOCYCLE;
CREATE SEQUENCE xxl_job_logglue_seq
INCREMENT BY 1
START WITH 1
NOMAXvalue
NOCYCLE;
CREATE SEQUENCE xxl_job_user_seq
INCREMENT BY 1
START WITH 1
NOMAXvalue
NOCYCLE;
如果想删除序列,可以使用下面的删除序列语句:
DROP SEQUENCE XXL_JOB_LOG_seq;
6、修改MyBatis 的 Mapper.xml 映射
6.1 改造所有insert语句,通过序列获取ID值,同时给value字典添加jdbcType属性值。
<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobGroup" useGeneratedKeys="true" keyColumn="id" keyProperty="id" >
INSERT INTO xxl_job_group ( id, app_name , title , address_type , address_list , update_time )
values (XXL_JOB_GROUP_SEQ.NEXTVAL, #{appname,jdbcType=VARCHAR}, #{title,jdbcType=VARCHAR}, #{addressType}, #{addressList,jdbcType=VARCHAR}, #{updateTime,jdbcType=DATE} )
</insert>
注意:上面的XXL_JOB_GROUP_SEQ.NEXTVAL就是通过序列给ID设置值, #{addressList,jdbcType=VARCHAR}就是在设置jdbcType字段属性。
6.2 修改所有的分页查询语言,使用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>
6.3 update语句中的clob字段、DATE字段、VARCHAR字段若为空时需要指定jdbcType字段类型,否则报错。oracle中的NUMBER类型不需要指定,指定了反而报错。但是我对比了一下mysql数据库中,可能用不到clob字段,所以我把clob改成了varchar类型,这个地方需要注意一下。
<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},
address_list = #{addressList,jdbcType=VARCHAR},
update_time = #{updateTime,jdbcType=DATE}
WHERE id = #{id}
</update>
6.4 表别名 AS t 需要替换成 t
<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>
6.5 MySQL中的转义字符 ` 和;替换成空格
6.6 遇到DATE_ADD(#{nowTime},INTERVAL -#{timeout} SECOND)修改
<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}+numtodsinterval(-#{timeout},'SECOND'))
</select>
6.7 !改为not
<select id="findFailJobLogIds" resultType="long" >
select * from (
select A.*, rownum rn from (
SELECT id FROM xxl_job_log
WHERE not(
(trigger_code in (0, 200) and handle_code = 0)
OR
(handle_code = 200)
)
AND alarm_status = 0
ORDER BY id ASC
) A
) B <![CDATA[ where rn < #{pagesize} ]]>
</select>
7、结束语
以上就是xxljob的整个适配过程,如果您感觉该文章实用,点赞收藏一下吧,您的鼓励是我最大的动力。如果觉得哪里有问题,可以评论区留言,我再更新一下文章,同时也欢迎批评指正。相互学习。