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

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>

<collection property="floorEntities" javaType="ArrayList" ofType="com.sf.entity.FloorEntity" select="getFloorByRid" column="rid"/>

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": []
}

多条件查询时
column传入多个参数值
使用column=“{prop1=col1,prop2=col2}”这样的语法，设置多个列名传入到嵌套查询语句
prop1参数名，col1对应表中列名，例如column=“{regionName=region_name,addrImage=addr_image}”