MyBatis:条件构造器QueryWrapper方法详解
阅读原文时间:2021年09月07日阅读:13

QueryWrapper

说明:       继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件 及 LambdaQueryWrapper, 可以通过 new QueryWrapper().lambda() 方法获取.

测试用表:

1. eq、ne

说明:

eq:等于,ne:不等于

测试:

@Test
public void contextLoads(){
QueryWrapper wrapper = new QueryWrapper<>();
//eq() 等于
wrapper.eq("last_name", "皮皮虾");
Employee one = employeeService.getOne(wrapper);
System.out.println(one);
}

@Test
public void contextLoads(){
QueryWrapper wrapper = new QueryWrapper<>();
//ne() 不等于
wrapper.ne("gender", 0);
List list = employeeService.list(wrapper);
System.out.println(list);
}

2. gt、ge、lt、le

说明:

gt:大于,ge:大于等于,lt:小于,le:小于等于

测试:

@Test
public void contextLoads(){
QueryWrapper gtWrapper = new QueryWrapper<>();
//gt() 大于
gtWrapper.gt("age", 27);
List gtList = employeeService.list(gtWrapper);
System.out.println(gtList);

    //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

    QueryWrapper<Employee> geWrapper = new QueryWrapper<>();  
    //ge() 大于等于  
    geWrapper.ge("age", 26);  
    List<Employee> geList = employeeService.list(geWrapper);  
    System.out.println(geList);  

}

@Test
public void contextLoads(){
QueryWrapper ltWrapper = new QueryWrapper<>();
//lt() 小于
ltWrapper.lt("age", 12);
List ltList = employeeService.list(ltWrapper);
System.out.println(ltList);

    //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

    QueryWrapper<Employee> geWrapper = new QueryWrapper<>();  
    //le() 小于等于  
    leWrapper.le("age", 12);  
    List<Employee> leList = employeeService.list(leWrapper);  
    System.out.println(leList);  

}

3. between、notBetween

说明:

between:在值1和值2之间,notBetween:不在值1和值2之间

测试:

@Test
public void contextLoads(){
QueryWrapper betweenWrapper = new QueryWrapper<>();
//between() 区间的值
betweenWrapper.between("age", 10, 20);
List betweenList = employeeService.list(betweenWrapper);
System.out.println(betweenList );

    //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

    QueryWrapper<Employee> notBetweenWrapper = new QueryWrapper<>();  
    //notBetween() 不在区间的值  
    notBetweenWrapper.notBetween("age", 10, 21);  
    List<Employee> notBetweenList = employeeService.list(notBetweenWrapper );  
    System.out.println(notBetweenList );  

}

4. like、notLike、likeLeft、likeRight

说明:

like:’%值%’,notLike:’%值%’,likeLeft:’%值’,likeRight:'值%'

测试:

@Test
public void contextLoads(){
QueryWrapper likeWrapper = new QueryWrapper<>();
//like() 模糊查询匹配值
likeWrapper.like("last_name", "s");
List likeList = employeeService.list(likeWrapper);
System.out.println(likeList );

    //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

    QueryWrapper<Employee> notLikeWrapper = new QueryWrapper<>();  
    //notLike() 模糊查询不匹配值  
    notLikeWrapper.notLike("last\_name", "s");  
    List<Employee> notLikeList = employeeService.list(notLikeWrapper);  
    System.out.println(notLikeList);  

}

@Test
public void contextLoads(){
QueryWrapper likeLeftWrapper = new QueryWrapper<>();
//likeLeft() 模糊查询匹配最后一位值
likeLeftWrapper.likeLeft("last_name", "s");
List likeLeftList = employeeService.list(likeLeftWrapper);
System.out.println(likeLeftList );

    //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

    QueryWrapper<Employee> likeRightWrapper = new QueryWrapper<>();  
    //likeRight() 模糊查询匹配第一位值  
    likeRightWrapper.likeRight("last\_name", "s");  
    List<Employee> likeRightList = employeeService.list(likeRightWrapper );  
    System.out.println(likeRightList );  

}

5. isNull、isNotNull

说明:

isNull:字段 IS NULL,isNotNull:字段 IS NOT NULL

测试:

@Test
public void contextLoads(){
QueryWrapper isNullWrapper = new QueryWrapper<>();
//isNull() 为空
isNullWrapper.isNull("email");
List isNullList = employeeService.list(isNullWrapper);
System.out.println(isNullList );

    //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

    QueryWrapper<Employee> isNotNullWrapper = new QueryWrapper<>();  
    //isNotNull() 不为空  
    isNotNullWrapper.isNotNull("last\_name", "s");  
    List<Employee> isNotNullList = employeeService.list(isNotNullWrapper );  
    System.out.println(isNotNullList );  

}

6. in、notIn

说明:

in:字段 IN (v0, v1, …),notIn:字段 NOT IN (value.get(0), value.get(1), …)

测试:

@Test
public void contextLoads(){
QueryWrapper inWrapper = new QueryWrapper<>();
//in() 符合多个条件的值
inWrapper.in("age", 8, 16, 26);
List inList = employeeService.list(inWrapper);
System.out.println(inList );

    //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

    QueryWrapper<Employee> notInWrapper= new QueryWrapper<>();  
    //notIn() 不符合多个条件的值  
    notInWrapper.notIn("age", 8, 16, 26);  
    List<Employee> notInList = employeeService.list(notInWrapper);  
    System.out.println(notInList );  

}

7. or、and

说明:

or:或者 ,and:和

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

测试:

@Test
public void contextLoads(){
QueryWrapper orWrapper = new QueryWrapper<>();
//or() 查询age大于20 或者 gender等于1 的数据
orWrapper.gt("age", 20).or().eq("gender", 1);
List orList = employeeService.list(orWrapper);
System.out.println(orList );
}

@Test
public void contextLoads(){
QueryWrapper andWrapper = new QueryWrapper<>();
//and() 查询last_name等于皮皮虾b 和 gender等于1 的数据 (不使用and() 默认就是and())
andWrapper.eq("last_name", "皮皮虾b").eq("gender", 1);
List andList = employeeService.list(andWrapper );
System.out.println(andList);
}

8. orderByAsc、orderByDesc

说明:

orderByAsc:升序:ORDER BY 字段, … ASC,orderByDesc:降序:ORDER BY 字段, … DESC

测试:

@Test
public void contextLoads(){
QueryWrapper Wrapper = new QueryWrapper<>();
//orderByAsc() 升序排序
Wrapper.orderByAsc("id");
List list = employeeService.list(Wrapper);
for(Employee e : list) {
System.out.println(e);
}
}

@Test
public void contextLoads(){
QueryWrapper Wrapper = new QueryWrapper<>();
//orderByDesc() 降序排序
Wrapper.orderByDesc("id");
List list = employeeService.list(Wrapper);
for(Employee e : list) {
System.out.println(e);
}
}

9. inSql、notInSql (不常用)

**说明:
**

inSql:字段 IN ( sql语句 ),notInSql:字段 NOT IN ( sql语句 )

测试:

@Test
public void contextLoads(){
QueryWrapper inSqlWrapper = new QueryWrapper<>();
//inSql() 符合sql语句的值
inSqlWrapper .inSql("select id from employee where id < 10"); List inSqlList = employeeService.list(inSqlWrapper );
System.out.println(inSqlList );

    //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

    QueryWrapper<Employee> notInSqlWrapper= new QueryWrapper<>();  
    //notInSql() 不符合sql语句的值  
    notInSqlWrapper.notInSql("select id from employee where id < 10");  
    List<Employee> notInSqlList = employeeService.list(notInSqlWrapper);  
    System.out.println(notInSqlList );  

}

10. exists、notExists (不常用)

说明:

exists:拼接 EXISTS ( sql语句 ),notExists:拼接 NOT EXISTS ( sql语句 )

测试:

@Test
public void contextLoads(){
QueryWrapper existsWrapper = new QueryWrapper<>();
//exists() 查询符合sql语句的值
existsWrapper.exists("select last_name,gender from employee where id = 1");
List existsList = employeeService.list(existsWrapper);
System.out.println(existsList );

    //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

    QueryWrapper<Employee> notExistsWrapper = new QueryWrapper<>();  
    //notExists() 查询不符合sql语句的值  
    notExistsWrapper.notExists("select last\_name,gender from employee where id = 1");  
    List<Employee> notExistsList = employeeService.list(notExistsWrapper);  
    System.out.println(notExistsList );

}

文章转载至:https://blog.csdn.net/llllllkkkkkooooo/article/details/108216957

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器

你可能感兴趣的文章