Mybatis多表查询嵌套封装结果集

递归查询封装: 表结构

**TravelSetmea(1)→TravelGroup(n)

TravelGroup(1)→TravelItem(n)

实体类

package com.hl.travel.entity;  
  
import java.io.Serializable;  
import java.util.List;  
  
/**  
* 旅行套餐  
*/  
@Data
public class Setmeal implements Serializable {  
private Integer id;  
private String name;  
private String code;  
private String helpCode;  
private String sex;//套餐适用性别：0不限 1男 2女  
private String age;//套餐适用年龄  
private Float price;//套餐价格  
private String remark;  
private String attention;  
private String img;//套餐对应图片存储路径  
private List<TravelGroup> travelGroups;//旅行套餐对应的报团分组，多对多关系 
}

Setmeal里面有List<TravelGroup> travelGroups

@Data
public class TravelGroup implements Serializable {  
private Integer id;//主键  
private String code;//旅行团项目编号  
private String name;//旅行团名称  
private String helpCode;//旅行团项目缩写  
private String sex;//适用性别  
private String remark;//介绍  
private String attention;//注意事项  
private List<TravelItem> travelItems;//一个报团包含多个自由行
}

Mapper层封装结果集

setmeal:

<resultMap id="findByIdResultMap" type="com.hl.travel.entity.Setmeal"> 
<id column="id" property="id"/>  
<result column="name" property="name"/>  
<result column="code" property="code"/>  
<result column="helpCode" property="helpCode"/>  
<result column="sex" property="sex"/>  
<result column="age" property="age"/>  
<result column="price" property="price"/>  
<result column="remark" property="remark"/>  
<result column="attention" property="attention"/>  
<result column="img" property="img"/>  
<collection property="travelGroups" column="id" select="com.hl.travel.dao.TravelGroupDao.findTravelGroupListById">  
</collection>  
</resultMap>
 
 
<select id="findDescById" resultMap="findByIdResultMap" parameterType="int">  
select * from t_setmeal where id=#{id}  
</select>
 

**注意这里

<collection property="travelGroups" column="id" select="com.hl.travel.dao.TravelGroupDao.findTravelGroupListById">

去TravelGroupDao编写接口

/**  
* 根据跟团游Id查套餐游Id  
* @param id  
* @return  
*/  
List<TravelGroup> findTravelGroupListById(Integer id);
 
 
sql:
 
<!--根据套餐id查询自由行信息-->  
<select id="findTravelGroupListById" resultMap="findByIdResultMap" parameterType="int">  
select * from t_travelgroup where id in (select travelgroup_id from t_setmeal_travelgroup where setmeal_id=#{id})  
</select>

同理： TravelGroupDao.xml也对TravelItem进行封装即可

xml:

<resultMap type="com.hl.travel.entity.TravelGroup" id="findByIdResultMap">  
<id column="id" property="id"/>  
<result column="name" property="name"/>  
<result column="code" property="code"/>  
<result column="helpCode" property="helpCode"/>  
<result column="sex" property="sex"/>  
<result column="remark" property="remark"/>  
<result column="attention" property="attention"/>  
<collection property="travelItems" column="id" select="com.hl.travel.dao.TravelItemDao.findTravelItemListById">  
</collection>  
</resultMap>
 
 
sql:
<!--根据套餐id查询自由行信息-->  
<select id="findTravelGroupListById" resultMap="findByIdResultMap" parameterType="int">  
select * from t_travelgroup where id in (select travelgroup_id from t_setmeal_travelgroup where setmeal_id=#{id})  
</select>
 

继续封装：

<collection property="travelItems" column="id" select="com.hl.travel.dao.TravelItemDao.findTravelItemListById">  
 

/**  
* 根据套餐游Id查自由行id  
* @param id  
* @return  
*/  
List<TravelItem> findTravelItemListById(Integer id);
 
 
 
sql:
 
 
<!--根据跟团游id查询自由行信息-->  
<select id="findTravelItemListById" resultType="com.hl.travel.entity.TravelItem" parameterType="int">  
select * from t_travelitem where id in (select travelitem_id from t_travelgroup_travelitem where travelgroup_id=#{id})  
</select>