Mybatis一对多嵌套查询

---

---

Mybatis中如何用一次调用做到嵌套查询，把数据放入嵌套的实例对象中？这里以省份-城市为例，看

查省份后查城市，实际只要调用查省份的接口就会自动查省份+城市，转位json最后效果如下

[
    {
        "value": "上海市",
        "label": "上海市",                                                                      
        "children": [
            {
                "value": "上海城区",
                "label": "上海城区",
                "children": null
            }
        ]
    },
    {
        "value": "内蒙古自治区",
        "label": "内蒙古自治区",
        "children": [
            {
                "value": "鄂尔多斯市",
                "label": "鄂尔多斯市",
                "children": null
            }
        ]
    },
    {
        "value": "北京市",
        "label": "北京市",
        "children": [
            {
                "value": "北京城区",
                "label": "北京城区",
                "children": null
            }
        ]
    },
    {
        "value": "吉林省",
        "label": "吉林省",
        "children": [
            {
                "value": "吉林市",
                "label": "吉林市",
                "children": null
            },
            {
                "value": "四平市",
                "label": "四平市",
                "children": null
            },
            {
                "value": "白城市",
                "label": "白城市",
                "children": null
            },
            {
                "value": "辽源市",
                "label": "辽源市",
                "children": null
            },
            {
                "value": "长春市",
                "label": "长春市",
                "children": null
            }
        ]
    }
]

AreaRegionListResult.java

接收收据实体类

package com.sabthever.model.result.supplier;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.List;

@ApiModel(description = "省市下拉框返回参数")
@Data
public class AreaRegionListResult {
    @ApiModelProperty("值")
    private String value;

    @ApiModelProperty("标签")
    private String label;

    @ApiModelProperty("子结构")
    private List<AreaRegionListResult> children;
}

Mapper.java中

package com.sabthever.mapper;

@Mapper
@Repository
public interface SupplierAreaMapper {

    public List<AreaRegionListResult> selectProvinces(@Param("department") String department);

    public List<AreaRegionListResult> selectCities(@Param("department") String department,@Param("province") String province);

}

Mapper.xml中

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sabthever.mapper.SupplierAreaMapper">
    <!-- 查城市 -->
	<resultMap id="cityMap" type="com.sabthever.model.result.supplier.AreaRegionListResult">
        <result property="value" column="city" />
        <result property="label" column="city" />
    </resultMap>
    <select id="selectCities" resultMap="cityMap">
        select distinct city from table
        where business_name=#{department} and shengfen=#{province}
        order by city;
    </select>

    <!-- 查省份 -->
    <resultMap id="provinceMap" type="com.sabthever.model.result.supplier.AreaRegionListResult">
        <result property="value" column="province" />  <!-- 查出来的province同时放到类中两个属性 -->
        <result property="label" column="province" />
        <!-- 根据column传入的两个参数(方法参数=查出来字段名),用select中的方法取值，传入类的children的List，中间的数据类为AreaRegionListResult -->
        <collection property="children" ofType="com.sabthever.model.result.supplier.AreaRegionListResult"
                    select="com.sabthever.mapper.SupplierAreaMapper.selectCities"
                    column="{department=department, province=province}"/>
    </resultMap>
    <select id="selectProvinces" resultMap="provinceMap">
        select shengfen as province,#{department} as department from table
        where business_name=#{department} group by province order by province;
    </select>
</mapper>

ServiceImpl.java中

这边就放了实现类，接口自己加一个

package com.cockpit.service.impl;

@Service
public class SupplierAreaServiceImpl implements SupplierAreaService {

    @Autowired
    private SupplierAreaMapper areaMapper;
 
    @Override
    public List<AreaRegionListResult> selectAreaTabList(String department) {
        List<AreaRegionListResult> site = areaMapper.selectProvinces(department)

        return site;
    }
}

其他

Controller层的东西就自己来了，调用Mapper层的不需要selectCities就可以获得相应数据

欧了！拜拜！！

转载自：Mybatis一对多嵌套查询