SpringBoot+Mybatis-plus多数据源配置（MySQL、Sqlserver）

前言：

本章案例利用dynamic-datasource-spring-boot-starter集成多数据源，mybaits-plus采用3.3.0版本，主要讲述配置多数据源，其案例中也包含了逻辑删除、攻击SQL阻断解析器、p6spySQL性能分析打印、事务以及分页和乐观锁插件。

dynamic-datasource-spring-boot-starter 是一个基于springboot的快速集成多数据源的启动器，其支持 Jdk 1.7+, SpringBoot 1.4.x 1.5.x 2.0.x。

一、pom.xml

<!--代码简化,工具相关 -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

<!--SQLServer 驱动-->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>sqljdbc4</artifactId>
    <version>4.0</version>
    <scope>runtime</scope>
</dependency>

<!--mysql 驱动-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.0</version>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>2.4.2</version>
</dependency>

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.8.0</version>
</dependency>

二、application.yml

server:
  port: 8888
  servlet:
    context-path: /server
spring:
  application:
    name: springboot-manyDataSources
  datasource:
    p6spy: true
    dynamic:
      datasource:
        master:
          driver-class-name: com.p6spy.engine.spy.P6SpyDriver
          url: jdbc:p6spy:mysql://localhost:3306/datasourceName?characterEncoding=utf8&useSSL=false&serverTimezone=GMT
          username: root
          password: root
#          url: jdbc:mysql://localhost:3306/ datasourceName?characterEncoding=utf8&useSSL=false&serverTimezone=GMT
#          username: root
#          password: root
#          driver-class-name: com.mysql.cj.jdbc.Driver

        db2:
          driver-class-name: com.p6spy.engine.spy.P6SpyDriver
          url: jdbc:p6spy:sqlserver://localhost:1433;DatabaseName=datasourceName
          username: sa
          password: root
#          url: jdbc:sqlserver://localhost:1433;DatabaseName= datasourceName
#          username: sa
#          password: root
#          driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

#日志
logging:
  level:
    com.example.demo : debug

三、spy.properties

modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
#driverlist=org.h2.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2

四、MybatisPlusConfig

@Configuration
@EnableTransactionManagement//开启事务
public class MybatisPlusConfig {
    /**
     * mybatisplus 分页插件
     * @return
     */
    @Bean
    public PaginationInterceptor paginationInterceptor(){
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();

        List<ISqlParser> sqlParserList = new ArrayList<>();
        // 攻击 SQL 阻断解析器、加入解析链;防止小白或者恶意进行delete update 全表操作。注：若表配置使用了逻辑删除将正常执行删除。
        sqlParserList.add(new BlockAttackSqlParser());
        paginationInterceptor.setSqlParserList(sqlParserList);
        return paginationInterceptor;
    }

    /**
     *乐观锁插件:当要更新一条记录的时候，希望这条记录没有被别人更新
     * @return
     */
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor() {
        return new OptimisticLockerInterceptor();
    }
}

五、entity

5.1 ManageUser

@Data
@Builder
@Accessors(chain = true)
@TableName("manage_user")
@EqualsAndHashCode(callSuper = false)
public class ManageUser extends Model<ManageUser> {

    private static final long serialVersionUID = 1L;

    /**
     * 管理员ID
     */
    @TableId(value = "ID", type = IdType.AUTO)
    private Integer id;
    /**
     * 登陆名
     */
    @TableField("USERNAME")
    private String username;
    /**
     * 密码
     */
    @TableField("PASSWORD")
    private String password;
    /**
     * 对应的角色Id
     */
    @TableField("ROLE_ID")
    private Integer roleId;
    /**
     * 是否删除
     */
    @TableField("IS_DEL")
    @TableLogic
    private Integer isDel = 0;

    @TableField("VERSION")
    @Version
    private Integer version;

    @Override
    protected Serializable pkVal() {
        return this.id;
    }

}

5.2 VDepart

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("v_depart")
public class VDepart extends Model<VDepart> {

    private static final long serialVersionUID = 1L;
    
    @TableId(value = "ID", type = IdType.AUTO)
    private Integer id;

    private String code;

    private String name;
    @Override
    protected Serializable pkVal() {
        return this.id;
    }
}

六、controller

6.1 ManageUserController

@Slf4j
@RestController
@RequestMapping("/manageUser")
@Transactional(rollbackFor=Exception.class)
public class ManageUserController {

    @Autowired
    private ManageUserService manageUserService;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/userList")
    public List<ManageUser> userList(){
        return manageUserService.list(null);
    }

    //逻辑删除
    @GetMapping("/deleteUser")
    public boolean deleteUser(){
        return manageUserService.removeById(4);
    }

    //jdbcTemplate
    @GetMapping("/deleteUserTrue/{userId}")
    public boolean deleteUserTrue(@PathVariable("userId") String userid){
        boolean flag = false;
        int update = jdbcTemplate.update("DELETE FROM MANAGE_USER WHERE ID = ?;",userid);
        if(update>0){flag = true;}
        log.info("影响的行数:{} ", update);
        return flag;
    }

    //SQL 阻断解析器
    @GetMapping("/deleteUserAll")
    public boolean deleteUserAll(){
        return manageUserService.remove(null);
    }

    //乐观锁
    @GetMapping("/updateUser")
    public boolean updateUser(){
        return manageUserService.updateUser();
    }

    //事务
    @GetMapping("/addUser")
    public boolean addUser(){
        boolean root = manageUserService.save(ManageUser.builder().username("root").password("4ec847db9bc2bad60e4279cce1fad5db").roleId(1).build());
        int i = 1/0;
        manageUserService.remove(null);
        return root;
    }
}

6.2 VDepartController

@RestController
@RequestMapping("/vDepart")
public class VDepartController {

    @Autowired
    private VDepartService vDepartService;

    @GetMapping("/vDepartList")
    public List<VDepart> accountList(){
        return vDepartService.list();
    }

    @GetMapping("/vDepartPage/{page}/{limit}")
    public List<VDepart> vDepartPage(@PathVariable("page") int page, @PathVariable("limit") int limit){
        IPage<VDepart> page1 = vDepartService.page(new Page<>(page, limit));
        return page1.getRecords();
    }
}

七、使用 @DS 切换数据源,不加@DS注解则默认master数据源

@DS 可以注解在方法上和类上，同时存在方法注解优先于类上注解。注解在service实现或mapper接口方法上，但强烈不建议同时在service和mapper注解。 (可能会有问题)

@DS("db2")
@Service
public class VDepartServiceImpl extends ServiceImpl<VDepartMapper, VDepart> implements VDepartService {

}

目录结构：

释：ManageUser为mysql数据库用户表，VDepart为sqlserver数据库视图