MP条件构造器练习

最新推荐文章于 2023-12-26 20:46:56 发布

博客新生儿~

最新推荐文章于 2023-12-26 20:46:56 发布

阅读量1.9k

点赞数 22

分类专栏： SpringBoot学习笔记文章标签： java spring boot

本文链接：https://blog.csdn.net/qfbeqhbviq/article/details/119054323

版权

SpringBoot学习笔记专栏收录该内容

13 篇文章 1 订阅

订阅专栏

1.allEq

1.1 boolean null2IsNull

allEq(Map<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)

个别参数说明:
params : key为数据库字段名,value为字段值
null2IsNull : 为true则在map的value为null时调用 isNull 方法,为false时则忽略value为null的

1.1.1 例一：

某个条件的值为null，allEq方法添加第二个参数为默认true时，查出来的sql条件为 IS NULL

@Test public void test1(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        Map<String,Object> map=new HashMap<>();
        map.put("name", "黑熊精");
        map.put("sex", null);
        queryWrapper.allEq(map);
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

查询结果为空，SQL:SELECT id,name,age,sex FROM demo_user WHERE (sex IS NULL AND name = ?)

1.1.2 例二：

某个条件的值为null，allEq方法添加第二个参数为false时，查出来的sql忽略值为null的条件

 @Test public void test2(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        Map<String,Object> map=new HashMap<>();
        map.put("name", "黑熊精");
        map.put("sex", null);
        queryWrapper.allEq(map,false);
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

查询结果为：User(id=1, name=黑熊精, age=3000, sex=男)
SQL：SELECT id,name,age,sex FROM demo_user WHERE (name = ?)

1.2 BiPredicate<R, V> filter

allEq(filter, Map<R, V> params)
allEq(BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)

个别参数说明:
filter : 过滤函数,是否允许字段传入比对条件中
params 与 null2IsNull : 同上

1.2.1 例一：

allEq方法中添加过滤条件参数，只保留符合条件的参数作为条件

@Test public void test3(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        Map<String,Object> map=new HashMap<>();
        map.put("name", "黑熊精");
        map.put("sex", null);
        queryWrapper.allEq((k, v) ->k.equals("name"), map);
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

仅保留name作为查询条件！
查询结果为：User(id=1, name=黑熊精, age=3000, sex=男)
SQL：SELECT id,name,age,sex FROM demo_user WHERE (name = ?)

1.2.1 例二：

添加第三个布尔条件为false，忽略值为null的条件

@Test public void test4(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        Map<String,Object> map=new HashMap<>();
        map.put("name", "黑熊精");
        map.put("sex", null);
        queryWrapper.allEq((k, v) ->!k.isEmpty(), map,false);
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

查询结果：User(id=1, name=黑熊精, age=3000, sex=男)
SQL：SELECT id,name,age,sex FROM demo_user WHERE (name = ?)

2.eq

eq(R column, Object val)
eq(boolean condition, R column, Object val)

等于 =
例: eq(“name”, “老王”)—>name = ‘老王’

2.1 例一：

@Test public void test5(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.eq("name", "金角大王");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

查询结果：User(id=3, name=金角大王, age=3000, sex=男)
SQL：SELECT id,name,age,sex FROM demo_user WHERE (name = ?)

2.2 例二：

第一个boolean值为判断条件，不符合条件的不加入sql语句

@Test
    public void  test6(){
        String name="金角大王";
        Integer age=null;
        QueryWrapper<User> queryWrapper=new QueryWrapper();
        boolean nameFlag= StringUtils.hasLength(name);
        boolean ageFlag=age!=null;
        queryWrapper.eq(nameFlag, "name", name)
                .eq(ageFlag, "age", age);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

查询结果：User(id=3, name=金角大王, age=3000, sex=男)
SQl：SELECT id,name,age,sex FROM demo_user WHERE (name = ?)

3.ne

ne(R column, Object val)
ne(boolean condition, R column, Object val)

不等于 <>
例: ne(“name”, “老王”)—>name <> ‘老王’

 @Test
    public void  test7(){
        QueryWrapper<User> queryWrapper=new QueryWrapper();
        queryWrapper.ne("sex", "男");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

查询结果：所有女生的信息
SQL：SELECT id,name,age,sex FROM demo_user WHERE (sex <> ?)
Parameters: 男(String)

4.gt、ge、le、lt

4.1 gt

gt(R column, Object val)
gt(boolean condition, R column, Object val)

大于 >
例: gt(“age”, 18)—>age > 18

@Test
    public void  test8(){
        QueryWrapper<User> queryWrapper=new QueryWrapper();
        queryWrapper.gt("age",19);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL：SELECT id,name,age,sex FROM demo_user WHERE (age > ?)

4.2 ge

ge(R column, Object val)
ge(boolean condition, R column, Object val)

大于等于 >=
例: ge(“age”, 18)—>age >= 18

@Test
    public void  test8(){
        QueryWrapper<User> queryWrapper=new QueryWrapper();
        queryWrapper.ge("age",19);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL；SELECT id,name,age,sex FROM demo_user WHERE (age >= ?)

4.3 lt

lt(R column, Object val)
lt(boolean condition, R column, Object val)

小于 <
例: lt(“age”, 18)—>age < 18

@Test
    public void  test8(){
        QueryWrapper<User> queryWrapper=new QueryWrapper();
        queryWrapper.lt("age",16);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL：SELECT id,name,age,sex FROM demo_user WHERE (age < ?)

4.4 le

le(R column, Object val)
le(boolean condition, R column, Object val)

小于等于 <=
例: le(“age”, 18)—>age <= 18

@Test
    public void  test8(){
        QueryWrapper<User> queryWrapper=new QueryWrapper();
        queryWrapper.le("age",16);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL：SELECT id,name,age,sex FROM demo_user WHERE (age <=?)

5 between、notBetween

5.1 between

between(R column, Object val1, Object val2)
between(boolean condition, R column, Object val1, Object val2)

BETWEEN 值1 AND 值2
例: between(“age”, 18, 30)—>age between 18 and 30

@Test
    public void  test9(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.between("age", 16, 17);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL:SELECT id,name,age,sex FROM demo_user WHERE (age BETWEEN ? AND ?)

5.1 notBetween

notBetween(R column, Object val1, Object val2)
notBetween(boolean condition, R column, Object val1, Object val2)

NOT BETWEEN 值1 AND 值2
例: notBetween(“age”, 18, 30)—>age not between 18 and 30

@Test
    public void  test9(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.notBetween("age",17,10000);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL:SELECT id,name,age,sex FROM demo_user WHERE (age NOT BETWEEN ? AND ?)

6 like、notLike

6.1 like

like(R column, Object val)
like(boolean condition, R column, Object val)

LIKE ‘%值%’
例: like(“name”, “王”)—>name like ‘%王%’

 @Test
    public void  test10(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.like("name", "精");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL：SELECT id,name,age,sex FROM demo_user WHERE (name LIKE ?)
Parameters: %精%(String)

6.2 notLike

notLike(R column, Object val)
notLike(boolean condition, R column, Object val)

NOT LIKE ‘%值%’
例: notLike(“name”, “王”)—>name not like ‘%王%’

@Test
    public void  test10(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.notLike("name", "精");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL:SELECT id,name,age,sex FROM demo_user WHERE (name NOT LIKE ?)
Parameters: %精%(String)

7 likeLeft、likeRight

7.1 likeLeft

likeLeft(R column, Object val)
likeLeft(boolean condition, R column, Object val)

LIKE ‘%值’
例: likeLeft(“name”, “王”)—>name like ‘%王’
left指的是%的位置，在’王’的左面，即以王字结尾的所有的名字

@Test
    public void  test11(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.likeLeft("name", "精");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL：SELECT id,name,age,sex FROM demo_user WHERE (name LIKE ‘%精’)

7.1 likeRight

likeRight(R column, Object val)
likeRight(boolean condition, R column, Object val)

LIKE ‘值%’
例: likeRight(“name”, “王”)—>name like ‘王%’

@Test
    public void  test11(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.likeRight("name", "精");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL:SELECT id,name,age,sex FROM demo_user WHERE (name LIKE ‘精%’)

8 isNull、isNotNoll

8.1 isNull

为空值的表字段作为条件

isNull(R column)
isNull(boolean condition, R column)

字段 IS NULL
例: isNull(“name”)—>name is null

@Test public void test12(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        Map<String,Object> map=new HashMap<>();
        queryWrapper.isNull("id");
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

SQL:SELECT id,name,age,sex FROM demo_user WHERE (id IS NULL)

8.1 isNotNull

非空值的表字段作为条件

isNotNull(R column)
isNotNull(boolean condition, R column)

字段 IS NOT NULL
例: isNotNull(“name”)—>name is not null

@Test public void test12(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        Map<String,Object> map=new HashMap<>();
        queryWrapper.isNotNull("age");
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

SQL：SELECT id,name,age,sex FROM demo_user WHERE (age IS NOT NULL)

9. in、notIn

9.1 in

例一：

in(R column, Collection<?> value)
in(boolean condition, R column, Collection<?> value)

字段 IN (value.get(0), value.get(1), …)
例: in(“age”,{1,2,3})—>age in (1,2,3)

@Test public void test13(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        Integer [] ids=new Integer[]{1,3,4,5};
        List<Integer> idsList = Arrays.asList(ids);//数组转集合
        queryWrapper.in(!idsList.isEmpty(), "id",idsList);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL：SELECT id,name,age,sex FROM demo_user WHERE (id IN (?,?,?,?))

例二：

in(R column, Object... values)
in(boolean condition, R column, Object... values)

字段 IN (v0, v1, …)
例: in(“age”, 1, 2, 3)—>age in (1,2,3)

@Test public void test14(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        Integer [] ids=new Integer[]{1,3,4,5};
        queryWrapper.in("id", ids);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL： Preparing: SELECT id,name,age,sex FROM demo_user WHERE (id IN (?,?,?,?))

9.2 notIn

notIn(R column, Object... values)
notIn(boolean condition, R column, Object... values)

字段 NOT IN (v0, v1, …)
例: notIn(“age”, 1, 2, 3)—>age not in (1,2,3)

@Test public void test14(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        Integer [] ids=new Integer[]{1,3,4,5};
        queryWrapper.notIn("id", ids);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL： SELECT id,name,age,sex FROM demo_user WHERE (id NOT IN (?,?,?,?))

10. inSql、notInSql

10.1 inSql

进行子查询

inSql(R column, String inValue)
inSql(boolean condition, R column, String inValue)

字段 IN ( sql语句 )
例: inSql(“age”, “1,2,3,4,5,6”)—>age in (1,2,3,4,5,6)
例: inSql(“id”, “select id from table where id < 3”)—>id in (select id from table where id < 3)

例一

@Test public void test15(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        queryWrapper.inSql("id", "1,3,5");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL：SELECT id,name,age,sex FROM demo_user WHERE (id IN (1,3,5))

例二

@Test
    public void test16(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        queryWrapper.inSql("id","select id from demo_user where id<5");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL：SELECT id,name,age,sex FROM demo_user
WHERE (id IN (select id from demo_user where id<5))

10.1 notInSql

notInSql(R column, String inValue)
notInSql(boolean condition, R column, String inValue)

字段 NOT IN ( sql语句 )
例: notInSql(“age”, “1,2,3,4,5,6”)—>age not in (1,2,3,4,5,6)
例: notInSql(“id”, “select id from table where id < 3”)—>id not in (select id from table where id < 3)

 @Test
    public void test17(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        queryWrapper.notInSql("id","select id from demo_user where id>5");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL：SELECT id,name,age,sex FROM demo_user WHERE (id NOT IN (select id from demo_user where id>5))

11. groupBy

groupBy(R... columns)
groupBy(boolean condition, R... columns)

分组：GROUP BY 字段, …
例: groupBy(“id”, “name”)—>group by id,name

@Test
    public void test18(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        queryWrapper.groupBy("age", "sex");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL：SELECT id,name,age,sex FROM demo_user GROUP BY age,sex
根据age根据年龄分组查询，当age相等时按照性别分组

11. orderBy

orderBy(boolean condition, boolean isAsc, R... columns)

排序：ORDER BY 字段, …
例: orderBy(true, true, “id”, “name”)—>order by id ASC,name ASC

 @Test
    public void test19(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        queryWrapper.orderBy(true, true, "age", "sex");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL：SELECT id,name,age,sex FROM demo_user ORDER BY age ASC,sex ASC
根据年龄升序排列，当年龄相同时按照性别排序
查询结果：

User(id=38, name=貂蝉, age=18, sex=女)
User(id=21, name=孙尚香D, age=18, sex=女)
User(id=39, name=西施, age=18, sex=女)
User(id=17, name=孙尚香, age=18, sex=女)
User(id=16, name=黄月英, age=18, sex=女)
User(id=12, name=貂蝉, age=18, sex=女)
User(id=62, name=赵云, age=18, sex=男)
User(id=49, name=小兰兰, age=18, sex=男)
User(id=44, name=大乔, age=19, sex=女)
User(id=31, name=王昭君, age=19, sex=女)

12. having

having(String sqlHaving, Object... params)
having(boolean condition, String sqlHaving, Object... params)

HAVING ( sql语句 )
例: having(“sum(age) > 10”)—>having sum(age) > 10
例: having(“sum(age) > {0}”, 11)—>having sum(age) > 11

@Test
    public void test20(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        queryWrapper.groupBy("age", "sex")
                .having("age>300" );
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

SQL：SELECT id,name,age,sex FROM demo_user GROUP BY age,sex HAVING age>300
根据年龄与性别进行分组查询，再从每组中选出age>300的用户信息

博客新生儿~

关注

22
点赞
踩
31

收藏

觉得还不错? 一键收藏
0
评论
MP条件构造器练习

1.allEq1.1allEq(Map<R, V> params)allEq(Map<R, V> params, boolean null2IsNull)allEq(boolean condition, Map<R, V> params, boolean null2IsNull)个别参数说明:params : key为数据库字段名,value为字段值null2IsNull : 为true则在map的value为null时调用 isNull 方法,为fals
复制链接

扫一扫

专栏目录