Mybatis中动态sql(多条件查询)

动态SQL指:通过mtbatis提供的各种标签实现动态拼接sql语句

例如:
<!--动态SQL语句,多条件查询-->
   <select id="findSomeByMore" resultType="userInfoModel" parameterType="hashmap">
      select * from userinfo
--        (where标签自动去掉满足条件的第一个and)
      <where>
         <if test="userName!=null">
            and username=#{userName}
         </if>
         <if test="gender != null">
            and gender=#{gender}
         </if>
         <if test="orderId != null">
            and id in (select userid from orders where id=#{orderId})
         </if>

      </where>

   </select>




批量删除
<delete id="partDelete" parameterType="list" >
   delete from userinfo
   <where>
      <if test="list!=null and list.size()>0">
         <foreach collection="list" item="id" open="id in (" separator="," close=")">
            #{id}
         </foreach>
      </if>
   </where>
</delete>


前端传入一个数组条件(data),如下: ```json { "userId": "10000", "password": "123456", "option": 2, "data":"(0,1)", "index": 1, "pageSize": 4 } ``` 通过Controller接收,如下: ```java @RequestMapping(value = "/apiGetOpinions", produces = "application/json;charset=UTF-8", method = RequestMethod.POST) @ResponseBody public List<OpinionDomain> getOpinions(@RequestBody Map<String, Object> loginInfo){ // 初始化舆情列表对象 OpinionDomain opinion = new OpinionDomain(); // 设置舆情查询条件(userId,index,pagesize) opinion.setUserId((String) loginInfo.get("userId")); opinion.setPositiveId(-1); opinion.setCurrIndex((Integer) loginInfo.get("index")); opinion.setPageSize((Integer) loginInfo.get("pageSize")); int option = (Integer) loginInfo.get("option"); switch (option){ case 0: opinion.setSort((Integer) loginInfo.get("data")); break; case 1: opinion.setPositiveId((Integer) loginInfo.get("data")); break; case 2: opinion.setCondition(" AND so.positive_id IN " + (String) loginInfo.get("data")); break; } List<OpinionDomain> opinions = new ArrayList<>(); opinions = opinionService.getOpinionByUserId(opinion); return opinions; } ``` 其opinion.setCondition(" AND so.positive_id IN " + (String) loginInfo.get("data"))拼接了一个动态查询条件,使用的mapper.xml代码如下: ``` <select id="getOpinionByUserId" resultType="com.bensu.sentiment.domain.OpinionDomain"> SELECT so.opinion_id, so.title, so.summarize, so.positive_id, so.origin_id, IFNULL(uo.likes,0)*1 AS likes, so.create_date FROM user_opinion uo, sentiment_opinion so WHERE uo.user_id = #{userId} <if test="positiveId >= 0"> AND positive_id = #{positiveId} </if> <if test="condition != null and condition !=''"> #{condition} </if> AND uo.opinion_id = so.opinion_id <if test="sort ==1"> ORDER BY create_date ASC </if> <if test="sort !=1"> ORDER BY create_date DESC </if> <if test="pageSize !=0"> LIMIT #{currIndex}, #{pageSize} </if> </select> ``` <if test="condition != null and condition !=''"> #{condition} </if> 就是这两行了,但是在运行好像没有编译成正常SQL条件,所以正确的查询结果一直出不来。 请大神指教,这动态条件改怎么处理!
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页