前言
上一章我们实现了springboot-mybatis-多数据源-annotations注解方式对mysql的操作访问,本章我们继续来实现 springboot-mybatis-多数据源-xml方式的案例。
创建空项目
添加依赖
<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.sbmmultidbxml.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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.mrcoder.sbmmultidbxml.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);
//设置master数据源mapper文件路径
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/master/*.xml"));
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.sbmmultidbxml.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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.mrcoder.sbmmultidbxml.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);
//设置slave数据源mapper文件路径
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/slave/*.xml"));
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.sbmmultidbxml.mapper.master;
import com.mrcoder.sbmmultidbxml.entity.Student;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
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.sbmmultidbxml.mapper.slave;
import com.mrcoder.sbmmultidbxml.entity.Teacher;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
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);
}
resources/mapper/master/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.mrcoder.sbmmultidbxml.mapper.master.StudentMapper">
<resultMap id="BaseResultMap" type="com.mrcoder.sbmmultidbxml.entity.Student">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<result column="age" property="age" jdbcType="INTEGER"/>
<result column="grade" property="grade" javaType="INTEGER"/>
</resultMap>
<sql id="Base_Column_List">
id, name, age, grade
</sql>
<select id="getList" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM student
</select>
<select id="getById" parameterType="java.lang.Long" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM student
WHERE id = #{id}
</select>
<insert id="insert" parameterType="com.mrcoder.sbmmultidbxml.entity.Student">
INSERT INTO
student
(age,grade,name)
VALUES
(#{age}, #{grade}, #{name})
</insert>
<update id="update" parameterType="com.mrcoder.sbmmultidbxml.entity.Student">
UPDATE
student
SET
age = #{age},
grade = #{grade},
name = #{name}
WHERE
id = #{id}
</update>
<delete id="delete" parameterType="java.lang.Long">
DELETE FROM
student
WHERE
id =#{id}
</delete>
</mapper>
resources/mapper/slave/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.mrcoder.sbmmultidbxml.mapper.slave.TeacherMapper">
<resultMap id="BaseResultMap" type="com.mrcoder.sbmmultidbxml.entity.Teacher">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<result column="age" property="age" jdbcType="INTEGER"/>
<result column="course" property="course" javaType="INTEGER"/>
</resultMap>
<sql id="Base_Column_List">
id, name, age, course
</sql>
<select id="getList" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM teacher
</select>
<select id="getById" parameterType="java.lang.Long" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM teacher
WHERE id = #{id}
</select>
<insert id="insert" parameterType="com.mrcoder.sbmmultidbxml.entity.Teacher">
INSERT INTO
teacher
(age,course,name)
VALUES
(#{age}, #{course}, #{name})
</insert>
<update id="update" parameterType="com.mrcoder.sbmmultidbxml.entity.Teacher">
UPDATE
teacher
SET
age = #{age},
course = #{course},
name = #{name}
WHERE
id = #{id}
</update>
<delete id="delete" parameterType="java.lang.Long">
DELETE FROM
teacher
WHERE
id =#{id}
</delete>
</mapper>
controller/CurdController
package com.mrcoder.sbmmultidbxml.controller;
import com.mrcoder.sbmmultidbxml.entity.Student;
import com.mrcoder.sbmmultidbxml.entity.Teacher;
import com.mrcoder.sbmmultidbxml.mapper.master.StudentMapper;
import com.mrcoder.sbmmultidbxml.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-xml
https://gitee.com/MrCoderStack/SpringBootDemo/tree/master/sbm-multidb-xml