前言
因公司要求系统需要全面国产化改造，其中也涉及到定时任务xxl-job的改造。
使用的xxl-job版本为：2.5.0
一、修改配置
1、修改pom.xml，引入postgresql组件
+ <dependency>
+ <groupId>org.postgresql</groupId>
+ <artifactId>postgresql</artifactId>
+ <version>42.7.4</version>
+ </dependency>2、修改application，删除mysql配置，增加postgresql配置
-### xxl-job, datasource
-spring.datasource.url=jdbc:mysql://127.0.0.1:3306/xxl_job?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
-spring.datasource.username=root
-spring.datasource.password=root_pwd
-spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
-### datasource-poolspring.datasource.type=com.zaxxer.hikari.HikariDataSourcespring.datasource.hikari.minimum-idle=10
@@ -40,6 +37,7 @@ spring.datasource.hikari.max-lifetime=900000spring.datasource.hikari.connection-timeout=10000spring.datasource.hikari.connection-test-query=SELECT 1spring.datasource.hikari.validation-timeout=1000
+# spring.datasource.hikari.schema=xxl_job二、修改SQL，适配postgresql
1、文件路径：xxl-job-admin/src/main/resources/mybatis-mapper/XxlJobGroupMapper.xml
- INSERT INTO xxl_job_group ( `app_name`, `title`, `address_type`, `address_list`, `update_time`)
+ INSERT INTO xxl_job_group ( app_name, title, address_type, address_list, update_time)- SET `app_name` = #{appname},
- `title` = #{title},
- `address_type` = #{addressType},
- `address_list` = #{addressList},
- `update_time` = #{updateTime}
+ SET app_name = #{appname},
+ title = #{title},
+ address_type = #{addressType},
+ address_list = #{addressList},
+ update_time = #{updateTime}2、文件路径：xxl-job-admin/src/main/resources/mybatis-mapper/XxlJobLogGlueMapper.xml
<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobLogGlue" useGeneratedKeys="true" keyProperty="id" >INSERT INTO xxl_job_logglue (
- `job_id`,
- `glue_type`,
- `glue_source`,
- `glue_remark`,
- `add_time`,
- `update_time`
+ job_id,^M
+ glue_type,^M
+ glue_source,^M
+ glue_remark,^M
+ add_time, ^M
+ update_time^M) VALUES (#{jobId},#{glueType}, WHERE id NOT in(SELECT id FROM(SELECT id FROM xxl_job_logglue
- WHERE `job_id` = #{jobId}
+ WHERE job_id = #{jobId}^MORDER BY update_time descLIMIT 0, #{limit}) t1
- ) AND `job_id` = #{jobId}
+ ) AND job_id = #{jobId} <delete id="deleteByJobId" parameterType="java.lang.Integer" >DELETE FROM xxl_job_logglue
- WHERE `job_id` = #{jobId}
+ WHERE job_id = #{jobId}^M</delete>3、xxl-job-admin/src/main/resources/mybatis-mapper/XxlJobLogMapper.xml
<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobLog" useGeneratedKeys="true" keyProperty="id" >INSERT INTO xxl_job_log (
- `job_group`,
- `job_id`,
- `trigger_time`,
- `trigger_code`,
- `handle_code`
+ job_group,
+ job_id,
+ trigger_time,
+ trigger_code,
+ handle_code <update id="updateTriggerInfo" >UPDATE xxl_job_logSET
- `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}
+ 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 xxl_job_logSET
- `handle_time`= #{handleTime},
- `handle_code`= #{handleCode},
- `handle_msg`= #{handleMsg}
- WHERE `id`= #{id}
+ handle_time= #{handleTime},
+ handle_code= #{handleCode},
+ handle_msg= #{handleMsg}
+ WHERE id= #{id}</update> <select id="findFailJobLogIds" resultType="long" >
- SELECT id FROM `xxl_job_log`
- WHERE !(
+ 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
+ AND alarm_status = 0ORDER BY id ASCLIMIT #{pagesize}</select> <update id="updateAlarmStatus" >UPDATE xxl_job_logSET
- `alarm_status` = #{newAlarmStatus}
- WHERE `id`= #{logId} AND `alarm_status` = #{oldAlarmStatus}
+ alarm_status = #{newAlarmStatus}
+ WHERE id= #{logId} AND alarm_status = #{oldAlarmStatus}</update>4、xxl-job-admin/src/main/resources/mybatis-mapper/XxlJobLogReportMapper.xml
<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobLogReport" useGeneratedKeys="true" keyProperty="id" >INSERT INTO xxl_job_log_report (
- `trigger_day`,
- `running_count`,
- `suc_count`,
- `fail_count`
+ trigger_day,
+ running_count,
+ suc_count,
+ fail_count) VALUES (#{triggerDay},#{runningCount}, <update id="update" >UPDATE xxl_job_log_report
- SET `running_count` = #{runningCount},
- `suc_count` = #{sucCount},
- `fail_count` = #{failCount}
- WHERE `trigger_day` = #{triggerDay}
+ SET running_count = #{runningCount},
+ suc_count = #{sucCount},
+ fail_count = #{failCount}
+ WHERE trigger_day = #{triggerDay}</update>5、xxl-job-admin/src/main/resources/mybatis-mapper/XxlJobRegistryMapper.xml
<select id="findDead" parameterType="java.util.HashMap" resultType="java.lang.Integer" >SELECT t.idFROM xxl_job_registry AS t
- WHERE t.update_time <![CDATA[ < ]]> DATE_ADD(#{nowTime},INTERVAL -#{timeout} SECOND)
+ WHERE t.update_time <![CDATA[ < ]]> NOW() - INTERVAL '${timeout} seconds'</select> <select id="findAll" parameterType="java.util.HashMap" resultMap="XxlJobRegistry">SELECT <include refid="Base_Column_List" />FROM xxl_job_registry AS t
- WHERE t.update_time <![CDATA[ > ]]> DATE_ADD(#{nowTime},INTERVAL -#{timeout} SECOND)
+ WHERE t.update_time <![CDATA[ > ]]> NOW() - INTERVAL '${timeout} seconds'</select> <update id="registryUpdate" >UPDATE xxl_job_registry
- SET `update_time` = #{updateTime}
- WHERE `registry_group` = #{registryGroup}
- AND `registry_key` = #{registryKey}
- AND `registry_value` = #{registryValue}
+ SET update_time = #{updateTime}
+ WHERE registry_group = #{registryGroup}
+ AND registry_key = #{registryKey}
+ AND registry_value = #{registryValue}</update> <insert id="registrySave" >
- INSERT INTO xxl_job_registry( `registry_group` , `registry_key` , `registry_value`, `update_time`)
+ INSERT INTO xxl_job_registry( registry_group , registry_key , registry_value, update_time)VALUES( #{registryGroup} , #{registryKey} , #{registryValue}, #{updateTime})</insert>总结
因为公司要求系统国产化，所以得把定时任务xxl-job改造适配磐维数据库，大体步骤如下。
1、修改配置。
2、修改SQL，适配postgresql。
最后因本人能力有限，有什么不对的地方望各位大佬指出好让我改进，多多包含，谢谢大家。