从零开始学springboot-mybatis-多数据源-xml

作者: 码哥说 | 来源:发表于2019-04-16 16:37 被阅读2次

前言

上一章我们实现了springboot-mybatis-多数据源-annotations注解方式对mysql的操作访问，本章我们继续来实现 springboot-mybatis-多数据源-xml方式的案例。

创建空项目

1.png

添加依赖

         <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>

2.png

添加配置

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;

完善

目录结构

3.png

根据目录结构，请自行创建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

请关注我的订阅号

订阅号.png

从零开始学springboot-mybatis-多数据源-xml

前言

创建空项目

添加依赖

添加配置

建库

完善

运行

项目地址

请关注我的订阅号

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

从零开始学springboot