一:背景
XXL-JOB版本是2.2.1-SNAPSHOT
二:配置连接池
2.1 首先找到xxl-job-admin目录下的pom.xml文件,把原本mysql-connector-java连接包注释掉、引入postgresql连接包。注意我这边金仓数据库安装时选择的oracle兼容模式
<!-- <dependency>-->
<!-- <groupId>mysql</groupId>-->
<!-- <artifactId>mysql-connector-java</artifactId>-->
<!-- <version>${mysql-connector-java.version}</version>-->
<!-- </dependency>-->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.9</version>
</dependency>
2.2 找到application-dev.properties文件,修改数据库连接配置
#spring.datasource.url=jdbc:mysql://127.0.0.1:3306/xxl_job_dev?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
#spring.datasource.username=xxl_job_dev
#spring.datasource.password=123456
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:postgresql://127.0.0.1:54321/xxl_job_dev
spring.datasource.username=xxl_job_dev
spring.datasource.password=123456
spring.datasource.driver-class-name=org.postgresql.Driver
三:启动遇到的问题
3.1 org.postgresql.util.PSQLException: ERROR : syntax error at o near “`”
解决方案:把所有Mapper.xml文件里面的“`”符号去掉即可
错误案例
修改为
3.2 org.postgresql.util.PSQLException: ERROR : operator does not exist: ! boolean
解决方案 找到findFailJobLogIds方法把原来的sql语句调整一下
# 异常SQL语句
SELECT id FROM `xxl_job_log`
WHERE !(
(trigger_code in (0, 200) and handle_code = 0)
OR
(handle_code = 200)
)
AND `alarm_status` = 0
ORDER BY id ASC
LIMIT #{pagesize}
# 修改为一下语句即可
SELECT id FROM xxl_job_log
WHERE (
(trigger_code in (0, 200) and handle_code = 0)
OR
(handle_code = 200)
) = 0
AND alarm_status = 0
ORDER BY id ASC
LIMIT #{pagesize}
3.3 org.postgresql.util.PSQLException: ERROR: syntax error at or near “SECOND”
注意:金仓数据默认不支持DATE_ADD函数、需要通过金仓数据库SYSTEM账号在对应的库执行create extension kdb_date_function; 语句创建扩展
已经支持DATE_ADD函数的只需要找到XxlJobRegistryMapper.xml把findDead方法与findAll方法对应的SQL修改一下即可
# 异常语句
DATE_ADD(#{nowTime},INTERVAL -#{timeout} SECOND)
# 修改为
DATE_ADD(#{nowTime},- INTERVAL '${timeout} second')