当业务数据量达到了⼀定程 度，DBA 需要合理配置数据库资源。即配置主库的机器⾼配置，把核⼼⾼频的数据放在主库上；把次要的数据放在从库，低配置。开源节流嘛，就这个意思。把数据放在不同的数据库⾥，就需要通过不同的数据源进⾏操作数据。
这⾥我们举个 springboot-mybatis-mutil-datasource ⼯程案例： user ⽤户 表在主库 master 上，地址表 city 在从库 cluster 上。下⾯实现获取 根据⽤户名获取⽤户信息，包括从库的地址信息，那么需要从主库和从库中分别获取数据，并在业务逻辑层组装返回。逻辑如图：
创建主数据库：
CREATE DATABASE masterdb;
主库中创建表 user
DROP TABLE IF EXISTS `city`;
CREATE TABLE user (
id INT(10) unsigned PRIMARY KEY NOT NULL COMMENT '⽤户编号' AUTO_INCREMENT,
user_name VARCHAR(25) COMMENT '⽤户名称',
description VARCHAR(25) COMMENT '描述'
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
插⼊数据
INSERT user VALUES (1 ,'小明','他有⼀个小学生');
创建从数据库：
CREATE DATABASE clusterdb;
从库中创建表：city
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '城市编号',
`province_id` int(10) unsigned NOT NULL COMMENT '省份编号',
`city_name` varchar(25) DEFAULT NULL COMMENT '城市名称',
`description` varchar(25) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
插入数据：
INSERT city VALUES (1 ,1,'杭州市','西湖区');
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>springboot</groupId>
<artifactId>springboot-mybatis-mutil-datasource</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot-mybatis-mutil-datasource</name>
<!-- Spring Boot 启动父依赖 -->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.3.RELEASE</version>
</parent>
<properties>
<mybatis-spring-boot>1.2.0</mybatis-spring-boot>
<mysql-connector>5.1.39</mysql-connector>
<druid>1.0.18</druid>
</properties>
<dependencies>
<!-- Spring Boot Web 依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Spring Boot Test 依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- Spring Boot Mybatis 依赖 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis-spring-boot}</version>
</dependency>
<!-- MySQL 连接驱动依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql-connector}</version>
</dependency>
<!-- Druid 数据连接池依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid}</version>
</dependency>
<!-- Junit单元测试 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
</project>
application.properties 中配置两个数据源
## master 主数据源配置
master.datasource.url=jdbc:mysql://localhost:3306/masterdb?useUnicode=true&characterEncoding=utf8
master.datasource.username=root
master.datasource.password=123456
master.datasource.driverClassName=com.mysql.jdbc.Driver
## cluster 从数据源配置
cluster.datasource.url=jdbc:mysql://localhost:3306/clusterdb?useUnicode=true&characterEncoding=utf8
cluster.datasource.username=root
cluster.datasource.password=123456
cluster.datasource.driverClassName=com.mysql.jdbc.Driver
多数据源配置的时候注意，必须要有⼀个主数据源，即 MasterDataSourceConfig 配置。
主数据源
package org.spring.springboot.config.ds;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
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
// 扫描 Mapper 接口
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
// 精确到 master 目录，以便跟其他数据源隔离
static final String PACKAGE = "org.spring.springboot.dao.master";
static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";
@Value("${master.datasource.url}")
private String url;
@Value("${master.datasource.username}")
private String user;
@Value("${master.datasource.password}")
private String password;
@Value("${master.datasource.driverClassName}")
private String driverClass;
/**
* 数据源
*/
@Bean(name = "masterDataSource")
@Primary
public DataSource masterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
/**
* 事务管理器
* 注意：@Primary进行修饰以后，当@Transational注解修饰的方法中设计到两个数据原时，
* 发生事务回滚只会回滚masterDataSource的sql。
*/
@Bean(name = "masterTransactionManager")
@Primary
public DataSourceTransactionManager masterTransactionManager() {
return new DataSourceTransactionManager(masterDataSource());
}
/**
* 编程式事务模板
*/
@Bean(name = "masterTransactionTemplate")
public TransactionTemplate masterTransactionTemplate() {
return new TransactionTemplate(masterTransactionManager());
}
/**
* SqlSession工厂
*/
@Bean(name = "masterSqlSessionFactory")
@Primary
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(masterDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(MasterDataSourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
从数据源
package org.spring.springboot.config.ds;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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
// 扫描 Mapper 接口
@MapperScan(basePackages = ClusterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "clusterSqlSessionFactory")
public class ClusterDataSourceConfig {
// 精确到 cluster 目录，以便跟其他数据源隔离
static final String PACKAGE = "org.spring.springboot.dao.cluster";
static final String MAPPER_LOCATION = "classpath:mapper/cluster/*.xml";
@Value("${cluster.datasource.url}")
private String url;
@Value("${cluster.datasource.username}")
private String user;
@Value("${cluster.datasource.password}")
private String password;
@Value("${cluster.datasource.driverClassName}")
private String driverClass;
/**
* 数据源
*/
@Bean(name = "clusterDataSource")
public DataSource clusterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
/**
* 事务管理器
*/
@Bean(name = "clusterTransactionManager")
public DataSourceTransactionManager clusterTransactionManager() {
return new DataSourceTransactionManager(clusterDataSource());
}
/**
* 编程式事务模板
*/
@Bean(name = "clusterTransactionTemplate")
public TransactionTemplate clusterTransactionTemplate() {
return new TransactionTemplate(clusterTransactionManager());
}
/**
* SqlSession工厂
*/
@Bean(name = "clusterSqlSessionFactory")
public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("clusterDataSource") DataSource clusterDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(clusterDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(ClusterDataSourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
package org.spring.springboot.domain;
import java.io.Serializable;
/**
* 城市实体类
*/
public class City {
/**
* 城市编号
*/
private Long id;
/**
* 省份编号
*/
private Long provinceId;
/**
* 城市名称
*/
private String cityName;
/**
* 描述
*/
private String description;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getProvinceId() {
return provinceId;
}
public void setProvinceId(Long provinceId) {
this.provinceId = provinceId;
}
public String getCityName() {
return cityName;
}
public void setCityName(String cityName) {
this.cityName = cityName;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
}
package org.spring.springboot.domain;
/**
* 用户实体类
*/
public class User {
/**
* 城市编号
*/
private Long id;
/**
* 城市名称
*/
private String userName;
/**
* 描述
*/
private String description;
/**
* 城市
*/
private City city;
public City getCity() {
return city;
}
public void setCity(City city) {
this.city = city;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
}
CityMapper.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="org.spring.springboot.dao.cluster.CityDao">
<resultMap id="BaseResultMap" type="org.spring.springboot.domain.City">
<result column="id" property="id" />
<result column="province_id" property="provinceId" />
<result column="city_name" property="cityName" />
<result column="description" property="description" />
</resultMap>
<sql id="Base_Column_List">
id, province_id, city_name, description
</sql>
<select id="findByName" resultMap="BaseResultMap" parameterType="java.lang.String">
select
<include refid="Base_Column_List" />
from city
where city_name = #{cityName}
</select>
</mapper>
UserMapper.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="org.spring.springboot.dao.master.UserDao">
<resultMap id="BaseResultMap" type="org.spring.springboot.domain.User">
<result column="id" property="id" />
<result column="user_name" property="userName" />
<result column="description" property="description" />
</resultMap>
<sql id="Base_Column_List">
id, user_name, description
</sql>
<select id="findByName" resultMap="BaseResultMap" parameterType="java.lang.String">
select
<include refid="Base_Column_List" />
from user
where id = 1
</select>
</mapper>
package org.spring.springboot.dao.cluster;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.spring.springboot.domain.City;
/**
* 城市 DAO 接口类
*/
@Mapper
public interface CityDao {
/**
* 根据城市名称，查询城市信息
*/
City findByName(@Param("cityName") String cityName);
}
package org.spring.springboot.dao.master;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.spring.springboot.domain.User;
/**
* 用户 DAO 接口类
*/
@Mapper
public interface UserDao {
/**
* 根据用户名获取用户信息
*/
User findByName(@Param("userName") String userName);
}
package org.spring.springboot.service;
import org.spring.springboot.domain.City;
import org.spring.springboot.domain.User;
/**
* 用户业务接口层
*/
public interface UserService {
/**
* 根据用户名获取用户信息，包括从库的地址信息
*
* @param userName
* @return
*/
User findByName(String userName);
}
package org.spring.springboot.service.impl;
import org.spring.springboot.dao.cluster.CityDao;
import org.spring.springboot.dao.master.UserDao;
import org.spring.springboot.domain.City;
import org.spring.springboot.domain.User;
import org.spring.springboot.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* 用户业务实现层
*/
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao; // 主数据源
@Autowired
private CityDao cityDao; // 从数据源
@Override
public User findByName(String userName) {
User user = userDao.findByName(userName);
City city = cityDao.findByName("温岭市");
user.setCity(city);
return user;
}
}
package org.spring.springboot.controller;
import org.spring.springboot.domain.City;
import org.spring.springboot.domain.User;
import org.spring.springboot.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
/**
* 用户控制层
*/
@RestController
public class UserRestController {
@Autowired
private UserService userService;
/**
* 根据用户名获取用户信息，包括从库的地址信息
*
* @param userName
* @return
*/
@RequestMapping(value = "/api/user", method = RequestMethod.GET)
public User findByName(@RequestParam(value = "userName", required = true) String userName) {
return userService.findByName(userName);
}
}
package org.spring.springboot;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* Spring Boot 应用启动类
*/
@SpringBootApplication
@EnableTransactionManagement
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class,args);
}
}
不同的 DataSource ，不同的 SqlSessionFactory 和 不同的 DAO 层，在业 务逻辑层做 整合。总结的架构图如下：
多数据源的事务处理是有问题的，一定要注意（可以使用分布式事务，但是很复杂）。
@Autowired
@Qualifier("masterTransactionTemplate")
TransactionTemplate masterTransactionTemplate;
@Autowired
@Qualifier("clusterTransactionTemplate")
TransactionTemplate clusterTransactionTemplate;
public void saveAll(Map<String, String> data) {
masterTransactionTemplate.execute((mStatus) -> {
clusterTransactionTemplate.execute((cStatus) -> {
try {
// 保存数据到主库
saveToMasterDatabase(data);
// 保存数据到从库
saveToClusterDatabase(data);
int a = 1 / 0;
return true;
} catch (Exception e) {
// 回滚主库
mStatus.setRollbackOnly();
// 回滚从库
cStatus.setRollbackOnly();
return false;
}
});
return true;
});
}