mybatis 一对多查询按结果嵌套处理、按查询嵌套处理，以及两者之间的区别

素颜马尾好姑娘i 2023-10-02 12:34 21阅读 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": []
    }