MyBatis-Plus条件构造器使用

条件构造器QueryWrapper常用方法

条件构造器QueryWrapper用于封装where语句。

代码语言：javascript复制

	/**
	  *附加条件构造器QueryWrapper常用方法 ---这几个肯定够用了
	  */
	 wrapper.eq("数据库字段名", "条件值"); //相当于where条件
	 
	 wrapper.between("数据库字段名", "区间一", "区间二");//相当于范围内使用的between
	 
	 wrapper.like("数据库字段名", "模糊查询的字符"); //模糊查询like
	 
	 wrapper.groupBy("数据库字段名");  //相当于group by分组
	 
	 wrapper.in("数据库字段名", "包括的值,分割"); //相当于in
	 
	 wrapper.orderByAsc("数据库字段名"); //升序
	 
	 wrapper.orderByDesc("数据库字段名");//降序
	 
	 wrapper.ge("数据库字段名", "要比较的值"); //大于等于
	 
	 wrapper.le("数据库字段名", "要比较的值"); //小于等于

QueryWrapper所有方法说明

查询方式	说明
setSqlSelect	设置 SELECT 查询字段
where	WHERE 语句，拼接 WHERE 条件
and	AND 语句，拼接 AND 字段 = 值
andNew	AND 语句，拼接 AND ( 字段 = 值 )
or	OR 语句，拼接 OR 字段 = 值
orNew	OR 语句，拼接 OR ( 字段 = 值 )
eq	等于 =
allEq	基于 map 内容等于 =
ne	不等于 <>
gt	大于 >
ge	大于等于 >=
lt	小于 <
le	小于等于 <=
like	模糊查询 LIKE
notLike	模糊查询 NOT LIKE
in	IN 查询
notIn	NOT IN 查询
isNull	NULL 值查询
isNotNull	IS NOT NULL
groupBy	分组 GROUP BY
having	HAVING 关键词
orderBy	排序 ORDER BY
orderAsc	ASC 升序排序 ORDER BY
orderDesc	DESC 降序排序 ORDER BY
exists	EXISTS 条件语句
notExists	NOT EXISTS 条件语句
between	BETWEEN 条件语句
notBetween	NOT BETWEEN 条件语句
addFilter	自由拼接 SQL
last	拼接在最后，例如： last(“LIMIT 1”)

QueryWrapper查询语句

代码语言：javascript复制

// 根据 ID 查询
T selectById(Serializable id);

// 根据 entity 条件，查询一条记录
T selectOne( Wrapper<T> queryWrapper);
 
// 查询（根据ID 批量查询）
List<T> selectBatchIds(Collection<? extends Serializable> idList);

// 根据 entity 条件，查询全部记录
List<T> selectList(Wrapper<T> queryWrapper);

// 查询（根据 columnMap 条件）
List<T> selectByMap(Map<String, Object> columnMap);

// 根据 Wrapper 条件，查询全部记录
List<Map<String, Object>> selectMaps(Wrapper<T> queryWrapper);

// 根据 Wrapper 条件，查询全部记录。注意： 只返回第一个字段的值
List<Object> selectObjs(Wrapper<T> queryWrapper);
 
// 根据 entity 条件，查询全部记录（并翻页）
IPage<T> selectPage(IPage<T> page, Wrapper<T> queryWrapper);

// 根据 Wrapper 条件，查询全部记录（并翻页）
IPage<Map<String, Object>> selectMapsPage(IPage<T> page,Wrapper<T> queryWrapper);

// 根据 Wrapper 条件，查询总记录数
Integer selectCount(Wrapper<T> queryWrapper);

方法一：根据主键id去查询单个结果 selectById

根据主键id去查询单个结果

代码语言：javascript复制

	// T selectById(Serializable id); ---参数为主键类型
	User user1 = userMapper.selectById(1);
	/**
	* 返回值结果
	* {"id": 1,"name": "df","age": 222}
	*/

方法二：查询多条数据库中的记录 selectList

查询多条数据库中的记录

代码语言：javascript复制

	/** 
	* List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);  
	* ---参数为Wrapper可以为空说明没有条件的查询
	*/
	List<User> users1 = userMapper.selectList(null);
	/**
	* 运行结果集
	* [{"id": 1,"name": "df","age": 222},{"id": 2,"name": "wang","age": 22}]
	*/

方法三：查询多条数据库中的记录—条件查询 selectList(wrapper)

查询多条数据库中的记录—条件查询

代码语言：javascript复制

	/**
	 * List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);
	 */
	//首先构造QueryWrapper来进行条件的添加
	QueryWrapper wrapper = new QueryWrapper();
	wrapper.eq("id",1);//相当于where id=1
	
	List<User> list = userMapper.selectList(wrapper);
	/**
	 * 返回值结果
	 * {"id": 1,"name": "df","age": 222}
	 */

方法四：根据主键的id集合进行多条数据的查询 selectBatchIds

根据主键的id集合进行多条数据的查询

代码语言：javascript复制

	/**
	 * List<T> selectBatchIds(@Param("coll") Collection<? extends Serializable> idList);   
	 * --条件为集合
	 */
	List list1 = Arrays.asList(1,2);
	List<User> list2 = userMapper.selectBatchIds(list1);
	/**
	 * 运行结果集
	 * [{"id": 1,"name": "df","age": 222},{"id": 2,"name": "wang","age": 22}]
	 */

方法五：分页查询 selectPage

分页查询

代码语言：javascript复制

	/** 
	 * IPage<T> selectPage(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper);  
	 * ---参数为分页的数据 条件构造器
	 */
	IPage<User> page = new Page<>(1,2);//参数一：当前页，参数二：每页记录数
	//这里想加分页条件的可以如方法三自己构造条件构造器
	IPage<User> userIPage = userMapper.selectPage(page, null);
	
	/**
	 * 运行结果集
	 * {"records":[{"id": 1,"name": "df","age": 222},{"id": 2,"name": "wang","age": 22}],
	 *  "total": 0,"size": 2,"current": 1,"searchCount": true,"pages": 0 }
	 */

示例

代码语言：javascript复制

package com.xiao.permission_system;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.additional.query.impl.LambdaQueryChainWrapper;
import com.mysql.cj.util.StringUtils;
import com.xiao.permission_system.entity.UserInfo;
import com.xiao.permission_system.mapper.UserInfoMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;;

@RunWith(SpringRunner.class)
@SpringBootTest
public class PermissionSystemApplicationTests {

    @Test
    public void contextLoads() {
    }

    @Autowired
    private UserInfoMapper userInfoMapper;

    /**
     * 普通查询
     */
    @Test
    public void selectById() {
        UserInfo userInfo = userInfoMapper.selectById(123);
        System.out.println(userInfo);
    }

    /**
     * 批量查询
     */
    @Test
    public void selectByIds() {
        List<Long> ids = Arrays.asList(123L,124L,125L);
        List<UserInfo> userInfo = userInfoMapper.selectBatchIds(ids);
        System.out.println(userInfo);
    }

    /**
     * 名字包含娟并且年龄小雨30
     */
    @Test
    public void selectByWrapper() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.like("username","娟").lt("age",30);
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }
    /**
     * 名字包含娟并且年龄大雨等于20且小于等于40并且email不为空
     */
    @Test
    public void selectByWrapper2() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.like("username","娟").between("age",20,30).isNotNull("email");
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }

    /**
     * 名字姓肖或者年量大雨等于20，按照年龄降序排列，年龄相同按照id生序排列
     */
    @Test
    public void selectByWrapper3() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.likeRight("username","肖")
                .or().ge("age",20).orderByDesc("age").orderByAsc("id");
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }

    /**
     * 创建日期为2019年10月2日并且直属上级名字为王姓
     */
    @Test
    public void selectByWrapper4() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}","2019-10-07")
                .inSql("parent_id","select id from user where username like '王%'");
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }

    /**
     * 名字为王姓并且（年龄小于40或邮箱不为空）
     */
    @Test
    public void selectByWrapper5() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        //注意：这里的or在and里面
        //参考：https://blog.csdn.net/u011229848/article/details/81902398?utm_source=blogxgwz3
        queryWrapper.likeRight("username","王")
                .and(wq->wq.lt("age",40).or().isNotNull("email"));
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }
    /**
     * 名字为王姓并且（年龄小于40并且大与20或邮箱不为空）
     */
    @Test
    public void selectByWrapper6() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.likeRight("username","王")
                .and(wq->wq.lt("age",40).gt("age",20).or().isNotNull("email"));
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }
    /**
     * 年龄23，30，40
     */
    @Test
    public void selectByWrapper8() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.in("age",Arrays.asList(20,30,40));
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }
    /**
     * 只返回满足条件的第一条语句即可, 用last拼接limit部分
     */
    @Test
    public void selectByWrapper9() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.in("age",Arrays.asList(20,30,40)).last("limit 1");
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }

    /**
     * 名字中包含雨并且年龄小于40(只取id,username)
     */
    @Test
    public void selectByWrapper10() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.select("id","username").like("username","雨").lt("age",40);
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }

    /**
     * 名字中包含雨并且年龄小于40(不取create_time,parent_id两个字段,即不列出全部字段)
     */
    @Test
    public void selectByWrapper11() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.like("username","雨").lt("age",40)
                    .select(UserInfo.class,info->!info.getColumn().equals("create_time")&&
                            !info.getColumn().equals("parent_id"));
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }

    /**
     * 名字为王姓和邮箱不为空，先判断条件是否满足
     */
    public void testCondition() {
        String username = "王";
        String email = "";
        condition(username,email);
    }

    private void condition(String username,String email){
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.likeRight(StringUtils.isNotBlank(username),"name",username)
                    .like(StringUtils.isNotBlank(email),"email",email);
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }

    /**
     * 实体作为条件构造器方法的参数
     */
    @Test
    public void selectByWrapperEntity() {
        UserInfo whereUser = new UserInfo();
        whereUser.setUsername("xiaojuan");
        whereUser.setAge(22);
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>(whereUser);
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }
    /**
     * AllEq用法
     */
    @Test
    public void selectByWrapperAllEq() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("nuserame","xiaojuan");
        params.put("age",null);
        queryWrapper.allEq(params);
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }

    /**
     * AllEq用法(排除不是条件的字段)
     */
    @Test
    public void selectByWrapperAllEq2() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("nuserame","xiaojuan");
        params.put("age",null);
        queryWrapper.allEq((k,v)->!k.equals("name"),params);
        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
        userInfoList.forEach(System.out::println);
    }

    /**
     * selectMaps
     */
    @Test
    public void selectByWrapperMaps() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.like("name","肖").lt("age",40);
        List<Map<String,Object>> userInfoList = userInfoMapper.selectMaps(queryWrapper);
        userInfoList.forEach(System.out::println);
    }

    /**
     * 按照直属上级分组，查询每组的平均年龄，最大年龄，最小年龄。并且只取年龄总和小于500的组
     */
    @Test
    public void selectByWrapperMaps2() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.select("avg(age) avg_age","min(min) min_age","max(age) max_age")
                .groupBy("parent_id").having("sum(age)<{0}",500);
        List<Map<String,Object>> userInfoList = userInfoMapper.selectMaps(queryWrapper);
        userInfoList.forEach(System.out::println);
    }

    /**
     * selectObjs
     */
    @Test
    public void selectByWrapperObjs() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.select("id","name").like("name","肖").lt("age",40);
        List<Object> userInfoList = userInfoMapper.selectObjs(queryWrapper);
        userInfoList.forEach(System.out::println);
    }

    /**
     * selectCount
     */
    @Test
    public void selectByWrapperCount() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.like("name","肖").lt("age",40);
        Integer count = userInfoMapper.selectCount(queryWrapper);
        System.out.println(count);
    }

    /**
     * selectOne
     */
    @Test
    public void selectByWrapperSelectOne() {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
        queryWrapper.like("name","肖").lt("age",40);
        UserInfo user = userInfoMapper.selectOne(queryWrapper);
        System.out.println(user);
    }

    /**
     * 使用Lambda
     */
    @Test
    public void selectLambda() {
       // LambdaQueryWrapper<UserInfo> lambda = new QueryWrapper<UserInfo>().lambda();
        LambdaQueryWrapper<UserInfo> lambda = new LambdaQueryWrapper<UserInfo>();
        lambda.like(UserInfo::getUsername,"娟").lt(UserInfo::getAge,40);
        List<UserInfo> userInfoList = userInfoMapper.selectList(lambda);
        userInfoList.forEach(System.out::println);
    }

    /**
     * 使用Lambda,名字为王姓且（年龄小于40或邮箱不为空）
     */
    @Test
    public void selectLambd2() {
        LambdaQueryWrapper<UserInfo> lambda = new LambdaQueryWrapper<UserInfo>();
        lambda.like(UserInfo::getUsername,"娟")
                .and(lqw->lqw.lt(UserInfo::getAge,40).or().isNotNull(UserInfo::getEmail));
        List<UserInfo> userInfoList = userInfoMapper.selectList(lambda);
        userInfoList.forEach(System.out::println);
    }

    /**
     * 使用Lambda链式
     */
    @Test
    public void selectLambd3() {
        List<UserInfo> userInfoList = new LambdaQueryChainWrapper<UserInfo>(userInfoMapper)
                .like(UserInfo::getUsername,"娟").ge(UserInfo::getAge,20).list();
        userInfoList.forEach(System.out::println);
    }
}

数据库 sql

0 人点赞