mybatis-plus-查询和分页

1.查询构造器：wapper
2.查询
- 2.1初始化表MyStudent
- 2.2进行查询
3.分页

1.查询构造器：wapper

mybatis-plus-查询和分页1.查询构造器：wapper2.查询3.分页

QueryWrapper（LambdaQueryWrapper）和UpdateWrapper（LambdaUpdateWrapper）的父类用于生成sql的where条件，entity属性也用于生成sql的where条件。MP3.X开始支持lambda表达式，LambdaQueryWrapper，LambdaUpdateWrapper支持lambda表达式的构造条件查询。

条件：

条件	说明
allEq	基于map的相等
eq	等于=
ne	不等于<>
gt	大于>
ge	大于等于>=
lt	小于 <
le	小于等于<=
between	BETWEEN 值1 AND 值2
notBetween	NOT BETWEEN 值1 AND 值2
like	LIKE ‘%值%’
notLike	NOT LIKE ‘%值%’
likeLeft	LIKE ‘%值’
likeRight	LIKE ‘值%’
isNull	字段 IS NULL
isNotNull	字段 IS NOT NULL
in	字段 IN(value1,value2,…)
notIn	字段 NOT IN(value1,value2,…)
inSql	字段 IN(sql语句) 例：InSql(“age”,“1,2,3”) --> age in(1,2,3) 例：inSql(“id”,“select id from table where id<3”) --> id in (select id from table where id<3)
notInSql	字段 NOT IN (sql语句)
groupBy	GROUP BY 字段
orderByAsc	升序 ORDER BY 字段，…ASC
orderByDesc	降序 ORDER BY 字段，…DESC
orderBy	自定义字段排序 orderBy(true,true,“id”,“name”) --> order by id ASC,name ASC
having	条件分组
or	OR语句，拼接 +OR 字段=值
and	AND语句，拼接 +AND 字段加值
apply	拼接sql
last	在sql语句后拼接自定义条件
exists	拼接EXISTS（sql语句）例：exists(“select id from table where age = 1”) --> exists(select id from table where age = 1)
notExists	拼接 NOT EXISTS（sql语句）
nested	正常嵌套不带AND或者OR

1）QueryWrapper：查询条件封装类

方法	说明
select	设置查询字段select后面的内容

2）UpdateWrapper：更新条件封装类

方法	说明
set	设置要更新的字段，MP拼接sql语句
setSql	参数是sql语句，MP不再处理语句

2.查询

2.1初始化表MyStudent

mybatis-plus-查询和分页1.查询构造器：wapper2.查询3.分页

2.2进行查询

1）allEq

以Map为参数条件

@Test
    public void testAllEq(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        //组装条件
        Map<String,Object> param = new HashMap<>();
        //map<key,value> key列名，value：查询的值
        param.put("name","张三");
        param.put("age","22");
        param.put("status",1);

        qw.allEq(param);
        //调用MP自己的查询方法
        //SELECT id,name,age,email,status FROM my_student WHERE name = ? AND age = ?
        //WHERE name = ? AND age = ? AND status = ?
        List<MyStudent> myStudents = myStudentsDao.selectList(qw);
        myStudents.forEach(item-> System.out.println(item));
    }

1.Map对象中有key的value是null

使用的是qw.allEq(param,true);

结果：WHERE name = ? AND age IS NULL

2.Map对象中有key的value是null

qw.allEq(param,false);

结果：WHERE name = ?

结论：

allEq(map,boolean)

true:处理null值，where条件加入字段is null

false：忽略null，不作为where条件

@Test
   public void testAllEq2(){
       QueryWrapper<MyStudent> qw = new QueryWrapper<>();
       //组装条件
       Map<String,Object> param = new HashMap<>();
       //map<key,value> key列名，value：查询的值
       param.put("name","张三");
       //age是null
       param.put("age",null);

       //allEq第二个参数为true
       qw.allEq(param,false);
       //调用MP自己的查询方法
       List<MyStudent> myStudents = myStudentsDao.selectList(qw);
       myStudents.forEach(item-> System.out.println(item));
   }

2)eq

/**
     * eq使用
     * eq("列名",值)
     */
    @Test
    public void testEq(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        //组成条件
        qw.eq("name","李四");
        //WHERE name = ?
        List<MyStudent> myStudents = myStudentsDao.selectList(qw);
        myStudents.forEach(item-> System.out.println("查询eq:"+item));
    }

3)ne

/**
    * ne使用
    * ne表示不等于<>
    * ne("列名",值)
    */
   @Test
   public void testNe(){
       QueryWrapper<MyStudent> qw = new QueryWrapper<>();
       //组成条件
       qw.ne("name","张三");
       //WHERE name <> ?
       List<MyStudent> myStudents = myStudentsDao.selectList(qw);
       myStudents.forEach(item-> System.out.println("查询ne:"+item));
   }

4)gt

/**
     * gt使用
     * gt表示大于（>）
     */
    @Test
    public void testGt(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.gt("age",30);//age>30
        //WHERE age > ?
        List<MyStudent> myStudents = myStudentsDao.selectList(qw);
        myStudents.forEach(item-> System.out.println("stu:"+item));
    }

5)ge

/**
     * ge 大于等于 （>=）
     */
    @Test
    public void testGe(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.ge("age",31);//>=31
        //WHERE age >= ?
        List<MyStudent> myStudents = myStudentsDao.selectList(qw);
        myStudents.forEach(item-> System.out.println("stu:"+item));
    }

6)lt

/**
     * lt 小于 （<）
     */
    @Test
    public void testLt(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.lt("age",32);//age<32
        //WHERE age < ?
        List<MyStudent> myStudents = myStudentsDao.selectList(qw);
        myStudents.forEach(item-> System.out.println("stu:"+item));
    }

7)le

/**
     * le 小于等于 （<=）
     */
    @Test
    public void testLe(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.le("age",32);//age<=32
        //WHERE age <= ?
        List<MyStudent> myStudents = myStudentsDao.selectList(qw);
        myStudents.forEach(item-> System.out.println("stu:"+item));
    }

8)between

/**
     * between (? and ?)
     */
    @Test
    public void testBetween(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        //between("列名",开始值,结束值)
        qw.between("age",22,28);
        //WHERE age BETWEEN ? AND ?
        //WHERE age >= 22 and age <= 28
        List<MyStudent> myStudents = myStudentsDao.selectList(qw);
        myStudents.forEach(item-> System.out.println("stu:"+item));
    }

9)notBetween

/**
     * notBetween(不在范围区间内)
     */
    @Test
    public void tsetNotBetween(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.notBetween("age",18,28);
        //WHERE age NOT BETWEEN ? AND ?
        //where age < 18 or age >28
        List<MyStudent> myStudents = myStudentsDao.selectList(qw);
        myStudents.forEach(item-> System.out.println(item));
    }

10)like

/**
     * like 匹配某个值
     */
    @Test
    public void testLike(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.like("name","张");
        //WHERE name LIKE %张%
        List<MyStudent> myStudents = myStudentsDao.selectList(qw);
        myStudents.forEach(item-> System.out.println(item));
    }

11)notLike

/**
     * notLike 不匹配某个值
     */
    @Test
    public void testNotLike(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.notLike("name","张");
        //WHERE name NOT LIKE %张%
        List<MyStudent> myStudents = myStudentsDao.selectList(qw);
        myStudents.forEach(item-> System.out.println(item));
    }

12)likeLeft

/**
     * likeLeft "%值"
     */
    @Test
    public void testLikeLeft(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.likeLeft("name","张");
        //WHERE name LIKE %张
        List<MyStudent> myStudents = myStudentsDao.selectList(qw);
        myStudents.forEach(item-> System.out.println(item));
    }

13)likeRight

/**
     * likeRight "%值"
     */
    @Test
    public void testLikeRight(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.likeRight("name","李");
        //WHERE name LIKE 李%
        List<MyStudent> myStudents = myStudentsDao.selectList(qw);
        myStudents.forEach(item-> System.out.println(item));
    }

14)isNull

/**
     * isNull,判断换字段是null
     */
    @Test
    public void testIsNull(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        //判断email is null
        //WHERE email IS NULL
        qw.isNull("email");
        print(qw);
    }

15)isNotNull

/**
     * isNotNull,判断换字段不是null
     */
    @Test
    public void testIsNotNull(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        //判断email is not null
        //WHERE email IS NOT NULL
        qw.isNotNull("email");
        print(qw);
    }

16)in

/**
     *in 值列表
     */
    @Test
    public void testIn(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        //in(列名，多个值的列表)
        //WHERE name IN (?,?,?)
        qw.in("name","张三","李四","周丽");
        print(qw);
    }
    /**
     *in 值列表
     */
    @Test
    public void testIn2(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        List<Object> list = new ArrayList<>();
        list.add(1);
        list.add(2);
        //WHERE status IN (?,?)
        qw.in("status",list);
        print(qw);
    }

17notIn

/**
     *notIn 值列表
     */
    @Test
    public void testNotIn(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        //notIn(列名，多个值的列表)
        //WHERE name NOT IN (?,?,?)
        qw.notIn("name","张三","李四","周丽");
        print(qw);
    }

18)inSql

/**
     *inSql():使用子查询
     */
    @Test
    public void testInSql(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        //WHERE age IN (select age from my_student where id=1)
        qw.inSql("age","select age from my_student where id=1");
        print(qw);
    }

19)notInSql

/**
     *notInSql():使用子查询
     */
    @Test
    public void testNotInSql(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        //WHERE age NOT IN (select age from my_student where id=1)
        qw.notInSql("age","select age from my_student where id=1");
        print(qw);
    }

    private void print(QueryWrapper qw){
        List<MyStudent> myStudents = myStudentsDao.selectList(qw);
        myStudents.forEach(System.out::println);
    }

20)groupBy

/**
     * groupBy：分组
     */
    @Test
    public void testGroupBy(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.select("name,count(*) personNumbers");//select name count(*) personNumbers
        qw.groupBy("name");
        //SELECT name,count(*) personNumbers FROM my_student GROUP BY name
        print(qw);
    }

21)orderByAsc

/**
     * orderByAsc：按升序排序
     */
    @Test
    public void testOrderByAsc(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.orderByAsc("name","age");
        //FROM my_student ORDER BY name ASC , age ASC
        print(qw);
    }

22)orderByDesc

/**
     * orderByDesc：按降序排序
     */
    @Test
    public void testOrderByDesc(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.orderByDesc("name","id");
        //FROM my_student ORDER BY name DESC , id DESC
        print(qw);
    }

23)orderBy

orderBy：指定字段和排序的方向

boolean condition:条件内容是否加入到sql语句的后面。

true:条件添加到sql语句

FROM my_student ORDER BY name ASC

false:条件不添加到sql语句

FROM my_student

@Test
    public void testOrderBy(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.orderBy(true,true,"name")
                .orderBy(true,false,"age")
                .orderBy(true,false,"email");
        //name asc,age desc,email desc
        //FROM my_student ORDER BY name ASC , age DESC , email DESC
        print(qw);
    }

24)and,or

/**
     * and,or方法
     */
    @Test
    public void testOr(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        //WHERE name = ? OR age = ?
        qw.eq("name","张三")
                .or()
                .eq("age",22);
        print(qw);
    }

25)last

/**
     * last：拼接sql语句到MP的sql语句的最后
     */
    @Test
    public void testLast(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        //SELECT id,name,age,email,status FROM my_student WHERE name = ? OR age = ? limit 1
        qw.eq("name","张三")
                .or()
                .eq("age",22)
                .last("limit 1");
        print(qw);
    }

26)exists

/**
     * exists：判断条件
     *
     * notExists
     */
    @Test
    public void testExists(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        //SELECT id,name,age,email,status FROM my_student
        // WHERE EXISTS (select id from my_student where age > 20)
        qw.exists("select id from my_student where age > 20");

        //SELECT id,name,age,email,status FROM my_student
        //WHERE NOT EXISTS (select id from my_student where age > 90)
        qw.notExists("select id from my_student where age > 90");
        print(qw);
    }

3.分页

前提：配置分页插件，实现物理分页。默认是内存分页

package com.putao.plus.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @Configuration标注是类就相当于xml配置文件
 */
@Configuration
public class Config {
    /**
     * 定义方法，返回值是java对象，这个对象放入到spring容器中
     * 使用@Bean修饰方法
     * @Bean等同于<bean></bean>
     */
    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }
}

分页查询

分页：

1.统计记录数，使用count(1)

SELECT COUNT(1) FROM my_student WHERE age > ?

2.实现分页，在sql语句末尾加入limit0,3

SELECT id,name,age,email,status FROM my_student WHERE age > ? LIMIT 0,3

@Test
    public void testPage(){
        QueryWrapper<MyStudent> qw = new QueryWrapper<>();
        qw.gt("age",22);
        IPage<MyStudent> page = new Page<>();
        //设置分页的数据
        page.setCurrent(1);//第一页
        page.setSize(3);//每页的记录数
        IPage<MyStudent> result = myStudentsDao.selectPage(page, qw);
        //获取分页后的记录
        List<MyStudent> myStudents = result.getRecords();
        System.out.println("myStudents.size:"+myStudents.size());
        //分页的信息
        long pages = result.getPages();
        System.out.println("页数："+pages);
        System.out.println("总记录"+result.getTotal());
        System.out.println("当前页码："+result.getCurrent());
        System.out.println("每页的记录"+result.getSize());
    }

mybatis-plus-查询和分页1.查询构造器：wapper2.查询3.分页

mybatis-plus-查询和分页

1.查询构造器：wapper

2.查询

2.1初始化表MyStudent

2.2进行查询

3.分页

继续阅读

SSM 整合 Spring+Spring MVC+MyBatisSSM 整合

MyEclipse安装MyBatipse 从dao快速定位mapper的sql语句

Mybatis问答集

【附源码】计算机毕业设计SSM网络求职招聘系统

Mybatis源码阅读（一）：Mybatis初始化1.1 解析properties、settings前言入口引申结语

mybatis源码解析（二）初始化SqlSessionFactory对象

Mybatis源码阅读（四）：核心接口4.2——Executor（上）

MyBatis源码解析(一)——MyBatis初始化过程解MyBatis源码解析(一)——MyBatis初始化过程解1. 准备工作2. MyBatis初始化过程

从你的WEB世界路过之--0.初识0) 前言1) WEB基本概念2) 响应数据3. 结语

Java 实现Gbase数据库增删改查功能

mybatis——使用代理对象进行数据库操作

mybatis中#{}和${}和区别，以及防止sql注入。

myeclipse 搭建mybatis框架时报错（1）Communications link failure 通讯链接故障

MyBatis学习笔记Mybatis学习笔记（maven、Idea版）

用mybatis的generator插件在项目中自动生成dao及entity

从mybatis到mybatis plus(mp)