springboot2.1.5 + mybatis to configure multiple data sources

Posted by partypete on Mon, 04 Nov 2019 16:37:20 +0100

1. Startup is the way to create bean s

pom.xml

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid-spring-boot-starter</artifactId>
   <version>1.1.10</version>
</dependency>

<!-- maven Not in the warehouse oracle Database driver, here jar Put it under the project for introduction -->
<dependency>
  <groupId>com.ojdbc</groupId>
  <artifactId>ojdbc</artifactId>
  <version>10.2.0.1</version>
  <scope>system</scope>
  <systemPath>${project.basedir}/src/main/lib/ojdbc14-10.2.0.1.0.jar</systemPath>
</dependency>

 <dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.38</version>
</dependency>

application.properties

#oracle Database
spring.datasource.master.url=jdbc:oracle:thin:@localhost:1521:test
spring.datasource.master.username=oracle
spring.datasource.master.password=123456
spring.datasource.master.driverClassName=oracle.jdbc.OracleDriver
spring.datasource.master.initialSize=5
spring.datasource.master.maxActive=15
spring.datasource.master.minIdle=5
spring.datasource.master.poolPreparedStatements=true
spring.datasource.master.validationQuery=SELECT 1 FROM DUAL

#mysql database
spring.datasource.slave.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf8
spring.datasource.slave.username=mysql
spring.datasource.slave.password=123456
spring.datasource.slave.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.slave.initialSize=5
spring.datasource.slave.maxActive=15
spring.datasource.slave.minIdle=5
spring.datasource.slave.poolPreparedStatements=true
spring.datasource.slave.validationQuery=SELECT 1

MasterDataSourceConfig.java

Note: Primary annotation means to change the data source as the main data source

package com.core.dataSource;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
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.beans.factory.annotation.Qualifier;
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 org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * oracle Data configuration
 */
@Configuration
//Configure mapper file directory for scanning
@MapperScan(basePackages = "com.wawj.core.mapper.master", sqlSessionTemplateRef = "masterSqlSessionTemplate")
public class MasterDataSourceConfig {

    /**
     * create data source
     * @return
     */
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * Create a factory
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //Specify the directory for mapper.xml
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/master/**/*.xml"));
        //Specify entity class directory
        bean.setTypeAliasesPackage("com.wawj.core.entity");
        return bean.getObject();
    }

    /**
     * Create transaction
     * @param dataSource
     * @return
     */
    @Bean
    @Primary
    public DataSourceTransactionManager masterTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * Create template
     * @param sqlSessionFactory
     * @return
     */
    @Bean
    @Primary
    public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

SlaveDataSourceConfig.java

package com.core.dataSource;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
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.beans.factory.annotation.Qualifier;
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 org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * oracle Data configuration
 */
@Configuration
//Configure mapper file directory for scanning
@MapperScan(basePackages = "com.wawj.core.mapper.slave", sqlSessionTemplateRef = "slaveSqlSessionTemplate")
public class SlaveDataSourceConfig {

    /**
     * create data source
     * @return
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * Create a factory
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean
    public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //Specify the directory for mapper.xml
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/slave/**/*.xml"));
        //Specify entity class directory
        bean.setTypeAliasesPackage("com.wawj.core.entity");
        return bean.getObject();
    }

    /**
     * Create transaction
     * @param dataSource
     * @return
     */
    @Bean
    public DataSourceTransactionManager slaveTransactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * Create template
     * @param sqlSessionFactory
     * @return
     */
    @Bean
    public SqlSessionTemplate slaveSqlSessionTemplate(@Qualifier("slaveSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

In this way, it's time to initialize in druid console( http://localhost:8080/druid/login.html )The data source label in does not exist. The currently connected data source will not be displayed until after the request

2. Use dynamic datasource for dynamic configuration (equivalent to creating with spring)

pom.xml

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid-spring-boot-starter</artifactId>
   <version>1.1.10</version>
</dependency>

<dependency>
   <groupId>com.baomidou</groupId>
   <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
   <version>2.5.6</version>
</dependency>

<!-- maven Not in the warehouse oracle Database driver, here jar Put it under the project for introduction -->
<dependency>
   <groupId>com.ojdbc</groupId>
   <artifactId>ojdbc</artifactId>
   <version>10.2.0.1</version>
   <scope>system</scope>
   <systemPath>${project.basedir}/src/main/lib/ojdbc14-10.2.0.1.0.jar</systemPath>
</dependency>

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.38</version>
</dependency>

application.properties

#Global default, which can be changed globally
spring.datasource.dynamic.druid.initial-size=5
spring.datasource.dynamic.druid.max-active=5
spring.datasource.dynamic.druid.min-idle=5
spring.datasource.dynamic.druid.pool-prepared-statements=true

#oracle Database
spring.datasource.dynamic.datasource.master.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.dynamic.datasource.master.druid.validation-query=SELECT 1 FROM DUAL
spring.datasource.dynamic.datasource.master.url=jdbc:oracle:thin:@localhost:1521:test
spring.datasource.dynamic.datasource.master.username=oracle
spring.datasource.dynamic.datasource.master.password=123456

#mysql database
spring.datasource.dynamic.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.slave.druid.validation-query=SELECT 1
spring.datasource.dynamic.datasource.slave.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf8
spring.datasource.dynamic.datasource.slave.username=mysql
spring.datasource.dynamic.datasource.slave.password=123456

#Default aihome database
spring.datasource.dynamic.primary=master
#Exclude the native Druid's quick configuration class (DruidDataSourceAutoConfigure will inject a DataSourceWrapper, which will find url,username,password, etc. under the native spring.datasource. The configuration path of our dynamic data source is changing.)
spring.autoconfigure.exclude=com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure

#Set dynamic login
spring.datasource.druid.stat-view-servlet.login-username=admin
#Set dynamic password
spring.datasource.druid.stat-view-servlet.login-password=admin

#mybatis mapper location
mybatis-plus.mapper-locations=classpath:mapper/**/*.xml
mybatis-plus.type-aliases-package=com.wawj.core.entity

This way, annotations (@ DS("data source name") are used to switch data sources

@DS You can annotate on methods and classes, and method annotations take precedence over class annotations.

Annotation is on service implementation or mapper interface method, but it is strongly not recommended to annotate both service and mapper (there may be problems)

annotation Result
No, @DS Default data source
@DS("dsName") dsName can be a group name or a specific library name

Add: exclude the native DruidDataSourceAutoConfigure, or exclude it in the springboot startup class (some versions of springboot may not be excluded, just configure the exclusion in application.properties)

@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
public class Application {

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

//or

#application.properties
spring.autoconfigure.exclude=com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure

Topics: Programming Spring Druid Oracle MySQL

Hot Topics

©2024 Programmer Think