spring+mybatis整合多数据源

本例 SpringBoot 版本：2.3.0.RELEASE

一、创建数据库与表结构数据

CREATE DATABASE `test1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `test1`;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(3) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `student` VALUES (1, '张梦为', 13);
INSERT INTO `student` VALUES (2, '上官婉儿', 22);
INSERT INTO `student` VALUES (3, '唐因', 25);

CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `test2`;
DROP TABLE IF EXISTS `lesson`;
CREATE TABLE `lesson`  (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `grade` float DEFAULT 0,
  `teacher` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `lesson` VALUES (1, '大学物理', 88, '张思瑞');
INSERT INTO `lesson` VALUES (2, '高等数学', 79, '李佛');
INSERT INTO `lesson` VALUES (3, '计算机', 99, '胡晓');

二、pom.xml文件添加相关依赖jar包

<?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.3.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.test</groupId>
    <artifactId>moredb</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>moredb</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.3</version>
        </dependency>

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

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

三、application.properties文件配置两个数据库信息

server.port=9090
server.tomcat.uri-encoding=UTF-8
spring.application.name=spring+mybatis整合多数据源
#主数据源
spring.datasource.first.url=jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=utf8&serverTimezone=CTT
spring.datasource.first.username=admin
spring.datasource.first.password=admin123
spring.datasource.first.driver-class-name=com.mysql.cj.jdbc.Driver
#从数据源
spring.datasource.second.url=jdbc:mysql://127.0.0.1:3306/test2?useUnicode=true&characterEncoding=utf8&serverTimezone=CTT
spring.datasource.second.username=admin
spring.datasource.second.password=admin123
spring.datasource.second.driver-class-name=com.mysql.cj.jdbc.Driver
#开启sql语句控制台显示
logging.level.com.test.moredb.dao=debug

四、配置启动类

package com.test.moredb;

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

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class MoredbApplication {

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

}

说明：SpringBoot会自动根据依赖来自动配置，但是我们的数据源配置被我们自己自定义配置了，此时SpringBoot 无法完成自动化配置，因此就会报错，所以此处我们需要排除  DataSourceAutoConfiguration 的自动配置。

五、添加配置参数类

package com.test.moredb.datasource;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component(value = "firstProperties")
@ConfigurationProperties(prefix = "spring.datasource.first")
public class FirstProperties {
	private String url;
	private String username;
	private String password;
	private String driverClassName;

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getDriverClassName() {
		return driverClassName;
	}

	public void setDriverClassName(String driverClassName) {
		this.driverClassName = driverClassName;
	}
}

package com.test.moredb.datasource;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component(value = "secondProperties")
@ConfigurationProperties(prefix = "spring.datasource.second")
public class SecondProperties {
	private String url;
	private String username;
	private String password;
	private String driverClassName;

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getDriverClassName() {
		return driverClassName;
	}

	public void setDriverClassName(String driverClassName) {
		this.driverClassName = driverClassName;
	}
}

说明：@ConfigurationProperties 注解表示使用不同的前缀的配置参数注入到实体类中，即 application.properties 文件中的参数信息转化为实力类的属性。

六、创建 mybatis 配置类

package com.test.moredb.datasource;

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.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

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

@Configuration
@MapperScan(basePackages = "com.test.moredb.dao.student", sqlSessionFactoryRef = "firstSqlSessionFactory")
public class FirstDataSourceConfig {

	@Resource(name = "firstProperties")
	private FirstProperties config;

	@Bean(name = "dsOne")
	@Primary
	public DataSource dsOne() {
		System.out.println("************************>" + config.getUrl());
		return DataSourceBuilder.create().url(config.getUrl()).username(config.getUsername()).password(config.getPassword()).driverClassName(config.getDriverClassName()).build();
	}

	@Bean(name = "firstSqlSessionFactory")
	@Primary
	public SqlSessionFactory firstSqlSessionFactory(@Qualifier(value = "dsOne") DataSource dataSource) throws Exception {
		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(dataSource);
		bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
		return bean.getObject();
	}

	@Bean(name = "firstSqlSessionTemplate")
	@Primary
	public SqlSessionTemplate firstSqlSessionTemplate(@Qualifier(value = "firstSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
		return new SqlSessionTemplate(sqlSessionFactory);
	}
}

package com.test.moredb.datasource;

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.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

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

@Configuration
@MapperScan(basePackages = "com.test.moredb.dao.lesson", sqlSessionFactoryRef = "secondSqlSessionFactory")
public class SecondDataSourceConfig {

	@Resource(name = "secondProperties")
	private SecondProperties config;

	@Bean(name = "dsTwo")
	public DataSource dsTwo() {
		System.out.println("------------------------>" + config.getUrl());
		return DataSourceBuilder.create().url(config.getUrl()).username(config.getUsername()).password(config.getPassword()).driverClassName(config.getDriverClassName()).build();
	}

	@Bean(name = "secondSqlSessionFactory")
	public SqlSessionFactory firstSqlSessionFactory(@Qualifier(value = "dsTwo") DataSource dataSource) throws Exception {
		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(dataSource);
		bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
		return bean.getObject();
	}

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

说明：

1、@Configuration 表示这个类为配置类；

2、@MapperScan 通过 “basePackages” 属性设置 Mapper 接口的所在位置，通过 “sqlSessionFactoryRef” 属性指定 sqlSessionFactory 实例；

3、SqlSessionFactoryBean.setMapperLocations(Resource... mapperLocations) 方法表示指定mybatis映射的sql文件的xml文件的位置，这个必须指定，不然会报 no statement 错误，我就因为这个问题被折腾了一小时...

4、@Qualifier 注解表示按照实例名去查找相应的实例注入，结合 @Autowired 注解，效果等同于 @Resource 注解，即 @Autowired + @Qualifier = @Resource；

5、配置多个数据源时，其中一个配置类必须加 @Primary 注解，表示将此数据源配置视为主数据源（即默认数据源），否则会报错；

6、如果是主从复制- -读写分离：比如test1中负责增删改，test2中负责查询。但是需要注意的是负责增删改的数据库必须是主库（master）。

七、创建表结构实体类

package com.test.moredb.entity;

import org.springframework.stereotype.Component;

import java.io.Serializable;

@Component
public class Student implements Serializable {

	private Integer id;
	private String name;
	private Integer age;

	public Integer getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

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

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}
}

package com.test.moredb.entity;

import org.springframework.stereotype.Component;

import java.io.Serializable;

@Component
public class Lesson implements Serializable {

	private Integer id;
	private String name;
	private float grade;
	private String teacher;

	public Integer getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

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

	public float getGrade() {
		return grade;
	}

	public void setGrade(float grade) {
		this.grade = grade;
	}

	public String getTeacher() {
		return teacher;
	}

	public void setTeacher(String teacher) {
		this.teacher = teacher;
	}
}

八、创建SQL接口

package com.test.moredb.dao.student;

import com.test.moredb.entity.Student;

import java.util.List;

public interface StudentMapper {

	List<Student> getList();
}

package com.test.moredb.dao.lesson;

import com.test.moredb.entity.Lesson;

import java.util.List;

public interface LessonMapper {

	List<Lesson> getList();
}

说明：

1、前面 mybais 配置类我们已经添加 @MapperScan 注解，这个注解会扫描指定路径下的所有接口，因此这里无需再添加 @Mapper 注解；

2、两个接口类必须放在两个不同的包下，否则会报错，因为这个被整了一个多小时...

九、创建SQL映射xml文件

在前面配置类中我们已经指定xml的文件路径位置（“classpath:mapper/*.xml”），因此我们在 resources 目录下创建 mapper 目录，并创建两个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.test.moredb.dao.student.StudentMapper">
    <select id="getList" resultType="com.test.moredb.entity.Student">
        select * from student;
    </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="com.test.moredb.dao.lesson.LessonMapper">
    <select id="getList" resultType="com.test.moredb.entity.Lesson">
        select * from lesson;
    </select>
</mapper>

十、创建controller

package com.test.moredb.controller;

import com.test.moredb.dao.lesson.LessonMapper;
import com.test.moredb.dao.student.StudentMapper;
import com.test.moredb.entity.Lesson;
import com.test.moredb.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class DbController {

	@Autowired
	private StudentMapper studentMapper;

	@Autowired
	private LessonMapper lessonMapper;

	@GetMapping("/student")
	public List<Student> queryForStudent() {
		return studentMapper.getList();
	}

	@GetMapping("/lesson")
	public List<Lesson> queryForLesson() {
		return lessonMapper.getList();
	}

	@GetMapping("/test")
	public String test() {
		return "页面数据测试！";
	}
}

十一、测试运行

此时，所有配置均已完成，整个结构如下图：

启动项目，测试结果：

不同数据库的数据显示出来，说明多数据源切换成功。