需求如图:
<!-- 新车大全找车,多条件查询 -->
<select id="getMoreinfo_page" parameterType="PageData" resultType="PageData"> SELECT cs.cs_id,cs.bs_id,cs.cb_id,cs.cs_show_name,cs.cb_name,cs.mb_name,cs.cs_pic,cs.max_price,cs.min_price,ch.car_id FROM `car_serial` cs LEFT JOIN car_hot_info ch ON cs.cs_id=ch.cs_id <if test="gear_boxArr != null and gear_boxArr != ''"> LEFT JOIN car_info ci ON cs.cs_id=ci.cs_id </if> where ch.car_id != '' <if test="priceArr != null and priceArr != ''"> and <foreach collection="priceArr" index="index" item="item"> <if test="index % 2 == 0"> cs.min_price <![CDATA[>= ]]> #{item} </if> <if test="index % 2 != 0"> and cs.min_price <![CDATA[<= ]]> #{item} <if test="index < priceArrlen"> or </if> </if> </foreach> </if> <if test="level != null and level != ''"> and cs.`level` in (#{level}) </if> <if test="country != null and country != ''"> and cs.country in (#{country}) </if> <if test="type != null and type != ''"> and cs.type in (#{type}) </if> <if test="displacementArr != null and displacementArr != ''"> and <foreach collection="displacementArr" index="index" item="item"> <if test="index != 0"> or </if> cs.displacement LIKE "%"#{item}"%" </foreach> </if> <if test="gear_boxArr != null and gear_boxArr != ''"> and <foreach collection="gear_boxArr" index="index" item="item"> <if test="index != 0"> or </if> ci.gear_box LIKE "%"#{item}"%" <if test="index == gear_boxArrlen"> GROUP BY ci.cs_id </if> </foreach> </if> </select>
其中mybatis中foreach无法获取数组的长度,由java传入数组长度:
String price = pd.getString("price"); String[] priceArr = price.split(","); System.out.println(priceArr.length); if(priceArr.length > 1){ pd.put("priceArr", priceArr); pd.put("priceArrlen", (priceArr.length-1)); } String displacement = pd.getString("displacement"); String[] displacementArr = displacement.split(","); System.out.println(displacementArr.length); if(displacementArr.length > 1){ pd.put("displacementArr", displacementArr); } String gear_box = pd.getString("gear_box"); String[] gear_boxArr = gear_box.split(","); System.out.println(gear_boxArr.length); if(gear_boxArr.length > 0){ System.out.println(gear_boxArr[0]); if(gear_boxArr[0].length()>1){ pd.put("gear_boxArr", gear_boxArr); pd.put("gear_boxArrlen", (gear_boxArr.length-1)); } } carInfo = carSeriaTaskServices.getMoreinfo_page(pd);
<select id="getMoreinfo_page" parameterType="PageData" resultType="PageData"> SELECT cs.cs_id,cs.bs_id,cs.cb_id,cs.cs_show_name,cs.cb_name,cs.mb_name,cs.cs_pic,cs.max_price,cs.min_price,ch.car_id FROM `car_serial` cs LEFT JOIN car_hot_info ch ON cs.cs_id=ch.cs_id <if test="gear_boxArr != null and gear_boxArr != ''"> LEFT JOIN car_info ci ON cs.cs_id=ci.cs_id </if> where ch.car_id != '' <if test="priceArr != null and priceArr != ''"> and <foreach collection="priceArr" index="index" item="item"> <if test="index % 2 == 0"> min_price <![CDATA[>= ]]> #{item} </if> <if test="index % 2 != 0"> and min_price <![CDATA[<= ]]> #{item} <if test="index < priceArrlen"> or </if> </if> </foreach> </if> <if test="level != null and level != ''"> and `level` in (#{level}) </if> <if test="country != null and country != ''"> and country in (#{country}) </if> <if test="type != null and type != ''"> and type in (#{type}) </if> <if test="displacementArr != null and displacementArr != ''"> and <foreach collection="displacementArr" index="index" item="item"> <if test="index != 0"> or </if> displacement LIKE "%"#{item}"%" </foreach> </if> <if test="gear_boxArr != null and gear_boxArr != ''"> and <foreach collection="gear_boxArr" index="index" item="item"> <if test="index != 0"> or </if> ci.gear_box LIKE "%"#{item}"%" <if test="index == gear_boxArrlen"> GROUP BY ci.cs_id </if> </foreach> </if> </select>