xxl-job适配sqlite本地数据库及mysql数据库。可根据配置指定使用哪种数据库。

飞翔的佩奇

2426人浏览 · 2024-09-19 09:41:21

飞翔的佩奇 · 2024-09-19 09:41:21 发布

xxl-job整合sqlite

根据mysql的脚本，整理为sqlite的脚本，详见tables_xxl_job.sqllite.sql
创建sqlite数据库文件并执行上述sql，得到xxl-job-admin的resource下db/xxl-job

xxl-job-admin的pom中添加sqlite驱动依赖

<!--sqlite-jdbc -->
		<dependency>
			<groupId>org.xerial</groupId>
			<artifactId>sqlite-jdbc</artifactId>
			<version>3.43.2.1</version>
		</dependency>

修改application.properties配置

添加配置：

# mysql||sqlite
spring.profiles.active=${CURRENT_DBTYPE:sqlite}
#spring.profiles.active=${CURRENT_DBTYPE:mysql}
database.type: ${spring.profiles.active}

mybatis配置：

### mybatis
mybatis.mapper-locations=classpath:/mybatis-mapper/*Mapper.xml
mybatis.configuration.database-id=${database.type}
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

注释掉datasource相关配置

### xxl-job, datasource
#spring.datasource.url=jdbc:mysql://127.0.0.1:3306/srvmonitor?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
#spring.datasource.username=root
#spring.datasource.password=root
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

创建application-mysql.properties及application-sqlite.properties

application-mysql.properties：

### mysql
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:127.0.0.1:3306}/srvmonitor?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

application-sqlite.properties:

### sqllite
spring.datasource.driver-class-name=org.sqlite.JDBC
spring.datasource.url=jdbc:sqlite::resource:${CONNECTION_URL:db/xxl_job}?date_string_format=yyyy-MM-dd HH:mm:ss

修改XxlJobAdminApplication启动类

@SpringBootApplication
public class XxlJobAdminApplication implements ApplicationContextAware {

	public static void main(String[] args) {
        SpringApplication.run(XxlJobAdminApplication.class, args);
	}

	@Bean
	public DatabaseIdProvider getDatabaseIdProvider() {
		DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
		Properties properties = new Properties();
		properties.setProperty("SQLite", "sqlite");
		properties.setProperty("MySQL", "mysql");
		databaseIdProvider.setProperties(properties);
		return databaseIdProvider;
	}

	@Override
	public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
		Environment environment = applicationContext.getBean(Environment.class);
		String url = environment.getProperty("spring.datasource.url");
		if (!StringUtils.isEmpty(url)) {
			DataSourceUtil.setDbType(url);
		}
	}
}

修改JobScheduleHelper中start方法中适配sqlite的语法

 preparedStatement = conn.prepareStatement(  "select * from xxl_job_lock where lock_name = 'schedule_lock' for update" );
                        if ("sqlite".equalsIgnoreCase(DataSourceUtil.getDbType())) {
                            preparedStatement = conn.prepareStatement("select * from xxl_job_lock where lock_name = 'schedule_lock'");
                        } else {
                            preparedStatement = conn.prepareStatement("select * from xxl_job_lock where lock_name = 'schedule_lock' for update");
                        }
                        preparedStatement.execute();

所有dao中pageList、pageListCount、save方法对应的xml，按照databaseId为mysql和sqlite分别写两套

sqlite不支持CONCAT函数

sqlite不支持useGeneratedKeys="true"来自动获取新增后的id（需要用到的地方，改成用select last_insert_rowid()获取最新的id）