创建数据库

新建俩数据库

数据库表字段
ds_mastert_useruid、user_name、age
ds_slave同上同上

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

ds_master

ds_master

ds_slave

ds_slave

后台代码

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

完整 pom.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
@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 类,配置第二个数据源

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
@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 实体类

1
2
3
4
5
6
7
8
9
10
11
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {

private Integer uid;

private String userName;

private Integer age;
}

修改 application.yaml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

1
2
3
4
public interface MasterUserMapper {

List<User> findAll();
}

cn.imzjw.mapper.slave.SlaveUserMapper.java

1
2
3
4
public interface SlaveUserMapper {

List<User> findAll();
}

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

MasterUserMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
<?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

1
2
3
4
5
6
7
8
9
10
11
12
13
<?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 测试测试

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@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

master_test

http://localhost:8080/slave

slave_test

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

源码下载

踩过的坑

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

1
2
3
mybatis:
configuration:
map-underscore-to-camel-case: true

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

1
2
3
4
5
6
7
8
9
10
11
@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 也要配置

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

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

error

所以必须要在它之前(正确的例子)

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