springboot 2.x 集成quartz持久化到一个单独的dataSource时遇到的坑

由于希望可以在集群环境中运行定时job，但考虑到多个job实例有可能带来job重复执行的问题，新项目的job打算从原生的spring task实现改成quartz job实现，并采用jdbc的存储方式。

如果是把quartz的表初始化到原先springboot配置的同一个数据库，并没有太多问题，但考虑到这样做会在业务表中插入很多不相关的表，决定把quartz的表建在单独的一个库中。查了quartz和springboot文档，quartz中的配置

org.quartz.jobStore.dataSource=NAME

org.quartz.dataSource.NAME.driver
org.quartz.dataSource.NAME.URL
org.quartz.dataSource.NAME.user
org.quartz.dataSource.NAME.password
org.quartz.dataSource.NAME.maxConnections
org.quartz.dataSource.NAME.validationQuery
org.quartz.dataSource.NAME.validateOnCheckout
org.quartz.dataSource.NAME.discardIdleConnectionsSeconds

但集成到springboot中，在application.yml用了自己的配置

By default, an in-memory JobStore is used. However, it is possible to configure a JDBC-based store if a DataSource bean is available in your application and if the spring.quartz.job-store-type property is configured accordingly, as shown in the following example:

spring.quartz.job-store-type=jdbc
When the JDBC store is used, the schema can be initialized on startup, as shown in the following example:

spring.quartz.jdbc.initialize-schema=always

Advanced Quartz configuration properties can be customized using spring.quartz.properties.*.

查了些资料后，application.yml配置如下

spring:
    redis:
        host: localhost
        database: 2
        password: guanlouyi
        port: 6399
    datasource:
        url: jdbc:mysql://192.168.40.241:3306/fintech_public?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
        username: root
        password: 20.112@,l        
        driver-class-name: com.mysql.jdbc.Driver   
    quartz:
        job-store-type: jdbc
        jdbc:
            initialize-schema: always
        properties:
            org:
                quartz:
                    dataSource:
                        quartzDS: 
                            driver: com.mysql.jdbc.Driver
                            URL: jdbc:mysql://192.168.40.241:3306/fintech_quartz?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
                            user: root
                            password: 20.112@,l
                    scheduler:
                        instanceName: clusteredScheduler
                        instanceId: AUTO
                    jobStore:
                        class: org.quartz.impl.jdbcjobstore.JobStoreTX
                        driverDelegateClass: org.quartz.impl.jdbcjobstore.StdJDBCDelegate
                        dataSource: quartzDS
                        tablePrefix: QRTZ_
                        isClustered: true
                        clusterCheckinInterval: 10000
                        useProperties: false  
                    threadPool:
                        class: org.quartz.simpl.SimpleThreadPool
                        threadCount: 10
                        threadPriority: 5
                        threadsInheritContextClassLoaderOfInitializingThread: true   

但是这样配置的datasouce并不生效，用的还是原来的数据库。查了些网上的资料，要给scheduler单独的dataSource，而且发现springboot文档里有

To have Quartz use a DataSource other than the application’s main DataSource, declare a DataSource bean, annotating its @Bean method with @QuartzDataSource. Doing so ensures that the Quartz-specific DataSource is used by both the SchedulerFactoryBean and for schema initialization.

于是application.yml改成（spring.datasource去掉改成@Bean@Primary形式，不这样会报同时存在两个dataSrouce,然后把quartz.dataSource的配置去掉了，加@QuartzDataSource注入：自以为）

spring:
    redis:
        host: localhost
        database: 2
        password: guanlouyi
        port: 6399
    quartz:
        job-store-type: jdbc
        jdbc:
            initialize-schema: never
        properties:
            org:
                quartz:
                    dataSource:
                    scheduler:
                        instanceName: clusteredScheduler
                        instanceId: AUTO
                    jobStore:
                        class: org.quartz.impl.jdbcjobstore.JobStoreTX
                        driverDelegateClass: org.quartz.impl.jdbcjobstore.StdJDBCDelegate
                        tablePrefix: QRTZ_
                        isClustered: false
                        clusterCheckinInterval: 10000
                        useProperties: true
                    threadPool:
                        class: org.quartz.simpl.SimpleThreadPool
                        threadCount: 10
                        threadPriority: 5
                        threadsInheritContextClassLoaderOfInitializingThread: true

datasource:
    primary:
        url: jdbc:mysql://192.168.40.241:3306/fintech_public?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
        username: root
        password: 20.112@,l
        driver-class-name: com.mysql.jdbc.Driver   
    scheduler:
        url: jdbc:mysql://192.168.40.241:3306/fintech_quartz
        username: root
        password: root
        driver-class-name: com.mysql.jdbc.Driver

 代码加上DataSourceConfig

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "datasource.primary")
    public DataSourceProperties primaryDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties(prefix = "datasource.scheduler")
    public DataSourceProperties quartzDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "primaryDataSource")
    public DataSource primaryDataSource() {
        return primaryDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean(name = "quartzDataSource")
    @ConfigurationProperties(prefix = "datasource.scheduler")
    @QuartzDataSource
    public DataSource quartzDataSource() {
        DataSource datasource = quartzDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class)
                .build();
        return datasource;
    }

启动报错：

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'adminEventsMapper' defined in file [E:\git\fintech-parent\fintech-microservice-dao\target\classes\com\panshi\fintech\microservice\dao\mapper\AdminEventsMapper.class]: Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Property 'sqlSessionFactory' or 'sqlSessionTemplate' are required

 

分析：可能是把spring.datasource去掉，用@Bean和@Primary的形式生成的DataSource顺序发生了改变，导致sqlSessionFactory和sqlSessionTemplate没有生成，mybatis的Mapper初始化时没有找到，在Mapper注入的地方加上@Lazy可以解决，但不打算采用此方法。后来网上看到有人也自己初始化sqlSessionFactory和sqlSessionTemplate，于是加上：

    /**
     * 创建 SqlSessionFactory
     */
    @Bean(name = "sqlSessionFactory")
    @Primary
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // bean.setMapperLocations(new
        // PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/db1/*.xml"));
        return bean.getObject();
    }


    @Bean(name = "sqlSessionTemplate")
    @Primary
    public SqlSessionTemplate primarySqlSessionTemplate(
            @Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

这个问题解决，但又出现另一个问题：

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'quartzScheduler' defined in class path resource [org/springframework/boot/autoconfigure/quartz/QuartzAutoConfiguration.class]: Invocation of init method failed; nested exception is org.quartz.SchedulerConfigException: DataSource name not set.

后来折腾了老半天，还是把dataSource加回application.yml

eureka:
    client:
        serviceUrl:
            defaultZone: http://localhost:8762/eureka
logging:
    path: /opt
    level:
        ROOT: DEBUG
        com.panshi.fintech: DEBUG
spring:
    redis:
        host: localhost
        database: 2
        password: guanlouyi
        port: 6399
    quartz:
        job-store-type: jdbc
        jdbc:
            initialize-schema: always
        properties:
            org:
                quartz:
                    dataSource:
                        quartzDataSource: 
                            driver: com.mysql.jdbc.Driver
                            URL: jdbc:mysql://192.168.40.241:3306/fintech_quartz
                            user: root
                            password: 20.112@,l                
                    scheduler:
                        instanceName: clusteredScheduler
                        instanceId: AUTO
                    jobStore:
                        class: org.quartz.impl.jdbcjobstore.JobStoreTX
                        driverDelegateClass: org.quartz.impl.jdbcjobstore.StdJDBCDelegate
                        tablePrefix: QRTZ_
                        isClustered: false
                        dataSource: quartzDataSource                           
                        clusterCheckinInterval: 10000
                        useProperties: true
                    threadPool:
                        class: org.quartz.simpl.SimpleThreadPool
                        threadCount: 10
                        threadPriority: 5
                        threadsInheritContextClassLoaderOfInitializingThread: true
datasource:
    primary:
        url: jdbc:mysql://192.168.40.241:3306/fintech_public?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
        username: root
        password: 20.112@,l
        driver-class-name: com.mysql.jdbc.Driver   
    scheduler:
        url: jdbc:mysql://192.168.40.241:3306/fintech_quartz
        username: root
        password: 20.112@,l
        driver-class-name: com.mysql.jdbc.Driver
file:
    path: /opt
    access-path: http://localhost    
swagger:
    enabled: true

原来这个配置还是需要的，但同时还要@Bean生成schedule的dataSource，同时加上

@QuartzDataSource