SpringBoot integrates multiple data sources (MyBats + Druid)

Posted by kf on Thu, 09 Dec 2021 02:22:42 +0100

Article catalog

SpringBoot integrates multiple data sources (MyBatis + Druid)

Have you ever encountered such a situation when writing a project? When you need the data on your good friend's computer and server, do you have to rely on the interface he gives you. Is this too restrictive. If there is a way to directly use the account and password on your partner's server to customize the data on your partner's server, isn't it cool?

1, Project environmental preparation

MySQL version 5.5 or above
JDK 1.8 up
Development tools: idea 2020, Maven version 3.5.2 and above
SpringBoot version 2.2.10

2, Project configuration initialization

2.0 project structure

2.1 coordinate dependence

 <dependencies>
        
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>
        <dependency>
            <groupId>org.mybatis.spring.bootgroupId>
            <artifactId>mybatis-spring-boot-starterartifactId>
            <version>2.1.3version>
        dependency>
		
		
        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
            <version>8.0.11version>
        dependency>

        
        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>druid-spring-boot-starterartifactId>
            <version>1.1.10version>
        dependency>
    dependencies>

2.2 application.yml

Because two data sources are configured, in order to distinguish them, we will customize the configuration and create two data sources in the database

spring:
  datasource:
    druid:
      db1:
        url: jdbc:mysql://localhost:3306/dts1?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
        driverClassName: com.mysql.cj.jdbc.Driver
        username: root
        password: root
      db2:
        url: jdbc:mysql://localhost:3306/dst2?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
        driverClassName: com.mysql.cj.jdbc.Driver
        username: root
        password: root

2.3 creating database

In order to simplify the operation, I just ensure that the names of the two databases are inconsistent, and the names and fields of other databases are consistent

We created a table called book and randomly inserted several pieces of data

DROP TABLE IF EXISTS `book`;

CREATE TABLE `book` (
  `id` tinyint(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `author` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `book` */

insert  into `book`(`id`,`name`,`author`) values (1,'Brief history of time','Hawking'),(2,'after school','Keio Dongye'),(3,'Travel in vain at night','Keio Dongye'),(4,'The Dream of Red Mansion','Qing Cao Xueqin');

3, MVC is written in three layers

3.1 mapper preparation

In MyBatis, I do not use annotation development, but use xml. Therefore, we also need to create mapper directory and map1 and map2 subdirectories under the Resources directory of SpringBoot

The project structure related to dao layer is as follows. Next, I only give the relevant codes in map1, and only the corresponding modifications in map2

- mapper
	- map1
		- BookMapper1
	- map2
		- BookMapper2

- resources
	- mapper
		- map1
			- BookMapper1.xml
		- map2
			- BookMapper2.xml

The BookMapper1 code is as follows:

package cn.gorit.mapper.map1;

import cn.gorit.entity.Book;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface BookMapper1 {

    List getAllBooks();
}

The configuration of BookMapper1.xml is as follows

<mapper namespace="cn.gorit.mapper.map1.BookMapper1">

    <select id="getAllBooks" resultType="cn.gorit.entity.Book">
        select * from book
    select>

mapper>

3.2 entity class preparation

package cn.gorit.entity;

public class Book {
    private Integer id;
    private String name;
    private String author;

	// getter, setter and constructor are omitted
}

3.3 config configuration class

DataSourceConfig configuration class writing

package cn.gorit.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/**
 * DataSourceConfig Two data sources are provided in: database1 and database2. The default method name is the instance name
 * @ConfigurationProperties  Use a different prefix profile
 */
@Configuration
public class DataSourceConfig {
	
    //  Identify as the first data source
    @Bean("dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource.druid.db1")
    DataSource database1() {
        return DruidDataSourceBuilder.create().build();
    }
	
    // Identify as the second data source
    @Bean("dataSource2")
    @ConfigurationProperties(prefix = "spring.datasource.druid.db2")
    DataSource database2() {
        return DruidDataSourceBuilder.create().build();
    }
}

Mybatisconfionone configuration class (configuration class corresponding to the first data source)

package cn.gorit.config;

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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

/**
* Similarly, for the second data source, create a new configuration class, change the path of MapperScan to map2, and the bean injection also needs to modify that
*/

@Configuration
@MapperScan(value = "cn.gorit.mapper.map1",sqlSessionFactoryRef = "sqlSessionFactoryBean1")
public class MyBatisConfigOne {

    @Autowired
    @Qualifier("dataSource1")
    DataSource db1;

    @Bean
    public SqlSessionFactory sqlSessionFactoryBean1() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(db1);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:./mapper/map1/*.xml"));
        return factoryBean.getObject();
    }

    @Bean
    SqlSessionTemplate sqlSessionTemplate1() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryBean1());
    }
}

3.4 control layer controller

package cn.gorit.controller;

import cn.gorit.mapper.map1.BookMapper1;
import cn.gorit.mapper.map2.BookMapper2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.HashMap;
import java.util.Map;

@RestController
public class BookController {

    @Autowired
    BookMapper1 bookMapper1;

    @Autowired
    BookMapper2 bookMapper2;

    @GetMapping("/book1")
    public Map<String,Object> test1() {
        Map<String,Object> map = new HashMap<>();
        map.put("code",200);
        map.put("msg","Get success");
        map.put("data",bookMapper1.getAllBooks());
        return map;
    }

    @GetMapping("/book2")
    public Map<String,Object> test2() {
        Map<String,Object> map = new HashMap<>();
        map.put("code",200);
        map.put("msg","Get success");
        map.put("data",bookMapper2.getAllBooks());
        return map;
    }
}

Programmer Think