MybatisPlus——条件构造器
MybatisPlus支持各种复杂的where条件,可以满足日常开发的所有需求
以下是BaseMapper<T>的各种方法,不难发现其中有很多Wrapper<T>
public interface BaseMapper<T> extends Mapper<T> {
int insert(T entity);
int deleteById(Serializable id);
int deleteById(T entity);
default int deleteByMap(Map<String, Object> columnMap) {
return this.delete((Wrapper)Wrappers.query().allEq(columnMap));
}
int delete(@Param("ew") Wrapper<T> queryWrapper);
int deleteBatchIds(@Param("coll") Collection<?> idList);
int updateById(@Param("et") T entity);
int update(@Param("et") T entity, @Param("ew") Wrapper<T> updateWrapper);
default int update(@Param("ew") Wrapper<T> updateWrapper) {
return this.update((Object)null, updateWrapper);
}
T selectById(Serializable id);
List<T> selectBatchIds(@Param("coll") Collection<? extends Serializable> idList);
void selectBatchIds(@Param("coll") Collection<? extends Serializable> idList, ResultHandler<T> resultHandler);
default List<T> selectByMap(Map<String, Object> columnMap) {
return this.selectList((Wrapper)Wrappers.query().allEq(columnMap));
}
default void selectByMap(Map<String, Object> columnMap, ResultHandler<T> resultHandler) {
this.selectList((Wrapper)Wrappers.query().allEq(columnMap), resultHandler);
}
default T selectOne(@Param("ew") Wrapper<T> queryWrapper) {
return this.selectOne(queryWrapper, true);
}
default T selectOne(@Param("ew") Wrapper<T> queryWrapper, boolean throwEx) {
List<T> list = this.selectList(queryWrapper);
int size = list.size();
if (size == 1) {
return list.get(0);
} else if (size > 1) {
if (throwEx) {
throw new TooManyResultsException("Expected one result (or null) to be returned by selectOne(), but found: " + size);
} else {
return list.get(0);
}
} else {
return null;
}
}
default boolean exists(Wrapper<T> queryWrapper) {
Long count = this.selectCount(queryWrapper);
return null != count && count > 0L;
}
Long selectCount(@Param("ew") Wrapper<T> queryWrapper);
List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);
void selectList(@Param("ew") Wrapper<T> queryWrapper, ResultHandler<T> resultHandler);
List<T> selectList(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper);
void selectList(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper, ResultHandler<T> resultHandler);
List<Map<String, Object>> selectMaps(@Param("ew") Wrapper<T> queryWrapper);
void selectMaps(@Param("ew") Wrapper<T> queryWrapper, ResultHandler<Map<String, Object>> resultHandler);
List<Map<String, Object>> selectMaps(IPage<? extends Map<String, Object>> page, @Param("ew") Wrapper<T> queryWrapper);
void selectMaps(IPage<? extends Map<String, Object>> page, @Param("ew") Wrapper<T> queryWrapper, ResultHandler<Map<String, Object>> resultHandler);
<E> List<E> selectObjs(@Param("ew") Wrapper<T> queryWrapper);
<E> void selectObjs(@Param("ew") Wrapper<T> queryWrapper, ResultHandler<E> resultHandler);
default <P extends IPage<T>> P selectPage(P page, @Param("ew") Wrapper<T> queryWrapper) {
page.setRecords(this.selectList(page, queryWrapper));
return page;
}
default <P extends IPage<Map<String, Object>>> P selectMapsPage(P page, @Param("ew") Wrapper<T> queryWrapper) {
page.setRecords(this.selectMaps(page, queryWrapper));
return page;
}
}
以下是Wrapper相关的关系图
实例:基于QureyWrapper的查询
以为例:查询出名字中带l的,年龄大于等于30的人的id,username,password,age字段
1.建立数据库
2.建表sql
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 50615
Source Host : localhost:3306
Source Schema : mybatis
Target Server Type : MySQL
Target Server Version : 50615
File Encoding : 65001
Date: 15/04/2024 07:39:36
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for a_user
-- ----------------------------
DROP TABLE IF EXISTS `a_user`;
CREATE TABLE `a_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`salary` decimal(10, 2) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;
3.正常sql语句查询
SELECT id,user_name,password,age
FROM a_user
WHERE user_name like ? AND age >= ?
4.创建实体类
package com.example.mybatisplus03.entity;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName("a_user")
public class User {
private Integer id;
private String userName;
private String password;
private Integer age;
private float salary;
}
5.创建UserMapper接口
package com.example.mybatisplus03.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mybatisplus03.entity.User;
public interface UserMapper extends BaseMapper<User> {
}
6.在Test测试类中写查询方法
package com.example.mybatisplus03;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.mybatisplus03.entity.User;
import com.example.mybatisplus03.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class TestUser {
@Autowired
private UserMapper userMapper;
@Test
void testSelect() {
System.out.println(("----- selectAll method test ------"));
//构建查询条件
QueryWrapper<User> wrapper = new QueryWrapper<User>()
.select("id","user_name","password","age")
.like("user_name","l")
.ge("age",30);
// 查询所有用户
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
}
7.查询结果
以为例:更新用户名为lcs的用户的年龄为20
1.正常sql语句查询
UPDATE a_user
SET age=20
WHERE (user_name = "lcs")
2. 在Test测试类中写更新方法
@Test
void testUpdate() {
System.out.println(("----- insert method test ------"));
//1.更新的数据
User user = new User();
user.setAge(20);
//2.更新的条件
QueryWrapper<User> wrapper = new QueryWrapper<User>()
.eq("user_name", "lcs");
//3.更新
int update = userMapper.update(user, wrapper);
System.out.println("update = " + update);
}
3.更新结果
实例:基于UpdateWrapper的查询
以为例:更新id为1,2,6,9的用户的年龄加1
1.正常sql语句查询
UPDATE a_user
SET age = age + 1
where id in (1,2,6,9)
2. 在Test测试类中写更新方法
@Test
void testUpdateWrapper() {
System.out.println(("----- delete method test ------"));
List<Integer> ids = List.of(1, 2, 6, 9);
UpdateWrapper<User> wrapper = new UpdateWrapper<User>()
.setSql("age = age + 1")
.in("id", ids);
int update = userMapper.update(null, wrapper);
System.out.println("update = " + update);
}
3.更新结果
实例:基于LambdaQueryWrapper的查询
第一种方法
第二种方法:采用了反射机制,避免硬编码机制
@Test
void testLambdaQueryWrapper() {
System.out.println(("----- selectAll method test ------"));
//构建查询条件
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<User>()
.select(User::getId,User::getUserName,User::getPassword,User::getAge)
.like(User::getUserName,"l")
.ge(User::getAge,30);
// 查询所有用户
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}