MyBatis实现多层级collection嵌套查询

发表于 更新于 分类于 ， 阅读次数： 本文字数： 5.3k 阅读时长 ≈ 5 分钟

数据结构

在这里插入图片描述

实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
@Data
public class AchievementRepetitionVo {
    @ApiModelProperty(value = "成果编号")
    private String code;
    @ApiModelProperty(value = "成果名称")
    private String name;
    @ApiModelProperty(value = "成果介绍")
    private String introduce;
    @ApiModelProperty(value = "查重范围文档数")
    private Integer checkNum;
    @ApiModelProperty(value = "最高相似度")
    private BigDecimal maxRate;
    @ApiModelProperty(value = "成果对比明细")
    private List<RepetitionDetailVo> detailList;
}

@Data
public class RepetitionDetailVo {
    @ApiModelProperty(value = "对比成果名称")
    private String contractName;
    @ApiModelProperty(value = "对比成果介绍")
    private String contractIntroduce;
    @ApiModelProperty(value = "对比明细")
    private List<RepetitionContractVo> contractList;
}

@Data
public class RepetitionContractVo {
    @ApiModelProperty(value = "检测成果句子")
    private String s1;
    @ApiModelProperty(value = "对比成果库句子")
    private String s2;
    @ApiModelProperty(value = "相似度")
    private BigDecimal rate;
}

第一层查询实现逻辑

1、外层service将code字段传入queryRepetitionReport方法，该方法查询的code作为参数(column="{code=code}")传给queryDetailList
2、mybatis循环调用queryDetailList

相关代码如下：

1
AchievementRepetitionVo queryRepetitionReport(String code);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<resultMap id="QueryRepetitionReportMap" type="AchievementRepetitionVo">
	<result column="code" jdbcType="VARCHAR" property="code" />
	<result column="name" jdbcType="VARCHAR" property="name" />
	<result column="introduce" jdbcType="VARCHAR" property="introduce" />
	<result column="check_num" jdbcType="INTEGER" property="checkNum" />
	<result column="rate" jdbcType="DECIMAL" property="maxRate" />
	<collection property="detailList" ofType="RepetitionDetailVo"
				select="queryDetailList"
				column="{code=code}">
	</collection>
</resultMap>

<select id="queryRepetitionReport" parameterType="string" resultMap="QueryRepetitionReportMap">
	select rate.code,application.name,rate.check_num,rate.rate,rate.introduce
	from t_achievement_repetition_rate rate
	left join t_achievement_application application on rate.code = application.code
	where rate.code = #{code}
</select>

第二层查询实现逻辑

queryDetailList接收上一层查询返回的code字段，每一次执行queryDetailList方法都会把查询出的code、contrast_code字段传给queryContractList方法
mybatis再循环调用queryContractList方法。

相关代码如下：

1
List<RepetitionDetailVo> queryDetailList(String code);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<resultMap id="QueryRepetitionDetailMap" type="com.aspirecn.rewardinnovation.entity.vo.achievementrepetition.RepetitionDetailVo">
	<result column="contractName" jdbcType="VARCHAR" property="contractName" />
	<result column="contractIntroduce" jdbcType="VARCHAR" property="contractIntroduce" />
	<collection property="contractList" ofType="com.aspirecn.rewardinnovation.entity.vo.achievementrepetition.RepetitionContractVo"
				select="queryContractList"
				column="{code=code,contrastCode=contrast_code}">
	</collection>
</resultMap>

<select id="queryDetailList"  resultMap="QueryRepetitionDetailMap">
	select result.code,application.name as contractName,result.contrast_code,result.contrast_introduce as contractIntroduce
	from t_achievement_repetition_result result
	left join t_achievement_application application on application.code = result.contrast_code
	where result.code = #{code}
	union
	select result.contrast_code as code,application.name as contractName,result.code as contrast_code,result.introduce as contractIntroduce
	from t_achievement_repetition_result result
	left join t_achievement_application application on application.code = result.code
	where result.contrast_code = #{code}
</select>

第三层查询实现逻辑

queryContractList接收上一层查询返回的code、contrastCode字段，
mybatis再循环调用queryContractList方法
相关代码如下：

1
List<RepetitionContractVo> queryContractList(String code, String contrastCode);
1
2
3
4
5
6
7
8
9
<select id="queryContractList" resultType="RepetitionContractVo">
	select result.s1,result.s2,result.rate
	from t_achievement_repetition_result result
	where result.code = #{code} and result.contrast_code = #{contrastCode}
	union all
	select result.s1,result.s2,result.rate
	from t_achievement_repetition_result result
	where result.contrast_code = #{code} and result.code = #{contrastCode}
</select>

总结

其实就是Collection放到resultMap,下一层把上一层的查询结果作为条件传入。
参考链接

碰到问题及解决方案

问题现象：
第二层或者第三层的映射文件加上parameterType=“string”，如下面的代码所示：

1
2
<select id="queryDetailList"  parameterType="string" resultMap="QueryRepetitionDetailMap">
<select id="queryContractList"  parameterType="string" resultType="RepetitionContractVo">

会报nested exception is org.apache.ibatis.reflection.ReflectionException: There is no setter for property named ‘code’ in 'class java.lang.String错误。

错误分析：
String类没有code的set方法。
问题原因：
数据类型不一致。
解决方案：

  • MyBatis collection使用标签column="{code=code,name=name}",Mapper中使用HashMap或者对象接收。
  • MyBatis collection使用标签column=“code”，Mapper中使用基本数据类型接收。