有些时候，我们需要拼接一些sql查询结果，作为vo的返回。比如下面这个vo，除了返回一些基础字段以外，还会返回一些子列表来表示多条信息。当然子列表还会继续套子列表。
举例如下：
public class CooperationLandVo implements Serializable {
/**
* updateFlag(新增：flase ；更新 true)
*/
@JSONField(ordinal=1)
protected Boolean updateFlag;
/**
* 合作机构id
*/
@JSONField(ordinal=2)
protected String cooperationId;
/**
* 合作机构名称
*/
@JSONField(ordinal=3)
protected String cooperationName;
/**
* 合作机构code
*/
@JSONField(ordinal=4)
protected String cooperationCode;
/**
* 合作状态（0：未合作，1：合作中，2：停止合作）
*/
@JSONField(ordinal=5)
protected String cooperateStatus;
/**
* 合作开始时间
*/
@JSONField(ordinal=6,format="yyyy-MM-dd HH:mm:ss")
protected Date cooperateStartTime;
/**
* 合作结束时间
*/
@JSONField(ordinal=7,format="yyyy-MM-dd HH:mm:ss")
protected Date cooperateEndTime;
/**
* 优先级权重
*/
@JSONField(ordinal=8)
protected Integer weight;
/**
* 机构类型：资方/险方
*/
@JSONField(ordinal = 9)
protected CooperationLandBaseDto.Type bigType;
/**
* 机构小类
*/
@JSONField(ordinal = 10)
protected Integer type;
@JSONField(ordinal=12)
List<CooperationLandCompanyVo> cooperationLandCompanyVoList;
@JSONField(ordinal=11)
List<CooperationLandProductVo> cooperationLandProductVoList;
其中，CooperationLandCompanyVo下又带有子列表CooperationLandSubcapVo
public class CooperationLandCompanyVo implements Serializable {
/**
* 合作机构id
*/
@JSONField(serialize=false)
protected String cooperationId;
/**
* 分公司编码
*/
protected String companyCode;
/**
* 落地城市名称
*/
protected String cityName;
/**
* 落地城市id
*/
protected String cityId;
/**
* 启用人
*/
protected String createUserName;
/**
* 启用人id
*/
protected String createUserId;
/**
* 启用时间
*/
@JSONField(format="yyyy-MM-dd HH:mm:ss")
protected Date groundTime;
/**
* 下级机构
*/
List<CooperationLandSubcapVo> cooperationLandSubcapVoList;
}
对于这样的结构，我想通过mybatis的查询直接获取到这个结构，我就需要拼装和嵌套resultMap。
如下：
这里我想问有没有不需要写resultMap就能返回嵌套结果的情况。比如直接使用某种方式，返回类型为resultType之类的，如果大神看到，求评论，求私信
<resultMap id="CooperationLandVo" type="com.ddjf.bims.base.persistence.vo.CooperationLandVo">
<id column="cooperationId" property="cooperationId" jdbcType="VARCHAR"/>
<result property="cooperateStartTime" column="cooperateStartTime" jdbcType="TIMESTAMP"/>
<result property="cooperationName" column="cooperationName" jdbcType="VARCHAR"/>
<result property="cooperationCode" column="cooperationCode" jdbcType="VARCHAR"/>
<result property="cooperateEndTime" column="cooperateEndTime" jdbcType="TIMESTAMP"/>
<result property="cooperateStatus" column="cooperateStatus" jdbcType="VARCHAR"/>
<result property="weight" column="weight" jdbcType="VARCHAR"/>
<result property="type" column="`type`" jdbcType="TINYINT"/>
<result property="bigType" column="`bigType`" jdbcType="VARCHAR"/>
<result property="updateFlag" column="updateFlag" jdbcType="TINYINT"/>
<collection property="cooperationLandProductVoList" column="cooperationId" ofType="com.ddjf.bims.base.persistence.vo.CooperationLandProductVo" javaType="java.util.List" select="getProductBycapitalId"/>
<collection property="cooperationLandCompanyVoList" column="cooperationId" ofType="com.ddjf.bims.base.persistence.vo.CooperationLandCompanyVo" javaType="java.util.List" select="getCompanyBycapitalId"/>
</resultMap>
然后对于其中的cooperationLandCompanyVoList这个子集合，返回也是用一个resultMap，嵌套下级机构的返回集合。如下：
<resultMap id="CooperationLandCompanySubVo" type="com.ddjf.bims.base.persistence.vo.CooperationLandCompanyVo">
<id property="cooperationId" column="cooperationId" jdbcType="VARCHAR"/>
<result property="cityId" column="cityId" jdbcType="VARCHAR"/>
<result property="cityName" column="cityName" jdbcType="VARCHAR"/>
<result property="companyCode" column="companyCode" jdbcType="VARCHAR"/>
<result property="groundTime" column="groundTime" jdbcType="TIMESTAMP"/>
<result property="createUserId" column="createUserId" jdbcType="VARCHAR"/>
<result property="createUserName" column="createUserName" jdbcType="VARCHAR"/>
<collection property="cooperationLandSubcapVoList" ofType="com.ddjf.bims.base.persistence.vo.CooperationLandSubcapVo" column="{cooperationId=cooperationId,companyCode=companyCode}" javaType="java.util.List" select="getSubcapBycapitalId"/>
</resultMap>
接着是三个嵌套部分的子查询：
我们设置了关联的column为cooperationId（看resultMap的collection列），但是对于实际的返回，我们不需要这一列的信息，因为在主查询中，已经获取了。
所以我们可以去掉在sql中对于这一列的“as”操作，也就是不将他映射至vo中。但是鉴于CooperationLandCompanyVo本身需要再次和下级嵌套，所以它的cooperationId是不可以省略的（如果没有这里的第二层嵌套关系，就可以全部省略）
这样，在最终获取结果集的时候，对于没有的子查询，不会返回[{}]（如果设置了不序列化cooperationId），而是[]。
前端展示时就不会多一条空记录了。
同样的，这里的
<!--查询所有下级机构配置-->
<select id="getSubcapBycapitalId" parameterType="java.util.Map" resultType="com.ddjf.bims.base.persistence.vo.CooperationLandSubcapVo">
SELECT
distinct bccl.coop_capital_id,
bccl.capital_id as subCapitalId,
bccl.capital_code as subCapitalCode,
bccl.capital_name as subCapitalName,
bccl.bank_code as bankCode,
bcci.bank_name as bankName
FROM
b_cooperate_capital_land bccl
<!--left join s_bank_info sbi on sbi.id = bccl.capital_id-->
left join b_cooperate_bank_info bcci on bccl.bank_code = bcci.bank_code
WHERE
bccl.delete_flag='0'
and
bccl.coop_capital_id = #{cooperationId}
AND
bccl.company_code = #{companyCode}
</select>
<!--查询所有分公司配置-->
<select id="getCompanyBycapitalId" parameterType="java.lang.String" resultMap="CooperationLandCompanySubVo">
SELECT
distinct bccl.coop_capital_id as cooperationId,
bccl.city_name as cityName,
bccl.company_code as companyCode,
bccl.ground_time as groundTime,
bccl.ground_user_id as createUserId,
su.fullname_ as createUserName
FROM
b_cooperate_capital_land bccl
left join sys_user su on su.id_ = bccl.ground_user_id
WHERE
bccl.delete_flag='0'
and
bccl.coop_capital_id = #{cooperationId}
</select>
<!--查询所有产品配置-->
<select id="getProductBycapitalId" parameterType="java.lang.String" resultType="com.ddjf.bims.base.persistence.vo.CooperationLandProductVo">
SELECT
distinct bccl.coop_capital_id ,
bccl.product_id as productId,
bp.product_name as productName,
bpn.loan_node_name as loanNodeName,
bccl.loan_node as loanNode
FROM
b_cooperate_capital_land bccl
left join b_product bp on bp.product_id = bccl.product_id
left join b_product_node bpn on bp.product_id = bpn.product_id and bccl.loan_node = bpn.loan_node_code
WHERE
bccl.delete_flag='0'
and
bccl.coop_capital_id = #{cooperationId}
</select>
<!--获取一条机构配置-->
<select id="getLandInfoDetail" parameterType="java.util.Map" resultMap="CooperationLandVo">
select
bcc.capital_id as cooperationId,
bcc.capital_name as cooperationName,
bcc.capital_code as cooperationCode,
bcc.cooperate_start_time as cooperateStartTime,
bcc.cooperate_end_time as cooperateEndTime,
bcc.cooperate_status as cooperateStatus,
bcc.weight as weight,
(
SELECT
IF (
bcc.capital_type = 'bank',
0,1)
)
AS `type`,
'CAP' as `bigType`,
(select IF(count(id) > 0, TRUE, FALSE) as e from b_cooperate_capital_land
where coop_capital_id = bcc.capital_id) as updateFlag
from
b_cooperate_capital bcc
left join b_cooperate_capital_land bccl on bccl.coop_capital_id = bcc.capital_id
where
bccl.delete_flag='0'
and
bcc.delete_flag = '0'
and
bcc.capital_id = #{cooperationId}
limit 1
</select>
如果是没有双层嵌套，这样已经满足了，返回类型直接是CooperationLandVo即可。但是如果存在这样的嵌套。需要在外层继续操作一下，手动去掉它。
public CooperationLandVo getCapitalLandInfoDetail(String cooperationId) {
//这里其实已经查询完毕了，下面的操作都是为了去掉空对象。
CooperationLandVo cooperationLandVo = cooperateCapitalLandManager.getLandInfoDetail(cooperationId);
if(CollectionUtils.isEmpty(cooperationLandVo.getCooperationLandCompanyVoList())||cooperationLandVo.getCooperationLandCompanyVoList().get(0)==null){
cooperationLandVo.setCooperationLandCompanyVoList(new ArrayList<CooperationLandCompanyVo>());
}else{
List<CooperationLandCompanyVo> cooperationLandCompanyVos = cooperationLandVo.getCooperationLandCompanyVoList();
for(CooperationLandCompanyVo cooperationLandCompanyVo:cooperationLandCompanyVos){
if(CollectionUtils.isEmpty(cooperationLandCompanyVo.getCooperationLandSubcapVoList())||cooperationLandCompanyVo.getCooperationLandSubcapVoList().get(0)==null){
cooperationLandCompanyVo.setCooperationLandSubcapVoList(new ArrayList<CooperationLandSubcapVo>());
}
}
}
if(CollectionUtils.isEmpty(cooperationLandVo.getCooperationLandProductVoList())||cooperationLandVo.getCooperationLandProductVoList().get(0)==null){
cooperationLandVo.setCooperationLandProductVoList(new ArrayList<CooperationLandProductVo>());
}
return cooperationLandVo;
}
返回结果：
{
"code": "0000",
"isSuccess": true,
"msg": "成功",
"result": {
"updateFlag": true,
"cooperationId": "0000000000",
"cooperationName": "招商银行",
"cooperationCode": "ZSYX",
"cooperateStatus": "1",
"cooperateStartTime": "2017-09-12 00:00:00",
"cooperateEndTime": "2020-09-12 00:00:00",
"weight": 0,
"bigType": "CAP",
"type": 0,
"cooperationLandProductVoList": [
{
"loanNode": "XXX",
"loanNodeName": "XXXXXX",
"productId": "XXXXX",
"productName": "XXXXX"
}
],
"cooperationLandCompanyVoList": [
{
"cityName": "上海市",
"companyCode": "021000",
"cooperationLandSubcapVoList": [
{
"bankCode": "XXXX",
"subCapitalCode": "122222",
"subCapitalId": "22222222",
"subCapitalName": "xx银行"
},
{
"bankCode": "XXXXXXX",
"bankName": "交通银行",
"subCapitalId": "3333333"
}
],
"createUserId": "1",
"createUserName": "系统管理员",
"groundTime": "2019-10-24 15:09:38"
},
{
"cityName": "淮安市",
"companyCode": "517170",
"cooperationLandSubcapVoList": [],
"createUserId": "1111",
"createUserName": "XXX",
"groundTime": "2017-11-16 18:29:13"
},
{
"cityName": "东莞市",
"companyCode": "769000",
"cooperationLandSubcapVoList": [],
"createUserId": "1222",
"createUserName": "XXXX",
"groundTime": "2017-11-16 18:23:13"
}
]
},
"success": true
}
