springboot multi data source -- mybatis

Multi database is to use multiple databases in different database instances, or different databases in the same database instance.

Start to implement JDBC template multiple data sources:

github: https://github.com/fengqing11/datasources-mybatis

Complete project structure:

Create database:
There are two databases. Please create two databases before creating the database.
jdbctemplate.sql

# Host: localhost  (Version: 5.7.26)
# Date: 2020-01-19 15:23:50
# Generator: MySQL-Front 5.3  (Build 4.234)

/*!40101 SET NAMES utf8 */;

#
# Structure for table "book"
#

CREATE TABLE `book` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `author` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#
# Data for table "book"
#

INSERT INTO `book` VALUES (1,'a Ah, big soda.','Yes, ah.');

jbdctemplate2.sql

# Host: localhost  (Version: 5.7.26)
# Date: 2020-01-19 15:23:59
# Generator: MySQL-Front 5.3  (Build 4.234)

/*!40101 SET NAMES utf8 */;

#
# Structure for table "book"
#

CREATE TABLE `book` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `author` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#
# Data for table "book"
#

INSERT INTO `book` VALUES (1,'soda','Yeah yeah');

To create a project, pom.xml is as follows:

<?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.2.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>xyz.fengqing11</groupId>
    <artifactId>datasources-mybatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>datasources-mybatis</name>
    <description>Demo project for Spring Boot</description>

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

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </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>

    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Note that one of the dependencies here is Druid spring boot starter, which can help developers easily inherit Druid database connection pool and monitoring in the spring boot project.

To configure a data connection:
Two data sources need to be configured. The main difference is that the database is different, and other configurations are the same

# Data source 1
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.one.jdbc-url=jdbc:mysql:///jdbctemplate?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.one.username=root
spring.datasource.one.password=root

# Data source 2
spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.two.jdbc-url=jdbc:mysql:///jbdctemplate2?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.two.username=root
spring.datasource.two.password=root

To configure a data connection:
Create DataSourceConfig configuration data source, and generate two data sources according to the configuration in application.properties.
Two data sources, dsOne and dsTwo, are provided. The default method name is instance name.
@The ConfigurationProperties annotation indicates that configurations with different prefixes are used to implement datasource instances

package xyz.fengqing11.datasourcesjdbctemplate.config;

import com.alibaba.druid.pool.DruidAbstractDataSource;
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 javax.sql.DataSource;
import java.beans.ConstructorProperties;

@Configuration
public class DataSourcesConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.one")
    DataSource dsOne(){
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.two")
    DataSource dsTwo(){
        return DataSourceBuilder.create().build();
    }
}

Configure MybatisConfig
Because there are two data sources, there are two mapper s

package xyz.fengqing11.datasourcesmybatis.mapper1;

import xyz.fengqing11.datasourcesmybatis.pojo.Book;

import java.util.List;

public interface BookMapper1 {
    List<Book> getAllBooks();
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="xyz.fengqing11.datasourcesmybatis.mapper2.BookMapper2">
    <select id="getAllBooks" resultType="xyz.fengqing11.datasourcesmybatis.pojo.Book">
        SELECT * FROM book
    </select>
</mapper>

mapping.xml also has two

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="xyz.fengqing11.datasourcesmybatis.mapper1.BookMapper1">
    <select id="getAllBooks" resultType="xyz.fengqing11.datasourcesmybatis.pojo.Book">
        SELECT * FROM book
    </select>
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="xyz.fengqing11.datasourcesmybatis.mapper2.BookMapper2">
    <select id="getAllBooks" resultType="xyz.fengqing11.datasourcesmybatis.pojo.Book">
        SELECT * FROM book
    </select>
</mapper>

The two mapper s are in two different files:

Create Book entity class

package xyz.fengqing11.datasourcesjdbctemplate.pojo;

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

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", author='" + author + '\'' +
                '}';
    }
}

Create BookController
For the sake of simplicity, there is no service layer, and the JdbcTemplate layer is directly injected into the Controller.
There are two different ways to inject. One is to use @ Resource annotation and specify the name attribute, that is, to assemble according to name, or to find the corresponding instance injection according to the instance name. The other is to use @ AutoWired annotation combined with @ Qualifier annotation, which is equivalent to the former.

package xyz.fengqing11.datasourcesmybatis.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import xyz.fengqing11.datasourcesmybatis.mapper1.BookMapper1;
import xyz.fengqing11.datasourcesmybatis.mapper2.BookMapper2;
import xyz.fengqing11.datasourcesmybatis.pojo.Book;

import java.util.List;

@RestController
public class BookController {
    @Autowired
    BookMapper1 bookMapper1;
    @Autowired
    BookMapper2 bookMapper2;

    @GetMapping("/test")
    public void test() {
        List<Book> bs1 = bookMapper1.getAllBooks();
        List<Book> bs2 = bookMapper2.getAllBooks();
        System.out.println("bs1:" + bs1);
        System.out.println("bs2:" + bs2);
    }

}