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。
⼀、l
<!--代码简化,⼯具相关 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!--SQLServer 驱动-->
springboot结构<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>
⼆、l
server:
port: 8888
servlet:
context-path: /server
spring:
application:
name: springboot-manyDataSources
datasource:
p6spy: true
dynamic:
datasource:
master:
driver-class-name: ine.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: sql.cj.jdbc.Driver
db2:
driver-class-name: ine.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:
三、spy.properties
modulelist=sion.p6spy.MybatisPlusLogFactory,ine.outage.P6OutageFactory
# ⾃定义⽇志打印
logMessageFormat=sion.p6spy.P6SpyLogger
#⽇志输出到控制台
appender=sion.p6spy.StdoutLogger
# 使⽤⽇志系统记录 sql
#appender=ine.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(){
veById(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(){
ve(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;
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));
Records();
}
}
七、使⽤ @DS 切换数据源，不加@DS注解则默认master数据源
@DS 可以注解在⽅法上和类上，同时存在⽅法注解优先于类上注解。注解在service实现或mapper接⼝⽅法上，但强烈不建议同时在service和mapper注解。 (可能会有问题)
@DS("db2")
@Service
public class VDepartServiceImpl extends ServiceImpl<VDepartMapper, VDepart> implements VDepartService {
}
⽬录结构：
释：ManageUser为mysql数据库⽤户表，VDepart为sqlserver数据库视图
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论