首先我们查询的是隧道的所有内容:
Controller层:
tunnelService.selectTunnelWithSiteByExample(example);
Service层:因为查询所有,所以返回的是一个list
List<Tunnel> vehSailInfoList = tunnelMapper.selectTunnelWithSiteByExample(example);
Mapper.xml层:
到这层我们要分步来写:
1.首先我们是查隧道关联站点,所以我们要在隧道Tunnel的实体里面,把站点的实体私有化放到里面去。
代码(这里最好用下private):
public HighwaySite site;//站点
2.编写查询语句首先查询隧道的所有属性:
<!-- 查询带站点信息的车道数据 -->
<select id="selectTunnelWithSiteByExample" parameterType="com.vrview.ssm.model.example.TunnelExample" resultMap="WithSiteMap">
<include refid="OracleDialectPrefix" />
select
<if test="distinct" >
distinct
</if>
<include refid="Base_Column_List" />
from HIS_VEH_SAIL_INFO
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null" >
order by ${orderByClause}
</if>
<include refid="OracleDialectSuffix" />
</select>
这里我们要注意返回的resultMap:
3.这里综合结果集:
<!-- 隧道结果集:带站点 (site要在model中定义)-->
<resultMap extends="BaseResultMap" id="WithSiteMap" type="com.vrview.ssm.model.Tunnel">
<association column="PASSSITE_ID" property="site" select="selectLaneSite" />
</resultMap>
4.最后在由根据从查询出来的隧道实体里面的字段PASSSITE_ID作为查询站点的id值用来条件查询站点
<!-- 查询站点 -->
<select id="selectLaneSite" parameterType="java.lang.Long" resultMap="com.vrview.ssm.mapper.HighwaySiteMapper.BaseResultMap">
SELECT * FROM CFG_HIGHWAY_SITE WHERE ID = #{passsiteId}
</select>
最后看下总的sql语句:
<!-- 隧道结果集:带站点 (site要在model中定义)-->
<resultMap extends="BaseResultMap" id="WithSiteMap" type="com.vrview.ssm.model.Tunnel">
<association column="PASSSITE_ID" property="site" select="selectLaneSite" />
</resultMap>
<!-- 查询站点 -->
<select id="selectLaneSite" parameterType="java.lang.Long" resultMap="com.vrview.ssm.mapper.HighwaySiteMapper.BaseResultMap">
SELECT * FROM CFG_HIGHWAY_SITE WHERE ID = #{passsiteId}
</select>
<!-- 查询带站点信息的车道数据 -->
<select id="selectTunnelWithSiteByExample" parameterType="com.vrview.ssm.model.example.TunnelExample" resultMap="WithSiteMap">
<include refid="OracleDialectPrefix" />
select
<if test="distinct" >
distinct
</if>
<include refid="Base_Column_List" />
from HIS_VEH_SAIL_INFO
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null" >
order by ${orderByClause}
</if>
<include refid="OracleDialectSuffix" />
</select>
最后将查询到的结果是list层层返回。