dao:
List<Geo> getGeoPage(@Param("idSet")Set<Long> idSet, @Param(value = "geoInfo") GeoInfo geoInfo,
@Param(value = "page") Page<Geo> page);
xml:
<select id="getGeoPage" resultMap="BaseResultMap">
select *
from T_GEO
<where>
<if test="geoInfo.cod != null" >
and substr(PAY_METHOD, 1,1)=#{geoInfo.cod,jdbcType=CHAR}
</if>
<if test="geoInfo.pol != null" >
and substr(PAY_METHOD, 2,1)=#{geoInfo.pol,jdbcType=CHAR}
</if>
<if test="idSet != null" >
and GEO_ID in
<foreach collection="idSet" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</where>
order by GEO_id asc
</select>
本来数据库的数据不多,因为某些原因代码中必须使用in,随着数据增多,in后面的数据量超过1000后会报错,在修改最小的情况下做了点小修改,让in后面的数据分隔为多个不超过999的list集合。
修改后:
dao:
List<Geo> getGeoListPage(@Param("idList")List<List<Long>> splitList, @Param(value = "geoInfo") GeoInfo geoInfo,
@Param(value = "page") Page<Geo> page);
xml:
<select id="getGeoListPage" resultMap="BaseResultMap">
select *
from T_GEO
<where>
<if test="geoInfo.cod != null" >
and substr(PAY_METHOD, 1,1)=#{geoInfo.cod,jdbcType=CHAR}
</if>
<if test="geoInfo.pol != null" >
and substr(PAY_METHOD, 2,1)=#{geoInfo.pol,jdbcType=CHAR}
</if>
<if test="idList != null" >
and
<foreach collection="idList" item="item" open="(" separator="or" close=")">
GEO_ID in
<foreach collection="item" item="item2" open="(" separator="," close=")">
#{item2}
</foreach>
</foreach>
</if>
</where>
order by GEO_id asc
</select>
附:分隔方法
/** | ||
+ * 按指定大小,分隔集合,将集合按规定个数分为n个部分 | ||
+ * | ||
+ * @param list | ||
+ * @param len | ||
+ * @return | ||
+ */ | ||
+ private static List<List<Long>> splitList(List<Long> list, int len) { | ||
+ if (list == null || list.size() == 0 || len < 1) { | ||
+ return null; | ||
+ } | ||
+ | ||
+ List<List<Long>> result = new ArrayList<List<Long>>(); | ||
+ | ||
+ | ||
+ int size = list.size(); | ||
+ int count = (size + len - 1) / len; | ||
+ | ||
+ | ||
+ for (int i = 0; i < count; i++) { | ||
+ List<Long> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1))); | ||
+ result.add(subList); | ||
+ } | ||
+ return result; | ||
+ } |