以下面sql为例,巡检点和仪表参数为一对多的关系
1.resultMap的result标签里面写select
<!--巡检点查询返回结果集-->
<resultMap id="voSelectMap" class="com.rf.dof.devp.patrol.inspectionpoint.vo.TbInspectionpointVO" >
<result column="EVENTID" property="entity.identifier" />
<result column="SITE_EVENTID" property="entity.siteEventid" />
<result column="INSPECTION_NAME" property="entity.inspectionName"/>
<result column="INSPECTION_DESC" property="entity.inspectionDesc" />
<result column="INSPECTION_POINT_TYPE" property="entity.inspectionPointType"/>
<result property="paramsList" column="EVENTID" select="com.rf.dof.devp.patrol.base.entity.TbInspectionpointEntity.selectParams"/>
</resultMap>
<!--参数查询返回结果集-->
<resultMap id="voSelectMap2" class="java.util.HashMap" >
<result column="EVENTID" property="identifier"/>
<result column="PARMS_NAME" property="parmsName" />
</resultMap>
<!--巡检点查询-->
<select id="selectInspectPoint" resultMap="voSelectMap" parameterClass="com.rf.dof.devp.patrol.inspectionpoint.vo.TbInspectionpointVO" >
<![CDATA[
SELECT
A .EVENTID,
A .SITE_EVENTID,
A .INSPECTION_NAME,
A .INSPECTION_DESC,
A .INSPECTION_POINT_TYPE
FROM
TB_INSPECTIONPOINT A
ORDER BY
A.CREATED_DATE DESC,A.CREATED_BY DESC
]]>
</select>
<!--仪表参数查询-->
<select id="selectParams" resultMap="voSelectMap2">
<![CDATA[
SELECT
TB_PARAMETERS.EVENTID,
TB_PARAMETERS.PARMS_NAME
FROM
TB_PARAMETERS
]]>
</select>
<result property="paramsList" column="EVENTID" select="com.rf.dof.devp.patrol.base.entity.TbInspectionpointEntity.selectParams">中column为参数查询sql的传入参数,参数为多个时可以写为{a=a,b=b,c=c}的形式,也可以简写为{a,b,c},简写时传入参数顺序要和sql中保持一致,property属性是仪表参数返回的list,这个属性要在巡检点返回的class中定义,并写get和set方法。
public class TbInspectionpointVO extends VO<TbInspectionpointEntity> {
/*
*
*/
private List paramsList;
public List getParamsList() {
return paramsList;
}
public void setParamsList(List paramsList) {
this.paramsList = paramsList;
}
}
经过多次测试发现,查询会有N+1的问题(主表查询一次,子表查询N次),效率比较低,尤其是字表数据量比较大的时候。
踩过的坑如下:
select属性只写了selectid的,没有加namespace,报错:com.ibatis.sqlmap.client.SqlMapException: There is no statement named selectParams in this SqlMap.
2. resultMap的result嵌套resultMap,再根据group by分组
<!--巡检点返回结果集 -->
<resultMap id="voSelectMap" class="com.rf.dof.devp.patrol.inspectionpoint.vo.TbInspectionpointVO" groupBy="entity.identifier">
<result column="EVENTID" property="entity.identifier" />
<result column="INSPECTION_NAME" property="entity.inspectionName"/>
<result column="INSPECTION_DESC" property="entity.inspectionDesc" />
<result property="paramsList" resultMap="com.rf.dof.devp.patrol.base.entity.TbInspectionpointEntity.voSelectMap2"/>
</resultMap>
<!--仪表参数返回结果集 -->
<resultMap id="voSelectMap2" class="java.util.HashMap">
<result column="EVENTID2" property="eventid2"/>
<result column="PARMS_NAME" property="parmsName" />
</resultMap>
<!--巡检点和仪表参数关联查询 -->
<select id="selectInspectPoint" resultMap="voSelectMap" parameterClass="com.rf.dof.devp.patrol.inspectionpoint.vo.TbInspectionpointVO" >
<![CDATA[
SELECT
A .EVENTID,
A .INSPECTION_NAME,
A .INSPECTION_DESC,
H .EVENTID EVENTID2,
H .PARMS_NAME
FROM
TB_INSPECTIONPOINT A
JOIN TB_PARAMETERS H ON A .EVENTID = H .INSPECTION_EVENTID
]]>
</select>
<result property="paramsList" resultMap="com.rf.dof.devp.patrol.base.entity.TbInspectionpointEntity.voSelectMap2"/>
查询语句只写了一条,所以这行代码里直接写resultMap就可以,查询结果如下:
<resultMap id="voSelectMap" class="com.rf.dof.devp.patrol.inspectionpoint.vo.TbInspectionpointVO" groupBy="entity.identifier"> group by标签后面的字段是property属性,而不是column,group by会在上面查询结果的基础上进行分组,同一巡检点下的仪表参数会被自动映射为list。
这种方法适用于不分页的查询,分页查询是根据sql查询结果进行分页的,而不是resultMap的映射结果。假如分页数据为4,那么分页结果如下,这种情况下,巡检点数据显示不完整。很明显,巡检点004的参数少了两个。
{
"result": {
"total": 31,
"rows": [{
"inspectionName": "必填项",
"parameters": [{
"parmsName": "必填项"
}, {
"parmsName": "井下压力"
}, {
"parmsName": "套压"
}]
}, {
"inspectionName": "巡检点004",
"parameters": [{
"parmsName": "井下压力3"
}]
}]
},
"resultCode": "x00000000000",
"errorMsg": ""
}