Twenty eight good people are like crisp people. They cut fools with swords at their waist. Although I don't see my head falling, I secretly teach you that your bone marrow is withered.

The previous chapter briefly introduced the multi data source configuration Jpa(16). If you haven't seen it, Please watch the previous chapter

In the work, in the scenario of business development or business data isolation, it is usually necessary to introduce multiple data sources into a project,

However, the default automation configuration of SpringBoot is single data source, which can be processed through some additional configurations.

1, Preparation before configuring multiple data sources

1, Prepare two databases, springboot and springboot2

The user table is stored in the springboot database

The dept table is stored in the springboot 2 database table

-- stay springboot Create in the database user surface
use springboot;
CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(15) DEFAULT NULL,
    `sex` varchar(20) DEFAULT NULL,
    `age` int(6) DEFAULT NULL,
    `description` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--  stay springboot2 Create in the database dept surface
use springboot2;
CREATE TABLE `dept` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(200) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1, 2. Prepare the corresponding entities User.java and Dept.java

User.java

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    /**
     * @param id id number
     * @param name full name
     * @param sex Gender
     * @param age Age
     * @param description describe
     */
    private Integer id;
    private String name;
    private String sex;
    private Integer age;
    private String description;
}

Dept.java

@Data
public class Dept {
    /**
     * @param id id number
     * @param name Department name
     */
    private Integer id;
    private String name;
}

1, III. use of Mybatis

For the use of Mybatis, please refer to the previous articles written by old Butterfly: SpringBoot integrates mybatis (VII)

Project directory:

2, Mybatis multi data source configuration

2, Add dependency to pom.xml

 <!--mysql Dependence of -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--introduce springboot And mybatis Integrated dependency-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.6</version>
        </dependency>
        <!-- introduce pagehelper Note that the version number of the paging plug-in should be the same as mybatis-plus Match to -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.12</version>
        </dependency>

2, II. Configure multiple data sources for application.yml

# Import database related configuration
#spring:
#  datasource:
#    driver-class-name: com.mysql.cj.jdbc.Driver
#    url: jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true
#    username: root
#    password: abc123
# Configure in the form of multiple data sources
spring:
  datasource:
    # Configure the first database
    one:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8&useSSL=false
      username: root
      password: abc123
      type: com.alibaba.druid.pool.DruidDataSource
    # Configure second database
    two:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/springboot2?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8&useSSL=false
      username: root
      password: abc123
      type: com.alibaba.druid.pool.DruidDataSource
#Used when integrating mybatis
mybatis:
  # The package alias needs to be removed
  # type-aliases-package: top.yueshushu.learn.pojo
  #The mapping file path cannot be used
  # mapper-locations: classpath:mybatis/**/*.xml
  configuration:
    #log information
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

2, Three entity configuration

Under the pojo package, create two packages, one and two.

The one package contains all the information about using the one database entity, and the two package contains all the information about using the two database entity

2, III. User.java user entity

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User implements Serializable {
    /**
     * @param id id number
     * @param name full name
     * @param sex Gender
     * @param age Age
     * @param description describe
     */
    private Integer id;
    private String name;
    private String sex;
    private Integer age;
    private String description;
}

2, III. Dept.java Department entity

@Data
public class Dept implements Serializable {
    /**
     * @param id id number
     * @param name Department name
     */
    private Integer id;
    private String name;
}

2, IV. mapper and its mapping file configuration

Under the mapper package, create the one package and the two package

All information using the one database is placed under the one package, and all information using the two database is placed under the two package

2, IV. UserMapper and its mapping file

2, IV. I. UserMapper.java interface

//@Mapper does not perform configuration scanning
public interface UserMapper {
    // Other methods. For specific use, please refer to the chapter of Mybatis
    void addUser(@Param("user") User user);
    List<User> listUser();
}

2, IV. I. II UserMapper.xml mapping file

<?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="top.yueshushu.learn.mapper.mapper1.UserMapper">

    <insert id="addUser">
        insert into user(name,sex,age,description) values(
            #{user.name},#{user.sex},#{user.age},#{user.description}
        )
    </insert>
    <select id="listUser" resultType="top.yueshushu.learn.pojo.one.User">
        select * from user
    </select>
</mapper>

2, IV. II DeptMapper and its mapping file

2, IV. II. I. DeptMapper.java interface

// @Mapper does not perform configuration scanning
public interface DeptMapper {
    // Other methods. For specific use, please refer to the chapter of Mybatis
    void addDept(@Param("dept") Dept dept);
    List<Dept> listDept();
}

2, IV. II. DeptMapper.xml mapping file

<?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="top.yueshushu.learn.mapper.mapper2.DeptMapper">
    <insert id="addDept">
        insert into dept(name) values(
            #{dept.name}
        )
    </insert>
    <select id="listDept" resultType="top.yueshushu.learn.pojo.two.Dept">
        select * from dept
    </select>
</mapper>

2, V. remove MapperScan scanning annotation from Startup class

// @MapperScan(value = "top.yueshushu.learn.mapper") / / remove the scan annotation
@SpringBootApplication
public class MyBatisApplication {
    public static void main(String[] args) {
        SpringApplication.run(MyBatisApplication.class,args);
        System.out.println("function Mybatis Multi data source profile");
    }
}

2, Vi. multi data source configuration

It mainly configures the data source, the DataSource, the scanning library, and the Mapper interface.

2, Vi.1 DataSource data source configuration

package top.yueshushu.learn.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 org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

/**
 * @ClassName:DataSourceConfig
 * @Description TODO
 * @Author zk_yjl
 * @Date 2021/9/2 11:50
 * @Version 1.0
 * @Since 1.0
 **/
@Configuration
public class DataSourceConfig {
    /**
     * Mybatis Configuring springboot database with multiple data sources
     * @return
     */
    @Bean(name="dataSourceOne")
    @ConfigurationProperties("spring.datasource.one")
    @Primary
    public DataSource dataSourceOne(){
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * Mybatis Configuring the springboot2 database with multiple data sources
     * @return
     */
    @Bean(name="dataSourceTwo")
    @ConfigurationProperties("spring.datasource.two")
    public DataSource dataSourceTwo(){
        return DruidDataSourceBuilder.create().build();
    }
}

On the dataSourceOne() method, an additional annotation @ Primary is added to specify the default library. The default library is springboot

2, Vi. II configuration of scanning library and scanning entity

2, Vi. II. I configure the main database dataSourceOne to scan Mapper and transactions

package top.yueshushu.learn.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

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

/**
 * @ClassName:JpaOneConfig
 * @Description Configuration information of Jpa main database of springboot database
 * @Author zk_yjl
 * @Date 2021/9/6 18:00
 * @Version 1.0
 * @Since 1.0
 **/
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = "top.yueshushu.learn.repository.one", // Specify the location to scan the warehouse
        entityManagerFactoryRef = "localContainerEntityManagerFactoryBeanOne", //Specifies the location of the scanned entity
        transactionManagerRef = "platformTransactionManagerOne") //Specify transaction
public class JpaOneConfig {
    @Resource(name="dataSourceOne")
    DataSource dataSourceOne;
    @Autowired
    JpaProperties jpaProperties;

    @Primary  //Configure default
    @Bean(name = "entityManagerPrimaryOne")
    public EntityManager entityManagerOne(EntityManagerFactoryBuilder builder) {
        return localContainerEntityManagerFactoryBeanOne(builder).getObject().createEntityManager();
    }

    @Bean
    @Primary //Configure default
    LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBeanOne(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(dataSourceOne)
                // Set package for entity
                .packages("top.yueshushu.learn.pojo.one")
                //Set configuration information
                .properties(jpaProperties.getProperties())
                //Set the name of the persistence
                .persistenceUnit("onePersistenceUnit")
                .build();
    }
    @Bean
    @Primary  //Configure default
    PlatformTransactionManager platformTransactionManagerOne(EntityManagerFactoryBuilder builder) {
        LocalContainerEntityManagerFactoryBean factoryBeanOne = localContainerEntityManagerFactoryBeanOne(builder);
        return new JpaTransactionManager(factoryBeanOne.getObject());
    }
}

2, Vi. II. II configure Mapper and transaction scanning from dataSourceTwo Library

package top.yueshushu.learn.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.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

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

/**
 * @ClassName:DataSourceTwoMapperConfig
 * @Description TODO
 * @Author zk_yjl
 * @Date 2021/9/2 12:29
 * @Version 1.0
 * @Since 1.0
 **/
@Configuration
@MapperScan(
        basePackages="top.yueshushu.learn.mapper.mapper2", //Configure mapper interface for scanning
        sqlSessionFactoryRef = "sqlSessionFactoryTwo", //Factory used
        sqlSessionTemplateRef = "sqlSessionTemplateTwo") //SqlSessionTemplate used
public class DataSourceTwoMapperConfig {
    @Resource(name="dataSourceTwo")
    private DataSource dataSourceTwo;
    /**mapper path scanned by this data source*/
    static final String MAPPER_LOCATION = "classpath:mybatis/mapper/mapper2/**/*.xml";
    /**
     * Create SqlSessionFactory object
     */
    @Bean(name="sqlSessionFactoryTwo")
    public SqlSessionFactory sqlSessionFactoryTwo(){
        try{
            SqlSessionFactoryBean sqlSessionFactoryBean=new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(dataSourceTwo);
            //Set mapper profile
            sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
            return sqlSessionFactoryBean.getObject();
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    /**
     * Configure transactions
     * @param dataSourceTwo
     * @return
     */
    @Bean
    public DataSourceTransactionManager dataSourceTransactionManagerTwo(
            @Qualifier("dataSourceTwo") DataSource dataSourceTwo) {
        return new DataSourceTransactionManager(dataSourceTwo);
    }
    /**
     * Create SqlSessionTemplate through SqlSessionFactory
     * @return
     */
    @Bean(name="sqlSessionTemplateTwo")
    public SqlSessionTemplate sqlSessionTemplate(){

        return new SqlSessionTemplate(sqlSessionFactoryTwo());
    }
}

2, VII. Interface and its implementation

2, Vii.1 UserService interface and its implementation class

UserService.java

public interface UserService {
    void addUser(User user);
    List<User> listUser();
}

UserServiceImpl.java

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;

    @Override
    public void addUser(User user) {
        userMapper.addUser(user);
    }
    @Override
    public List<User> listUser() {
       return userMapper.listUser();
    }
}

2, VII. II. DeptService interface and its implementation class

DeptService.java

public interface DeptService {
    void addDept(Dept user);
    List<Dept> listDept();
}

DeptServiceImpl.java

@Service
public class DeptServiceImpl implements DeptService {
    @Autowired
    private DeptMapper deptMapper;

    @Override
    public void addDept(Dept user) {
        deptMapper.addDept(user);
    }
    @Override
    public List<Dept> listDept() {
       return deptMapper.listDept();
    }
}

2, Eight tests

2, Viii.1 creating test classes

@SpringBootTest
@RunWith(SpringJUnit4ClassRunner.class)
@Log4j2
public class MyBatisTests {
    @Autowired
    private UserService userService;
    @Autowired
    private DeptService deptService;
    @Test
    public void addUserTest(){
        //1. Build object
        User user=new User();
        user.setName("Huanhuan");
        user.setAge(22);
        user.setSex("female");
        user.setDescription("A very lovely girl");
        //2. Adding method
        userService.addUser(user);
        log.info("Employee added successfully");
    }

    @Test
    public void listUserTest(){
        List<User> userList=userService.listUser();
        userList.forEach(n->log.info(n));
    }
    @Test
    public void addDeptTest(){
        //1. Build object
        Dept dept=new Dept();
        dept.setName("Information Management Department");
        //2. Adding method
        deptService.addDept(dept);
        log.info("Department added successfully");
    }

    @Test
    public void listDeptTest(){
        List<Dept> deptList=deptService.listDept();
        deptList.forEach(n->log.info(n));
    }
    /**
     * Data source switching configuration
     */
    @Test
    public void allDataSourceTest(){
        addUserTest();
        listDeptTest();
        addDeptTest();
        listUserTest();
    }
}

2, VIII. II test data sources

Database source 1

add to

query

Only this one is stored in the user table of the springboot database

Data source 2

add to

query

Only this data is stored in the dept table of springboot2 database

Data source switching configuration test

Query the database table again

Data source switching configuration succeeded

2, IX. solve the problem of log invalidation

Reason: when configuring multiple data sources, we redefined SqlSessionFactory

If printing is required, add MyBatisProperties when configuring SqlSessionFactory

After the old butterfly test, it is found that the configuration log can only be configured on the slave database, i.e. dataSourceTwo database.

sqlSessionFactoryOne remains unchanged.

2, IX. one can only be configured from the data source dataSourceTwo

/**
     * Create SqlSessionFactory object
     */
    @Bean(name="sqlSessionFactoryTwo")
    //The parameter MyBatisProperties is introduced to solve the problem of log invalidation
    public SqlSessionFactory sqlSessionFactoryTwo(MybatisProperties mybatisProperties){
        try{
            SqlSessionFactoryBean sqlSessionFactoryBean=new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(dataSourceTwo);
            //Set mapper profile
            sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
           // Pass the configuration of mybatis.
            sqlSessionFactoryBean.setConfiguration(mybatisProperties.getConfiguration());
            return sqlSessionFactoryBean.getObject();
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
	//Transaction configuration is omitted 
    /**
     * Create SqlSessionTemplate through SqlSessionFactory
     * @return
     */
    //The parameter MyBatisProperties is introduced to solve the problem of log invalidation
    @Bean(name="sqlSessionTemplateTwo")
    public SqlSessionTemplate sqlSessionTemplate(MybatisProperties mybatisProperties){
        return new SqlSessionTemplate(sqlSessionFactoryTwo(mybatisProperties));
    }

At this time, query again

The code in this chapter is placed on github:

https://github.com/yuejianli/springboot/tree/develop/MultipeDataSource_MyBatis

Thank you for watching, if you like, please pay attention to me, thank you again!!!