mybatis plus的常規(guī)用法

上篇文章我們介紹過通過 Mybatis Plus 進(jìn)行增刪改查，如下這段代碼：

/**
 * 根據(jù)id修改
 * UPDATE user SET user_name=?, user_age=? WHERE (id = ?)
 */
@Test
public void testudpateById(){
    User user = new User();
    user.setUserAge("25");
    user.setUserName("test update");
    UpdateWrapper updateWrapper = new UpdateWrapper();
    updateWrapper.eq("id","3");
    int num = userMapper.update(user, updateWrapper);
    System.out.println("修改的記錄數(shù)為："+num);
}
     
/**
 * 查詢指定記錄
 * SELECT id,user_name,user_age FROM user WHERE (user_name = ?)
 */
@Test
public void testSelectWrapper(){
    QueryWrapper wrapper = new QueryWrapper();
    wrapper.eq("user_name","IT可樂");
    List< User > users = userMapper.selectList(wrapper);
    users.forEach(x- > System.out.println(x.getId()+"-"+x.getUserName()+"-"+x.getUserAge()));
}

上面兩個(gè)方法分別是根據(jù)id修改表記錄，和根據(jù)user_name查詢記錄。構(gòu)造的條件使用了 UpdateWrapper 和 QueryWrapper ，那么這是什么呢？其實(shí) mybatis plus 通過條件構(gòu)造器可以組成復(fù)雜的SQL語句。本篇博客我們將詳細(xì)介紹。

1、Wrapper

Mybatis Plus 提供的幾種條件構(gòu)造器，關(guān)系如下：

我們主要通過 QueryWrapper 和 UpdateWrapper 進(jìn)行條件構(gòu)造，這兩個(gè)和 LambdaQueryWrapper、LambdaUpdateWrapper 差不多是等價(jià)的，只不過后者采用了 JDK1.8 提供的lambda 語法，使用起來更簡潔。

2、語法詳情總結(jié)

關(guān)于條件構(gòu)造器的各個(gè)用法介紹，可以參考官網(wǎng)：https://mp.baomidou.com/guide/wrapper.html#abstractwrapper

這里我們做一下總結(jié)：

3、語法詳情演示

對于上表出現(xiàn)的每個(gè)語法，這里通過代碼展示出來。

更多可以參考地址：https://github.com/YSOcean/mybatisplusstudy.git

package com.ys.mybatisplusstudy;
 
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ys.mybatisplusstudy.entry.User;
import com.ys.mybatisplusstudy.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
 
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
@SpringBootTest
public class WrapperTest {
    @Autowired
    private UserMapper userMapper;
 
 
    /**
     * 新增一條記錄
     */
    @Test
    public void testInsert(){
        User user = new User();
        user.setId(4L);
        user.setUserName("test insert");
        user.setUserAge("1");
        int insert = userMapper.insert(user);
        System.out.println("影響記錄數(shù)："+insert);
    }
 
    /**
     * allEq 全部等于
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name = ? AND id = ?)
     */
    @Test
    public void testAllEq(){
        QueryWrapper queryWrapper = new QueryWrapper();
        Map map = new HashMap<  >();
        map.put("id","3");
        map.put("user_name","IT可樂");
        queryWrapper.allEq(map);
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * eq 等于
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (id = ?)
     */
    @Test
    public void testEq(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("id","3");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
    /**
     * ne 不等于
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (id <  > ?)
     */
    @Test
    public void testNe(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.ne("id","3");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
    /**
     * gt 大于
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age > ?)
     */
    @Test
    public void testGt(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.gt("user_age","18");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
    /**
     * ge 大于等于
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age >= ?)
     */
    @Test
    public void testGe(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.ge("user_age","18");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
    /**
     * lt 小于
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age < ?)
     */
    @Test
    public void testLt(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.lt("user_age","18");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
    /**
     * le 小于等于
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age <= ?)
     */
    @Test
    public void testLe(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.le("user_age","18");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
    /**
     * between 值1和值2之間,兩邊臨界值都包含
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age BETWEEN ? AND ?)
     */
    @Test
    public void testBetween(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.between("user_age","18","25");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * notBetween 不在值1和值2之間，兩邊臨界值都包含
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age NOT BETWEEN ? AND ?)
     */
    @Test
    public void testNoBetween(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.notBetween("user_age","18","25");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
    /**
     * like 模糊查詢，會在參數(shù)左右兩邊加上 %
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name LIKE ?)
     */
    @Test
    public void testLike(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.like("user_name","可樂");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * notLike NOT LIKE ‘%parameter%’
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name NOT LIKE ?)
     */
    @Test
    public void testNotLike(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.notLike("user_name","可樂");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
    /**
     * likeLeft LIKE ‘%parameter’
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name LIKE '%parameter')
     */
    @Test
    public void testLikeLeft(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.likeLeft("user_name","可樂");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * likeRight LIKE ‘parameter%’
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name LIKE 'parameter%')
     */
    @Test
    public void testLikeRight(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.likeRight("user_name","可樂");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
    /**
     * isNull 判斷字段為null
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name IS NULL)
     */
    @Test
    public void testIsNull(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.isNull("user_name");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * isNotNull 判斷字段不為null
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name IS NOT NULL)
     */
    @Test
    public void testIsNotNull(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.isNotNull("user_name");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
 
    /**
     * in 范圍定值查詢
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age IN (?,?,?))
     */
    @Test
    public void testIn(){
        QueryWrapper queryWrapper = new QueryWrapper();
        List< Integer > queryList = new ArrayList<  >();
        queryList.add(18);
        queryList.add(1);
        queryList.add(25);
        queryWrapper.in("user_age",queryList);
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * notIn
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age IN (?,?,?))
     */
    @Test
    public void testNotIn(){
        QueryWrapper queryWrapper = new QueryWrapper();
        List< Integer > queryList = new ArrayList<  >();
        queryList.add(18);
        queryList.add(1);
        queryList.add(25);
        queryWrapper.notIn("user_age",queryList);
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * inSql
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (id IN (select id from user))
     */
    @Test
    public void testInSql(){
        QueryWrapper queryWrapper = new QueryWrapper();
        //查詢所有數(shù)據(jù)
        queryWrapper.inSql("id","select id from user");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * notInSql
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (id NOT IN (select id from user where id > 2))
     */
    @Test
    public void testNotInSql(){
        QueryWrapper queryWrapper = new QueryWrapper();
        //查詢所有數(shù)據(jù)
        queryWrapper.notInSql("id","select id from user where id > 2");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
 
    /**
     * groupBy 分組
     * 下面SQL有個(gè)問題，在MySQL8.0版本中，是可以執(zhí)行下面SQL語句的，select user_name并沒有出現(xiàn)在group by 語句中
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user GROUP BY id,user_age
     */
    @Test
    public void testGroupBy(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.groupBy("id","user_age");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
 
    /**
     * orderByAsc 升序
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user ORDER BY id ASC,user_age ASC
     */
    @Test
    public void testOrderByAsc(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.orderByAsc("id","user_age");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * orderByDesc 降序
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user ORDER BY id DESC,user_age DESC
     */
    @Test
    public void testOrderByDesc(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.orderByDesc("id","user_age");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * orderBy 指定順序排序
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user ORDER BY id ASC,user_age ASC
     */
    @Test
    public void testOrderBy(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.orderBy(true,true,"id","user_age");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
 
    /**
     * having
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user GROUP BY id,user_age HAVING sum(user_age) >?
     */
    @Test
    public void testHaving(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.groupBy("id","user_age");
        queryWrapper.having("sum(user_age) >{0}","25");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * having
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (id = ? OR user_age = ?)
     */
    @Test
    public void testOr(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("id",1);
        queryWrapper.or();
        queryWrapper.eq("user_age",25);
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * and
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE ((id = ? AND user_age <  > ?))
     */
    @Test
    public void testAnd(){
        QueryWrapper< User > queryWrapper = new QueryWrapper<  >();
        queryWrapper.and(i- >i.eq("id",1).ne("user_age",18));
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
 
    /**
     * nested
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE ((id = ? AND user_age <  > ?))
     */
    @Test
    public void testNested(){
        QueryWrapper< User > queryWrapper = new QueryWrapper<  >();
        queryWrapper.nested(i- >i.eq("id",1).ne("user_age",18));
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * apply
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age >?)
     */
    @Test
    public void testApplyd(){
        QueryWrapper< User > queryWrapper = new QueryWrapper<  >();
        queryWrapper.apply("user_age >{0}","25 or 1=1");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * last
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user limit 1
     */
    @Test
    public void testLast(){
        QueryWrapper< User > queryWrapper = new QueryWrapper<  >();
        queryWrapper.last("limit 1 ");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
 
    /**
     * exists
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (EXISTS (select id from user where user_age = 1))
     */
    @Test
    public void testExists(){
        QueryWrapper< User > queryWrapper = new QueryWrapper<  >();
        queryWrapper.exists("select id from user where user_age = 1");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
 
 
    /**
     * notExists
     * 實(shí)例SQL：SELECT id,user_name,user_age FROM user WHERE (EXISTS (select id from user where user_age = 1))
     */
    @Test
    public void testNotExists(){
        QueryWrapper< User > queryWrapper = new QueryWrapper<  >();
        queryWrapper.notExists("select id from user where user_age = 1");
        List< User > list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
}

4、LambdaQueryWrapper和LambdaUpdateWrapper（推薦）

LambdaQueryWrapper 和 LambdaUpdateWrapper 這是相對于 QueryWrapper 及 UpdateWrapper 的 Lmbda 語法實(shí)現(xiàn)方式。

分別通過如下兩種方式獲?。?/p>

//兩種方式        
LambdaQueryWrapper queryLambda = new QueryWrapper().lambda();
LambdaQueryWrapper lambdaQueryWrapper = new LambdaQueryWrapper<  >();

//兩種方式
LambdaUpdateWrapper updateLambda = new UpdateWrapper().lambda();
LambdaUpdateWrapper lambdaUpdateWrapper = new LambdaUpdateWrapper();

注意：獲取LambdaQueryWrapper 和 LambdaUpdateWrapper 對象時(shí)，為了使用lambda語法，要使用泛型。

下面我演示幾個(gè)實(shí)例：

/**
 * LambdaQueryWrapper
 * SQL實(shí)例：SELECT id,user_name,user_age FROM user WHERE (id = ? AND user_age <  > ?)
 */
@Test
public void testLambdaQueryWrapper(){
    LambdaQueryWrapper< User > queryLambda = new LambdaQueryWrapper<  >();
    queryLambda.eq(User::getId,"1").ne(User::getUserAge,25);
    List< User > users = userMapper.selectList(queryLambda);
    System.out.println(users);
 
}
 
/**
 * LambdaQueryWrapper
 * SQL實(shí)例：UPDATE user SET user_name=? WHERE (user_name = ?)
 */
@Test
public void testLambdaUpdateWrapper(){
    User user = new User();
    user.setUserName("LambdaUpdateWrapper");
    LambdaUpdateWrapper< User > userLambdaUpdateWrapper = new LambdaUpdateWrapper<  >();
    userLambdaUpdateWrapper.eq(User::getUserName,"IT可樂");
    userMapper.update(user,userLambdaUpdateWrapper);
 
}

5、總結(jié)

對于mybatis plus 中的四種條件構(gòu)造器，我們就到此結(jié)束了，大家可以按照我的實(shí)例敲一遍代碼，基本上就沒啥問題了。

有沒有發(fā)現(xiàn)使用 Lamba 語法很爽，語法簡潔，另外有個(gè)優(yōu)點(diǎn)是，使用QueryWrapper或者UpdateWrapper時(shí)，對于條件的某個(gè)列，我們是寫的字符串配置，比如 QueryWrapper.eq("id",1);這里的id是數(shù)據(jù)庫表的列名，很有可能我們會寫錯(cuò)，但是通過lambda 的方式，LambdaQueryWrapper.eq(User::getId,1)，這樣就不會有寫錯(cuò)的可能了。所以推薦大家使用Lambda 的方式。

至此，mybatis plus 的常規(guī)用法就全部介紹結(jié)束了，當(dāng)然，事情還遠(yuǎn)沒有結(jié)束，為了讓大家用的更爽，后續(xù)將給大家介紹一些高階玩法。