2021-11-05

最新推荐文章于 2023-11-22 11:15:48 发布

ZhanJiao Chen

最新推荐文章于 2023-11-22 11:15:48 发布

阅读量190

点赞数 1

分类专栏：技术类型 XXM-JOB 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_43049574/article/details/121165751

版权

技术类型同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

XXM-JOB

1 篇文章 0 订阅

订阅专栏

这篇博客详细介绍了如何将XXL-JOB从MySQL数据库版本升级到Oracle Version2.3.0，包括XML文件的修改、自增字段处理、Oracle特定语法调整、分页查询转换、配置文件和依赖更新，以及初始化语句的创建。主要改动涉及AS关键字去除、特殊字符替换、Oracle序列使用和数据操作的适配。

摘要由CSDN通过智能技术生成

改造XXL-JOB ORACLE版本-Version 2.3.0

调动中心需要改动的地方：主要是改xml文件在这里插入图片描述

1、下面出现修改xml文件的地方，其他的xml文件全部要修改
2、将所有 As t 替换为 t，也就是去掉AS关键字，Oracle不支持这样给表设置别名在这里插入图片描述

3、将所有`号和;号替换为空在这里插入图片描述

4、自增长字段处理：采用Oracle的序列去替代Mysql的自增id，具体看后面的初始化语句，现在先把insert标签的这些属性去掉在这里插入图片描述

5、还有两个地方数据新增后，需要用到自增id，但是自增id标签之前去掉了，现在使用序列的方式去添加id 在这里插入图片描述

6、 DATE_ADD 函数改造在这里插入图片描述

7、 Mysql的！写法可以用not替换，以及limit用rownum替换写法在这里插入图片描述

8、 triggerdaycount,triggerDayCountRunning,triggerDayCountSuc改为全大写在这里插入图片描述

9、 Oracle的分页替换，开始页和显示条数也会处理下，这里提供一个列子参考下，其他的xml文件也是这样改在这里插入图片描述

//转成Oracle分页开始-------
if(start == 0){
	start = 1;
}else {
    start += 1;
    length += start;
}
//转成Oracle分页结束-------

<!--Mysql版本
		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
		LIMIT #{offset}, #{pagesize}-->

		<!--Oracle版本-->
		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 ROWNUM <![CDATA[ <= ]]> #{pagesize} )
		WHERE rn <![CDATA[ >= ]]> #{offset}

10、配置文件修改，pom文件添加Oracle依赖

<!-- oracle -->
<dependency>
  <groupId>com.oracle</groupId>
  <artifactId>ojdbc6</artifactId>
  <version>${ojdbc6.version}</version>
</dependency>

### xxl-job, datasource Oracle
spring.datasource.url=jdbc:oracle:thin:@ip:1521:orcl
spring.datasource.username=账号
spring.datasource.password=密码
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

### datasource-pool
spring.datasource.druid.validationQuery=SELECT 'x' FROM DUAL
spring.datasource.druid.initialSize=3
spring.datasource.druid.maxActive=3
spring.datasource.druid.minIdle=2
spring.datasource.druid.maxWait=60000
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
spring.datasource.druid.minEvictableIdleTimeMillis=300000

11、初始化语句


 --  XXL-JOB v2.3.0  Mysql 改造 Oracle版本
--Create sequence
create sequence XXL_JOB_GROUP_ID_SEQ
minvalue 1
maxvalue 999999999999
start with 2
increment by 1
cache 20
cycle;
 
--Create sequence
create sequence XXL_JOB_INFO_ID_SEQ
minvalue 1
maxvalue 999999999999
start with 2
increment by 1
cache 20
cycle;
 
--Create sequence
create sequence XXL_JOB_LOG_GLUE_ID_SEQ
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 20
cycle;
 
--Create sequence
create sequence XXL_JOB_LOG_ID_SEQ
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 20
cycle;
 
--Create sequence
create sequence XXL_JOB_REGISTRY_ID_SEQ
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 20
cycle;
 
--Create sequence
create sequence XXL_JOB_LOG_REPORT_ID_SEQ
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 20
cycle;
 
--Create sequence
create sequence XXL_JOB_USER_ID_SEQ
minvalue 1
maxvalue 999999999999
start with 2
increment by 1
cache 20
cycle;
 
 -- 初始化表
CREATE TABLE XXL_JOB_INFO (
  id NUMBER(10,0) NOT NULL,
  job_group NUMBER(10,0) NOT NULL,
  job_desc varchar2(512) NOT NULL,
  add_time date DEFAULT NULL,
  update_time date DEFAULT NULL,
  author varchar2(128) DEFAULT NULL,
  alarm_email varchar2(1024) 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(100) DEFAULT NULL,
  executor_handler varchar2(512) DEFAULT NULL ,
  executor_param varchar2(1024) DEFAULT NULL ,
  executor_block_strategy varchar2(100) DEFAULT NULL,
  executor_timeout NUMBER(10,0) DEFAULT 0 NOT NULL  ,
  executor_fail_retry_count NUMBER(10,0)  DEFAULT 0 NOT NULL,
  glue_type varchar2(100) NOT NULL,
  glue_source CLOB ,
  glue_remark varchar2(256) DEFAULT NULL,
  glue_updatetime date DEFAULT NULL ,
  child_jobid varchar2(512) 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,
  PRIMARY KEY (id)
) ;
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(10,0) NOT NULL ,
  job_group NUMBER(10,0) NOT NULL ,
  job_id NUMBER(10,0) NOT NULL ,
  executor_address varchar2(512) DEFAULT NULL ,
  executor_handler varchar2(512) DEFAULT NULL ,
  executor_param varchar2(1024) DEFAULT NULL,
  executor_sharding_param varchar2(40) DEFAULT NULL ,
  executor_fail_retry_count NUMBER(10,0) DEFAULT 0 NOT NULL ,
  trigger_time date DEFAULT NULL ,
  trigger_code NUMBER(10,0) NOT NULL ,
  trigger_msg CLOB ,
  handle_time date DEFAULT NULL ,
  handle_code NUMBER(10,0) NOT NULL ,
  handle_msg CLOB ,
  alarm_status NUMBER(3,0) DEFAULT 0 NOT NULL,
  PRIMARY KEY (id)
) ;
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-告警失败';


CREATE TABLE XXL_JOB_LOG_REPORT (
  id NUMBER(10,0) NOT NULL,
  trigger_day timestamp DEFAULT NULL  NULL,
  running_count NUMBER(10,0) DEFAULT 0 NOT NULL ,
  suc_count NUMBER(10,0) DEFAULT 0 NOT NULL ,
  fail_count NUMBER(10,0) DEFAULT 0 NOT NULL ,
  update_time timestamp DEFAULT CURRENT_TIMESTAMP  NULL,
  PRIMARY KEY (id)
) ;
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 '执行失败-日志数量';


CREATE TABLE XXL_JOB_USER (
  id NUMBER(10,0) NOT NULL,
  username varchar2(50) NOT NULL,
  password varchar2(50) NOT NULL,
  role NUMBER(4) DEFAULT 0 NOT NULL ,
  permission varchar2(255) DEFAULT NULL,
  PRIMARY KEY (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列表，多个逗号分割';


CREATE TABLE XXL_JOB_LOCK (
  lock_name varchar2(50) NOT NULL,
  PRIMARY KEY (lock_name)
) ;

comment on column XXL_JOB_LOCK.lock_name is '锁名称';



CREATE TABLE XXL_JOB_LOGGLUE (
  id NUMBER(10,0) NOT NULL,
  job_id NUMBER(10,0) NOT NULL,
  glue_type varchar2(100) DEFAULT NULL,
  glue_source CLOB ,
  glue_remark varchar2(256) NOT NULL,
  add_time timestamp DEFAULT NULL NULL ,
  update_time timestamp DEFAULT CURRENT_TIMESTAMP  NULL,
  PRIMARY KEY (id)
) ;
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(10,0) NOT NULL,
  registry_group varchar2(512) NOT NULL,
  registry_key varchar2(512) NOT NULL,
  registry_value varchar2(512) NOT NULL,
  update_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL ,
  PRIMARY KEY (id)
) ;
 
CREATE TABLE XXL_JOB_GROUP (
  id NUMBER(10,0) NOT NULL,
  app_name varchar2(128) NOT NULL ,
  title varchar2(24) NOT NULL ,
  address_type NUMBER(3,0) DEFAULT 0 NOT NULL ,
  address_list varchar2(1024) DEFAULT NULL ,
  update_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL ,
  PRIMARY KEY (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 '执行器地址列表，多地址逗号分隔';
 
 -- 初始化数据
INSERT INTO XXL_JOB_LOCK (lock_name) VALUES ( 'schedule_lock'); 
INSERT INTO XXL_JOB_USER(id, username, password, role, permission) VALUES (1, 'admin', 'e10adc3949ba59abbe56e057f20f883e', 1, NULL);

INSERT INTO XXL_JOB_GROUP(id, app_name, title, address_type, address_list) VALUES (XXL_JOB_GROUP_ID_SEQ.nextval, 'xxl-job-executor-sample', '示例执行器', 0, NULL);
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 (XXL_JOB_INFO_ID_SEQ.nextval, 1,  '测试任务1', sysdate, sysdate, 'XXL', '', 'CRON', '0 0 0 * * ? *', 'DO_NOTHING',  'FIRST', 'demoJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', 's', 'GLUE代码初始化', sysdate, '', 0, 0, 0);

commit;

好了，到这里就结束了，如果能能帮到你请帮忙点赞支持下。

ZhanJiao Chen

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2021-11-05

改造XXL-JOB ORACLE版本-Version 2.3.0调动中心需要改动的地方：主要是改xml文件1、下面出现修改xml文件的地方，其他的xml文件全部要修改2、将所有 As t 替换为 t，也就是去掉AS关键字，Oracle不支持这样给表设置别名3、将所有`号和;号替换为空4、自增长字段处理：采用Oracle的序列去替代Mysql的自增id，具体看后面的初始化语句，现在先把insert标签的这些属性去掉5、还有两个地方数据新增后，需要用到自增id，但是自增id标签之前去掉了，
复制链接

扫一扫

专栏目录