Wrapper查询构造器

查询条件

QueryWrapper 查询条件封装类

UpdateWrapper 更新条件封装类

查询实例

前期准备

• 创建一个数据库 mybatisplus

• 创建 user 表

  CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
  );
  

• 创建 springboot 工程

  • 导入对应 maven 坐标

    <?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.5.3</version>
    <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.cmy</groupId>
    <artifactId>mybatis_plus</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatis_plus</name>
    <description>Demo project for Spring Boot</description>
    <properties>
    <java.version>1.8</java.version>
    </properties>
    <dependencies>
    <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <!-- mybatis-plus -->
    <dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
    </dependency>
    <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
    </dependency>
    <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
    </dependency>
    </dependencies>
    <build>
    <plugins>
    <plugin>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-maven-plugin</artifactId>
    </plugin>
    </plugins>
    </build>
    </project>
    

  • mysql数据库相关配置

    spring:
    datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1/mybatisplus?useUnicode=true&characterEncoding=utf-8
    username: root
    password: root
    

  • mybatis-plus 日志信息配置

    mybatis-plus:
    configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    

  • 创建实体类 User

    package com.cmy.mybatis_plus.entity;
    import com.baomidou.mybatisplus.annotation.IdType;
    import com.baomidou.mybatisplus.annotation.TableId;
    /**
    * 实体类 user
    */
    public class User {
    /**
    * 指定主键id生成的方式
    * value 是主键字段的名称，如果是id，可以不用写
    * type 指定主键的类型，主键的值如何生成。idType.AUTO 自动增长
    */
    @TableId(
    value = "id",
    type = IdType.AUTO
    )
    private Long id;
    private String name;
    private String email;
    private Integer age;
    public Long getId() {
    return id;
    }
    public void setId(Long id) {
    this.id = id;
    }
    public String getName() {
    return name;
    }
    public void setName(String name) {
    this.name = name;
    }
    public String getEmail() {
    return email;
    }
    public void setEmail(String email) {
    this.email = email;
    }
    public Integer getAge() {
    return age;
    }
    public void setAge(Integer age) {
    this.age = age;
    }
    @Override
    public String toString() {
    return "User{" +
    "id=" + id +
    ", name='" + name + ''' +
    ", email='" + email + ''' +
    ", age=" + age +
    '}';
    }
    }
    

  • 自定义 User 的 Mapper 接口

    package com.cmy.mybatis_plus.mapper;
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import com.cmy.mybatis_plus.entity.User;
    /**
    * 自定义 Mapper 接口，就是 dao 接口
    * 1. 实现BaseMapper
    * 2. 指定实体类（泛型）
    *
    * BaseMapper 是 MP 框架中的对象，定义了 17 个操作方法（CRUD）
    */
    public interface UserMapper extends BaseMapper<User> {
    }
    

    用于CRUD

查询操作

allEq

条件用 Map 进行封装

“name” -> “zhangsan”

“age” -> 21

@Autowired
private UserMapper userMapper;
@Test
public void testAllEq() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
// 封装条件
Map<String, Object> hashMap = new HashMap<>();
hashMap.put("name", "zhangsan");
hashMap.put("age", 21);
queryWrapper.allEq(hashMap);
// 调用 MP 自己的方法
// SELECT id,name,email,age FROM user WHERE (name = ? AND age = ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> {
System.out.println(user);
});
}

allEq(Map<R, V> params, boolean null2IsNull)有两个参数，第二个参数默认为 true，表示查询的条件的值可以为 null。例如age IS NULL

eq

eq("列名", 值)

@Autowired
private UserMapper userMapper;
@Test
public void testEq() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "zhangsan");
// SELECT id,name,email,age FROM user WHERE (name = ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

ne

ne("列名", 值)

@Autowired
private UserMapper userMapper;
@Test
public void testNe() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ne("name", "zhangsan");
// SELECT id,name,email,age FROM user WHERE (name <> ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

gt

gt("age", 20) -> age > 20

@Autowired
private UserMapper userMapper;
@Test
public void testGt() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.gt("age", 20);
// SELECT id,name,email,age FROM user WHERE (age > ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

ge

ge("age", 21) -> age >= 21

@Autowired
private UserMapper userMapper;
@Test
public void testGe() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 21);
// SELECT id,name,email,age FROM user WHERE (age >= ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

lt

lt("age", 21) -> age < 21

@Autowired
private UserMapper userMapper;
@Test
public void testLt() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.lt("age", 21);
// SELECT id,name,email,age FROM user WHERE (age < ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

le

le("age", 21) -> age <= 21

@Autowired
private UserMapper userMapper;
@Test
public void testLe() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.le("age", 21);
// SELECT id,name,email,age FROM user WHERE (age <= ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

between

between("age", 18, 25) -> age BETWEEN 18 AND 25

@Autowired
private UserMapper userMapper;
@Test
public void testBetween() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.between("age", 18, 25);
// SELECT id,name,email,age FROM user WHERE (age BETWEEN ? AND ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

notBetween

notBetween("age", 18, 25) -> age NOT BETWEEN 18 AND 25

@Autowired
private UserMapper userMapper;
@Test
public void testNotBetween() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notBetween("age", 18, 25);
// SELECT id,name,email,age FROM user WHERE (age NOT BETWEEN ? AND ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

like

like 匹配值 -> "%值%"

@Autowired
private UserMapper userMapper;
@Test
public void testLike() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "李");
// SELECT id,name,email,age FROM user WHERE (name LIKE ?) %李%(String)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

notLike

@Autowired
private UserMapper userMapper;
@Test
public void testNotLike() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notLike("name", "李");
// SELECT id,name,email,age FROM user WHERE (name NOT LIKE ?) %李%(String)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

likeLeft

likeLeft 匹配值 -> "%值"

@Autowired
private UserMapper userMapper;
@Test
public void testLikeLeft() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeLeft("name", "四");
// SELECT id,name,email,age FROM user WHERE (name LIKE ?) %四(String)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

likeRight

likeRight 匹配值 -> "值%"

@Autowired
private UserMapper userMapper;
@Test
public void testLikeRight() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "李");
// SELECT id,name,email,age FROM user WHERE (name LIKE ?) 李%(String)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

isNull

@Autowired
private UserMapper userMapper;
@Test
public void testIsNull() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.isNull("name");
// SELECT id,name,email,age FROM user WHERE (name IS NULL)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

isNotNull

@Autowired
private UserMapper userMapper;
@Test
public void testIsNotNull() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.isNotNull("name");
// SELECT id,name,email,age FROM user WHERE (name IS NOT NULL)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

in

in("name", "张三", "李四") -> name in ("张三", "李四")

@Autowired
private UserMapper userMapper;
@Test
public void testIn() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("name", "zs", "zhangsan");
// SELECT id,name,email,age FROM user WHERE (name IN (?,?))
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

notIn

notIn("name", "张三", "李四") -> name not in ("张三", "李四")

@Autowired
private UserMapper userMapper;
@Test
public void testNotIn() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notIn("name", "zs", "zhangsan");
// SELECT id,name,email,age FROM user WHERE (name NOT IN (?,?))
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

inSql

@Autowired
private UserMapper userMapper;
@Test
public void testInSql() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("age", "select age from user where id = 10");
// SELECT id,name,email,age FROM user WHERE (age IN (select age from user where id = 10))
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

notInSql

@Autowired
private UserMapper userMapper;
@Test
public void testNotInSql() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notInSql("age", "select age from user where id = 10");
// SELECT id,name,email,age FROM user WHERE (age NOT IN (select age from user where id = 10))
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

groupBy

groupBy 多个字段

@Autowired
private UserMapper userMapper;
@Test
public void testGroupBy() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("name, count(*) personNumbers");
queryWrapper.groupBy("name");
// SELECT id,name,email,age FROM user WHERE (age NOT IN (select age from user where id = 10))
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

orderByAsc

@Autowired
private UserMapper userMapper;
@Test
public void testOrderByAsc() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByAsc("name", "age");
// SELECT id,name,email,age FROM user ORDER BY name ASC,age ASC
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

orderByDesc

@Autowired
private UserMapper userMapper;
@Test
public void testOrderByDesc() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("name", "age");
// SELECT id,name,email,age FROM user ORDER BY name DESC,age DESC
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

orderBy

@Autowired
private UserMapper userMapper;
@Test
public void testOrderBy() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderBy(true, true, "name").orderBy(true, false, "age");
// SELECT id,name,email,age FROM user ORDER BY name ASC,age DESC
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

or

@Autowired
private UserMapper userMapper;
@Test
public void testOr() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "zs").or().eq("age", 21);
// SELECT id,name,email,age FROM user WHERE (name = ? OR age = ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

and

@Autowired
private UserMapper userMapper;
@Test
public void testAnd() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "zs").eq("age", 21);
// SELECT id,name,email,age FROM user WHERE (name = ? AND age = ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

last

@Autowired
private UserMapper userMapper;
@Test
public void testLast() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "zs").or().eq("age", 21).last("limit 1");
// SELECT id,name,email,age FROM user WHERE (name = ? OR age = ?) limit 1
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

exists

@Autowired
private UserMapper userMapper;
@Test
public void testExists() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.exists("select name from user where age > 21");
// SELECT id,name,email,age FROM user WHERE (EXISTS (select name from user where id = 1))
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

notExists

@Autowired
private UserMapper userMapper;
@Test
public void testNotExists() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notExists("select name from user where age > 21");
// SELECT id,name,email,age FROM user WHERE (NOT EXISTS (select name from user where id = 1))
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(user -> System.out.println(user));
}

分页查询

• 创建一个 config 类，定义方法，将分页拦截器注入到 spring 容器中

  package com.cmy.mybatis_plus.config;
  import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
  import org.springframework.context.annotation.Bean;
  import org.springframework.context.annotation.Configuration;
  /**
  * @Configuration 配置文件
  */
  @Configuration
  public class Config {
  /**
  * 定义方法，方法返回值是java 对象，放入到 spring 容器中
  */
  @Bean
  public PaginationInnerInterceptor paginationInnerInterceptor() {
  return new PaginationInnerInterceptor();
  }
  }
  

• 编写测试代码

  @Autowired
  private UserMapper userMapper;
  @Test
  public void testPage() {
  QueryWrapper<User> queryWrapper = new QueryWrapper<>();
  IPage<User> page = new Page<>();
  // 设置分页参数
  page.setCurrent(1); // 第一页
  page.setSize(3); // 每页的记录数
  IPage<User> result = userMapper.selectPage(page, queryWrapper);
  // 获取分页后的记录
  List<User> userList = result.getRecords();
  System.out.println("userList.size() = " + userList.size());
  // 分页的信息
  System.out.println("页数：" + result.getPages());
  System.out.println("总记录数：" + result.getTotal());
  System.out.println("当前页" + result.getCurrent());
  System.out.println("每页多少条记录：" + result.getSize());
  }
  

最后

以上就是饱满中心为你收集整理的MyBatis-Plus高级查询WrapperWrapper查询构造器查询实例的全部内容，希望文章能够帮你解决MyBatis-Plus高级查询WrapperWrapper查询构造器查询实例所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。