2. 技术博客
  3. XxlJob（2.2.1）Mysql转Oracle解决方案

XxlJob（2.2.1）Mysql转Oracle解决方案

tech2022-07-16  164

XxlJob（2.2.1）Mysql转Oracle解决方案

前言步骤1：建库脚本转Oracle步骤2：配置Oracle数据源步骤3：改造Mybatis的Mapper（关键点）感谢原创申明

前言

最近做的银行微服务项目引入了调度中心的概念，于是乎就开始琢磨了，经网上搜索很朋友推荐，就开始关注到XxlJob了，一款不错的分布式任务调度平台，还是开源的！ 从官网下载、部署、运行后发现目前版本只支持Mysql数据库，而我们应用使用的Oracle数据库，没有mysql环境，只能适应性改造。 网上搜了些资料，有一些思路但不够全面，经过努力踩坑，总结了一个完整的改造思路，分享给大家！ XxlJob的基础部署不赘述了，直接上干货。

步骤1：建库脚本转Oracle

网上有MySQL转Oracle脚本的方式，比较麻烦，我转了一份，大家直接用即可。

tables_xxl_job_oracle.sql

drop table XXL_JOB_INFO; drop table XXL_JOB_LOG; drop table XXL_JOB_LOG_REPORT; drop table XXL_JOB_LOGGLUE; drop table XXL_JOB_REGISTRY; drop table XXL_JOB_GROUP; drop table XXL_JOB_USER; drop table XXL_JOB_LOCK; --1.XXL_JOB_INFO CREATE TABLE "XXL_JOB_INFO" ( "ID" NUMBER(20,0) NOT NULL, "JOB_GROUP" NUMBER(20,0) NOT NULL, "JOB_CRON" VARCHAR2(128) NOT NULL, "JOB_DESC" VARCHAR2(510) NOT NULL, "ADD_TIME" DATE DEFAULT NULL NULL, "UPDATE_TIME" DATE DEFAULT NULL NULL, "AUTHOR" VARCHAR2(64) DEFAULT NULL NULL, "ALARM_EMAIL" VARCHAR2(255) DEFAULT NULL NULL, "EXECUTOR_ROUTE_STRATEGY" VARCHAR2(50) DEFAULT NULL NULL, "EXECUTOR_HANDLER" VARCHAR2(255) DEFAULT NULL NULL, "EXECUTOR_PARAM" VARCHAR2(1024) DEFAULT NULL NULL, "EXECUTOR_BLOCK_STRATEGY" VARCHAR2(100) DEFAULT NULL NULL, "EXECUTOR_TIMEOUT" NUMBER(11,0) DEFAULT 0 NOT NULL, "EXECUTOR_FAIL_RETRY_COUNT" NUMBER(11,0) DEFAULT 0 NOT NULL, "GLUE_TYPE" VARCHAR2(100) NOT NULL, "GLUE_SOURCE" CLOB DEFAULT NULL NULL, "GLUE_REMARK" VARCHAR2(256) DEFAULT NULL NULL, "GLUE_UPDATETIME" DATE DEFAULT NULL NULL, "CHILD_JOBID" VARCHAR2(255) DEFAULT NULL NULL, "TRIGGER_STATUS" NUMBER(4,0) DEFAULT 0 NOT NULL, "TRIGGER_LAST_TIME" NUMBER(20,0) DEFAULT 0 NOT NULL, "TRIGGER_NEXT_TIME" NUMBER(20,0) DEFAULT 0 NOT NULL, PRIMARY KEY ("ID") ) NOCOMPRESS NOPARALLEL ; COMMENT ON COLUMN "XXL_JOB_INFO"."JOB_GROUP" IS '执行器主键ID'; COMMENT ON COLUMN "XXL_JOB_INFO"."JOB_CRON" IS '任务执行CRON'; COMMENT ON COLUMN "XXL_JOB_INFO"."AUTHOR" IS '作者'; COMMENT ON COLUMN "XXL_JOB_INFO"."ALARM_EMAIL" 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 '下次调度时间'; --2.XXL_JOB_LOG CREATE TABLE "XXL_JOB_LOG" ( "ID" NUMBER(20,0) NOT NULL, "JOB_GROUP" NUMBER(20,0) NOT NULL, "JOB_ID" NUMBER(20,0) NOT NULL, "EXECUTOR_ADDRESS" VARCHAR2(255) DEFAULT NULL NULL, "EXECUTOR_HANDLER" VARCHAR2(255) DEFAULT NULL NULL, "EXECUTOR_PARAM" VARCHAR2(1024) DEFAULT NULL NULL, "EXECUTOR_SHARDING_PARAM" VARCHAR2(40) DEFAULT NULL NULL, "EXECUTOR_FAIL_RETRY_COUNT" NUMBER(11,0) DEFAULT 0 NOT NULL, "TRIGGER_TIME" DATE DEFAULT NULL NULL, "TRIGGER_CODE" NUMBER(11,0) NOT NULL, "TRIGGER_MSG" CLOB DEFAULT NULL NULL, "HANDLE_TIME" DATE DEFAULT NULL NULL, "HANDLE_CODE" NUMBER(11,0) NOT NULL, "HANDLE_MSG" CLOB DEFAULT NULL NULL, "ALARM_STATUS" NUMBER(4,0) DEFAULT 0 NOT NULL, PRIMARY KEY ("ID") ) NOCOMPRESS NOPARALLEL ; CREATE INDEX "I_TRIGGER_TIME" ON "XXL_JOB_LOG" ("TRIGGER_TIME"); CREATE INDEX "I_HANDLE_CODE" ON "XXL_JOB_LOG" ("HANDLE_CODE"); 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-告警失败'; --3.XXL_JOB_LOG_REPORT CREATE TABLE "XXL_JOB_LOG_REPORT" ( "ID" NUMBER(20,0) NOT NULL, "TRIGGER_DAY" DATE DEFAULT NULL NULL, "RUNNING_COUNT" NUMBER(20,0) DEFAULT 0 NOT NULL, "SUC_COUNT" NUMBER(20,0) DEFAULT 0 NOT NULL, "FAIL_COUNT" NUMBER(20,0) DEFAULT 0 NOT NULL, PRIMARY KEY ("ID") ) NOCOMPRESS NOPARALLEL ; CREATE UNIQUE INDEX "I_TRIGGER_DAY" ON "XXL_JOB_LOG_REPORT" ("TRIGGER_DAY"); COMMENT ON COLUMN "XXL_JOB_LOG_REPORT"."ID" IS 'report主键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 '执行失败-日志数量'; --4.XXL_JOB_LOGGLUE CREATE TABLE "XXL_JOB_LOGGLUE" ( "ID" NUMBER(20,0) NOT NULL, "JOB_ID" NUMBER(20,0) NOT NULL, "GLUE_TYPE" VARCHAR2(100) DEFAULT NULL NULL, "GLUE_SOURCE" CLOB DEFAULT NULL NULL, "GLUE_REMARK" VARCHAR2(256) NOT NULL, "ADD_TIME" DATE DEFAULT NULL NULL, "UPDATE_TIME" DATE DEFAULT NULL NULL, PRIMARY KEY ("ID") ) NOCOMPRESS NOPARALLEL ; 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备注'; --5.XXL_JOB_REGISTRY CREATE TABLE "XXL_JOB_REGISTRY" ( "ID" NUMBER(20,0) NOT NULL, "REGISTRY_GROUP" VARCHAR2(510) NOT NULL, "REGISTRY_KEY" VARCHAR2(510) NOT NULL, "REGISTRY_VALUE" VARCHAR2(510) NOT NULL, "UPDATE_TIME" DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY ("ID") ) NOCOMPRESS NOPARALLEL ; CREATE INDEX "I_G_K_V" ON "XXL_JOB_REGISTRY" ("REGISTRY_GROUP", "REGISTRY_KEY", "REGISTRY_VALUE"); --6.XXL_JOB_GROUP CREATE TABLE "XXL_JOB_GROUP" ( "ID" NUMBER(20,0) NOT NULL, "APP_NAME" VARCHAR2(128) NOT NULL, "TITLE" VARCHAR2(64) NOT NULL, "ORDER" NUMBER(11,0) DEFAULT 0 NOT NULL, "ADDRESS_TYPE" NUMBER(4,0) DEFAULT 0 NOT NULL, "ADDRESS_LIST" VARCHAR2(512) DEFAULT NULL NULL, PRIMARY KEY ("ID") ) NOCOMPRESS NOPARALLEL ; COMMENT ON COLUMN "XXL_JOB_GROUP"."APP_NAME" IS '执行器AppName'; COMMENT ON COLUMN "XXL_JOB_GROUP"."TITLE" IS '执行器名称'; COMMENT ON COLUMN "XXL_JOB_GROUP"."ORDER" IS '排序'; COMMENT ON COLUMN "XXL_JOB_GROUP"."ADDRESS_TYPE" IS '执行器地址类型：0=自动注册、1=手动录入'; COMMENT ON COLUMN "XXL_JOB_GROUP"."ADDRESS_LIST" IS '执行器地址列表，多地址逗号分隔'; --7.XXL_JOB_USER CREATE TABLE "XXL_JOB_USER" ( "ID" NUMBER(20,0) NOT NULL, "USERNAME" VARCHAR2(50) NOT NULL, "PASSWORD" VARCHAR2(50) NOT NULL, "ROLE" NUMBER(4,0) NOT NULL, "PERMISSION" VARCHAR2(510) DEFAULT NULL NULL, PRIMARY KEY ("ID") ) NOCOMPRESS NOPARALLEL ; CREATE UNIQUE INDEX "I_USERNAME" ON "XXL_JOB_USER" ("USERNAME"); 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列表，多个逗号分割'; --8.XXL_JOB_LOCK CREATE TABLE "XXL_JOB_LOCK" ( "LOCK_NAME" VARCHAR2(50) NOT NULL, PRIMARY KEY ("LOCK_NAME") ) NOCOMPRESS NOPARALLEL ; COMMENT ON COLUMN "XXL_JOB_LOCK"."LOCK_NAME" IS '锁名称'; CREATE SEQUENCE XXL_JOB_GROUP_SEQ MINVALUE 1 MAXVALUE 99999999999999999999 INCREMENT BY 1 START WITH 1000 NOCACHE; CREATE SEQUENCE XXL_JOB_INFO_SEQ MINVALUE 1 MAXVALUE 99999999999999999999 INCREMENT BY 1 START WITH 1000 NOCACHE; CREATE SEQUENCE XXL_JOB_LOG_SEQ MINVALUE 1 MAXVALUE 99999999999999999999 INCREMENT BY 1 START WITH 1000 NOCACHE; CREATE SEQUENCE XXL_JOB_REPORT_SEQ MINVALUE 1 MAXVALUE 99999999999999999999 INCREMENT BY 1 START WITH 1000 NOCACHE; CREATE SEQUENCE XXL_JOB_LOGGLUE_SEQ MINVALUE 1 MAXVALUE 99999999999999999999 INCREMENT BY 1 START WITH 1000 NOCACHE; CREATE SEQUENCE XXL_JOB_REGISTRY_SEQ MINVALUE 1 MAXVALUE 99999999999999999999 INCREMENT BY 1 START WITH 1000 NOCACHE; CREATE SEQUENCE XXL_JOB_USER_SEQ MINVALUE 1 MAXVALUE 99999999999999999999 INCREMENT BY 1 START WITH 1000 NOCACHE; CREATE SEQUENCE XXL_JOB_LOG_REPORT_SEQ MINVALUE 1 MAXVALUE 99999999999999999999 INCREMENT BY 1 START WITH 1000 NOCACHE; INSERT INTO xxl_job_group(id, app_name, title, "ORDER", address_type, address_list) VALUES (1, 'xxl-job-executor-sample', '示例执行器', 1, 0, NULL); INSERT INTO xxl_job_info(id, job_group, job_cron, job_desc, add_time, update_time, author, alarm_email, 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, '0 0 0 * * ? *', '测试任务1', to_date('2018-11-03 22:21:31','YYYY-MM-DD hh24:mi:ss'), to_date('2018-11-03 22:21:31','YYYY-MM-DD hh24:mi:ss'), 'XXL', '', 'FIRST', 'demoJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', to_date('2018-11-03 22:21:31','YYYY-MM-DD hh24:mi:ss'), ''); 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'); commit;

步骤2：配置Oracle数据源

在pom.xml中增加ojdbc6配置。 <!-- oracle --> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc6</artifactId> <version>11.2.0.3</version> </dependency> 在application.properties中增加Oracle数据源配置。 ### oracle spring.datasource.url=jdbc:oracle:thin:@//localhost:1521/orcl spring.datasource.username=impo spring.datasource.password=impo123 spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver spring.datasource.test-while-idle=true spring.datasource.test-on-borrow=false spring.datasource.test-on-return=false spring.datasource.validation-query=SELECT 1 FROM DUAL spring.datasource.time-between-eviction-runs-millis=300000 spring.datasource.min-evictable-idle-time-millis=1800000 spring.datasource.initial-size=5 spring.datasource.max-active=50 spring.datasource.max-wait=60000 spring.datasource.min-idle=5 MySQL的验证语句是SELECT 1 而Oracle的是SELECT 1 FROM DUAL 这个要记得改。 spring.datasource.hikari.connection-test-query=SELECT 1 FROM DUAL

步骤3：改造Mybatis的Mapper（关键点）

1、将所有 As t 替换为 t。

As t 替换为 t

2、将所有`号和;号替换为空。

;替换为空

3、设置自动增长列返回值，需指定返回列keyColumn。 <insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobGroup" useGeneratedKeys="true" keyColumn="ID" keyProperty="id"> 4、显式设置自动增长列，Oracle使用的SEQUENCE是有差异的。 INSERT INTO xxl_job_group (id, app_name, title, address_type, address_list) values (XXL_JOB_GROUP_SEQ.NEXTVAL, #{appname}, #{title}, #{addressType}, #{addressList}) 5、update改造，使用更规范的写法排除空值赋值异常，加入jdbcType。 UPDATE xxl_job_group set app_name = #{appname,jdbcType=VARCHAR}, title = #{title,jdbcType=VARCHAR}, address_type = #{addressType,jdbcType=VARCHAR}, address_list = #{addressList,jdbcType=VARCHAR} WHERE id = #{id}

Mybatis JdbcType与Oracle、MySql数据类型对应列表，请参考博文：http://blog.csdn.net/loongshawn/article/details/50496460

6、分页改造，Oracle使用rownum，Mysql使用limit。 <select id="pageList" parameterType="java.util.HashMap" resultMap="XxlJobGroup"> SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM ROW_ID 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 ) TMP_PAGE WHERE ROWNUM <![CDATA[ <= ]]> #{pagesize} ) WHERE ROW_ID <![CDATA[ > ]]> #{offset} </select> 7、Oracle模糊查询性能更优、更简洁的写法，使用INSTR。 <select id="pageList" parameterType="java.util.HashMap" resultMap="XxlJobGroup"> SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM ROW_ID 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 INSTR(t.app_name,#{appname}) <![CDATA[ > ]]> 0 </if> <if test="title != null and title != ''"> AND INSTR(t.title,#{title}) <![CDATA[ > ]]> 0 </if> </trim> ORDER BY t.app_name, t.title, t.id ASC ) TMP_PAGE WHERE ROWNUM <![CDATA[ <= ]]> #{pagesize} ) WHERE ROW_ID <![CDATA[ > ]]> #{offset} </select> 8、查询写法，如果返回类型是Map时，非全大写的别名需增加“”号。 <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> 9、Mysql的！写法可以用not替换，以及limit用rownum替换写法。 <select id="findFailJobLogIds" resultType="java.lang.Long" > SELECT id 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 ) t1 where rownum <![CDATA[ <= ]]> #{pagesize} </select>

感谢

感谢XxlJob的作者http://www.xuxueli.com/分享这么好的开源软件。

原创申明

本文系作者原创，如需分享、转载、引用等，请注明原创，谢谢~
最新回复(0)