背景
之前写了个springboot整合mybatis-plus和dynamic-datasource-spring-boot-starter做多数据源切换的案列 16.从零开始学springboot-整合mybatisPlus-多数据源-代码生成器
，但是呢，考虑到在复杂的业务场景中，多数据源必须对事务有很好的支持，这种情况下dynamic-datasource-spring-boot-starter目前就不适用了，因为dynamic-datasource-spring-boot-starter目前不支持多数据源事务（作者称后续会支持），所以，我们这次采用druid和aop的方式来做多数据源切换，后续业务场景需要支持事务的话直接加入Atomikos 、Bitronix、Narayana依赖即可。
多数据源事务（分布式事务）概念
这边简单介绍下，假设有A、B两个数据库，有这么一个事务A库中某表插入一条记录然后B库某表插入一天记录，此时，事务执行中发生错误的话，应该AB库都需要回退。
创建父项目
1.png
2.png
删除src目录，因为父项目不写代码
创建子项目
3.png
4.png
5.png
建立pom父子关系
SpringBootDemo/pom.xml:
<?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>com.mrcoder</groupId>
<artifactId>SpringBootDemo</artifactId>
<version>1.0.0</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.3.RELEASE</version>
</parent>
<packaging>pom</packaging>
<modules>
<module>sbmp-multidb-druid</module>
</modules>
</project>
sbmp-multidb-druid/pom.xml：
<?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>
<parent>
<groupId>com.mrcoder</groupId>
<artifactId>SpringBootDemo</artifactId>
<version>1.0.0</version>
<relativePath>../pom.xml</relativePath>
</parent>
<groupId>com.mrcoder</groupId>
<artifactId>sbmp-multidb-druid</artifactId>
<version>0.0.1</version>
<name>sbmp-multidb-druid</name>
<description>Demo project for Spring Boot</description>
<properties>
<spring.boot.version>2.1.0.RELEASE</spring.boot.version>
<druid.version>1.1.10</druid.version>
<mybatisplus.version>3.0.7.1</mybatisplus.version>
<lombok.version>1.16.14</lombok.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatisplus.version}</version>
</dependency>
<!-- mybatis-plus代码生成器 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>${mybatisplus.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>${mybatisplus.version}</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>${lombok.version}</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
添加配置
application.yml:
spring:
aop:
proxy-target-class: true
auto: true
datasource:
druid:
db1:
username: root
password: 123456
url: jdbc:mysql://192.168.145.131:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
driver-class-name: com.mysql.cj.jdbc.Driver
db2:
username: root
password: 123456
url: jdbc:mysql://192.168.145.131:3306/test2?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
driver-class-name: com.mysql.cj.jdbc.Driver
建库
新增test、test2库
test新增表:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`modify_time` datetime DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'admin', '22', '2018-02-10 10:11:18', '2018-02-10 10:11:20');
test2新增表:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_no` varchar(11) DEFAULT NULL COMMENT '订单号',
`user_id` int(11) DEFAULT NULL COMMENT '用户ID',
`price` decimal(11,2) DEFAULT NULL COMMENT '支付金额',
`paid_time` datetime DEFAULT NULL COMMENT '支付时间',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`modify_time` datetime DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '20180210001', '1', '100.00', '2018-02-10 10:16:11', '2018-02-10 10:16:15', '2018-02-10 10:16:17');
目录结构
6.png
标红部分请手动新建package和class(也可以使用mybatis-plus-generate代码生成器，这边因为上章使用代码生成器了，这次我们手动创建)
完善
config/DataSourceSwitch
package com.mrcoder.sbmpmultidbdruid.config;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface DataSourceSwitch {
DBTypeEnum value() default DBTypeEnum.db1;
}
config/DataSourceSwitchAspect
package com.mrcoder.sbmpmultidbdruid.config;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
@Component
@Order(value = -100)
@Slf4j
@Aspect
public class DataSourceSwitchAspect {
@Pointcut("execution(* com.mrcoder.sbmpmultidbdruid.mapper.db1.*.*(..))")
private void db1Aspect() {
}
@Pointcut("execution(* com.mrcoder.sbmpmultidbdruid.mapper.db2.*.*(..))")
private void db2Aspect() {
}
@Before("db1Aspect()")
public void db1() {
log.info("切换到db1 数据源...");
DbContextHolder.setDbType(DBTypeEnum.db1);
}
@Before("db2Aspect()")
public void db2() {
log.info("切换到db2 数据源...");
DbContextHolder.setDbType(DBTypeEnum.db2);
}
}
config/DbContextHolder
package com.mrcoder.sbmpmultidbdruid.config;
public class DbContextHolder {
private static final ThreadLocal contextHolder = new ThreadLocal<>();
/**
* 设置数据源
* @param dbTypeEnum
*/
public static void setDbType(DBTypeEnum dbTypeEnum) {
contextHolder.set(dbTypeEnum.getValue());
}
/**
* 取得当前数据源
* @return
*/
public static String getDbType() {
return (String) contextHolder.get();
}
/**
* 清除上下文数据
*/
public static void clearDbType() {
contextHolder.remove();
}
}
config/DBTypeEnum
package com.mrcoder.sbmpmultidbdruid.config;
public enum DBTypeEnum {
db1("db1"), db2("db2");
private String value;
DBTypeEnum(String value) {
this.value = value;
}
public String getValue() {
return value;
}
}
config/DynamicDataSource
package com.mrcoder.sbmpmultidbdruid.config;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DbContextHolder.getDbType();
}
}
config/MybatisPlusConfig
package com.mrcoder.sbmpmultidbdruid.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@EnableTransactionManagement
@Configuration
@MapperScan("com.mrcoder.webapi.mapper")
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
//paginationInterceptor.setLocalPage(true);
return paginationInterceptor;
}
@Bean(name = "db1")
@ConfigurationProperties(prefix = "spring.datasource.druid.db1")
public DataSource db1() {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "db2")
@ConfigurationProperties(prefix = "spring.datasource.druid.db2")
public DataSource db2() {
return DruidDataSourceBuilder.create().build();
}
/**
* 动态数据源配置
*
* @return
*/
@Bean
@Primary
public DataSource multipleDataSource(@Qualifier("db1") DataSource db1,
@Qualifier("db2") DataSource db2) {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DBTypeEnum.db1.getValue(), db1);
targetDataSources.put(DBTypeEnum.db2.getValue(), db2);
dynamicDataSource.setTargetDataSources(targetDataSources);
dynamicDataSource.setDefaultTargetDataSource(db1);
return dynamicDataSource;
}
@Bean("sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory() throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(multipleDataSource(db1(), db2()));
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setMapUnderscoreToCamelCase(true);
configuration.setCacheEnabled(false);
sqlSessionFactory.setConfiguration(configuration);
//PerformanceInterceptor(),OptimisticLockerInterceptor()
//添加分页功能
sqlSessionFactory.setPlugins(new Interceptor[]{
paginationInterceptor()
});
// sqlSessionFactory.setGlobalConfig(globalConfiguration());
return sqlSessionFactory.getObject();
}
/* @Bean
public GlobalConfiguration globalConfiguration() {
GlobalConfiguration conf = new GlobalConfiguration(new LogicSqlInjector());
conf.setLogicDeleteValue("-1");
conf.setLogicNotDeleteValue("1");
conf.setIdType(0);
conf.setMetaObjectHandler(new MyMetaObjectHandler());
conf.setDbColumnUnderline(true);
conf.setRefresh(true);
return conf;
}*/
}
controller/IndexController
package com.mrcoder.sbmpmultidbdruid.controller;
import com.mrcoder.sbmpmultidbdruid.entity.Order;
import com.mrcoder.sbmpmultidbdruid.entity.User;
import com.mrcoder.sbmpmultidbdruid.service.OrderService;
import com.mrcoder.sbmpmultidbdruid.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.math.BigDecimal;
import java.util.List;
/**
* 前端控制器
*/
@RestController
public class IndexController {
@Autowired
private UserService userService;
@Autowired
private OrderService orderService;
@GetMapping("/user")
public ResponseEntity<List<User>> getUserList() {
return ResponseEntity.ok(userService.getUserList());
}
@GetMapping("/price")
public ResponseEntity<BigDecimal> getPrice() {
return ResponseEntity.ok(userService.getOrderPriceByUserId(1));
}
@GetMapping("/order")
public ResponseEntity<List<Order>> getOrderList() {
return ResponseEntity.ok(orderService.getOrderList());
}
@GetMapping("/price2")
public ResponseEntity<BigDecimal> getPrice2() {
return ResponseEntity.ok(orderService.getOrderPriceByUserId(1));
}
}
entity/Order
package com.mrcoder.sbmpmultidbdruid.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
@Data
@TableName("orders")
public class Order extends Model<Order> {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 订单号
*/
@TableField("order_no")
private String orderNo;
/**
* 用户ID
*/
@TableField("user_id")
private Integer userId;
/**
* 支付金额
*/
private BigDecimal price;
/**
* 支付时间
*/
@TableField("paid_time")
private Date paidTime;
/**
* 创建时间
*/
@TableField("create_time")
private Date createTime;
/**
* 修改时间
*/
@TableField("modify_time")
private Date modifyTime;
@Override
protected Serializable pkVal() {
return this.id;
}
}
entity/User
package com.mrcoder.sbmpmultidbdruid.entity;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
@Data
public class User extends Model<User> {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 姓名
*/
private String name;
/**
* 年龄
*/
private Integer age;
/**
* 创建时间
*/
@TableField(value = "create_time",fill = FieldFill.INSERT)
private Date createTime;
/**
* 修改时间
*/
@TableField(value = "modify_time",fill = FieldFill.INSERT_UPDATE)
private Date modifyTime;
@Override
protected Serializable pkVal() {
return this.id;
}
}
mapper/db1/UserMapper
package com.mrcoder.sbmpmultidbdruid.mapper.db1;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mrcoder.sbmpmultidbdruid.entity.User;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
* Mapper 接口
*/
public interface UserMapper extends BaseMapper<User> {
}
mapper/db2/OrderMapper
package com.mrcoder.sbmpmultidbdruid.mapper.db2;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mrcoder.sbmpmultidbdruid.entity.Order;
import org.apache.ibatis.annotations.Select;
import java.math.BigDecimal;
import java.util.List;
/**
* Mapper 接口
*/
public interface OrderMapper extends BaseMapper<Order> {
@Select("SELECT price from orders WHERE user_id = #{userId}")
BigDecimal getPriceByUserId(Integer userId);
}
service/impl/OrderServiceImpl
package com.mrcoder.sbmpmultidbdruid.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.mrcoder.sbmpmultidbdruid.entity.Order;
import com.mrcoder.sbmpmultidbdruid.mapper.db2.OrderMapper;
import com.mrcoder.sbmpmultidbdruid.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
import java.util.List;
/**
* 服务实现类
*/
@Service
public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements OrderService {
@Autowired
private OrderMapper orderMapper;
@Override
public List<Order> getOrderList() {
return orderMapper.selectList(null);
}
@Override
public BigDecimal getOrderPriceByUserId(Integer userId) {
return orderMapper.getPriceByUserId(userId);
}
}
service/impl/UserServiceImpl
package com.mrcoder.sbmpmultidbdruid.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.mrcoder.sbmpmultidbdruid.config.DBTypeEnum;
import com.mrcoder.sbmpmultidbdruid.config.DataSourceSwitch;
import com.mrcoder.sbmpmultidbdruid.entity.User;
import com.mrcoder.sbmpmultidbdruid.mapper.db2.OrderMapper;
import com.mrcoder.sbmpmultidbdruid.mapper.db1.UserMapper;
import com.mrcoder.sbmpmultidbdruid.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.math.BigDecimal;
import java.util.List;
/**
* 服务实现类
*/
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
@Autowired
private OrderMapper orderMapper;
@Autowired
private UserMapper userMapper;
@Override
@DataSourceSwitch(DBTypeEnum.db2)
public List<User> getUserList() {
return userMapper.selectList(null);
}
@Override
//@DataSourceSwitch(DBTypeEnum.db2)
public BigDecimal getOrderPriceByUserId(Integer userId) {
return orderMapper.getPriceByUserId(userId);
}
}
service/OrderService
package com.mrcoder.sbmpmultidbdruid.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.mrcoder.sbmpmultidbdruid.entity.Order;
import java.math.BigDecimal;
import java.util.List;
/**
* 服务类
*/
public interface OrderService extends IService<Order> {
List<Order> getOrderList();
BigDecimal getOrderPriceByUserId(Integer userId);
}
service/UserService
package com.mrcoder.sbmpmultidbdruid.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.mrcoder.sbmpmultidbdruid.entity.User;
import java.math.BigDecimal;
import java.util.List;
/**
* 服务类
*/
public interface UserService extends IService<User> {
List<User> getUserList();
BigDecimal getOrderPriceByUserId(Integer userId);
}
SbmpMultidbDruidApplication
package com.mrcoder.sbmpmultidbdruid;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.mrcoder.sbmpmultidbdruid.mapper")
public class SbmpMultidbDruidApplication {
public static void main(String[] args) {
SpringApplication.run(SbmpMultidbDruidApplication.class, args);
}
}
运行
最后我们运行，访问
7.png
8.png
9.png
10.png
项目地址
https://github.com/MrCoderStack/SpringBootDemo/tree/master/sbmp-multidb-druid
https://gitee.com/MrCoderStack/SpringBootDemo/tree/master/sbmp-multidb-druid
请关注我的订阅号
订阅号.png
