【MyBatisPlus】快速入门

1. 简单使用

public interface UserMapper extends BaseMapper<User> {

}

class UserMapperTest {
    @Autowired
    private UserMapper userMapper;

    @Test
    void testInsert() {
        User user = new User();
        user.setUsername("xiaowang");
        user.setPassword("123");
        user.setPhone("18688990011");
        user.setBalance(200);
        user.setInfo("{\"age\": 24, \"intro\": \"英文老师\", \"gender\": \"female\"}");
        user.setCreateTime(LocalDateTime.now());
        user.setUpdateTime(LocalDateTime.now());
        userMapper.insert(user);
    }

    @Test
    void testSelectById() {
        User user = userMapper.selectById(5L);
        System.out.println("user = " + user);
    }


    @Test
    void testQueryByIds() {
        List<User> users = userMapper.selectBatchIds(Arrays.asList(1L, 2L, 3L, 4L));
        users.forEach(System.out::println);
    }

    @Test
    void testUpdateById() {
        User user = new User();
        user.setId(5L);
        user.setBalance(20000);
        userMapper.updateById(user);
    }

    @Test
    void testDeleteUser() {
        userMapper.deleteById(5L);
    }
}

2. 条件构造器 —— 针对于复杂查询

@Test
public void testQueryWrapper(){
    // select id, username, info, balance from user where username like %o% and balance = 1000;
    QueryWrapper<User> wrapper = new QueryWrapper<User>()
            .select("id", "username", "info", "balance")
            .like("username", "o")
            .ge("balance", 1000);
    List<User> users = userMapper.selectList(wrapper);
    users.forEach(System.out::println);
}

@Test
public void testUpdateByQueryWrapper(){
    // update user set balance = 2000 where username = "jack";
    User user = new User();
    user.setBalance(2000);
    QueryWrapper<User> wrapper = new QueryWrapper<User>().eq("username", "jack");
    userMapper.update(user, wrapper);
}

@Test
public void testUpdateWrapper(){
    // update user set balance = balance - 200 where id in (1,2,4);
    List<Long> ids = Arrays.asList(1L, 2L, 4L);
    UpdateWrapper<User> wrapper = new UpdateWrapper<User>()
                        .setSql("balance = balance - 200")
                        .in("id", ids);
    userMapper.update(null, wrapper);
}

lambdaQueryWrapper解决上述testQueryWrapper硬编码问题

@Test
public void testLambdaQueryWrapper(){
	// select id, username, info, balance from user where username like %o% and balance = 1000;
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<User>()
            .select(User::getId, User::getUsername, User::getInfo, User::getBalance)
            .like(User::getUsername, "o")
            .ge(User::getBalance, 1000);
    List<User> users = userMapper.selectList(wrapper);
    users.forEach(System.out::println);
}

条件构造器的用法：

• QueryWrapper和LambdaQueryWrapper通常用来构建select、delete、update的where条件部分
• UpdateWrapper和LambdaUpdateWrapper通常只有在set语句比较特殊才使用
• 尽量使用LambdaQueryWrapper和LambdaUpdateWrapper，避免硬编码

3. 自定义SQL

4. IService

4.1 基本接口方法

IService底层操作数据库，用的还是mapper

4.1.1 新增

4.1.2 删除

4.1.3 修改

4.1.4 查找

4.2 开发基础业务接口

IUserService.java

public interface IUserService extends IService<User> {
}

UserServiceImpl.java

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
}

@SpringBootTest
public class IUserServiceTest {
    @Autowired
    private IUserService userService;

    @Test
    void testSaveUser() {
        User user = new User();
        user.setUsername("LiLei");
        user.setPassword("123");
        user.setPhone("18688990011");
        user.setBalance(200);
        user.setInfo("{\"age\": 24, \"intro\": \"英文老师\", \"gender\": \"female\"}");
        user.setCreateTime(LocalDateTime.now());
        user.setUpdateTime(LocalDateTime.now());
        userService.save(user);
    }

    @Test
    void testQueryByIds() {
        List<User> users = userService.listByIds(Arrays.asList(1L, 2L, 3L, 4L));
        users.forEach(System.out::println);
    }
}

@Api(tags = "用户管理接口")
@RequestMapping("/users")
@RestController
@RequiredArgsConstructor
public class UserController {
    // final配合@RequiredArgsConstructor，可以通过构造函数注入
    private final IUserService userService;

    @ApiOperation("用户新增接口")
    @PostMapping
    public void saveUser(@RequestBody UserFormDTO userDTO){
        // 使用BeanUtil，将UserFormDTO对象的属性拷贝到User.class
        User user = BeanUtil.copyProperties(userDTO, User.class);
        userService.save(user);
    }

    @ApiOperation("删除用户接口")
    @DeleteMapping("{id}")
    // @PathVariable用于RESTFUL风格，从路径中获取参数
    public void deleteUserById(@ApiParam("用户id") @PathVariable("id") Long id){
        userService.removeById(id);
    }

    @ApiOperation("根据id查询用户接口")
    @GetMapping("{id}")
    public UserVO queryUserById(@ApiParam("用户id") @PathVariable("id") Long id){
        User user = userService.getById(id);
        return BeanUtil.copyProperties(user, UserVO.class);
    }

    @ApiOperation("根据id批量查询用户接口")
    @GetMapping
    // @RequestParam用于从路径中获取参数，比如ids=1,2,4
    public List<UserVO> queryUserByIds(@ApiParam("用户id集合") @RequestParam("ids") List<Long> ids){
        List<User> users = userService.listByIds(ids);
        // List<PO> -> List<VO>
        return BeanUtil.copyToList(users, UserVO.class);
    }
}

4.3 开发复杂业务接口

Controller

@Api(tags = "用户管理接口")
@RequestMapping("/users")
@RestController
@RequiredArgsConstructor
public class UserController {
	@ApiOperation("扣减用户余额接口")
    @PutMapping("/{id}/deduction/{money}")
    public void deductMoneyById(
            @ApiParam("用户id") @PathVariable("id") Long id,
            @ApiParam("用户money") @PathVariable("money") Integer money){
        userService.deductBalance(id, money);
    }
}

service

@Service
@RequiredArgsConstructor
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {

    private final UserMapper userMapper;

    @Override
    public void deductBalance(Long id, Long money) {
        User user = getById(id);
        if(user == null || user.getStatus() == 2){
            throw new RuntimeException("用户状态异常！");
        }
        if(user.getBalance() < money){
            throw new RuntimeException("用户余额不足！");
        }
        userMapper.deductBalance(id, money);
    }
}

mapper

public interface UserMapper extends BaseMapper<User> {
    void updateBalanceByIds(@Param(Constants.WRAPPER) UpdateWrapper<User> wrapper, @Param("amount") int amount);

    @Update("UPDATE user SET balance = balance - #{money} WHERE id = #{id}")
    void deductBalance(@Param("id") Long id, @Param("money") Long money);

}

4.4 Lambda方法

controller

@Api(tags = "用户管理接口")
@RequestMapping("/users")
@RestController
@RequiredArgsConstructor
public class UserController {
    // final配合@RequiredArgsConstructor，可以通过构造函数注入
    private final IUserService userService;

    @ApiOperation("根据复杂条件查询用户接口")
    @GetMapping("/list")
    public List<UserVO> queryUsers(UserQuery query){
        List<User> users = userService.queryUsers(query.getName(), query.getStatus(), query.getMinBalance(), query.getMaxBalance());
        return BeanUtil.copyToList(users, UserVO.class);
    }
}

service

public interface IUserService extends IService<User> {
    List<User> queryUsers(String name, Integer status, Integer minBalance, Integer maxBalance);
}

@Service
@RequiredArgsConstructor
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
    @Override
    public List<User> queryUsers(String name, Integer status, Integer minBalance, Integer maxBalance) {
    	// select * from user where name = ? ans status = ? and balance >= minBalance and balance <= maxBalance;
        return lambdaQuery()
                .like(name != null, User::getUsername, name)
                .eq(status != null, User::getStatus, status)
                .gt(minBalance != null, User::getBalance, minBalance)
                .lt(maxBalance != null, User::getBalance, maxBalance)
                .list();
    }
}

4.5 批量新增

5. 代码生成

MyBatisPlus使用的过程如下：

这些代码都比较固定，只是类名不同，我们可以用插件自动生成这些比较固定的代码

配置数据库信息

配置代码生成信息

6. 分页功能

6.1 分页插件基本使用

MyBatisPlus内置的分页插件如下：

首先，需要在配置类中注册MyBatisPlus的核心插件，同时添加分页插件

@Configuration
public class MybatisConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        // 1 初始化核心插件
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 2 添加分页插件
        PaginationInnerInterceptor pageInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
        pageInterceptor.setMaxLimit(1000L); // 设置分页上限
        interceptor.addInnerInterceptor(pageInterceptor);
        return interceptor;
    }
}

分页对象

引入依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-extension</artifactId>
    <version>3.5.3.1</version>
</dependency>

@Test
public void testPageQuery(){
    // 创建Page对象，设置分页参数
    int pageNo = 2, pageSize = 2;
    Page<User> page = Page.of(pageNo, pageSize);
    // 排序条件
    page.addOrder(new OrderItem("balance", true));
    page.addOrder(new OrderItem("id", true));
    // 分页查询
    Page<User> p = userService.page(page);
    long total = p.getTotal();  // 数据总条数
    long pages = p.getPages();  // 总页数
    List<User> users = p.getRecords();  // 第pageNo页数据，pageSize条
    users.forEach(System.out::println);
}

6.1 通用分页实体

统一的分页查询条件，包括页码、页大小、排序方式、是否升序

@Data
@ApiModel(description = "分页查询实体")
public class PageQuery {
    private Integer pageNo;
    private Integer pageSize;
    private String sortBy;
    private Boolean isAsc;
}

用户条件查询实体

@Data
@ApiModel(description = "用户条件查询实体")
public class UserQuery extends PageQuery{
    @ApiModelProperty("用户名关键字")
    private String name;
    @ApiModelProperty("用户状态：1-正常，2-冻结")
    private Integer status;
    @ApiModelProperty("余额最小值")
    private Integer minBalance;
    @ApiModelProperty("余额最大值")
    private Integer maxBalance;
}

返回实体PageDTO

@Data
@NoArgsConstructor
@AllArgsConstructor
public class PageDTO<V> {
    @ApiModelProperty("总条数")
    private Long total;
    @ApiModelProperty("总页数")
    private Long pages;
    @ApiModelProperty("结果集合")
    private List<V> list;
}

controller

@ApiOperation("根据复杂条件分页查询用户接口")
@GetMapping("/page")
public PageDTO<UserVO> queryUsersPage(UserQuery query){
    return userService.queryUsersPage(query);
}

service

@Service
@RequiredArgsConstructor
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
    private final UserMapper userMapper;

    @Override
    public PageDTO<UserVO> queryUsersPage(UserQuery query) {
        String name = query.getName();
        Integer status = query.getStatus();
        // 分页参数
        Page<User> page = Page.of(query.getPageNo(), query.getPageSize());
        // 排序条件
        if(StrUtil.isNotBlank(query.getSortBy())){
            page.addOrder(new OrderItem(query.getSortBy(), query.getIsAsc()));
        }else{
            // 排序条件为空，按更新时间排序
            page.addOrder(new OrderItem("update_time", false));
        }

        Page<User> p = lambdaQuery()
                .like(name != null, User::getUsername, name)
                .eq(status != null, User::getStatus, status)
                .page(page);

        PageDTO<UserVO> dto = new PageDTO<>();
        dto.setTotal(p.getTotal());
        dto.setPages(p.getPages());
        List<User> records = p.getRecords();
        
        // 查询结果为空，PageDTO的结果设置为空集合
        if(CollUtil.isEmpty(records)){
            dto.setList(Collections.emptyList());
            return dto;
        }
        // 工具包直接将List<User>转为List<UserVO>
        dto.setList(BeanUtil.copyToList(records, UserVO.class));
        return dto;
    }
}