表:
node表
record表
查询目标结构:
[
{
"name": "节点1",
"node_id": 1,
"records": [
{
"id": 2,
"node_id": 1,
"name": "200614T110523N5200P1"
},
{
"id": 6,
"node_id": 1,
"name": "200614T110523N5200P1"
}
]
},
{
"name": "节点2",
"node_id": 2,
"records": [
{
"id": 3,
"node_id": 2,
"name": "200614T110523N5200P2"
},
{
"id": 7,
"node_id": 2,
"name": "200614T110523N5200P2"
}
]
}
]
方法声明:
LinkedList<LinkedHashMap<String, Object>> getNodeListWithRecords(@Param("ids") String ids);
MapperXml实现:
<!-- 自定义查询结果类型 -->
<resultMap id="nodes" type="java.util.LinkedHashMap">
<!-- 此处node_id为node_tbl.id的别名 -->
<result column="id" jdbcType="INTEGER" property="node_id" />
<!-- 子查询结果集,{nid = id}中 nid为子查询的传参名,id为 上方column id项-->
<collection column="{nid = id}" ofType="java.util.LinkedList" property="records" select="getRecordList">
</collection>
</resultMap>
<!-- 主查询 -->
<select id="getNodeListWithRecords" resultMap="nodes">
SELECT * FROM node_tbl WHERE id in (${ids})
</select>
<!-- 子查询 -->
<select id="getRecordList" resultType="java.util.LinkedHashMap">
SELECT * FROM record_tbl WHERE node_id = ${nid}
</select>