-
需求
数据库表是按照日期进行分表,查找数据时候,不同日期的数据到不同的表中查找,此时日期作为参数传递。在有父、子查询的SQL语句时,希望子查询能够使用到父查询中的参数(如日期)。 -
实现
将参数作为数据库表返回值的某一列,进行传递。此时,可以在resultMap中获取到。示例如下。 -
Dao层
List <PicMoreDetailVo> selectPicList(@Param("tableName") String tableName, @Param("picVo") PicManagerVo picManagerVo);
- 父查询
<select id="selectPicList" resultMap="bagIdFromMonthTable">
SELECT id,create_time,'${tableName}' as tableName FROM baginfo_${tableName} WHERE stationId = #{picVo.stationId} AND create_time BETWEEN #{picVo.startTime} AND #{picVo.endTime}
ORDER BY create_time ${picVo.sort} LIMIT #{picVo.currentPageNo},#{picVo.pageSize}
</select>
- 父查询返回map
<resultMap type="com.qxmz.vo.pic.PicMoreDetailVo" id="bagIdFromMonthTable">
<id property="bagId" column="id"/>
<id property="createTime" column="create_time"/>
<collection property="picList" ofType="com.qxmz.vo.pic.PicMoreReqVo" select="selectPicListByBagIdFromMonthTable"
column="{id=id,tableName=tableName}">
</collection>
</resultMap>
- 子查询
<select id="selectPicListByBagIdFromMonthTable" resultMap="picId">
SELECT id,pic_url,isMainPic FROM baginfo_pic_${tableName} where bagInfo_id=#{id}
</select>
- 子查询返回map
<resultMap type="com.qxmz.vo.pic.PicMoreReqVo" id="picId">
<id column="id" property="picId"/>
<result column="isMainPic" property="isMainPic"/>
<result column="pic_url" property="picUrl"/>
</resultMap>
- 另类场景:
在父子查询参数传递过程中,除上面String类型外,如果有list这种复杂类型参数,可以将list转为String类型进行传递,类似"a,b,c",然后再在子查询中转为list使用。由于本人从没遇到过这种场景,下文仅仅作为演示。另外,对于这种场景,也可以考虑从业务上解决。 - Controller层
@GetMapping("/select/test")
@ApiOperation(value = "select test", notes = "select test", httpMethod = "GET")
public Object judgeStationList() {
try {
List<String> timeList = new ArrayList<>();
timeList.add("2021-06-09 10:46:16");
//timeList.add("2021-06-09 10:46:17");
String timeListStr = StringUtils.strip(timeList.toString(), "[]");
return bagInfoDao.parentSelect(timeListStr);
} catch (Exception e) {
logger.error("异常:{}", e);
return "error";
}
}
- Dao层
List<BagInfo> parentSelect(String timeListStr);
- 父查询
<select id="parentSelect" resultMap="testListMap">
select id,create_time,'${timeListStr}' as childTimeListStr from baginfo_2021_06
<where>
<if test="timeListStr!=null and timeListStr!='' ">
and create_time in
<foreach item="time" collection="timeListStr.split(',')" open="(" separator="," close=")">
#{time}
</foreach>
</if>
</where>
</select>
- 父查询返回Map
<resultMap type="com.qxmz.vo.pic.PicMoreDetailVo" id="testListMap">
<id property="bagId" column="id"/>
<id property="createTime" column="create_time"/>
<collection property="picList" ofType="com.qxmz.vo.pic.PicMoreReqVo" select="childSelect"
column="{id=id,childTimeListStr=childTimeListStr}">
</collection>
</resultMap>
- 子查询
<select id="childSelect" resultType="com.qxmz.vo.pic.PicMoreReqVo">
select id picId,isMainPic,pic_url picUrl from baginfo_pic_2021_06
<where>
bagInfo_id=#{id}
<if test="childTimeListStr!=null and childTimeListStr!='' ">
and create_time in
<foreach item="time" collection="childTimeListStr.split(',')" open="(" separator="," close=")">
#{time}
</foreach>
</if>
</where>
</select>