QueryWrapper条件构造器验证

痛定思痛。 2023-01-05 05:05 299阅读 0赞

# allEq #

官方文档：

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: allEq({
        id:1,name:"老王",age:null})--->id = 1 and name = '老王' and age is null
    例2: allEq({
        id:1,name:"老王",age:null}, false)--->id = 1 and name = '老王'

验证**boolean null2IsNull**：  
![在这里插入图片描述][20210111164510638.png]

@Test
        public void allEq() {
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            Map<String,String> params = new HashMap<>();
            params.put("name",null);
            **queryWrapper.allEq(params);**
            List<TCourse> list = itCourseService.list(queryWrapper);
            for(TCourse tCourse:list){
        
                System.out.println(tCourse);
            }
        }

boolean null2IsNull = true 结果：
    TCourse(id=13, name=null)
    boolean null2IsNull = false`在这里插入代码片` 结果：
    TCourse(id=2, name=math)
    TCourse(id=3, name=英语)
    TCourse(id=4, name=物理)
    TCourse(id=5, name=政治)
    TCourse(id=6, name=化学)
    TCourse(id=7, name=生物)
    TCourse(id=8, name=history)
    TCourse(id=9, name=劳动)
    TCourse(id=10, name=体育)
    TCourse(id=11, name=music)
    TCourse(id=12, name=美术)
    TCourse(id=13, name=null)

allEq(BiPredicate<R, V> 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: allEq((k,v) -> k.indexOf("a") >= 0, {
        id:1,name:"老王",age:null})--->name = '老王' and age is null
    例2: allEq((k,v) -> k.indexOf("a") >= 0, {
        id:1,name:"老王",age:null}, false)--->name = '老王'

验证**BiPredicate<R, V> filter**

@Test
        public void allEqBiPredicate() {
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            Map<String,String> params = new HashMap<>();
            params.put("name","英语");
            params.put("id","4");
            queryWrapper.allEq((k,v)->k.equals("id"),params);
            List<TCourse> list = itCourseService.list(queryWrapper);
            for(TCourse tCourse:list){
        
                System.out.println(tCourse);
            }
        }
    结果：
    TCourse(id=4, name=物理)

filter:过滤条件：key为“id”的条件被加入到筛选条件中  
相当于：

allEq((k,v)->k.equals("id"),params)---->id = "4"
    allEq(params)---->id = "4" and name = "英语"

## eq,ne,gt,ge,lt,le ##

值得一提的是，除了可以比较数字大小外，还可以比较字母

@Test
        public void le(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.le("name","w");
            List<TCourse> list = itCourseService.list(queryWrapper);
            for(TCourse tCourse:list){
        
                System.out.println(tCourse);
            }
        }
        结果：
        TCourse(id=2, name=math)
    	TCourse(id=8, name=history)
    	TCourse(id=11, name=music)

## between 和 notBetween ##

@Test
        public void between(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.between("name","a","z");
            List<TCourse> list = itCourseService.list(queryWrapper);
            for(TCourse tCourse:list){
        
                System.out.println(tCourse);
            }
        }
    
    TCourse(id=2, name=math)
    TCourse(id=8, name=history)
    TCourse(id=11, name=music)

@Test
        public void notBetween(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.notBetween("name","a","z");
            List<TCourse> list = itCourseService.list(queryWrapper);
            for(TCourse tCourse:list){
        
                System.out.println(tCourse);
            }
        }
    
    TCourse(id=3, name=英语)
    TCourse(id=4, name=物理)
    TCourse(id=5, name=政治)
    TCourse(id=6, name=化学)
    TCourse(id=7, name=生物)
    TCourse(id=9, name=劳动)
    TCourse(id=10, name=体育)
    TCourse(id=12, name=美术)

## like,notLike,likeLeft,likeRight ##

likeLeft:%值

@Test
        public void likeLeft(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.likeLeft("name","物");
            List<TCourse> list = itCourseService.list(queryWrapper);
            for(TCourse tCourse:list){
        
                System.out.println(tCourse);
            }
        }
    
    TCourse(id=7, name=生物)

likeRight:值%

@Test
        public void likeRight(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.likeRight("name","物");
            List<TCourse> list = itCourseService.list(queryWrapper);
            for(TCourse tCourse:list){
        
                System.out.println(tCourse);
            }
        }
    
    TCourse(id=4, name=物理)

## isNull,isNotNull,in,notIn ##

@Test
        public void isNull(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.isNull("name");
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }

![在这里插入图片描述][20210112145120654.png]

## inSql ##

@Test
        public void inSql(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.inSql("id","1,2,3,4");
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    结果：
    TCourse(id=2, name=math, type=理科, haveExam=true)
    TCourse(id=3, name=英语, type=文科, haveExam=true)
    TCourse(id=4, name=物理, type=理科, haveExam=true)
    
    @Test
        public void inSql(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.inSql("id","select id from t_course where id > 5 ");
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    结果
    TCourse(id=6, name=化学, type=理科, haveExam=false)
    TCourse(id=7, name=生物, type=理科, haveExam=false)
    TCourse(id=8, name=history, type=文科, haveExam=false)
    TCourse(id=9, name=劳动, type=艺术, haveExam=true)
    TCourse(id=10, name=体育, type=艺术, haveExam=true)
    TCourse(id=11, name=music, type=艺术, haveExam=false)
    TCourse(id=12, name=美术, type=艺术, haveExam=false)
    TCourse(id=13, name=null, type=null, haveExam=false)

## groupBy ##

可以传多个列

@Test
        public void groupBy(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.groupBy("type","have_exam");
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    结果：
    TCourse(id=13, name=null, type=null, haveExam=false)
    TCourse(id=8, name=history, type=文科, haveExam=false)
    TCourse(id=3, name=英语, type=文科, haveExam=true)
    TCourse(id=6, name=化学, type=理科, haveExam=false)
    TCourse(id=2, name=math, type=理科, haveExam=true)
    TCourse(id=11, name=music, type=艺术, haveExam=false)
    TCourse(id=9, name=劳动, type=艺术, haveExam=true)

## orderByAsc，orderByDesc，orderBy ##

@Test
        public void orderBy(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.orderByAsc("type");
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    TCourse(id=13, name=null, type=null, haveExam=false)
    TCourse(id=3, name=英语, type=文科, haveExam=true)
    TCourse(id=5, name=政治, type=文科, haveExam=true)
    TCourse(id=8, name=history, type=文科, haveExam=false)
    TCourse(id=2, name=math, type=理科, haveExam=true)
    TCourse(id=4, name=物理, type=理科, haveExam=true)
    TCourse(id=6, name=化学, type=理科, haveExam=false)
    TCourse(id=7, name=生物, type=理科, haveExam=false)
    TCourse(id=9, name=劳动, type=艺术, haveExam=true)
    TCourse(id=10, name=体育, type=艺术, haveExam=true)
    TCourse(id=11, name=music, type=艺术, haveExam=false)
    TCourse(id=12, name=美术, type=艺术, haveExam=false)
    
    //也可以传多个参数
    @Test
        public void orderBy(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.orderByAsc("type","name");
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    TCourse(id=13, name=null, type=null, haveExam=false)
    TCourse(id=8, name=history, type=文科, haveExam=false)
    TCourse(id=5, name=政治, type=文科, haveExam=true)
    TCourse(id=3, name=英语, type=文科, haveExam=true)
    TCourse(id=2, name=math, type=理科, haveExam=true)
    TCourse(id=6, name=化学, type=理科, haveExam=false)
    TCourse(id=4, name=物理, type=理科, haveExam=true)
    TCourse(id=7, name=生物, type=理科, haveExam=false)
    TCourse(id=11, name=music, type=艺术, haveExam=false)
    TCourse(id=10, name=体育, type=艺术, haveExam=true)
    TCourse(id=9, name=劳动, type=艺术, haveExam=true)
    TCourse(id=12, name=美术, type=艺术, haveExam=false)

## orderBy ##

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

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

@Test
        public void orderBy(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.orderBy(true,false,"type","name");
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    TCourse(id=12, name=美术, type=艺术, haveExam=false)
    TCourse(id=9, name=劳动, type=艺术, haveExam=true)
    TCourse(id=10, name=体育, type=艺术, haveExam=true)
    TCourse(id=11, name=music, type=艺术, haveExam=false)
    TCourse(id=7, name=生物, type=理科, haveExam=false)
    TCourse(id=4, name=物理, type=理科, haveExam=true)
    TCourse(id=6, name=化学, type=理科, haveExam=false)
    TCourse(id=2, name=math, type=理科, haveExam=true)
    TCourse(id=3, name=英语, type=文科, haveExam=true)
    TCourse(id=5, name=政治, type=文科, haveExam=true)
    TCourse(id=8, name=history, type=文科, haveExam=false)
    TCourse(id=13, name=null, type=null, haveExam=false)

## having ##

@Test
        public void having(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.groupBy("type","have_exam")
                    .having("count(*) >= 2");
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    TCourse(id=3, name=英语, type=文科, haveExam=true)
    TCourse(id=6, name=化学, type=理科, haveExam=false)
    TCourse(id=2, name=math, type=理科, haveExam=true)
    TCourse(id=11, name=music, type=艺术, haveExam=false)
    TCourse(id=9, name=劳动, type=艺术, haveExam=true)

## func ##

func(Consumer<Children> consumer)
    func(boolean condition, Consumer<Children> consumer)

func 方法(主要方便在出现if…else下调用不同方法能不断链)

@Test
        public void func(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.func(i -> {
        
                System.out.println(i);
                if(true) {
        
                    i.eq("id", 3);
                } else {
        
                    i.ne("id", 3);
                }
            });
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }

![在这里插入图片描述][20210114163123201.png]

## or ##

or()
    or(boolean condition)

## 拼接 OR ##

注意事项:

主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)

例: eq("id",1).or().eq("name","老王")--->id = 1 or name = '老王'

@Test
        public void or(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("id","2").or().eq("name","化学");
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    TCourse(id=2, name=math, type=理科, haveExam=true)
    TCourse(id=6, name=化学, type=理科, haveExam=false)

or(Consumer<Param> consumer)
    or(boolean condition, Consumer<Param> consumer)

## OR 嵌套 ##

例: or(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))—>or (name = ‘李白’ and status <> ‘活着’)

@Test
        public void or2(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.or((i) -> i.eq("id","2").ne("name","化学"));
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    TCourse(id=2, name=math, type=理科, haveExam=true)
    --> or(id = '2' and name <> '化学')

## and ##

and(Consumer<Param> consumer)
    and(boolean condition, Consumer<Param> consumer)

AND 嵌套  
例: and(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))—>and (name = ‘李白’ and status <> ‘活着’)

@Test
        public void and(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.and((i) -> i.eq("id","2").ne("name","化学"));
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    TCourse(id=2, name=math, type=理科, haveExam=true)

## nested ##

nested(Consumer<Param> consumer)
    nested(boolean condition, Consumer<Param> consumer)

正常嵌套 不带 AND 或者 OR  
例: nested(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))—>(name = ‘李白’ and status <> ‘活着’)

@Test
        public void nested(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.nested((i) -> i.eq("id","2").ne("name","化学"));
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    TCourse(id=2, name=math, type=理科, haveExam=true)

## apply ##

作用：拼接sql

@Test
        public void apply(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<TCourse>();
            queryWrapper.apply("date_format(date_column,'%Y-%m-%d') = {0}", "2008-08-08");
            //queryWrapper.eq("date_format(date_column,'%Y-%m-%d')", "2008-08-08");
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    TCourse(id=2, name=math, type=理科, haveExam=true)

例: apply(“date\_format(dateColumn,’%Y-%m-%d’) = ‘2008-08-08’”)—>date\_format(dateColumn,’%Y-%m-%d’) = ‘2008-08-08’")  
例: apply(“date\_format(dateColumn,’%Y-%m-%d’) = **\{0\}**”, “2008-08-08”)—>date\_format(dateColumn,’%Y-%m-%d’) = ‘2008-08-08’")  
以上第一种方式会有sql注入风险，不推荐使用。建议使用第二种方式。

## last ##

last(String lastSql)
    last(boolean condition, String lastSql)

无视优化规则直接拼接到 sql 的最后  
注意事项:

只能调用一次,多次调用以最后一次为准 **有sql注入的风险,请谨慎使用**

例: last(“limit 1”)

@Test
        public void last(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.in("name", Arrays.asList("英语","物理","化学","政治","生物"))
                    .last("limit 2");
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    TCourse(id=3, name=英语, type=文科, haveExam=true, dateColumn=Wed Sep 09 08:00:00 CST 2009)
    TCourse(id=4, name=物理, type=理科, haveExam=true, dateColumn=null)

## exists ##

exists(String existsSql)
    exists(boolean condition, String existsSql)

拼接 EXISTS ( sql语句 )  
例: exists(“select id from table where age = 1”)—>exists (select id from table where age = 1)

@Test
        public void exit(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.exists("select * from t_course where id > 5");
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    TCourse(id=2, name=math, type=理科, haveExam=true, dateColumn=Fri Aug 08 08:00:00 CST 2008)
    TCourse(id=3, name=英语, type=文科, haveExam=true, dateColumn=Wed Sep 09 08:00:00 CST 2009)
    TCourse(id=4, name=物理, type=理科, haveExam=true, dateColumn=null)
    TCourse(id=5, name=政治, type=文科, haveExam=true, dateColumn=null)
    TCourse(id=6, name=化学, type=理科, haveExam=false, dateColumn=null)
    TCourse(id=7, name=生物, type=理科, haveExam=false, dateColumn=null)
    TCourse(id=8, name=history, type=文科, haveExam=false, dateColumn=null)
    TCourse(id=9, name=劳动, type=艺术, haveExam=true, dateColumn=null)
    TCourse(id=10, name=体育, type=艺术, haveExam=true, dateColumn=null)
    TCourse(id=11, name=music, type=艺术, haveExam=false, dateColumn=null)
    TCourse(id=12, name=美术, type=艺术, haveExam=false, dateColumn=null)
    TCourse(id=13, name=null, type=null, haveExam=false, dateColumn=null)

notExists

notExists(String notExistsSql)
    notExists(boolean condition, String notExistsSql)

拼接 NOT EXISTS ( sql语句 )  
例: notExists(“select id from table where age = 1”)—>not exists (select id from table where age = 1)

## QueryWrapper ##

## select ##

select(String... sqlSelect)
    select(Predicate<TableFieldInfo> predicate)
    select(Class<T> entityClass, Predicate<TableFieldInfo> predicate)

设置查询字段  
说明:

以上方法分为两类.  
第二类方法为:过滤查询字段(主键除外),入参不包含 class 的调用前需要wrapper内的entity属性有值! 这两类方法重复调用以最后一次为准

例: select(“id”, “name”, “age”)  
例: select(i -> i.getProperty().startsWith(“test”))

@Test
        public void select(){
        
            QueryWrapper<TCourse> queryWrapper = new QueryWrapper<>();
            queryWrapper.select("id","type");
            List<TCourse> list = itCourseService.list(queryWrapper);
            list.forEach(System.out::println);
        }
    
    TCourse(id=2, name=null, type=理科, haveExam=false, dateColumn=null)
    TCourse(id=3, name=null, type=文科, haveExam=false, dateColumn=null)
    TCourse(id=4, name=null, type=理科, haveExam=false, dateColumn=null)
    TCourse(id=5, name=null, type=文科, haveExam=false, dateColumn=null)
    TCourse(id=6, name=null, type=理科, haveExam=false, dateColumn=null)
    TCourse(id=7, name=null, type=理科, haveExam=false, dateColumn=null)
    TCourse(id=8, name=null, type=文科, haveExam=false, dateColumn=null)
    TCourse(id=9, name=null, type=艺术, haveExam=false, dateColumn=null)
    TCourse(id=10, name=null, type=艺术, haveExam=false, dateColumn=null)
    TCourse(id=11, name=null, type=艺术, haveExam=false, dateColumn=null)
    TCourse(id=12, name=null, type=艺术, haveExam=false, dateColumn=null)
    TCourse(id=13, name=null, type=null, haveExam=false, dateColumn=null)

[20210111164510638.png]: /images/20221119/c9d0fc63a81141f4a01f4296a280b862.png
[20210112145120654.png]: https://img-blog.csdnimg.cn/20210112145120654.png
[20210114163123201.png]: https://img-blog.csdnimg.cn/20210114163123201.png