Springboot + MybatisPlus 配置双数据源代码配置

享巴巴 2020-07-20 PM 49℃ 0条

最近做了一个数据推送程序,把源站数据库中的数据进行格式转换定时推送到目标站数据库中,里面用到了双数据源,途中遇到了一些坑,现在发出来,因为在实际生产过程中,配置双数据的场景也是比较多的!

1.首先把整个配置双数据源代码工程结构贴一下
20200720160722.png

2.配置applciation-dev.yml数据源的yml文件,yml格式贴出来以后可能有点问题,自己调整。

spring:
  # 数据源配置
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    one-data:
      driverClassName: com.mysql.jdbc.Driver
      dbUrl: jdbc:mysql://localhost:3306/se_ysfu?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false
      username: se_ysfu
      password: se_ysfu
      initialSize: 20
      maxActive: 100
      minIdle: 20
      maxWait: 60000
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 30
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 30000
      maxEvictableIdleTimeMillis: 60000
      validationQuery: SELECT 1 FROM DUAL
      testOnBorrow: false
      testOnReturn: false
      testWhileIdle: true
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      filters: stat,wall
    two-data:
      driverClassName: com.mysql.jdbc.Driver
      dbUrl: jdbc:mysql://localhost:3306/hg_video?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false
      username: root
      password: root
      initialSize: 20
      maxActive: 100
      minIdle: 20
      maxWait: 60000
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 30
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 30000
      maxEvictableIdleTimeMillis: 60000
      validationQuery: SELECT 1 FROM DUAL
      testOnBorrow: false
      testOnReturn: false
      testWhileIdle: true
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      filters: stat,wall

3.配置application.yml

server:
  tomcat:
    uri-encoding: UTF-8
  port: 8080
spring:
  profiles:
    active: dev
  application:
    name: videodatapush
  http:
    encoding:
      charset: UTF-8
      force: true
      enabled: true

# mybatis 配置
mybatis-plus:
  type-aliases-package: com.xbbwl.videodatapush
  mapper-locations: classpath:/dataOneMapper/*.xml,classpath:/dataTwoMapper/*.xml
  configuration:
    map-underscore-to-camel-case: true

4.配置文件读取类oneDataConfig

package com.xbbwl.videodatapush.config;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * 数据源1(from端)配置
 */
@Component
@ConfigurationProperties(prefix = "spring.datasource.one-data")
@Data
public class oneDataConfig {
    private String dbUrl;
    private String username;
    private String password;
    private String driverClassName;
    private int initialSize;
    private int maxActive;
    private int minIdle;
    private int maxWait;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private int maxEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private String filters;
    private String connectionProperties;
}

5.配置文件读取类twoDataConfig

package com.xbbwl.videodatapush.config;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * 数据源2(to端)
 */
@Component
@ConfigurationProperties(prefix = "spring.datasource.two-data")
@Data
public class twoDataConfig {
    private String dbUrl;
    private String username;
    private String password;
    private String driverClassName;
    private int initialSize;
    private int maxActive;
    private int minIdle;
    private int maxWait;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private int maxEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private String filters;
    private String connectionProperties;
}

6.手动配置数据源DruidOneConfig

package com.xbbwl.videodatapush.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.annotation.Resource;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.xbbwl.videodatapush.mapper.one"},sqlSessionTemplateRef = "sqlSessionTemplateOne")
public class DruidOneConfig {
    //注入oneDataConfig
    @Resource
    private oneDataConfig oneconfig;
    @Bean("dataSourceOne")
    public DataSource dataSourceOne () {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(oneconfig.getDbUrl());
        datasource.setUsername(oneconfig.getUsername());
        datasource.setPassword(oneconfig.getPassword());
        datasource.setDriverClassName(oneconfig.getDriverClassName());
        datasource.setInitialSize(oneconfig.getInitialSize());
        datasource.setMinIdle(oneconfig.getMinIdle());
        datasource.setMaxActive(oneconfig.getMaxActive());
        datasource.setMaxWait(oneconfig.getMaxWait());
        datasource.setTimeBetweenEvictionRunsMillis(oneconfig.getTimeBetweenEvictionRunsMillis());
        datasource.setMinEvictableIdleTimeMillis(oneconfig.getMinEvictableIdleTimeMillis());
        datasource.setMaxEvictableIdleTimeMillis(oneconfig.getMaxEvictableIdleTimeMillis());
        datasource.setValidationQuery(oneconfig.getValidationQuery());
        datasource.setTestWhileIdle(oneconfig.isTestWhileIdle());
        datasource.setTestOnBorrow(oneconfig.isTestOnBorrow());
        datasource.setTestOnReturn(oneconfig.isTestOnReturn());
        datasource.setPoolPreparedStatements(oneconfig.isPoolPreparedStatements());
        datasource.setMaxPoolPreparedStatementPerConnectionSize(oneconfig.getMaxPoolPreparedStatementPerConnectionSize());
        try {
            datasource.setFilters(oneconfig.getFilters());
        } catch (Exception e) {
            e.printStackTrace();
        }
        datasource.setConnectionProperties(oneconfig.getConnectionProperties());
        return datasource;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactoryOne() throws Exception{
        /*
         * 当我们配置多数据源的时候,baseMapper失效,里面的方法无法使用,
         * 可以把下面的SqlSessionFactoryBean替换成MybatisSqlSessionFactoryBean即可正常使用baseMapper
         */
        //SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        MybatisSqlSessionFactoryBean factory =new MybatisSqlSessionFactoryBean();
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        factory.setDataSource(dataSourceOne());
        factory.setMapperLocations(resolver.getResources("classpath*:/dataOneMapper/*.xml"));
        //注意配置多数据源的时候,在配置文件中的配置的驼峰会失效 ,所以在这里配置
        factory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return factory.getObject();
    }
    @Bean(name="transactionManagerOne")
    public DataSourceTransactionManager transactionManagerOne(){
        return  new DataSourceTransactionManager(dataSourceOne());
    }
    @Bean(name = "sqlSessionTemplateOne")
    public SqlSessionTemplate sqlSessionTemplateOne() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryOne());
    }
}

7.手动配置DruidTwoConfig

package com.xbbwl.videodatapush.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.annotation.Resource;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.xbbwl.videodatapush.mapper.two"},sqlSessionTemplateRef = "sqlSessionTemplateTwo")
public class DruidTwoConfig {
    //注入twoDataConfig
    @Resource
    private twoDataConfig twoconfig;
    @Bean("dataSourceTwo")
    public DataSource dataSourceTwo () {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(twoconfig.getDbUrl());
        datasource.setUsername(twoconfig.getUsername());
        datasource.setPassword(twoconfig.getPassword());
        datasource.setDriverClassName(twoconfig.getDriverClassName());
        datasource.setInitialSize(twoconfig.getInitialSize());
        datasource.setMinIdle(twoconfig.getMinIdle());
        datasource.setMaxActive(twoconfig.getMaxActive());
        datasource.setMaxWait(twoconfig.getMaxWait());
        datasource.setTimeBetweenEvictionRunsMillis(twoconfig.getTimeBetweenEvictionRunsMillis());
        datasource.setMinEvictableIdleTimeMillis(twoconfig.getMinEvictableIdleTimeMillis());
        datasource.setMaxEvictableIdleTimeMillis(twoconfig.getMaxEvictableIdleTimeMillis());
        datasource.setValidationQuery(twoconfig.getValidationQuery());
        datasource.setTestWhileIdle(twoconfig.isTestWhileIdle());
        datasource.setTestOnBorrow(twoconfig.isTestOnBorrow());
        datasource.setTestOnReturn(twoconfig.isTestOnReturn());
        datasource.setPoolPreparedStatements(twoconfig.isPoolPreparedStatements());
        datasource.setMaxPoolPreparedStatementPerConnectionSize(twoconfig.getMaxPoolPreparedStatementPerConnectionSize());
        try {
            datasource.setFilters(twoconfig.getFilters());
        } catch (Exception e) {
            e.printStackTrace();
        }
        datasource.setConnectionProperties(twoconfig.getConnectionProperties());
        return datasource;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactoryTwo() throws Exception{
        /*
         * 当我们配置多数据源的时候,baseMapper失效,里面的方法无法使用,
         * 可以把下面的SqlSessionFactoryBean替换成MybatisSqlSessionFactoryBean即可正常使用baseMapper
         */
        //SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        MybatisSqlSessionFactoryBean factory =new MybatisSqlSessionFactoryBean();
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        factory.setDataSource(dataSourceTwo());
        factory.setMapperLocations(resolver.getResources("classpath*:/dataTwoMapper/*.xml"));
        //注意配置多数据源的时候,在配置文件中的配置的驼峰会失效 ,所以在这里配置
        factory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return factory.getObject();
    }
    @Bean(name="transactionManagerTwo")
    public DataSourceTransactionManager transactionManagerTwo(){
        return  new DataSourceTransactionManager(dataSourceTwo());
    }
    @Bean(name = "sqlSessionTemplateTwo")
    public SqlSessionTemplate sqlSessionTemplateTwo() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryTwo());
    }
}

8.方便测试可以配置一个swagger

package com.xbbwl.videodatapush.config;

import com.google.common.base.Predicates;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Contact;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@Configuration
@EnableSwagger2
public class SwaggerConfig {

    @Bean
    public Docket webApiConfig(){

        return new Docket(DocumentationType.SWAGGER_2)
                .groupName("webApi")
                .apiInfo(webApiInfo())
                .select()
                .paths(Predicates.not(PathSelectors.regex("/admin/.*")))
                .paths(Predicates.not(PathSelectors.regex("/error.*")))
                .build();

    }

    private ApiInfo webApiInfo(){

        return new ApiInfoBuilder()
                .title("数据推送")
                .description("本文档描述数据转换微服务接口定义")
                .version("1.0")
                .contact(new Contact("DaHai", "http://xbbwl.com", "admin@ahoom.cn"))
                .build();
    }
}

9.剩下的代码都是和业务相关 ,可以使用mybatis代码生成器生成,注意代码生成器使用需要引入相关jar包,在pom中贴出。

package com.xbbwl.videodatapush;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
import com.baomidou.mybatisplus.generator.config.GlobalConfig;
import com.baomidou.mybatisplus.generator.config.PackageConfig;
import com.baomidou.mybatisplus.generator.config.StrategyConfig;
import com.baomidou.mybatisplus.generator.config.rules.DateType;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
import org.junit.Test;

public class getCode {

    @Test
    public void main1() {

        // 1、创建代码生成器
        AutoGenerator mpg = new AutoGenerator();

        // 2、全局配置
        GlobalConfig gc = new GlobalConfig();
        String projectPath = System.getProperty("user.dir");
        System.out.println(projectPath);
        gc.setOutputDir("E:\\hg\\videodatapush\\src\\main\\java");
        gc.setAuthor("dahai");
        gc.setOpen(false); //生成后是否打开资源管理器
        gc.setFileOverride(false); //重新生成时文件是否覆盖
        /*
         * mp生成service层代码,默认接口名称第一个字母有 I
         * UcenterService
         * */
        gc.setServiceName("%sService");    //去掉Service接口的首字母I
        gc.setIdType(IdType.ID_WORKER); //主键策略
        gc.setDateType(DateType.ONLY_DATE);//定义生成的实体类中日期类型
        gc.setSwagger2(true);//开启Swagger2模式

        mpg.setGlobalConfig(gc);

        // 3、数据源配置
        DataSourceConfig dsc = new DataSourceConfig();
        dsc.setUrl("jdbc:mysql://localhsot:3306/data?serverTimezone=GMT%2B8");
        dsc.setDriverName("com.mysql.jdbc.Driver");
        dsc.setUsername("root");
        dsc.setPassword("root");
        dsc.setDbType(DbType.MYSQL);
        mpg.setDataSource(dsc);

        // 4、包配置
        PackageConfig pc = new PackageConfig();
        pc.setModuleName("videodatapush"); //模块名
        pc.setParent("com.xbbwl");
        pc.setController("controller");
        pc.setEntity("pojo");
        pc.setService("service");
        pc.setMapper("mapper.one");
        mpg.setPackageInfo(pc);

        // 5、策略配置
        StrategyConfig strategy = new StrategyConfig();
        strategy.setInclude("mac_vod");
        strategy.setNaming(NamingStrategy.underline_to_camel);//数据库表映射到实体的命名策略
        strategy.setTablePrefix(pc.getModuleName() + "_"); //生成实体时去掉表前缀

        strategy.setColumnNaming(NamingStrategy.underline_to_camel);//数据库表字段映射到实体的命名策略
        strategy.setEntityLombokModel(true); // lombok 模型 @Accessors(chain = true) setter链式操作

        strategy.setRestControllerStyle(true); //restful api风格控制器
        strategy.setControllerMappingHyphenStyle(true); //url中驼峰转连字符

        mpg.setStrategy(strategy);

        // 6、执行
        mpg.execute();
    }
}

10.pom依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.4.RELEASE</version>
        <relativePath/>
    </parent>
    <groupId>com.xbbwl</groupId>
    <artifactId>videodatapush</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>videodatapush</name>
    <description>videodatapush-datacenter</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </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>

        <!--mybatis-plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>

        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>


        <!--lombok用来简化实体类-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>

        <!-- Apache Velocity模板引擎 -->
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.0</version>
        </dependency>

        <!--swagger-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version> 2.9.2</version>
            <scope>provided </scope>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
            <scope>provided </scope>
        </dependency>

    </dependencies>


        <build>
            <finalName>${project.artifactId}</finalName>
            <resources>
                <resource>
                    <directory>src/main/java</directory>
                    <includes>
                        <include>**/*.xml</include>
                    </includes>
                    <filtering>false</filtering>
                </resource>
            </resources>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>


</project>

11.启动类,注意mapper扫描

package com.xbbwl.videodatapush;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.xbbwl.videodatapush.mapper")

public class VideodatapushApplication {

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

}

12.注意点

1).使用mybatisplus的时候,如果配置双数据源,本身的basemapper将会无法使用,那是因为覆盖了默认配置。
   可以在public SqlSessionFactory sqlSessionFactory()方法中,使用MybatisSqlSessionFactoryBean factory =new 
   MybatisSqlSessionFactoryBean();代替 SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
   即可又使用basemapper了。
2).配置多数据源的时候,在配置文件中的配置的驼峰会失效问题,就算配置了也没有用。
   解决方法:在public SqlSessionFactory sqlSessionFactory()方法中添加一个
   new MybatisSqlSessionFactoryBean().getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
   就可以了 。

13.剩下的问题不大,如果需要源码,联系邮箱。

非特殊说明,本博所有文章均为博主原创。

评论啦~