17.从零开始学springboot-整合mybatisPlus-druid多源案例

最新推荐文章于 2024-12-15 11:59:01 发布

原创最新推荐文章于 2024-12-15 11:59:01 发布 · 905 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#springboot #demo #mybatisplus #druid #多数据源

从零开始学springboot 专栏收录该内容

60 篇文章

订阅专栏

本文详细介绍了如何在SpringBoot项目中实现多数据源的动态切换，通过使用Druid和AOP方式，并配合MyBatis Plus进行数据库操作，同时为后续支持事务管理预留了扩展方案。

背景

之前写了个springboot整合mybatis-plus和dynamic-datasource-spring-boot-starter做多数据源切换的案列 http://wrsee.com/articles/79 ，但是呢，考虑到在复杂的业务场景中，多数据源必须对事务有很好的支持，这种情况下dynamic-datasource-spring-boot-starter目前就不适用了，因为dynamic-datasource-spring-boot-starter目前不支持多数据源事务（作者称后续会支持），所以，我们这次采用druid和aop的方式来做多数据源切换，后续业务场景需要支持事务的话直接加入Atomikos 、Bitronix、Narayana依赖即可。

多数据源事务（分布式事务）概念

这边简单介绍下，假设有A、B两个数据库，有这么一个事务A库中某表插入一条记录然后B库某表插入一天记录，此时，事务执行中发生错误的话，应该AB库都需要回退。

创建父项目

删除src目录，因为父项目不写代码

创建子项目

建立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');

目录结构

标红部分请手动新建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);
    }

}