mybatis 实现递归查询出树结构节点
结构如下
[{
"children": [{
"id": 2,
"parentId": "1",
"sortName": "小文",
"grade": "B",
"children": []
}],
"id": 1,
"parentId": "0",
"sortName": "小张",
"grade": "A",
}]
思路
- 数据库sql:自查询,查询出所有的字段
- mybatis:需要使用resultMap嵌套collection,然后在connection中嵌套resultMap,然后再在resultMap中嵌套collection,最后在collection中嵌套最后一个resultMap
- 关于映射问题:因为从sql语句返回的就是具有三层关系的数据,所以需要用resultMap嵌套collection完成,
至于这三个entity会在代码上贴出
剩下的service层和controller就和普通的一样就可以了
代码实现
实体类
首先是实体类对象,需要通过resultMap和collection关联:
@Getter
@Setter
public class Sort {
private Long id;
private Long parentId;
private String sortName;
private String grade;
private Timestamp createTime;
private Timestamp updateTime;
@Transient
private List<Sort> children;
然后编写SQL语句,如下:
① mapper
List<Sort> getAllSort();
mapper.xml
单值传递
<resultMap id="BaseResultTreeMap" type="org.sang.bean.Sort">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="parent_id" jdbcType="BIGINT" property="parentId"/>
<result column="parent_Name" jdbcType="VARCHAR" property="parentName"/>
<result column="sort_name" jdbcType="VARCHAR" property="sortName"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
<!--使用mybatis collection 进行集合查询-->
<result column="grade" jdbcType="VARCHAR" property="grade"/>
<collection property="children" ofType="Sort" select="selectTree" column="id" javaType="java.util.ArrayList"/>
</resultMap>
<!--父级查询-->
<select id="getAllSort" resultMap="BaseResultTreeMap">
select * from sort s where s.id parent_id = 0 //从parent_id为0开始递归
</select>
<!--关联集合查询-->
<select id="selectTree" parameterType="String" resultMap="BaseResultTreeMap">
select * from sort s where s.parent_id=#{id}
</select>
注释:
- collection 即为嵌套的List配置
- property 为 private List children 的字段名 children
- ofType 为private List children 的类型Sort
- select 为要递归的sql语句
- column 上一条语句查询的结果作为下一条语句的参数
多值传递
将column改为
column="{id=id,grade=grade}"
结果如下
<resultMap id="BaseResultTreeMap" type="org.sang.bean.Sort">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="grade" jdbcType="VARCHAR" property="grade"/>
<result column="parent_id" jdbcType="BIGINT" property="parentId"/>
<result column="parent_Name" jdbcType="VARCHAR" property="parentName"/>
<result column="sort_name" jdbcType="VARCHAR" property="sortName"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
<!--使用mybatis collection 进行集合查询-->
<collection property="children" ofType="Sort" select="selectTree" column="{id=id,grade=grade}" javaType="java.util.ArrayList"/>
</resultMap>
<!--父级查询-->
<select id="getAllSort" resultMap="BaseResultTreeMap">
select * from sort s where s.parent_id = 0 //从parent_id为0开始递归
</select>
<!--关联集合查询-->
<select id="selectTree" parameterType="String" resultMap="BaseResultTreeMap">
select * from sort s where s.parent_id=#{id} and grade = #{grade}
</select>
如果没有在resultMap中配置需要直接使用,可以如下在参数中
把查到结果中对应的字段值 匹配 配置的 参数名,一一对应的设置为下一次查询的参数值。
grade = #{grade,jdbcType=VARCHAR}
<resultMap id="BaseResultTreeMap" type="org.sang.bean.Sort">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="grade" jdbcType="VARCHAR" property="grade"/>
<result column="parent_id" jdbcType="BIGINT" property="parentId"/>
<result column="parent_Name" jdbcType="VARCHAR" property="parentName"/>
<result column="sort_name" jdbcType="VARCHAR" property="sortName"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
<!--使用mybatis collection 进行集合查询-->
<collection property="children" ofType="Sort" select="selectTree" column="{id=id,grade=grade}" javaType="java.util.ArrayList"/>
</resultMap>
<!--父级查询-->
<select id="getAllSort" resultMap="BaseResultTreeMap">
select * from sort s where s.id parent_id = 0 //从parent_id为0开始递归
</select>
<!--关联集合查询-->
<select id="selectTree" parameterType="String" resultMap="BaseResultTreeMap">
select * from sort s where s.parent_id=#{id}
and grade = #{grade,jdbcType=VARCHAR}
</select>
递归完成,剩下的即为service和controller的业务代码