mybatis一对多高效级联查询

使用场景：对于有父子关系的一对多级联查询，最简单的比如一条sql返回省市区的级联关系

数据库关系图：

 

 

数据库：

省份表

CREATE TABLE `position_provice` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`provice_id` int(11) unsigned NOT NULL COMMENT '省份id、省份编号',
`provice_name` char(32) NOT NULL COMMENT '省份名称',
PRIMARY KEY (`id`),
UNIQUE KEY `provice_id` (`provice_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

市级

CREATE TABLE `position_city` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`province_id` int(10) unsigned NOT NULL COMMENT '省id（父id）',
`city_id` bigint(20) unsigned NOT NULL COMMENT '市id',
`city_name` char(64) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `city_id` (`city_id`) USING BTREE,
KEY `province_id` (`province_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=346 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

 

区县

CREATE TABLE `position_county` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '县级主键',
`city_id` bigint(20) unsigned NOT NULL COMMENT '市id（父id）',
`county_id` bigint(20) unsigned NOT NULL COMMENT '县级id',
`county_name` char(64) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `county_id` (`county_id`) USING BTREE,
KEY `city_id` (`city_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2857 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

 

实体类VO关系：

/**
 * 省级实体
 * Created by Admin on 2019/2/15.
 */
public class Province implements Serializable {

    private static final long serialVersionUID = 7513759370791928018L;

    private String value;

    private String label;

    private List<City> children;

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    public String getLabel() {
        return label;
    }

    public void setLabel(String label) {
        this.label = label;
    }

    public List<City> getChildren() {
        return children;
    }

    public void setChildren(List<City> children) {
        this.children = children;
    }
}

 

/**
 * 市级实体
 * Created by Admin on 2019/2/15.
 */
public class City  implements Serializable{

    private static final long serialVersionUID = 6804704927100589037L;

    private String value;

    private String label;

    private String pId;
    
    private List<Region> children;

 

 

 

/**
 * 区级实体
 * Created by Admin on 2019/2/15.
 */
public class Region implements Serializable {


    private static final long serialVersionUID = 5384031670312405686L;

    private String value;

    private String label;

    private String cId;

    public String getcId() {
        return cId;
    }

 

 

 mybatis：

    <resultMap id="ProvinceMap" type="cn.grains.dubbo.system.Model.Province" >
        <result column="provice_id" property="label" jdbcType="VARCHAR" />
        <result column="provice_name" property="value" jdbcType="VARCHAR"/>
        <collection property="children" ofType="cn.grains.dubbo.system.Model.City">
            <result column="city_id" property="label"/>
            <result column="city_name" property="value"/>
            <collection property="children" ofType="cn.grains.dubbo.system.Model.Region">
                <result column="county_id" property="label"/>
                <result column="county_name" property="value"/>
            </collection>
        </collection>
    </resultMap>


    <select id="getSpecificPlace" resultMap="ProvinceMap">
        SELECT *FROM position_provice pp
        LEFT JOIN position_city pci ON pp.provice_id = pci.province_id
        LEFT JOIN position_county pco ON pci.city_id = pco.city_id
    </select>

运行查看结果：

 

 

 

 

 

 结果完全对的上，OK。

我们再来看看mybatis的标签<collection >

property：VO关联字段

ofType：从表所对应的VO。