mybatis-plus分页递归查询
时间: 2023-11-17 15:03:03 浏览: 174
batis-plus是Mybatis的增强工具,在分页查询方面提供了很好的支持。下面是mybatis-plus分页递归查询的步骤:
1.在实体类中添加@TableField注解,指定父节点id的字段名和子节点集合的字段名。
2.在Mapper接口中添加递归查询方法,使用@Select注解指定SQL语句,使用@Param注解指定参数。
3.在XML文件中编写递归查询SQL语句,使用WITH RECURSIVE关键字实现递归查询,使用UNION ALL关键字将多个查询结果合并。
4.在Service层中调用递归查询方法,使用Page对象实现分页查询。
下面是mybatis-plus分页递归查询的示例代码:
1.实体类:
```java
@Data
@TableName("tree")
public class Tree {
@TableId(type = IdType.AUTO)
private Integer id;
private String name;
@TableField("parent_id")
private Integer parentId;
@TableField(exist = false)
private List<Tree> children;
}
```
2.Mapper接口:
```java
public interface TreeMapper extends BaseMapper<Tree> {
@Select("WITH RECURSIVE cte(id, name, parent_id) AS (SELECT id, name, parent_id FROM tree WHERE id = #{id} UNION ALL SELECT t.id, t.name, t.parent_id FROM tree t JOIN cte ON t.parent_id = cte.id) SELECT * FROM cte")
List<Tree> selectChildrenById(@Param("id") Integer id, Page<Tree> page);
}
```
3.XML文件:
```xml
<select id="selectChildrenById" resultMap="BaseResultMap">
WITH RECURSIVE cte(id, name, parent_id) AS (
SELECT id, name, parent_id FROM tree WHERE id = #{id}
UNION ALL
SELECT t.id, t.name, t.parent_id FROM tree t JOIN cte ON t.parent_id = cte.id
)
SELECT * FROM cte
LIMIT #{page.offset}, #{page.size}
</select>
```
4.Service层:
```java
@Service
public class TreeService extends ServiceImpl<TreeMapper, Tree> {
public IPage<Tree> selectChildrenById(Integer id, Page<Tree> page) {
List<Tree> list = baseMapper.selectChildrenById(id, page);
page.setRecords(list);
return page;
}
}
```
阅读全文
相关推荐












