前言
上两章我们实现了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