前言
对于树形结构的数据库设计通常是基于继承关系设计的,也就是通过父ID关联来实现的,还有就是基于左右值编码设计。本文以继承关系设计的树形结构来讨论下MyBatis树形结构查询。以深度为二的树为例,要将这种结构的数据查询出来,通常的做法是先查询一级数据,再查询二级数据,然后在程序里组装起来,想了下,其实还可以更简单,不用分开两次,就能将不同级别数据一次性查询映射出来。
1.数据准备
以区域表为例,数据如下
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for area
-- ----------------------------
DROP TABLE IF EXISTS `area`;
CREATE TABLE `area` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'PK',
`area_name` varchar(50) NOT NULL COMMENT '地区名称',
`parent_id` int(11) NOT NULL COMMENT '父Id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7668 DEFAULT CHARSET=utf8 COMMENT='地区表';
-- ----------------------------
-- Records of area
-- ----------------------------
INSERT INTO `area` VALUES ('1', '广东省', '0');
INSERT INTO `area` VALUES ('2', '深圳市', '1');
INSERT INTO `area` VALUES ('3', '广州市', '1');
INSERT INTO `area` VALUES ('4', '湖南省', '0');
INSERT INTO `area` VALUES ('5', '长沙市', '4');
INSERT INTO `area` VALUES ('6', '株洲市', '4');
2.实体类
public class Area {
private Integer id;
private String areaName;
private Integer parentId;
List<Area> children;
//getter setter
}
3.查询sql
SELECT
t_top.id AS id1,
t_top.area_name AS area_name1,
t_top.parent_id AS parent_id1,
t_second.id AS id2,
t_second.area_name AS area_name2,
t_second.parent_id AS parent_id2
FROM
(
SELECT
id,
area_name,
parent_id
FROM
area
WHERE
parent_id = 0
) AS t_top
INNER JOIN (
SELECT
id,
area_name,
parent_id
FROM
area
WHERE
parent_id IN (
SELECT
id
FROM
area
WHERE
parent_id = 0
)
) AS t_second ON t_top.id = t_second.parent_id
基本思路就是将一级区域查询出来作为临时表t_top,将二级区域查询出来作为临时表t_second,然后将两张表做inner join操作,查询结果:
4.ResultMap映射
<resultMap id="TreeResultMap" type="com.domain.Area" >
<id column="id1" property="id" jdbcType="INTEGER" />
<result column="area_name1" property="areaName" jdbcType="VARCHAR" />
<result column="parent_id1" property="parentId" jdbcType="INTEGER" />
<collection property="children" ofType="com.domain.Area">
<id column="id2" property="id" jdbcType="INTEGER" />
<result column="area_name2" property="areaName" jdbcType="VARCHAR" />
<result column="parent_id2" property="parentId" jdbcType="INTEGER" />
</collection>
</resultMap>
单元测试结果: