mybatis 一对多查询按结果嵌套处理、按查询嵌套处理

待我称王封你为后i 2021-09-24 23:28 411阅读 0赞

## mybatis 一对多查询 按结果嵌套处理、按查询嵌套处理 ##

最近用到一对多查询，记录一下

## 实体类 ##

public class RegionEntity implements Serializable {
    	
    	private Long rid;
    	
    	private String regionName;
    	
    	private String addrImage;
    	
    	private String type;
    	
    	private List<FloorEntity> floorEntities;
    }
    public class FloorEntity implements Serializable {
    
    	private Long id;
    	
    	private Long rid;
    	
    	private String floorName;
    }

一个区域对应多个楼层

## xml ##

#### 按结果嵌套处理 ####

把需要查询的结果，通过sql语句实现

<select id="getRegionInfo" resultMap="regionMap">
    		select f.id,f.floor_name,r.rid,r.region_name,r.addr_image,r.type
    		from region r,floor f
    		where f.rid=r.rid and r.rid=#{id}
        </select>
    	<resultMap id="regionMap" type="com.sf.entity.RegionEntity">
    		<result property="rid" column="rid"/>
    		<result property="regionName" column="region_name"/>
    		<result property="addrImage" column="addr_image"/>
    		<result property="type" column="type"/>
    		<collection property="floorEntities" ofType="com.sf.entity.FloorEntity">
    			<result property="id" column="id"/>
    			<result property="floorName" column="floor_name"/>
    			<result property="rid" column="rid"/>
    		</collection>
    	</resultMap>

#### 按查询嵌套处理 ####

把sql语句分开实现，通过一对多关联起来

<select id="getRegionInfo2" resultMap="regionMap2">
            select * from region where rid = #{id}
        </select>
    	<resultMap id="regionMap2" type="com.sf.entity.RegionEntity">
    		<result property="rid" column="rid"/>
    		<result property="regionName" column="region_name"/>
    		<result property="addrImage" column="addr_image"/>
    		<result property="type" column="type"/>
    		<collection property="floorEntities" javaType="ArrayList" ofType="com.sf.entity.FloorEntity" select="getFloorByRid" column="rid"/>
    	</resultMap>
    	<select id="getFloorByRid" resultType="com.sf.entity.FloorEntity">
            select * from floor where rid = #{id}
        </select>

##  ##

property值为实体类中对象的值，javaType为对应的实体类，select为多方查询语句的命名id，column为两表关联id

## dao层 ##

RegionEntity getRegionInfo(Long id);

service、serviceImpl省略

## controller层 ##

@RequestMapping("/info/{rid}")
    public R info(@PathVariable("rid") Long rid) {
    	RegionEntity region =regionService.getRegionInfo(rid);
    	return region;
    }

浏览器测试  
http://localhost:8089/info/26

{
    	"rid": 26,
    	"regionName": "401",
    	"addrImage": "default.png",
    	"deptId": 1,
    	"type": "0",
    	"floorEntities": [{
    		"id": 1,
    		"rid": 26,
    		"floorName": "一楼"
    	}, {
    		"id": 2,
    		"rid": 26,
    		"floorName": "二楼"
    	}, {
    		"id": 3,
    		"rid": 26,
    		"floorName": "三楼"
    	}]

## 按结果嵌套处理、按查询嵌套处理两者的区别 ##

按结果嵌套处理：若多的一方为空，则查询结果为空  
按查询嵌套处理：若多的一方为空，依然可以输出查询结果，只是多的一项为null  
例如：

{
    	"rid": 27,
    	"regionName": "402",
    	"addrImage": "default.png",
    	"deptId": 1,
    	"type": "0",
    	"floorEntities": []
    }