spring boot 定时任务Mybatis-plus debug

需求

项目需要实现一个定时研判数据的服务。

从database01获取指定类型（type）的产品信息（name、code），然后根据code从database02获取该产品昨日的数据，将数据存入database01，并调用研判接口来对数据进行研判，之后将异常数据存入另一张表。

实现

spring boot定时任务 + mybatis-plus + 多数据源。

pom.xml依赖:

<properties>
    <java.version>1.8</java.version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <spring-boot.version>2.3.7.RELEASE</spring-boot.version>
</properties>

<dependencies>
    <!-- mysql驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <!--  mybatis-plus启动器      -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.4.2</version>
    </dependency>
    <!--  mybatis-plus扩展      -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-extension</artifactId>
        <version>3.4.2</version>
    </dependency>
    <!--  mybatis-plus测试工具      -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter-test</artifactId>
        <version>3.4.2</version>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
</dependencies>

<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-dependencies</artifactId>
            <version>${spring-boot.version}</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>
    </dependencies>
</dependencyManagement>

application.yml配置：

spring:
  # 数据源配置
  datasource:
    secm: #数据库1
      driver-class-name: com.mysql.cj.jdbc.Driver # mysql的驱动你可以配置别的关系型数据库
      url: jdbc:mysql://127.0.0.1:3306/secm #数据源地址
      username: root # 用户名
      password: 123456 # 密码
    ssm: # 数据库2
      driver-class-name: com.mysql.cj.jdbc.Driver 
      url: jdbc:mysql://127.0.0.1:3306/ssm
      username: root 
      password: 123456 
    fdps: # 数据库3
      driver-class-name: com.mysql.cj.jdbc.Driver 
      url: jdbc:mysql://127.0.0.1:3306/fdps
      username: root 
      password: 123456
    type: com.zaxxer.hikari.HikariDataSource


job:
  corn:
    #judge: 10-58/10 * * */1 * ? # 秒 分 时 日 月 周（日和周其中必须有一个为'?'，-可以表示区间，','可以是列表，'/x'表示每x执行一次
    judge: '* * * 0/1 * ?' # 一天执行一次

数据源配置文件：

package com.ncwu.scheduled.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;


@Configuration
public class DataSourceConfig {

    /**
     * db1数据源 配置项
     *
     * @return
     */
    @Primary
    @Bean(name = "secmDataSourceProperties")
    @ConfigurationProperties(prefix = "spring.datasource.secm")
    public DataSourceProperties secmDataSourceProperties() {
        return new DataSourceProperties();
    }

    /**
     * db1 数据源
     * @param dataSourceProperties
     * @return
     */
    @Primary
    @Bean(name = "secmDataSource")
    public DataSource secmDataSource(@Qualifier("secmDataSourceProperties") DataSourceProperties dataSourceProperties){
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    /**
     * db2数据源 配置项
     *
     * @return
     */
    @Bean(name = "ssmDataSourceProperties")
    @ConfigurationProperties(prefix = "spring.datasource.ssm")
    public DataSourceProperties ssmDataSourceProperties() {
        return new DataSourceProperties();
    }

    /**
     * db2 数据源
     * @param dataSourceProperties
     * @return
     */
    @Bean(name = "ssmDataSource")
    public DataSource ssmDataSource(@Qualifier("ssmDataSourceProperties") DataSourceProperties dataSourceProperties){
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    /**
     * db3数据源 配置项
     *
     * @return
     */
    @Bean(name = "fdpsDataSourceProperties")
    @ConfigurationProperties(prefix = "spring.datasource.fdps")
    public DataSourceProperties fdpsDataSourceProperties() {
        return new DataSourceProperties();
    }

    /**
     * db3 数据源
     * @param dataSourceProperties
     * @return
     */
    @Bean(name = "fdpsDataSource")
    public DataSource fdpsDataSource(@Qualifier("fdpsDataSourceProperties") DataSourceProperties dataSourceProperties){
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

}

每一个数据源对应一个MybatisPlus配置文件：

package com.ncwu.scheduled.config.mybatis;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.MybatisXMLLanguageDriver;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.core.toolkit.GlobalConfigUtils;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.logging.stdout.StdOutImpl;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.ncwu.scheduled.mapper.secm", sqlSessionTemplateRef = "secmSqlSessionTemplate")
public class MybatisPlusConfigSECM {

    @Primary
    @Bean("secmSqlSessionFactory")
    public SqlSessionFactory secmSqlSessionFactory(@Qualifier("secmDataSource") DataSource dataSource) throws Exception {
        // 创建一个工厂bean，并设置数据源
        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(dataSource);
        // 创建一个配置类
        MybatisConfiguration configuration = new MybatisConfiguration();
        // 其他一些配置
        configuration.setDefaultScriptingLanguage(MybatisXMLLanguageDriver.class);
        configuration.setLogImpl(StdOutImpl.class);
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        sqlSessionFactory.setConfiguration(configuration);
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().
                getResources("classpath*:com/ncwu/scheduled/mapper/secm/*.xml"));

        // MP v3.2 以上版本使用 PaginationInnerInterceptor 替换 PaginationInterceptor
        final MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        sqlSessionFactory.setPlugins(new Interceptor[]{
                mybatisPlusInterceptor
//                new PerformanceInterceptor() //MP v3.2 以上版本请使用第三方性能分析工具，例p6spy
//                        .setFormat(true),
        });
        // 全局的数据库配置，用于设置表前缀，相当于在每一个实体类上加@TableName("test_"+<Class.name>)
        GlobalConfig.DbConfig dbConfig = new GlobalConfig.DbConfig();
        dbConfig.setTablePrefix("test_");
        final GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setDbConfig(dbConfig).setBanner(false);
        //GlobalConfigUtils.setGlobalConfig(configuration, globalConfig);
        sqlSessionFactory.setGlobalConfig(globalConfig.setBanner(false));
        return sqlSessionFactory.getObject();
    }
    
    @Primary
    @Bean(name = "secmTransactionManager")
    public DataSourceTransactionManager secmTransactionManager(@Qualifier("secmDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Primary
    @Bean(name = "secmSqlSessionTemplate")
    public SqlSessionTemplate secmSqlSessionTemplate(@Qualifier("secmSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

其他两个同上（去掉 @Primary注解）

定时任务配置：

package com.ncwu.scheduled.config;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.ncwu.scheduled.mapper.secm.SensorMapper;
import com.ncwu.scheduled.pojo.secm.Sensor;
import com.ncwu.scheduled.service.SensorService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;

import java.util.List;

@Slf4j
@Configuration
@EnableScheduling
public class ScheduledConfig {

    @Autowired
    SensorService sensorService;

    @Autowired
    SensorMapper sensorMapper;

    @Scheduled(cron = "${job.corn.judge}")
    public void judgeJob() {

        assert sensorService.getBaseMapper() == sensorMapper;
        final LambdaQueryWrapper<Sensor> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.select(Sensor::getSensorType)
                .groupBy(Sensor::getSensorType);
        final List<Object> types = sensorService.listObjs(queryWrapper);

        for (Object type : types) {
            queryWrapper.clear();
            queryWrapper.eq(Sensor::getSensorType, type);
            final List<Sensor> list = sensorService.list(queryWrapper);
            log.info("type: {}, count: {}", type, list.size());
        }
    }

}

测试

测试发现，Mybatis-Plus QueryWrapper查询指定type的list结果都是为空，复制log中的sql到命令行执行结果没问题，但是代码中就是获取不到，就算使用mapper.xml文件也不行，但是使用所属分类id查询就能获取到信息。 最后花了一天时间从头学习Mybatis-Plus，学完之后还是无法解决。。。

最后，从头理了一下思路，原来是数据源url最后没有 ?characterEncoding=utf-8，造成字符串编码问题。