SpringBoot + Mybatis + Druid configure multiple data sources

preface

With the increasing amount of concurrent data, a single database can not bear such a large pressure, so it is more and more important for a project to use multiple databases, of course, the use of database mode may be different, such as master-slave mode, distributed mode. Either pattern requires reading and writing to multiple databases, so you need to configure multiple data sources in your project. In the development of Java projects, the most commonly used data operation framework is Mybatis, and Spring Boot is basically used in the development framework. Spring Boot integrates Mybatis to realize multiple data sources in two ways: subcontracting and AOP. Subcontracting is used here because the hierarchy is clearer.

1. Prepare the database environment

Take Mysql as an example, create two local databases for testing. Create a new table user in the data_one library and a new table TEL in the data_two library. In this solution, there is no limit on the number of databases and different databases can be deployed on different servers.

The library structure is as follows:

The table structure is as follows:

2. Environment preparation

2.1 Environment Configuration

tool	version
Mysql	8.0.16
SpringBoot	2.2.7
Mybatis	2.1.0
JDK	1.8

2.2 Creating a SpringBoot project

2.2.1 Configuring POM Files and adding dependencies

<dependencies>
    <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>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.16</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.16</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.0</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.16.18</version>
        <optional>true</optional>
    </dependency>
</dependencies>
Copy the code

2.2.2 Configuring the application.yml file

Add user and TEL in the outer layer of the original database configuration to distinguish databases. The configuration name can be selected by yourself, but do not use the same name.

Note: We used url for single-source configuration, but jDBC-URL for multi-source configuration

spring:
  application:
    name: datasource

  datasource:
    druid:
      # Maximum active number
      maxActive: 20
      # initialize the number
      initialSize: 1
      # Maximum connection wait timeout
      maxWait: 60000
      validation-query: select 1;
      filter: stat,wall

    user:	Configure the data_one database
      jdbc-url: JDBC: mysql: / / 127.0.0.1:3306 / data_one? useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver

    tel:	Configure the datA_two database
      jdbc-url: JDBC: mysql: / / 127.0.0.1:3306 / data_two? useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver

mybatis:
  type-aliases-package: com.mulits.datasource.model	Use hump annotation mapping
Copy the code

2.2.3 Modifying the startup Class

Because the project uses the Spring Boot framework, which will automatically configure the data source and automatically read the data source information from YML, when we configure the customized data source, Need to exclude = DataSourceAutoConfiguration banned from class for automatic configuration of data sources.

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
public class DatasourceApplication {

    public static void main(String[] args) { SpringApplication.run(DatasourceApplication.class, args); }}Copy the code

3. Case realization

3.1 Creating a Model DirectoryUser,TelEntity class.

@Data
public class User implements Serializable {
    private Long id;
    private String name;

    public User(Long id,String name){
        this.id = id;
        this.name = name; }}Copy the code

@Data
public class Tel implements Serializable {
    private Long id;
    private String tel;

    public Tel(Long id,String tel){
        this.id = id;
        this.tel = tel; }}Copy the code

3.2 Creating the Config directory and creating the DataConfig class

3.2.1 Creating the UserDataConfig class

I set data_one as the Primary database, so I need to configure the @primary annotation.

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.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration	// Register to the container
@MapperScan(basePackages = "com.mulits.datasource.mapper.user.**",
sqlSessionTemplateRef = "userSqlSessionTemplate")
public class UserDataConfig {
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.user")// Set the configuration
    public DataSource userDataSource(a) {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public SqlSessionFactory userSqlSessionFactory(@Qualifier("userDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean
    @Primary
    public DataSourceTransactionManager userTransactionManager(@Qualifier("userDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    @Primary
    public SqlSessionTemplate userSqlSessionTemplate(@Qualifier("userSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return newSqlSessionTemplate(sqlSessionFactory); }}Copy the code

3.2.2 Creating the TelDataConfig class

Since data_ONE is already set as the database, you do not need to add the @primary annotation when configuring the data_two database; otherwise, an error will be reported.

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.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.mulits.datasource.mapper.tel.**",
sqlSessionTemplateRef = "telSqlSessionTemplate")
public class TelDataConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.tel")
    public DataSource telDataSource(a) {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public SqlSessionFactory telSqlSessionFactory(@Qualifier("telDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean
    public DataSourceTransactionManager telTransactionManager(@Qualifier("telDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public SqlSessionTemplate telSqlSessionTemplate(@Qualifier("telSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return newSqlSessionTemplate(sqlSessionFactory); }}Copy the code

Note:

• When configuring multiple data sources, you must have a primary data source.
• @PrimaryIndicates that this Bean is considered first when there are more than one similar Bean candidate. When configuring multiple data sources, note that there must be a primary data source, using@PrimaryMark the Bean;
• @MapperScanMapper interface scanning and container management;

3.3 Creating a Mapper Directory

Mapper files of different databases cannot be contained in the same package. Config throws an injection error during injection. When used, the value is the same as when using a single data source.

3.3.1 Create a user directory in the mapper directory and create a UserMapper interface

@Mapper
@Repository
public interface UserMapper {
    @Select("select * from user where id = #{id}")
    List<User> selectUser(@Param("id") Long id);
}
Copy the code

3.3.2 In the Mapper directory, create a TEL directory and create a TelMapper interface

@Mapper
@Repository
public interface TelMapper {

    @Select("select * from tel where id = #{id}")
    List<Tel> selectTel(@Param("id") Long id);
}
Copy the code

4. Test configuration cases

4.1 Creating a Controller Class

Because this case mainly realizes multi-data source configuration, so I do not write the business class Service interface, directly inject Mapper;

@RestController
public class DataSourceController {
    @Autowired
    private UserMapper userMapper;

    @Autowired
    private TelMapper telMapper;

    @GetMapping("/selectInfo/{id}")
    public Map<String,Object> testSelect(@PathVariable("id") Long id){
        Map<String,Object> map = new HashMap<>();
        List<User> userInfo = userMapper.selectUser(id);
        List<Tel> telInfo = telMapper.selectTel(id);
        map.put("user",userInfo);
        map.put("tel",telInfo);
        returnmap; }}Copy the code

4.2 Querying Two Librariesid=1Data and return

Open the browser, enter the address: http://localhost:8080/selectInfo/1

Springboot + Druid + MyBatis = Springboot + druid + MyBatis