Mybatis -plus -jion
是一个 MyBatis-Plus 的增强工具，在 MyBatis-Plus 的基础上只做增强不做改变，为简化开发、提高效率而生。
- **无侵入**：只做增强不做改变，引入它不会对现有工程产生影响，如丝般顺滑
- **损耗小**：启动即会自动注入基本 JOIN，性能基本无损耗，直接面向对象操作
- **支持 Lambda 形式调用**：通过 Lambda 表达式，方便的编写各类查询条件，无需再担心字段写错
- **无学习成本**：支持MP风格的查询, 您会MP就会MPJ, 无需额外的学习成本
- **完美适配**：兼容MP的别名、逻辑删除、枚举列、TypeHandle列等特性
- **注解支持**：支持注解形式一对一、一对多和连表查询形式的一对一和一对多
继承与BaseMapper接口，所以可以使用 BaseMapper的方法。
1、jion的依赖和与配置文件
1.依赖：
<dependency><groupId>com.github.yulichang</groupId><artifactId>mybatis-plus-join-boot-starter</artifactId><version>1.5.2</version>
</dependency>
2.让Dao层实现MPJBaseMapperch接口 、Service实现、实现类impl 类实现接口
@Mapper
public interface UserMapper extends MPJBaseMapper<User> {}public interface UserService extends MPJBaseService<User> {}@Service
public class UserServiceImpl extends MPJBaseServiceImpl<UserMapper, User> implements UserService {}
3.配置文件
mybatis-plus-join:
#是否打印 mybatis plus join banner 默认true
banner: true
#全局启用副表逻辑删除(默认true) 关闭后关联查询不会加副表逻辑删除
sub-table-logic: true
#拦截器MappedStatement缓存(默认true)
ms-cache: true
#表别名(默认 t)
table-alias: t
#副表逻辑删除条件的位置，支持where、on
#默认ON （1.4.7.2及之前版本默认为where）
logic-del-type: on
4.额外的注解映射
@Data
@TableName("user")
public class User {@TableIdprivate Integer id;private Integer pid;//父id/* 其他属性略 *//*** 查询上级 一对一*/@TableField(exist = false)@EntityMapping(thisField = "pid", joinField = "id")private User parentUser;/*** 查询下级 一对多*/@TableField(exist = false)@EntityMapping(thisField = "id", joinField = "pid")private List<User> childUser;/*** 带条件的查询下级 一对多*/@TableField(exist = false)@EntityMapping(thisField = "id", joinField = "pid",condition = {//sex = '0' 默认条件是等于@Condition(column = "sex", value = "0"),//name like '%a%'@Condition(column = "name", value = "张三", keyWord = SqlKeyword.LIKE)},apply = @Apply(value = "id between 1 and 20"))//拼接sql 同 wrapper.apply()private List<User> childUserCondition;/*** 查询地址 (一对多)*/@TableField(exist = false)@EntityMapping(thisField = "id", joinField = "userId")private List<Address> addressList;/*** 绑定字段 （一对多）*/@TableField(exist = false)@FieldMapping(tag = User.class, thisField = "id", joinField = "pid", select = "id")private List<Integer> childIds;
}
2、MPJBaseMapper 接口的 中的方法的使用
代码结构使用注意：
MPJLambdaWrapper<主表> wrapper = new MPJLambdaWrapper<主表.class>().leftJoin(从表.class, 从表::getUserId, 主表::getId).eq(主表::getId, 2);
Integer count = userMapper.selectJoinCount(对应需要封装的实体.class,wrapper);
System.out.println(count);
1.selectJoinCount（）联表查询总的条数
MPJLambdaWrapper<User> wrapper = new MPJLambdaWrapper<User>().leftJoin(Address.class, Address::getUserId, User::getId).eq(User::getId, 2);
Integer count = userMapper.selectJoinCount(wrapper);
System.out.println(count);
1. 对应的sql语句
SELECT COUNT(*)
FROM user tLEFT JOIN address t1 ON (t1.user_id = t.id)
WHERE (t.id = ?)
2.selectJoinOne() 联表查询一条记录
MPJLambdaWrapper<User> wrapper = new MPJLambdaWrapper<User>().selectAll(User.class).select(Address::getAddress).leftJoin(Address.class, Address::getUserId,User::getId).eq(User::getId, 2);
// DTO 自己封装的包含两个实体类字段的 类UserDTO dto = userMapper.selectJoinOne(UserDTO.class, wrapper);
1. 对应的sql语句
SELECT t.id,t.name,t.sex,t.head_img,t1.address
FROM user t LEFT JOIN address t1 ON t1.user_id = t.id
WHERE (t.id = 2)
3.selectJoinList() 查询复合条件的所有记录
MPJLambdaWrapper<User> wrapper = new MPJLambdaWrapper<User>().selectAll(User.class).select(Address::getTel).leftJoin(Address.class, Address::getUserId, User::getId).eq(User::getId, 2);List<UserDTO> list = userMapper.selectJoinList(UserDTO.class, wrapper);}
1. 对应的sql语句
SELECT t.id,t.name,t.sex,t.head_img,t1.tel
FROM user tLEFT JOIN address t1 ON t1.user_id = t.id
WHERE (t.id = 2)
4.selectJoinPage(new Page<>(1, 10), UserDTO.class, wrapper) jion的分页查询
1. new Page<>(1, 10)：分一页，10条数据。
MPJLambdaWrapper<User> wrapper = new MPJLambdaWrapper<User>().selectAll(User.class).select(Address::getAddress).leftJoin(Address.class, Address::getUserId, User::getId).eq(User::getId, 1);IPage<UserDTO> page = userMapper.selectJoinPage(new Page<>(1, 10), UserDTO.class, wrapper);
2. 对应的sql语句
5.selectJoinMap() 联表查询一条记录，返回Map集合 类似selectJoinOne()
MPJLambdaWrapper<User> wrapper = new MPJLambdaWrapper<User>().selectAll(User.class).select(Address::getAddress).leftJoin(Address.class, Address::getUserId,User::getId).eq(User::getId, 2);
// DTO 自己封装的包含两个实体类字段的 类Map<UserDTO> dtoMap = userMapper.selectJoinMap(UserDTO.class, wrapper);
1. 对应的sql语句
SELECT t.id,t.name,t.sex,t.head_img,t1.address
FROM user t LEFT JOIN address t1 ON t1.user_id = t.id
WHERE (t.id = 2)
6.selectJoinMaps () 连表查询所有命中记录,返回List
public List<Map<String, Object>> getUserWithRoleInfo() {// 使用lambda表达式构建关联查询条件LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();// 进行内联查询，关联role表，关联条件是user表的roleId等于role表的idreturn userMapper.selectJoinMaps(queryWrapper, User.class, "role",user -> user.getRoleId().equals(joiner.table("role").get("id")));
7.selectJoinMapsPage() 连表分页查询返回命中记录,返回List
public Page<Map<String, Object>> getUserWithRoleInfoPageByRoleName(int currentPage, int pageSize, String roleName) {Page<User> page = new Page<>(currentPage, pageSize);LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();// 添加筛选条件，通过子查询找到符合角色名的role表中的id，再匹配user表中的roleIdqueryWrapper.inSql(User::getRoleId, "select id from role where role_name = '" + roleName + "'");return userMapper.selectJoinMapsPage(page, queryWrapper, User.class, "role",user -> user.getRoleId().equals(joiner.table("role").get("id")));}
8.deleteJoin 联表删除，支持逻辑删除
// yml文件配置开启逻辑删除
mybatis-plus-join:#关闭副表逻辑删除 默认开启(true)sub-table-logic: false
9.deleteJion() 联表删除符合的主表的记录，返回 int 条数
DeleteJoinWrapper<User> wrapper = JoinWrappers.delete(User.class).leftJoin(Address.class, Address::getUserId, User::getId).leftJoin(Area.class, Area::getId, Address::getAreaId).eq(User::getId, 1);//删除主表User的记录int i = userMapper.deleteJoin(wrapper);
1. 对应的sql语句
==> Preparing: DELETE t FROM `user` t LEFT JOIN address t1 ON (t1.user_id = t.id) LEFT JOIN area t2 ON (t2.id = t1.area_id) WHERE (t.id = 1)
==> Parameters: 1(Integer)
<== Updates: 1
10.deleteAll() 联表删除表全部的记录.
DeleteJoinWrapper<User> wrapper = JoinWrappers.delete(User.class)//删除全部的表数据 (主表和副表).deleteAll()//也可以删除指定的表数据,调用 delete() 传要删除的实体类class 如下//.delete(User.class, Address.class, Area.class).leftJoin(Address.class, Address::getUserId, User::getId).leftJoin(Area.class, Area::getId, Address::getAreaId).eq(User::getId, 1);int i = userMapper.deleteJoin(wrapper);
1. 对应的sql语句
==> Preparing: DELETE t,t1,t2 FROM `user` t LEFT JOIN address t1 ON (t1.user_id = t.id) LEFT JOIN area t2 ON (t2.id = t1.area_id) WHERE (t.id = ?)
==> Parameters: 1(Integer)
<== Updates: 19
11.update 联表修改表记录
12.updateJion（）、wrapper设置set语句和where条
UpdateJoinWrapper<User> update = JoinWrappers.update(User.class).set(User::getName, "aaaaaa").set(Address::getAddress, "bbbbb").leftJoin(Address.class, Address::getUserId, User::getId).eq(User::getId, 1);int i = userMapper.updateJoin(null, update);
1. 对应的sql语句
UPDATE`user` tLEFT JOIN address t1
ON (t1.user_id = t.id)SET t.`name`=?, t1.address=?
WHERE (t.id = ?)
13.updateJoinAndNull () 联表更新，更新字段可以为null
UpdateJoinWrapper<User> update = JoinWrappers.update(User.class).set(User::getName, "aaaaaa").set(Address::getAddress, "bbbbb").leftJoin(Address.class, Address::getUserId, User::getId).eq(User::getId, 1);int i = userMapper.updateJoinAndNull(null, update);
1. 对应的sql语句
UPDATE`user` tLEFT JOIN address t1
ON (t1.user_id = t.id)SET t.`name`=?, t1.address=?
WHERE (t.id = ?)
3、MPJlambadaWarpper 的方法的使用
1.distinct 去重
wrapper.distinct()
2.select 查询指定字段
//单个字段
select(User::getId)
//多个字段
select(User::getId,User::getSex,User::getName)
3.selectFilter() 过滤字段
//只要 java 字段名以 "test" 开头的
selectFilter(User.class, i -> i.getColumProperty().startsWith("test"))
// 只要 数据库 字段名以 "test" 开头的
.selectFilter(User.class, i -> i.getColumn().startsWith("test"))
// 只要 java 字段类型是String的
.selectFilter(User.class, e -> e.getColumnType() == String.class)
4.selectAll() 去重 查询类的全部字段
// 查询指定类的全部字段
wrapper.selectAll(User.class)
// 查询User类的全部字段，除了name，支持可变参数
wrapper.selectAll(User.class, User::getName)
5.selectAsClass 查询两个类的交集字段
// t.name AS nickname
wrapper.selectAsClass("t.name", UserDTO::getNickname))
6.selectFunc sql函数
// 支持定义别名
selectSum(User::getId) -> SUM(t.id) AS id
selectCount(User::getId) -> COUNT(t.id) AS id
selectMax(User::getId) -> MAX(t.id) AS id
selectMin(User::getId) -> MIN(t.id) AS id
selectAvg(User::getId) -> AVG(t.id) AS id
selectLen(User::getId) -> LEN(t.id) AS id
7.selectCollection 一对多查询，类中存在其他类的集合
1. 将Address类的字段全部映射到UserDTO类的 addressLis集合中
.selectCollection(Address.class, UserDTO::getAddressList)
2. <font style="color:#ECAA04;">指定实体字段映射</font>(只映射 id 和 address 两个字段) id、result方法对应mybatis中ResultMap里的 `<id>` 和 `<result>` 标签
.selectCollection(Address.class, UserDTO::getAddressList, map -> map//此处只能映射 Address.class 中的字段到 UserDTO::getAddressList 中.id(Address::getId).result(Address::getAddress)//别名映射.result(Address::getAddress, AddressDTO::getAddress));
3. 字段映射，把address表中的id映射到UserDTO的 `List<String>` 属性的addressIds字段中
.selectCollection(Address.class, UserDTO::getAddressIds, map -> map.result(Address::getId))
4. 不指定实体字段映射(只映射 id 和 address 两个字段) id、result方法对应mybatis中ResultMap里的 `<id>` 和 `<result>` 标签
.selectCollection(UserDTO::getAddressList, map -> map//可以映射不同类的字段 到 UserDTO::getAddressList 中 比如 Address 和 User.id(Address::getId).result(User::getName, AddressDTO::getAddress)//别名映射.result(User::getAddr, AddressDTO::getAddress));
8.selectAssociation 一对一查询
// 支持定义别名
selectSum(User::getId) -> SUM(t.id) AS id
selectCount(User::getId) -> COUNT(t.id) AS id
selectMax(User::getId) -> MAX(t.id) AS id
selectMin(User::getId) -> MIN(t.id) AS id
selectAvg(User::getId) -> AVG(t.id) AS id
selectLen(User::getId) -> LEN(t.id) AS id
9.selectSub 子查询
MPJLambdaWrapper<User> wrapper = JoinWrappers.lambda(User.class).selectSub(User.class, w -> w.select(User::getId).eq(User::getId, User::getId).last("limit 1"), User::getId).leftJoin(Address.class, Address::getUserId, User::getId).le(User::getId, 100);wrapper.list();
1. 对应的字段
SELECT (SELECT st.id FROM `user` st WHERE (st.id = t.id) limit 1 ) AS id
FROM `user` t LEFT JOIN address t1
ON (t1.user_id = t.id)
WHERE t1.del= false AND (t.id <= ?)
10.selectFunc sql函数
// 支持定义别名
selectSum(User::getId) -> SUM(t.id) AS id
selectCount(User::getId) -> COUNT(t.id) AS id
selectMax(User::getId) -> MAX(t.id) AS id
selectMin(User::getId) -> MIN(t.id) AS id
selectAvg(User::getId) -> AVG(t.id) AS id
selectLen(User::getId) -> LEN(t.id) AS id
4、union/union all 联合查询
1.实例
MPJLambdaWrapper<User> w = JoinWrappers.lambda(User.class).selectAll(User.class).union(User.class, union -> union.selectAll(User.class)).union(User.class, union -> union.selectAll(User.class));//union all 调用unionAll即可 如下//.unionAll(User.class, union -> union...);w.list();
1. 对应的sql
SELECT t.id,t.pid,t.`name`,t.`json`,t.sex,t.head_img,t.create_time
FROM `user` t
UNION
SELECT t.id,t.pid,t.`name`,t.`json`,t.sex,t.head_img,t.create_time
FROM `user` t
UNION
SELECT t.id,t.pid,t.`name`,t.`json`,t.sex,t.head_img,t.create_time
FROM `user` t
5、条件构造器
无需编写繁琐的 SQL 语句，从而提高开发效率并减少 SQL 注入的风险。
所有的 QueryWrapper、UpdateWrapper、LambdaQueryWrapper 和 LambdaUpdateWrapper 都继承自 AbstractWrapper。
1.allEq 方法是 MyBatis-Plus 中用于构建查询条件的方法之一，它允许我们通过一个 Map 来设置多个字段的相等条件。
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.allEq(Map.of("id", 1, "name", "老王", "age", null));
-- 普通 Wrapper 和 Lambda Wrapper 生成的 SQL 相同
SELECT * FROM user WHERE id = 1 AND name = '老王' AND age IS NULL
2.eq()方法是 它允许我们通过一个字段的相等条件。ne()反之
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.eq(User::getName, "老王");
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "老王");
3.gt()方法是 ，它允许我们设置单个字段大于条件。ge() 大于等于 it() 小于 le() 小于等于
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.allEq(Map.of("id", 1, "name", "老王", "age", null));
-- 普通 Wrapper 和 Lambda Wrapper 生成的 SQL 相同
SELECT * FROM user WHERE id = 1 AND name = '老王' AND age IS NULL
4.beetween() 设置一个字段的between条件。notbeetween()反之
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.between("age", 18, 30);
-- 普通 Wrapper 和 Lambda Wrapper 生成的 SQL 相同
SELECT * FROM user WHERE age BETWEEN 18 AND 30
5.like() 设置一个字段的模糊查询。notlike()反之
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "王");
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.like(User::getName, "王");
6.likeLeft() 设置一个字段的左模糊条件。likeRight()反之
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeLeft("name", "王");
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.likeLeft(User::getName, "王");
7.isNull() 设置单个字段的 非空 条件
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.isNull("name");LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.isNull(User::getName);
8.in() 设置一个字段的 IN 条件。notIn()反之
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notIn("age", Arrays.asList(1, 2, 3));LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.notIn(User::getAge, Arrays.asList(1, 2, 3));
9.inSql() 设置一个字段的 IN 条件、可以写sql语句 notInSql()反之
QQueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("age", "1,2,3,4,5,6");
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.inSql(User::getAge, "1,2,3,4,5,6");
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("id", "select id from other_table where id < 3");
10.eqSql() 你设置一个字段=某个 SQL 语句的结果。这个方法特别适用于需要将字段值与子查询结果进行比较的场景。
11.gtSql() :>某个sql、geSql():>=某个sql、ltSql()<某个sql、leSql()<=某个sql
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.geSql("id", "select id from table where name = 'xx'");LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.geSql(User::getId, "select id from table where name = 'xx'");
对应的sql
-- 普通 Wrapper 和 Lambda Wrapper 生成的 SQL 相同
SELECT * FROM user WHERE id >= (select id from table where name = 'xx')
12.groupBy() 设置字段的分组条件。having：条件
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.groupBy("age").having("sum(age) > 10");LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.groupBy(User::getAge).having("sum(age) > {0}", 10);
13.orderByAsc() 查询结果的升序排序条件。通过指定一个或多个字段，orderByDesc() 反之
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByAsc("id", "name");LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.orderByAsc(User::getId, User::getName);
14.orderBy() 设置一个字段的排序方法。true:Asc false:Desc
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderBy(true, true, "id", "name");LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.orderBy(true, true, User::getId, User::getName);
15.func() 提供了一种在链式调用中根据条件执行不同查询操作的机制。通过传入一个 Consumer 函数式接口，func 方法允许你在不中断链式调用的情况下，根据条件执行不同的查询构建逻辑。
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.func(i -> {if (true) {i.eq("id", 1);} else {i.ne("id", 1);}
});
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.func(i -> {if (true) {i.eq(User::getId, 1);} else {i.ne(User::getId, 1);}
});
-- 根据条件生成的 SQL 会有所不同
-- 如果条件为 true，则生成的 SQL 为：
SELECT * FROM user WHERE id = 1-- 如果条件为 false，则生成的 SQL 为：
SELECT * FROM user WHERE id != 1
16.or() 通过调用 or 方法，可以改变后续查询条件的连接方式，从默认的 AND 连接变为 OR 连接。
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("id", 1).or().eq("name", "老王");
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.eq(User::getId, 1).or().eq(User::getName, "老王");
-- 普通 Wrapper 和 Lambda Wrapper 生成的 SQL 相同
SELECT * FROM user WHERE id = 1 OR name = '老王'
17.and() 在查询条件中添加 AND 逻辑。通过调用 and 方法，可以创建 AND 嵌套条件，即在一个 AND 逻辑块中包含多个查询条件。
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.and(i -> i.and(j -> j.eq("name", "李白").eq("status", "alive")).and(j -> j.eq("name", "杜甫").eq("status", "alive")));LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.and(i -> i.and(j -> j.eq(User::getName, "李白").eq(User::getStatus, "alive")).and(j -> j.eq(User::getName, "杜甫").eq(User::getStatus, "alive")));
-- 普通 Wrapper 和 Lambda Wrapper 生成的 SQL 相同
SELECT * FROM user WHERE ((name = '李白' AND status = 'alive') AND (name = '杜甫' AND status = 'alive'))
18.nested() 创建一个独立的查询条件块，不带默认的 AND 或 OR 逻辑。通过调用 nested 方法，可以在查询条件中添加一个嵌套的子句，该子句可以包含多个查询条件，并且可以被外部查询条件通过 AND 或 OR 连接。
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.nested(i -> i.eq("name", "李白").ne("status", "活着"));LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.nested(i -> i.eq(User::getName, "李白").ne(User::getStatus, "活着"));
-- 普通 Wrapper 和 Lambda Wrapper 生成的 SQL 相同
SELECT * FROM user WHERE (name = '李白' AND status <> '活着')
19.apply() 直接拼接 SQL 片段到查询条件中。这个方法特别适用于需要使用数据库函数或其他复杂 SQL 构造的场景。
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.apply("id = 1");LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.apply("date_format(dateColumn, '%Y-%m-%d') = '2008-08-08'");
//使用参数占位符
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.apply("date_format(dateColumn, '%Y-%m-%d') = {0}", "2008-08-08");
-- 普通 Wrapper 生成的 SQL
SELECT * FROM user WHERE id = 1-- Lambda Wrapper 生成的 SQL
SELECT * FROM user WHERE date_format(dateColumn, '%Y-%m-%d') = '2008-08-08'-- 使用参数占位符生成的 SQL
SELECT * FROM user WHERE date_format(dateColumn, '%Y-%m-%d') = '2008-08-08'
20.last() 直接在查询的最后添加一个 SQL 片段，而不受 MyBatis-Plus 的查询优化规则影响。这个方法应该谨慎使用，因为它可能会绕过 MyBatis-Plus 的查询优化。
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.last("limit 1");LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.last("limit 1");
-- 普通 Wrapper 和 Lambda Wrapper 生成的 SQL 相同
SELECT * FROM user LIMIT 1
21.exists() 在查询中添加一个 EXISTS 子查询。 notExists()反之
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.exists("select id from table where age = 1");LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.exists("select id from table where age = 1");
-- 普通 Wrapper 和 Lambda Wrapper 生成的 SQL 相同
SELECT * FROM user WHERE EXISTS (select id from table where age = 1)
22.select() 指定在查询结果中包含哪些字段,predicate 可以过滤字段
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id", "name", "age");LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.select(User::getId, User::getName, User::getAge);QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select(i -> i.getProperty().startsWith("test"));
-- 普通 Wrapper 和 Lambda Wrapper 生成的 SQL 相同
SELECT id, name, age FROM user-- 使用 Predicate 过滤字段生成的 SQL
SELECT testField1, testField2 FROM user
23.set() 设置更新语句中的 SET 字段、可以设置为null
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.set("name", "老李头");LambdaUpdateWrapper<User> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
lambdaUpdateWrapper.set(User::getName, "老李头");
-- 普通 Wrapper 和 Lambda Wrapper 生成的 SQL 相同
UPDATE user SET name = '老李头'
24.setSql() 允许你直接设置更新语句中的 SET 部分 SQL语句
setSql("name = '老李头'")
setSql("dateColumn={0}", LocalDate.now())
setSql("type={0,javaType=int,jdbcType=NUMERIC,typeHandler=xxx.xxx.MyTypeHandler}", "待处理字符串");
25.setIncrBy() 允许你指定一个字段，并使其在数据库中的值增加指定的数值。setDecrBy()反之
UpdateWrapper<Product> updateWrapper = new UpdateWrapper<>();
updateWrapper.setIncrBy(Product::getNum, 1);LambdaUpdateWrapper<Product> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
lambdaUpdateWrapper.setIncrBy(Product::getNum, 1);
L-- 普通 Wrapper 和 Lambda Wrapper 生成的 SQL 相同
UPDATE product SET num = num + 1
26.orderBy() 设置一个字段的排序方法。true:Asc false:Desc
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderBy(true, true, "id", "name");
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.orderBy(true, true, User::getId, User::getName);
27.orderBy() 设置一个字段的排序方法。true:Asc false:Desc
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderBy(true, true, "id", "name");
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.orderBy(true, true, User::getId, User::getName);
28.orderBy() 设置一个字段的排序方法。true:Asc false:Desc
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderBy(true, true, "id", "name");
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.orderBy(true, true, User::getId, User::getName);
6、自定义查询
1.使用@select()注解
public interface UserMapper extends BaseMapper<User> {@Select("SELECT * FROM user ${ew.customSqlSegment}")List<User> selectByCustomSql(@Param(Constants.WRAPPER) Wrapper<User> wrapper);
}
要使用自定义 SQL，只需调用上述方法并传入一个 Wrapper 对象：
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "张三");List<User> userList = userMapper.selectByCustomSql(queryWrapper);
2.使用xml 查询
List<MysqlData> getAll(Wrapper ew);
<select id="getAll" resultType="MysqlData">SELECT * FROM mysql_data ${ew.customSqlSegment}
</select>