xxl-job整合sqlite

1. 根据mysql的脚本，整理为sqlite的脚本，详见tables_xxl_job.sqllite.sql

2. 创建sqlite数据库文件并执行上述sql，得到xxl-job-admin的resource下db/xxl-job

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

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

4. 修改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
   

5. 创建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
   

6. 修改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);
   		}
   	}
   }
   

7. 修改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();
   

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

   sqlite不支持CONCAT函数

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