Mybatis进阶详细用法

目录

条件构造器

案例

自定义SQL

案例

Service接口

案例

综合案例

LambdaQuery()

案例

LambdaUpate()

案例

批量导入的性能问题

---

---

条件构造器

案例

    @Test
    void testQueryMapper() {
        // 创建 QueryWrapper 实例
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("id," + "username," + "info," + "balance ");
        // 添加条件：用户名包含 "o"，余额大于等于 1000
        queryWrapper.like("username", "o").ge("balance", 1000);
        // 使用 MyBatis-Plus 的方法执行查询
        List<User> users = userMapper.selectList(queryWrapper);
       users.forEach(System.out::println);
    }

    @Test
    void testUpdateWapper() {
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();
        // 使用实体的函数式方式指定要更新的字段
        wrapper.set("balance", 5000).eq("username", "jack");
        // 执行更新操作
        int updated = userMapper.update(null, wrapper);
        if (updated == 1) {
            // 更新成功
            System.out.println("更新成功");
        } else {
            // 更新失败或无记录被更新
            System.out.println("更新失败");
        }
    }

    @Test
    void testUpdateWapper1(){
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.in("id", Arrays.asList(1, 2, 4));
        updateWrapper.setSql("balance = balance-200");
        userMapper.update(null,updateWrapper);
    }

自定义SQL

案例

Service接口

案例

  @Test
    void testSaveUser() {
        User user = new User();
//        user.setId(5L);
        user.setUsername("马超");
        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 testQuery() {
        List<User> users = userService.listByIds(Arrays.asList(1L, 2l, 3l));
        users.forEach(System.out::println);
    }

综合案例

@RequestMapping("/users")
@RestController
@Api(tags = "用户管理接口")
//配合final 完成构造函数注入
@RequiredArgsConstructor
public class UserController {

    private final IUserService iUserService;

    @ApiOperation("新增用户管理接口")
    @PostMapping
    public void saveUser(@RequestBody UserFormDTO userFormDTO) {
        User user = new User();
//        1.把Dto拷贝到Po
        BeanUtil.copyProperties(userFormDTO, user);
        iUserService.save(user);
    }

    @ApiOperation("删除用户管理接口")
    @DeleteMapping("{id}")
    public void deleteUserById(@ApiParam("用户id") @PathVariable("id") long id) {
        iUserService.removeById(id);
    }

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

    @ApiOperation("根据id批量查询用户接口")
    @GetMapping
    public List<UserVO> queryUserByIds(@ApiParam("用户集合ids集合") @RequestParam("ids") List<Long> ids) {
//       根据ids集合查询用户集合
        List<User> users = iUserService.listByIds(ids);
//        拷贝
        return BeanUtil.copyToList(users, UserVO.class);
    }


    @ApiOperation("根据id扣减用户余额")
    @PutMapping("/{id}/deduction/{money}")
    public void deductMoneyById(@ApiParam("用户id") @PathVariable("id") Long id, @ApiParam("扣减的金额") @PathVariable("money") Integer money) {
        iUserService.deductMoneyById(id, money);
    }

}

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

    private final UserMapper userMapper;

    @Override
    public void deductMoneyById(Long id, Integer money) {
//        查询用用户余额是否支持扣减
//        已经注入mapper了
//        User user1 = baseMapper.selectById(id);
        User user = this.getById(id);
        if (user == null || user.getStatus() == 2) {
            throw new RuntimeException("用户状态异常");
        }

//        校验用户余额是否支持扣减
        if (user.getBalance() - money < 0) {
            throw new RuntimeException("用户余额不足");
        }
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();
        wrapper.eq("id",id);
//        完成扣减
        baseMapper.deductMoneyById(wrapper,money);
    }
}

 void deductMoneyById(@Param("ew") UpdateWrapper<User> eq, @Param("money")Integer money);

  <update id="deductMoneyById">
        UPDATE user SET balance = balance-#{money}  ${ew.customSqlSegment}
    </update>

LambdaQuery()

案例

    @ApiOperation("根据复杂条件查询用户接口")
    @GetMapping("/list")
    public List<UserVO> queryUsers( UserQuery query ) {
//       根据ids集合查询用户集合
        List<User> users = iUserService.queryUsers(query);
//        拷贝
        return BeanUtil.copyToList(users, UserVO.class);
    }

    @Override
    public List<User> queryUsers(UserQuery query) {
        List<User> list = lambdaQuery()
                .like(query.getName() != null, User::getUsername, query.getName())
                .eq(query.getStatus() != null, User::getStatus, query.getStatus())
                .gt(query.getMinBalance() != null, User::getBalance, query.getMinBalance())
                .lt(query.getMaxBalance() != null, User::getBalance, query.getMaxBalance())
                .list();

        // 执行查询并返回结果
        return list;
    }

LambdaUpate()

案例

批量导入的性能问题

考虑网络IO,虚拟机内存,sql语句的数量