Mybaits多数据库配置

---

前言

本文借鉴了Springboot +Mybatis实现多数据源配置

源代码链接

一、项目结构

二、数据库设计

1.数据库表设计

本文创建两个相同的用户表user1、user2.

三、配置文件

application.properties

#注意将数据库修改为自己的数据库，这里是userTest
#修改mysql账户、密码

#这里是第一个数据库
spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/userTest?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
spring.datasource.primary.username=root
spring.datasource.primary.password=
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver
#这里是第二个数据库
spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/userTest?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
spring.datasource.secondary.username=root
spring.datasource.secondary.password=
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver

四、实体类

User

package com.jdkcb.mybatisstuday.pojo;

public class User {



    private Integer user_id;
    private String user_name;
    private Integer user_age;


    public Integer getUser_id() {
        return user_id;
    }

    public void setUser_id(Integer user_id) {
        this.user_id = user_id;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public Integer getUser_age() {
        return user_age;
    }

    public void setUser_age(Integer user_age) {
        this.user_age = user_age;
    }



    @Override
    public String toString() {
        return "User{" +
                "user_id=" + user_id +
                ", user_name='" + user_name + '\'' +
                ", user_age=" + user_age +
                '}';
    }




}

五、Mapper

PrimaryUserMapper.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.jdkcb.mybatisstuday.mapper.one.PrimaryUserMapper">

    <select id="findAll" resultType="com.jdkcb.mybatisstuday.pojo.User">
                select * from user1;
    </select>
    <select id="findUserById" resultType="com.jdkcb.mybatisstuday.pojo.User">
        select * from user1 where user_id=#{user_id}
    </select>
</mapper>

SecondaryUserMapper.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.jdkcb.mybatisstuday.mapper.two.SecondaryUserMapper">

    <select id="findAll" resultType="com.jdkcb.mybatisstuday.pojo.User">
                select * from user2;
    </select>
    <select id="findUserById" resultType="com.jdkcb.mybatisstuday.pojo.User">
        select * from user2 where user_id=#{user_id}
    </select>

</mapper>

mapper类
PrimaryUserMapper

package com.jdkcb.mybatisstuday.mapper.one;

import com.jdkcb.mybatisstuday.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
@Mapper
public interface PrimaryUserMapper {

//    @Select("select * from sys_user;")
    List<User> findAll();

    User findUserById(@Param("user_id") int user_id);
}

SecondaryUserMapper

package com.jdkcb.mybatisstuday.mapper.two;

import com.jdkcb.mybatisstuday.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;

import java.util.List;


@Component
@Mapper
public interface SecondaryUserMapper {

    //    @Select("select * from sys_user;")
    List<User> findAll();

    User findUserById(@Param("user_id") int user_id);
}

六、配置类

config
PrimaryDataSourceConfig

package com.jdkcb.mybatisstuday.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 javax.sql.DataSource;


@Configuration
// 配置mybatis的接口类放的地方
@MapperScan(basePackages = "com.jdkcb.mybatisstuday.mapper.one", sqlSessionFactoryRef = "PrimarySqlSessionFactory")
public class PrimaryDataSourceConfig {

    // 将这个对象放入Spring容器中
    @Bean(name = "PrimaryDataSource")
    // 表示这个数据源是默认数据源
    @Primary
    // 读取application.properties中的配置参数映射成为一个对象
    // prefix表示参数的前缀
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource getPrimaryDateSource() {
        return DataSourceBuilder.create().build();
    }


    @Bean(name = "PrimarySqlSessionFactory")
    // 表示这个数据源是默认数据源
    @Primary
    // @Qualifier表示查找Spring容器中名字为test1DataSource的对象
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("PrimaryDataSource") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                // 设置mybatis的xml所在位置
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/one/*.xml"));
        return bean.getObject();
    }
    @Bean("PrimarySqlSessionTemplate")
    // 表示这个数据源是默认数据源
    @Primary
    public SqlSessionTemplate primarySqlSessionTemplate(
            @Qualifier("PrimarySqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }





}

SecondaryDataSourceConfig

package com.jdkcb.mybatisstuday.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 javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.jdkcb.mybatisstuday.mapper.two", sqlSessionFactoryRef = "SecondarySqlSessionFactory")
public class SecondaryDataSourceConfig {


    @Bean(name = "SecondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource getSecondaryDataSource() {
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "SecondarySqlSessionFactory")
    public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("SecondaryDataSource") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/two/*.xml"));
        return bean.getObject();
    }
    @Bean("SecondarySqlSessionTemplate")
    public SqlSessionTemplate secondarySqlSessionTemplate(
            @Qualifier("SecondarySqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }
}

七、控制层

UserController

package com.jdkcb.mybatisstuday.controller;

import com.jdkcb.mybatisstuday.mapper.one.PrimaryUserMapper;
import com.jdkcb.mybatisstuday.mapper.two.SecondaryUserMapper;
import com.jdkcb.mybatisstuday.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {

    @Autowired
    private PrimaryUserMapper primaryUserMapper;

    @Autowired
    private SecondaryUserMapper secondaryUserMapper;

    //查询所有用户
    @RequestMapping("primary")
    public Object primary(){
        List<User> list = primaryUserMapper.findAll();
        list.addAll(secondaryUserMapper.findAll());
        return list;
    }

    //查询单个用户，将用户id进行取模运算也就是说user_id为奇数则在表1，否则在表2
    @RequestMapping("findUserById")
    public User findUserById(@RequestBody User user){
        int user_id=user.getUser_id();
        User seleteUser;
        if(user_id%2==1){
            seleteUser= primaryUserMapper.findUserById(user_id);
        }
        else{
            seleteUser=secondaryUserMapper.findUserById(user_id);
        }

        return seleteUser;
    }
}

八、实现

通过Id查询用户实现

通过查询所有用户实现