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数据库视图
