KaoTi

坚持的力量

9, mybatisplus-进阶用法

发布于 2020-07-22 | 分类于 mybatis | 10分钟 | 1830字数

逻辑删除

在做数据删除时, 不删除数据, 仅仅通过添加一个标记来标识该数据被删除, 而不会做真正的删除。

使用方法

1. 数据库

在数据库表中添加一个字段, 用来标识数据是否被删除。

ALTER TABLE `test`.`User` ADD COLUMN `delete` INT(1) DEFAULT 0;

2. 修改配置

配置com.baomidou.mybatisplus.core.config.GlobalConfig$DbConfig

application.yml

mybatis-plus:
    global-config:
        db-config:
            logic-delete-field: flag  # 全局逻辑删除的实体字段名(since 3.3.0,配置后可以忽略不配置步骤2)
            logic-delete-value: 1 # 逻辑已删除值(默认为 1)
            logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)

3. 修改实体类

在实体类中添加删除标记字段, 并加上@TableLogic注解

@TableLogic
private Integer deleted;

public Integer getDeleted() {
	return deleted;
}

public void setDeleted(Integer deleted) {
	this.deleted = deleted;
}

测试

UserService.java

/**
 * 根据id删除
 * @param id
 * @return
 */
public int deleteById(Integer id);

UserServiceImpl.java

@Override
public int deleteById(Integer id) {
	int deleteById = userMapper.deleteById(id);
	return deleteById;
}

UserController.java

@RequestMapping("/delete/{id}")
public int deleteById(@PathVariable("id") Integer id){
	return userService.deleteById(id);
}

运行访问 http://127.0.0.1:8080/user/delete/1

2020-07-20 18:04:11.099 DEBUG 18176 --- [nio-8080-exec-1] t.i.m.s.mapper.UserMapper.deleteById     : ==>  Preparing: UPDATE User SET deleted=1 WHERE id=? AND deleted=0 
2020-07-20 18:04:11.132 DEBUG 18176 --- [nio-8080-exec-1] t.i.m.s.mapper.UserMapper.deleteById     : ==> Parameters: 1(Integer)
2020-07-20 18:04:11.146 DEBUG 18176 --- [nio-8080-exec-1] t.i.m.s.mapper.UserMapper.deleteById     : <==    Updates: 1

删除果断只是更新deleted字段了

查询数据 http://127.0.0.1:8080/delete/1

2020-07-20 18:05:37.509 DEBUG 18176 --- [nio-8080-exec-8] t.i.m.s.mapper.UserMapper.selectById     : ==>  Preparing: SELECT id,name,age,sex,deleted FROM User WHERE id=? AND deleted=0 
2020-07-20 18:05:37.509 DEBUG 18176 --- [nio-8080-exec-8] t.i.m.s.mapper.UserMapper.selectById     : ==> Parameters: 1(Integer)
2020-07-20 18:05:37.538 DEBUG 18176 --- [nio-8080-exec-8] t.i.m.s.mapper.UserMapper.selectById     : <==      Total: 0

分页使用

MybatisPlusConfig.java

大单页限制数量一般最大设置为20

//Spring boot方式
@EnableTransactionManagement
@Configuration
@MapperScan("top.itkaoti.mybatis.*.mapper*")
public class MybatisPlusConfig {

  @Bean
  public PaginationInterceptor paginationInterceptor() {
      PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
      // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
      // paginationInterceptor.setOverflow(false);
      // 设置最大单页限制数量，默认 500 条，-1 不受限制
       paginationInterceptor.setLimit(20);
      // 开启 count 的 join 优化,只针对部分 left join
      paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
      return paginationInterceptor;
  }
}

UserService.java

/**
 * @param currentPage 当前查询页码
 * @param pageSize  每页条数
 * @return
 */
public IPage<User> selectUsers(int currentPage, int pageSize);

UserServiceImpl.java

@Override
public IPage<User> selectUsers(int currentPage, int pageSize) {
	Page<User> page = new Page<>();
	page.setCurrent(currentPage);
	page.setSize(pageSize);
	return userMapper.selectPage(page, new QueryWrapper<User>());
}

UserController.java

@RequestMapping("/select/{pageSize}/{currentPage}")
public IPage<User> selectUsers(@PathVariable("pageSize") Integer pageSize, @PathVariable("currentPage") Integer currentPage){
	return userService.selectUsers(currentPage, pageSize);
}

运行 请求http://127.0.0.1:8080/user/select/5/1

2020-07-21 14:29:06.849 DEBUG 8112 --- [nio-8080-exec-9] t.i.m.s.mapper.UserMapper.selectPage     : ==>  Preparing: SELECT COUNT(1) FROM User WHERE deleted = 0 
2020-07-21 14:29:06.850 DEBUG 8112 --- [nio-8080-exec-9] t.i.m.s.mapper.UserMapper.selectPage     : ==> Parameters: 
2020-07-21 14:29:06.856 DEBUG 8112 --- [nio-8080-exec-9] t.i.m.s.mapper.UserMapper.selectPage     : ==>  Preparing: SELECT id,name,age,sex,deleted FROM User WHERE deleted=0 LIMIT ?,? 
2020-07-21 14:29:06.856 DEBUG 8112 --- [nio-8080-exec-9] t.i.m.s.mapper.UserMapper.selectPage     : ==> Parameters: 0(Long), 5(Long)
2020-07-21 14:29:06.863 DEBUG 8112 --- [nio-8080-exec-9] t.i.m.s.mapper.UserMapper.selectPage     : <==      Total: 5

先查询了总条数, 后使用limit分页查询

多数据源整合

前期准备

创建另一个数据库email

CREATE DATABASE test1;

USE test1;

CREATE TABLE `test1`.`email`( 
	`id` INT(255) NOT NULL AUTO_INCREMENT, 
	`name` VARCHAR(255), 
	`email` VARCHAR(255), 
	`deleted` VARCHAR(255),
	PRIMARY KEY (`id`) 
) CHARSET=utf8; 


INSERT INTO `test1`.`email` (`name`, `email`) VALUES ('xh', '[email protected]'); 
INSERT INTO `test1`.`email` (`name`, `email`) VALUES ('xm', '[email protected]'); 
INSERT INTO `test1`.`email` (`name`, `email`) VALUES ('xz', '[email protected]');

使用mybatisplus自动生成mapper, 实体类

使用方法

添加依赖

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

修改application.yml

注释掉之前的datasource配置, 新添如下

spring:
    datasource:
        dynamic:
            primary: test #设置默认的数据源或者数据源组,默认值即为master
            strict: false #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候回抛出异常,不启动会使用默认数据源.
            datasource:
                test:
                    url: jdbc:mysql://172.28.149.239:3306/test?useSSL=false
                    username: root
                    password: root
                    driver-class-name: com.mysql.cj.jdbc.Driver
                test1:
                    url: jdbc:mysql://172.28.149.239:3306/test1?useSSL=false
                    username: root
                    password: root
                    driver-class-name: com.mysql.cj.jdbc.Driver

primary: 主数据源, 即你没有配置dataSource的会默认使用该数据源, 默认为master

修改SpringBootMybatisTestApplication.java,启动类, 添加email包扫描

@SpringBootApplication
@MapperScan({ "top.itkaoti.mybatis.springbootmybatistest.mapper", "top.itkaoti.mybatis.springbootmybatistest.email.mapper"})
public class SpringBootMybatisTestApplication {

    ...
}

编写EmailController

@RestController
@RequestMapping("/email")
public class EmailController {
	
	@Autowired
	private IEmailService emailService;

	@RequestMapping("/{id}")
	public Email selectById(@PathVariable("id") Integer id){
		return emailService.getById(id);
	}
}

注解配置

直接在service层添加@DS注解

@Service
@DS("test1")
public class EmailServiceImpl extends ServiceImpl<EmailMapper, Email> implements IEmailService {

}

运行, 可以看到多数据源的加载


2020-07-21 17:43:24.816  INFO 19068 --- [           main] com.zaxxer.hikari.HikariDataSource       : test - Starting...
2020-07-21 17:43:25.126  INFO 19068 --- [           main] com.zaxxer.hikari.HikariDataSource       : test - Start completed.
2020-07-21 17:43:25.126  INFO 19068 --- [           main] com.zaxxer.hikari.HikariDataSource       : test1 - Starting...
2020-07-21 17:43:25.202  INFO 19068 --- [           main] com.zaxxer.hikari.HikariDataSource       : test1 - Start completed.
2020-07-21 17:43:25.203  INFO 19068 --- [           main] c.b.d.d.DynamicRoutingDataSource         : dynamic-datasource - load a datasource named [test] success
2020-07-21 17:43:25.203  INFO 19068 --- [           main] c.b.d.d.DynamicRoutingDataSource         : dynamic-datasource - load a datasource named [test1] success
2020-07-21 17:43:25.203  INFO 19068 --- [           main] c.b.d.d.DynamicRoutingDataSource         : dynamic-datasource initial loaded [2] datasource,primary datasource named [test]

请求: http://127.0.0.1:8080/email/1

{"id":1,"name":"xh","email":"[email protected]","deleted":"0"}

请求: http://127.0.0.1:8080/user/1

{"id":1,"name":"xiaodoubi","age":27,"sex":1,"deleted":0}

乐观锁插件

乐观锁实现方式：

  • 取出记录时，获取当前version
  • 更新时，带上这个version
  • 执行更新时， set version = newVersion where version = oldVersion
  • 如果version不对，就更新失败
update tbl_user set name = 'xxx' , version = 3 where id = 100 and version = 2

使用方法

配置插件

@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor() {
    return new OptimisticLockerInterceptor();
}

添加加实体类version字段

ALTER TABLE `test1`.`email` ADD COLUMN `version` INT(255) DEFAULT 0 NULL AFTER `email`;

Email.java

@Version
private int version;

public int getVersion() {
	return version;
}

public void setVersion(int version) {
	this.version = version;
}

MybatisPlusConfig.java

@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor() {
  return new OptimisticLockerInterceptor();
}

运行测试

EmailController.java

@RequestMapping("/update")
public boolean updateEmailByid(Email email){
	return emailService.saveOrUpdate(email);
}

运行请求: http://127.0.0.1:8080/email/update?id=1&name=xx&[email protected]

2020-07-22 10:29:34.872 DEBUG 10648 --- [nio-8080-exec-2] t.i.m.s.e.mapper.EmailMapper.selectById  : ==>  Preparing: SELECT id,name,email,deleted,version FROM email WHERE id=? AND deleted='0' 
2020-07-22 10:29:34.903 DEBUG 10648 --- [nio-8080-exec-2] t.i.m.s.e.mapper.EmailMapper.selectById  : ==> Parameters: 1(Integer)
2020-07-22 10:29:34.974 DEBUG 10648 --- [nio-8080-exec-2] t.i.m.s.e.mapper.EmailMapper.selectById  : <==      Total: 1
2020-07-22 10:29:35.091 DEBUG 10648 --- [nio-8080-exec-2] t.i.m.s.e.mapper.EmailMapper.updateById  : ==>  Preparing: UPDATE email SET name=?, email=?, version=? WHERE id=? AND version=? AND deleted='0' 
2020-07-22 10:29:35.091 DEBUG 10648 --- [nio-8080-exec-2] t.i.m.s.e.mapper.EmailMapper.updateById  : ==> Parameters: xx(String), [email protected](String), 1(Integer), 1(Integer), 0(Integer)
2020-07-22 10:29:35.156 DEBUG 10648 --- [nio-8080-exec-2] t.i.m.s.e.mapper.EmailMapper.updateById  : <==    Updates: 1

查询数据库 我们发现被修改的记录version字段增长了1

mybatisplustest项目地址

mybatis-plus官方教程

# mybatis
1, linux-安装mysql 上一篇
8, mybatisPlus-核心功能 下一篇
0%