序言
在项目中,单个数据库承受的压力是有限的,为了支持业务高并发的场景,多数据源的使用已经成为必须前提。这里使用SpringBoot整合MyBatis实现多数据源的使用案例,案例目前就使用两个数据库。Springboot整合mybatis实现多数据源有两种方式:分包和AOP。这里使用的分包,因为层次更加清晰。
一、项目环境说明
jdk版本为jdk1.8.0_172;
数据库使用的是MySQL的MariaDB;
SpringBoot版本:2.2.5.RELEASE
MyBatis版本:2.1.2
这里说明环境是因为不同版本对应的配置参数可能会有变化,如果出现参数配置了还出现参数配置问题,有可能就是因为版本更新之后参数变量名有变动或者包名有变动。
二、项目搭建
2.1 初始化项目
这里IDE使用IntelliJIdea,项目初始化表方便,File->New->New Project 选择SpringInitializr初始化一个springboot项目。
2.2 项目目录说明
这里只对关键目录做出说明:
config目录:存放数据源配置类,多数据源每个数据源各单独一个配置类。
controller目录:存放controller,开放调用接口。
mapper目录:在这个目录下有对应数据源ds1、ds2的mapper接口文件。
pojo目录:存放数据实体类。
service目录:存放业务类。
resources目录:存放配置文件的资源目录,mapper下面ds1、ds2分别为数据源1数据源2的xml文件目录。
2.3 代码实现
首先我们从应用配置文件application.yml开始配置,配置多数据源需要为每个数据源的datasource连接参数做出配置.
# Spring 配置
spring:
#数据源配置
datasource:
ds1: #数据源1
jdbc-url: jdbc:mysql://127.0.0.1:3306/mytest01
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
ds2: #数据源1
jdbc-url: jdbc:mysql://127.0.0.1:3306/mytest02
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
第二步我们需要为每个数据源配置对应的DataSource,我们把配置类放在config.datasource目录中。
先实现ds1的配置类:
```
package com.example.multdatasource.config.datasource;
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.example.multdatasource.mapper.ds1",sqlSessionTemplateRef = "sqlSessionTemplate1")
public class DatasourceConfig1 {
@Bean(name = "datasource1")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.ds1")
public DataSource getDatasource(){
return DataSourceBuilder.create().build();
}
@Primary
@Bean(name = "sqlSessionFactory1")
public SqlSessionFactory getSqlSessionFactory1(@Qualifier("datasource1") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/ds1/*.xml"));
return sqlSessionFactoryBean.getObject();
}
@Bean("dataSourceTransactionManager1")
@Primary
public DataSourceTransactionManager getDataSourceTransactionManager1(@Qualifier("datasource1") DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
@Bean("sqlSessionTemplate1")
@Primary
public SqlSessionTemplate getSqlSessionTemplate1(@Qualifier("sqlSessionFactory1") SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
}
```
然后再实现ds2的DataSource配置:
package com.example.multdatasource.config.datasource;
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.example.multdatasource.mapper.ds2",sqlSessionTemplateRef = "sqlSessionTemplate2")
public class DatasourceConfig2 {
@Bean(name = "datasource2")
@ConfigurationProperties(prefix = "spring.datasource.ds2")
public DataSource getDatasource(){
return DataSourceBuilder.create().build();
}
@Bean(name = "sqlSessionFactory2")
public SqlSessionFactory getSqlSessionFactory2(@Qualifier("datasource2") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/ds2/*.xml"));
return sqlSessionFactoryBean.getObject();
}
@Bean("dataSourceTransactionManager2")
public DataSourceTransactionManager getDataSourceTransactionManager2(@Qualifier("datasource2") DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
@Bean("sqlSessionTemplate2")
public SqlSessionTemplate getSqlSessionTemplate2(@Qualifier("sqlSessionFactory2") SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
}
ds1与ds2的数据源配置大致一样,但是稍有差别,ds1的配置类中我们加了@Primary注解,这些注解可以让我们默认ds1为主数据源。配置类中我们指定数据源的mapper.xml文件路径。
第三步我们实现业务逻辑,这里只实现增删操作。在MariaDB中有数据库testdb01库user表,testdb02库role表。
mapper/ds1/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="com.example.multdatasource.mapper.ds1.UserMapper">
<resultMap id="UserResult" type="com.example.multdatasource.pojo.User">
<result property="id" column="id" javaType="Integer" ></result>
<result property="name" column="name" javaType="String" ></result>
<result property="gender" column="gender" javaType="String" ></result>
<result property="age" column="age" javaType="Integer" ></result>
</resultMap>
<select id="selectByName" resultMap="UserResult">
SELECT * FROM USER WHERE name=#{name}
</select>
<insert id="insertUser" parameterType="com.example.multdatasource.pojo.User" >
insert into
USER(name, gender, age)
values(#{name},#{gender},#{age})
</insert>
</mapper>
```
mapper/ds1/UserMapper.java实现
```
package com.example.multdatasource.mapper.ds1;
import com.example.multdatasource.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;
@Mapper
@Component("userMapper")
public interface UserMapper {
User selectByName(@Param("name") String name);
int insertUser(User user);
}
```
mapper/ds2/RoleMapper.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.example.multdatasource.mapper.ds2.RoleMapper">
<resultMap id="RoleResult" type="com.example.multdatasource.pojo.Role">
<result property="id" column="id" javaType="Integer" ></result>
<result property="name" column="name" javaType="String" ></result>
<result property="role" column="role" javaType="String" ></result>
</resultMap>
<select id="selectByName" resultMap="RoleResult">
SELECT * FROM ROLE WHERE name=#{name}
</select>
<insert id="insertRole" parameterType="com.example.multdatasource.pojo.Role" >
insert into
ROLE(name, gender)
values(#{name},#{role})
</insert>
</mapper>
mapper/ds2/RoleMapper.java实现
```
package com.example.multdatasource.mapper.ds2;
import com.example.multdatasource.pojo.Role;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;
@Mapper
@Component("roleMapper")
public interface RoleMapper {
Role selectByName(String name);
}
```
实现对应的service
UserService.java
```
package com.example.multdatasource.service;
import com.example.multdatasource.mapper.ds1.UserMapper;
import com.example.multdatasource.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
public User selectOneUser(String name){
return userMapper.selectByName(name);
}
public int insertOne(User user){
return userMapper.insertUser(user);
}
}
```
RoleService.java
```
package com.example.multdatasource.service;
import com.example.multdatasource.mapper.ds2.RoleMapper;
import com.example.multdatasource.pojo.Role;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class RoleService {
@Autowired
private RoleMapper roleMapper;
public Role getOneByName(String name){
return roleMapper.selectByName(name);
}
}
```
实现对应的Controller
UserController.java
```
package com.example.multdatasource.controller;
import com.example.multdatasource.pojo.User;
import com.example.multdatasource.service.UserService;
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.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;
import java.util.Map;
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("/getOne")
@ResponseBody
public User getOne(@RequestBody Map<String,Object> requestBody){
String name = requestBody.get("name").toString();
System.out.println("getOne");
return userService.selectOneUser(name);
}
@RequestMapping("/addOne")
@ResponseBody
public Map<String,Object> insertOnt(@RequestBody User user){
Map<String,Object> response = new HashMap<>();
int insertCount = userService.insertOne(user);
String result = insertCount == 1 ? "success" : "failed";
response.put("result",result);
return response;
}
}
```
RoleController.java
```
package com.example.multdatasource.controller;
import com.example.multdatasource.pojo.Role;
import com.example.multdatasource.service.RoleService;
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.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;
import java.util.Map;
@RestController
@RequestMapping("/role")
public class RoleController {
@Autowired
private RoleService roleService;
@RequestMapping("/getOne")
@ResponseBody
public Map<String,Object> getOne(@RequestBody Map<String,Object> request){
Map<String,Object> result = new HashMap<>();
String name = request.get("name").toString();
Role role = roleService.getOneByName(name);
result.put("role",role);
return result;
}
}
```
应用启动我们就可以访问localhost:8080/role/getOne去数据源ds2查询role数据,访问localhost:8080/user/addOn去数据源ds1新增一个user记录。
项目地址:https://gitee.com/charberming/demos.git ,多数据源使用案例在 multdatasource目录