14.从零开始学springboot-mybatis-多数据源-annotations注解方式

前言

上两章我们实现了springboot+mybatis基于annotations注解方式和mapper.xml方式对mysql的操作访问，那么springboot结合mybatis对多数据源的支持如何呢？答案是完全OK，本章我们就来实现 springboot-mybatis-多数据源-annotations注解方式的案例。

创建空项目

添加依赖

         <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.0.0</version>
        </dependency>

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

添加配置

application.yml：

spring:
  datasource:
    master:
      username: root
      password: 123456
      jdbc-url: jdbc:mysql://192.168.145.131:3306/test
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave:
      username: root
      password: 123456
      jdbc-url: jdbc:mysql://192.168.145.131:3306/test2
      driver-class-name: com.mysql.cj.jdbc.Driver

建库

创建test、test2库
test：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) NOT NULL,
  `grade` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

test2:

CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) NOT NULL,
  `course` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

完善

目录结构

根据目录结构，请自行创建package和class。

config/MasterConfig

package com.mrcoder.sbmmultidbannotations.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.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 org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.mrcoder.sbmmultidbannotations.mapper.master", sqlSessionTemplateRef = "masterSqlSessionTemplate")
public class MasterConfig {

    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    @Primary
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "masterSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

config/SlaveConfig

package com.mrcoder.sbmmultidbannotations.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.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.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.mrcoder.sbmmultidbannotations.mapper.slave", sqlSessionTemplateRef = "slaveSqlSessionTemplate")
public class SlaveConfig {

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

    @Bean(name = "slaveSqlSessionFactory")
    public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean(name = "slaveTransactionManager")
    public DataSourceTransactionManager slaveTransactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "slaveSqlSessionTemplate")
    public SqlSessionTemplate slaveSqlSessionTemplate(@Qualifier("slaveSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

entity/Student

package com.mrcoder.sbmmultidbannotations.entity;
import java.io.Serializable;

public class Student implements Serializable {
    private int id;

    private String name;

    private int age;

    private int grade;

    public Student() {
    }

    public Student(String name, int age, int grade) {
        this.name = name;
        this.age = age;
        this.grade = grade;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", grade=" + grade +
                '}';
    }

    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 int getAge() {
        return age;
    }

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

    public int getGrade() {
        return grade;
    }

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

entity/Teacher

package com.mrcoder.sbmmultidbannotations.entity;
import java.io.Serializable;

public class Teacher implements Serializable {

    private int id;
    private String name;
    private int age;
    private int course;

    public Teacher() {
    }

    public Teacher(String name, int age, int course) {
        this.name = name;
        this.age = age;
        this.course = course;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age='" + age + '\'' +
                ", course='" + course + '\'' +
                '}';
    }

    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 int getAge() {
        return age;
    }

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

    public int getCourse() {
        return course;
    }

    public void setCourse(int course) {
        this.course = course;
    }
}

mapper/master/StudentMapper

package com.mrcoder.sbmmultidbannotations.mapper.master;
import com.mrcoder.sbmmultidbannotations.entity.Student;
import org.apache.ibatis.annotations.*;
import java.util.List;

public interface StudentMapper {
    @Select("SELECT * FROM student")
    List<Student> getList();

    @Select("SELECT * FROM student WHERE id = #{id}")
    Student getById(int id);

    @Insert("INSERT INTO student(age,grade,name) VALUES(#{age}, #{grade}, #{name})")
    void insert(Student student);

    @Update("UPDATE student SET name=#{name},age=#{age}, grade=#{grade} WHERE id =#{id}")
    void update(Student student);

    @Delete("DELETE FROM student WHERE id =#{id}")
    void delete(int id);
}

mapper/slave/TeacherMapper

package com.mrcoder.sbmmultidbannotations.mapper.slave;
import com.mrcoder.sbmmultidbannotations.entity.Teacher;
import org.apache.ibatis.annotations.*;
import java.util.List;

public interface TeacherMapper {
    @Select("SELECT * FROM teacher")
    List<Teacher> getList();

    @Select("SELECT * FROM teacher WHERE id = #{id}")
    Teacher getById(int id);

    @Insert("INSERT INTO teacher(age,course,name) VALUES(#{age}, #{course}, #{name})")
    void insert(Teacher teacher);

    @Update("UPDATE teacher SET name=#{name},age=#{age}, course=#{course} WHERE id =#{id}")
    void update(Teacher teacher);

    @Delete("DELETE FROM teacher WHERE id =#{id}")
    void delete(int id);
}

controller/CurdController

package com.mrcoder.sbmmultidbannotations.controller;
import com.mrcoder.sbmmultidbannotations.entity.Student;
import com.mrcoder.sbmmultidbannotations.entity.Teacher;
import com.mrcoder.sbmmultidbannotations.mapper.master.StudentMapper;
import com.mrcoder.sbmmultidbannotations.mapper.slave.TeacherMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

@RestController
public class CurdController {

    @Autowired
    private StudentMapper studentMapper;

    @Autowired
    private TeacherMapper teacherMapper;

    @RequestMapping("/students")
    public List<Student> getStudentList() {
        List<Student> studentList = studentMapper.getList();
        return studentList;
    }

    @RequestMapping("/teachers")
    public List<Teacher> getTeacherList() {
        List<Teacher> teacherList = teacherMapper.getList();
        return teacherList;
    }
}

运行

http://localhost:8080/students 访问master数据源(test库)读取student表
http://localhost:8080/teachers 访问slave数据源(test2库)读取teacher表

项目地址

https://github.com/MrCoderStack/SpringBootDemo/tree/master/sbm-multidb-annotations

https://gitee.com/MrCoderStack/SpringBootDemo/tree/master/sbm-multidb-annotations

请关注我的订阅号