SpringBoot +MyBatis 两表关联嵌套查询

忘是亡心i 2023-05-30 08:49 3阅读 0赞

# 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();
        }

## 大功告成！！！ ##