Spring Boot + MyBatis 配置多数据源

创建数据库

新建俩数据库

数据库	表	表字段
ds_master	t_user	uid、user_name、age
ds_slave	同上	同上

为了后面看出效果，表中插入的数据都不一样

ds_master

ds_slave

后台代码

创建 Spring Boot 的工程，这里我的版本是 2.4.4，勾选 Lombok、Spring Web、MySQL Driver

完整 pom.xml

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

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

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>

</dependencies>

新建 MasterDataSourceConfig 类，作用就是配置我们主数据库相关的 bean

@Configuration
@MapperScan(basePackages = "cn.imzjw.mapper.master", sqlSessionFactoryRef = "MasterSqlSessionFactory")
public class MasterDataSourceConfig {

    /**
     * 映射文件路径
     */
    public static final String MAPPER_LOCATION = "classpath:mapper/*Mapper.xml";

    /**
     * 表示这个数据源是默认的数据源
     * 必须要加 Primary 注解，如果两个数据源都没有 Primary 注解会报错
     *
     * @return
     */
    @Bean(name = "MasterDataSource")
    @Primary
    // 配置文件前缀
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource getPrimaryDateSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "MasterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("MasterDataSource") DataSource datasource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        // 配置映射文件
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        // 下划线转驼峰
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return bean.getObject();
    }

    @Bean("MasterSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("MasterSqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }
}

注解说明

• MapperScan：扫描 mapper 接口的
• Primary：表示使用的是默认数据库
• ConfigurationProperties：读取配置文件中的配置参数映射成一个对象，其中 prefix 表示参数的前缀

新建 SlaveDataSourceConfig 类，配置第二个数据源

@Configuration
@MapperScan(basePackages = "cn.imzjw.mapper.slave", sqlSessionFactoryRef = "SlaveSqlSessionFactory")
public class SlaveDataSourceConfig {

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

    @Bean(name = "SlaveSqlSessionFactory")
    public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("SlaveDataSource") DataSource datasource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        // 配置映射文件
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        // 下划线转驼峰
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return bean.getObject();
    }

    @Bean("SlaveSqlSessionTemplate")
    public SqlSessionTemplate slaveSqlSessionTemplate(@Qualifier("SlaveSqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }
}

新建 User 实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {

    private Integer uid;
    
    private String userName;
    
    private Integer age;
}

修改 application.yaml

spring:
  datasource:
    # 默认数据库配置
    master:
      jdbc-url: jdbc:mysql:///ds_master?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver
    # 次数据库配置
    slave:
      jdbc-url: jdbc:mysql:///ds_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver

新建 mapper 接口和对应的 xml 文件

cn.imzjw.mapper.master.MasterUserMapper.java

public interface MasterUserMapper {

    List<User> findAll();
}

cn.imzjw.mapper.slave.SlaveUserMapper.java

public interface SlaveUserMapper {

    List<User> findAll();
}

注： mapper 接口要在不同的包下

MasterUserMapper.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="cn.imzjw.mapper.master.MasterUserMapper">

    <select id="findAll" resultType="cn.imzjw.entity.User">
        SELECT *
        FROM t_user
    </select>

</mapper>

SlaveUserMapper.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="cn.imzjw.mapper.slave.SlaveUserMapper">

    <select id="findAll" resultType="cn.imzjw.entity.User">
        SELECT *
        FROM t_user
    </select>

</mapper>

代码部分就已经完成了，现在新建个 Controller 测试测试

测试

@RestController
public class TestController {

    @Autowired
    private MasterUserMapper masterUserMapper;

    @Autowired
    private SlaveUserMapper slaveUserMapper;

    @RequestMapping("master")
    public Object primary() {
        return masterUserMapper.findAll();
    }

    @RequestMapping("slave")
    public Object secondary() {
        return slaveUserMapper.findAll();
    }
}

最后启动 Spring Boot 工程，分别访问 http://localhost:8080/master

和 http://localhost:8080/slave

到此，就已经完结了!!!

踩过的坑

实体类下划线转驼峰的问题，在多数据源中，尽管你在配置文件中配置了如下代码，也是没有效果的

mybatis:
  configuration:
    map-underscore-to-camel-case: true

解决办法就是在 MasterDataSourceConfig 里的工厂方法配置

@Bean(name = "MasterSqlSessionFactory")
@Primary
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("MasterDataSource") DataSource datasource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(datasource);
    // 配置映射文件
    bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
    // 下划线转驼峰
    bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
    return bean.getObject();
}

此外 SlaveDataSourceConfig 也要配置

第二个坑就是，配置映射文件必须要在下划线转驼峰之前，不然就会报错，以下就是个错误的例子

// 下划线转驼峰
bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
// 配置映射文件
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));

所以必须要在它之前（正确的例子）

// 配置映射文件
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
// 下划线转驼峰
bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);