本意是想用mybatis <foreach>中 index作为mysql语句中select的一个字段,但是最后生成mysql语句这个所有循环中的index值都是foreach循环最后一次时对应的个值。例子如下:
<select id="hourSpaceInfo" resultType="com.jieshun.jpark.business.entity.api.HourSpaceDTO">
<foreach collection="hourList" item="ite" open="" separator="union" close="" index="ind">
SELECT
#{ind} hour,
rest_space spaceCount
FROM
table_eg
WHERE
park_code = #{parkCode}
AND stats_date = #{date}
</foreach>
</select>
这个hourList长度为24,index正常应该是0开始,23结束,理想中结果应该是:
SELECT
0 hour,
rest_space spaceCount
FROM
table_eg
WHERE
park_code = 'parkCode'
AND stats_date ='date'
union
SELECT
1 hour,
rest_space spaceCount
FROM
table_eg
WHERE
park_code = 'parkCode'
AND stats_date ='date'
...
结果最终的sql语句中,#{ind}这个取值在每个循环中都取到了23,所以最后实际结果是:
SELECT
23 hour,
rest_space spaceCount
FROM
table_eg
WHERE
park_code = 'parkCode'
AND stats_date ='date'
union
SELECT
23 hour,
rest_space spaceCount
FROM
table_eg
WHERE
park_code = 'parkCode'
AND stats_date ='date'
...
最后考虑可能#{}的问题,mybatis中针对index标签的取值将#{}改为${},结果正常了,即改成
<select id="hourSpaceInfo" resultType="com.jieshun.jpark.business.entity.api.HourSpaceDTO">
<foreach collection="hourList" item="ite" open="" separator="union" close="" index="ind">
SELECT
${ind} hour,
rest_space spaceCount
FROM
table_eg
WHERE
park_code = #{parkCode}
AND stats_date = #{date}
</foreach>
</select>