SpringBoot +MyBatis 两表关联嵌套查询

SpringBoot +MyBatis 两表关联嵌套查询

废话不多说，代码走起来！

效果图

{
    "respCode": "200",
    "respMessage": "成功",
    "data": [
        {
            "cityId": 11,
            "cityName": "河南",
            "appDto": [
                {
                    "appId": 1,
                    "appName": "城市之窗",
                    "province": 11
                },
                {
                    "appId": 2,
                    "appName": "政务通",
                    "province": 11
                },
                {
                    "appId": 3,
                    "appName": "南阳通",
                    "province": 11
                },
                {
                    "appId": 4,
                    "appName": "洛阳通",
                    "province": 11
                }
            ]
        },
        {
            "cityId": 12,
            "cityName": "黑龙江",
            "appDto": [
                {
                    "appId": 5,
                    "appName": "黑龙江通",
                    "province": 12
                }
            ]
        },
        {
            "cityId": 20,
            "cityName": "宁夏",
            "appDto": [
                {
                    "appId": 6,
                    "appName": "宁夏通",
                    "province": 20
                }
            ]
        },
        {
            "cityId": 17,
            "cityName": "江西",
            "appDto": [
                {
                    "appId": 7,
                    "appName": "江西通",
                    "province": 17
                }
            ]
        }
    ]
}

数据库表

sport_contro_app表和sport_contro_city表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for sport_contro_app
-- ----------------------------
DROP TABLE IF EXISTS `sport_contro_app`;
CREATE TABLE `sport_contro_app` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'APP id',
  `name` varchar(32) NOT NULL COMMENT 'app名称',
  `province` bigint(11) NOT NULL COMMENT '所属省份编号 0 河南省',
  `version` varchar(50) NOT NULL COMMENT '版本',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'APP详情记录创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '该条记录修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for sport_contro_city
-- ----------------------------
DROP TABLE IF EXISTS `sport_contro_city`;
CREATE TABLE `sport_contro_city` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT COMMENT '省市级联id',
  `parent_id` int(5) unsigned NOT NULL DEFAULT '0' COMMENT '归属省份编号',
  `name` varchar(120) NOT NULL COMMENT '城市名称',
  `type` int(1) NOT NULL DEFAULT '2' COMMENT '类型（0为国家1省份直辖市 特别行政区 2地级市3地级区县）',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3410 DEFAULT CHARSET=utf8;

封装的CityDto

import lombok.Data;

import java.io.Serializable;
import java.util.List;

@Data
public class CityDto implements Serializable {
    /**
     *  省市级联id
     */
    private  long cityId;
    /**
     *  城市名称
     */
    private String cityName;
    /**
     *  app集合
     */
    private List<AppDto> appDto;
}

封装的AppDto

import lombok.Data;

import java.io.Serializable;

@Data
public class AppDto implements Serializable {
    /**
     * app id
     * */
    private long appId;
    /**
     * App名称
     * */
    private String appName;
    /**
     * 所属省份编号
     * */
    private long province;
}

Mapper接口

 /**
     * 查询城市和app接口
     * @param
     * @return
     */
    List<CityDto> findAllCityAndApp();

Mapper.xml 文件

<resultMap id="CityAndApp" type="com.deyi.sponsor.pojo.dto.CityDto">
		<id column="city_id" property="cityId" jdbcType="BIGINT" />
		<result column="city_name" property="cityName" jdbcType="VARCHAR"/>
		<collection property="appDto" ofType="com.deyi.sponsor.pojo.dto.AppDto">
			<id column="id" property="appId" jdbcType="BIGINT" />
			<result column="name" property="appName" jdbcType="VARCHAR" />
			<result column="province" property="province" jdbcType="BIGINT" />
		</collection>
	</resultMap>
	
	<select id="findAllCityAndApp" resultMap="CityAndApp">
		SELECT
			city.id as city_id,
			city.name as city_name,
			app.province,
			app.id,
			app.name
		FROM
			sport_contro_app app
		LEFT JOIN sport_contro_city city ON app.province = city.id
	</select>

Service接口

/**
     * 查询城市和app接口
     * @param
     * @return
     */
    OutputObject findAllCityAndApp();

ServiceImpl实现类

 /**
     * 查询城市和app接口
     * @param
     * @return
     */
    @Override
    public OutputObject findAllCityAndApp(){
        List<CityDto> list = sportPlanMapper.findAllCityAndApp();
        return new OutputObject(StatusCodeEnum.SUCCESS.getCode(), StatusCodeEnum.SUCCESS.getMessage(), list);
    }

Controller控制器

 /**
     * 查询城市和app接口
     * @param
     * @return
     */
    @PostMapping("/allCityAndApp/find")
    public OutputObject findAllCityAndApp(){
        return sportPlanService.findAllCityAndApp();
    }

大功告成！！！