Springboot之 Mybatis 多數據源實現

語言: CN / TW / HK

簡介

上篇講解了 JPA 多數據源實現 ;這篇講解一下 Mybatis 多數據源實現 。主要採用將不同數據庫的 Mapper 接口分別存放到不同的 package,Spring 去掃描不同的包,注入不同的數據源來實現多數據源。原理跟 JPA 多數據源實現基本一致。

創建 mybatis-multip-datasource 項目

數據庫腳本參考:

pom.xml文件引入如下依賴

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.olive</groupId>
	<artifactId>mybatis-multip-datasource</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>jpa-multip-datasource</name>
	<url>http://maven.apache.org</url>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.14</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>8</maven.compiler.source>
		<maven.compiler.target>8</maven.compiler.target>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
		
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
		</dependency>
		
		<dependency>
		    <groupId>org.springframework.boot</groupId>
		    <artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		
	</dependencies>
</project>

配置兩個數據源

分別為第一個主數據源(primary),第二數據源(second),具體配置如下:

# 基本配置
server:
  port: 8080

# 數據庫
spring:
  datasource:
    primary:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3306/db01?characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true
      username: root
      password: root
    second:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3306/crm72?characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true
      username: root
      password: root

  jackson:
    serialization:
      indent-output: true

配置數據源

DataSourceConfig配置

package com.olive.config;

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 javax.sql.DataSource;

/**
 * @Description: 數據源配置
 */
@Configuration
public class DataSourceConfig {

    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    @Primary
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondDataSource")
    @Qualifier("secondDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.second")
    public DataSource secondDataSource() {
        return DataSourceBuilder.create().build();
    }
}

PrimaryConfig數據源

/**
 * @Description: 主數據源配置
 */
@Configuration
@MapperScan(basePackages = "com.olive.mapper.primary",
            sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class PrimaryConfig {

    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

    @Bean(name = "primarySqlSessionFactory")
    @Primary
    public SqlSessionFactory primarySqlSessionFacotory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(primaryDataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/primary/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean(name = "primaryTransactionManager")
    @Primary
    public DataSourceTransactionManager primaryTransactionManager() {
        return new DataSourceTransactionManager(primaryDataSource);
    }

    @Bean(name = "primarySqlSessionTemplate")
    @Primary
    public SqlSessionTemplate primarySqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

SecondConfig數據源

/**
 * @Description: 主數據源配置
 */
@Configuration
@MapperScan(basePackages = "com.olive.mapper.second",
        sqlSessionTemplateRef = "secondSqlSessionTemplate")
public class SecondConfig {

    @Autowired
    @Qualifier("secondDataSource")
    private DataSource secondDataSource;

    @Bean(name = "secondSqlSessionFactory")
    public SqlSessionFactory secondSqlSessionFacotory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(secondDataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/second/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean(name = "secondTransactionManager")
    public DataSourceTransactionManager secondTransactionManager() {
        return new DataSourceTransactionManager(secondDataSource);
    }

    @Bean(name = "secondSqlSessionTemplate")
    public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

創建學生與老師實體類

Student實體類

package com.olive.entity.primary;

import java.io.Serializable;
import lombok.Data;

@Data
public class StudentDO implements Serializable{

    private Long id;

    private String name;

    private int sex;
    
    private String grade;
}

Teacher實體類

package com.olive.entity.second;

import java.io.Serializable;
import lombok.Data;

@Data
public class TeacherDO implements Serializable {

	private Long id;

	private String name;

	private int sex;
	
	private String office;
}

數據庫持久類

StudentMapper類

package com.olive.mapper.primary;

import com.olive.entity.primary.StudentDO;
import org.apache.ibatis.annotations.Param;


public interface StudentMapper {

    int save(@Param("studentDO") StudentDO studentDO);
}

TeacherMapper類

package com.olive.mapper.second;

import com.olive.entity.second.TeacherDO;
import org.apache.ibatis.annotations.Param;

public interface TeacherMapper {

    int save(@Param("teacherDO") TeacherDO teacherDO);
}

Mybatis xml映射

StudentMapper.xml

<?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="com.olive.mapper.primary.StudentMapper">
  <resultMap id="BaseResultMap" type="com.olive.entity.primary.StudentDO">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="sex" jdbcType="INTEGER" property="sex" />
    <result column="grade" jdbcType="VARCHAR" property="grade" />
  </resultMap>

  <insert id="save">
    INSERT INTO t_student (user_name, sex, grade) VALUES (#{studentDO.name}, #{studentDO.sex}, #{studentDO.grade});
  </insert>
</mapper>

TeacherMapper.xml

<?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="com.olive.mapper.second.TeacherMapper">
  <resultMap id="BaseResultMap" type="com.olive.entity.second.TeacherDO">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="sex" jdbcType="INTEGER" property="sex" />
    <result column="office" jdbcType="VARCHAR" property="office" />
  </resultMap>

  <insert id="save">
    INSERT INTO t_teacher ( user_name, sex, office) VALUES (#{teacherDO.name}, #{teacherDO.sex}, #{teacherDO.office});
  </insert>
</mapper>

主要注意這兩個xml文件需要放到不同的目錄,如下圖

創建springboot引導類

package com.olive;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

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

}

測試

package com.olive;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import com.olive.entity.primary.StudentDO;
import com.olive.entity.second.TeacherDO;
import com.olive.mapper.primary.StudentMapper;
import com.olive.mapper.second.TeacherMapper;

@SpringBootTest
public class MybatisTest {

	@Autowired
	StudentMapper studentMapper;

	@Autowired
	TeacherMapper teacherMapper;

	@Test
	public void userSave() {
		StudentDO studentDO = new StudentDO();
		studentDO.setName("BUG弄潮兒");
		studentDO.setSex(1);
		studentDO.setGrade("一年級");
		studentMapper.save(studentDO);

		TeacherDO teacherDO = new TeacherDO();
		teacherDO.setName("Java樂園");
		teacherDO.setSex(2);
		teacherDO.setOffice("語文");
		teacherMapper.save(teacherDO);
	}
}