【MyBatisPlus】-条件构造器、自定义SQL以及Service接口

条件构造器

MyBatisPlus支持各种复杂的where条件，可以满足日常开发的所有需求

基于QuerWrapper的查询

    @Test
    void testQueryWrapper(){
        //构造查询条件
        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
    void testUpdateByQueryWrapper(){

        User user = new User();
        user.setBalance(2000);

        //更新条件
        QueryWrapper<User> wrapper = new QueryWrapper<User>().eq("username","jack");

        userMapper.update(user,wrapper);
    }

基于UpdateWrapper的更新

    @Test
    void testUpdateQueryWrapper(){
        List<Long> ids = List.of(1L,2L,4L);
        UpdateWrapper<User> wrapper = new UpdateWrapper<User>()
                .setSql("balance = balance - 200")
                .in("id",ids);
        userMapper.update(null,wrapper);

    }

LambdaWrapper

为了避免硬编码

@Test
void testLambdaQueryWrapper(){
    //构造查询条件
    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);
}

自定义SQL

我们可以利用MyBatisPlus的Wrapper来构建复杂的Where条件，然后自己定义SQL语句中剩下的部分。

1）基于Wrapper构建where条件

    @Test
    void testCustomUpdate(){
        //更新条件
        List<Long> ids = List.of(1L,2L,4L);
        int amount = 200;
        QueryWrapper<User> wrapper = new QueryWrapper<User>().in("id",ids);
        //自定义方法
        userMapper.updateBalanceByIds(wrapper,amount);
    }

2）在mapper方法参数中用Param注解声明wrapper变量名称，必须是ew

void updateBalanceByIds(@Param(Constants.WRAPPER) QueryWrapper<User> wrapper,@Param("amount") int amount);
}

3)自定义SQL,并使用Wrapper条件

    <update id="updateBalanceByIds">
        update user set balance = balance - #{amount} ${ew.customSqlSegment}
    </update>

Service接口

先在自定义的service接口继承

再在实现类里继承

 

IService的Lambda查询

按mybatis的写法

Lambda查询

    @Override
    public List<User> queryUsers(String name, Integer status, Integer minBalance, Integer maxBalance) {


        return  lambdaQuery()
                .like(name != null, User::getUsername, name)
                .eq(status != null, User::getStatus, status)
                .ge(minBalance != null, User::getBalance, minBalance)
                .le(maxBalance != null, User::getBalance, maxBalance)
                .list();
    }

IService的Lambda更新

@Override
    @Transactional
    public void deductBalance(Long id, Integer money) {
        //1.查询用户
        User user = getById(id);
        //校验用户状态
        if(user == null || user.getStatus() ==2){
            throw new RuntimeException("用户状态异常");
        }
        //余额是否充足
        if(user.getBalance() < money){
            throw new RuntimeException("用户余额不足");
        }
        //扣减余额
        int remainBalance = user.getBalance() - money;
        lambdaUpdate()
                .set(User::getBalance,remainBalance)
                .set(remainBalance==0,User::getStatus,2)
                .eq(User::getId,id)
                .eq(User::getBalance,user.getBalance()) //乐观锁
                .update();

    }

IService批量新增

批处理方案：

• 普通for循环逐条插入速度极差，不推荐
• MP的批量新增，基于预编译的批处理，性能不错
• 配置jdbc参数，开rewriteBatchedStatements ，性能最好