MyBatis中執(zhí)行相關SQL語句的方法

更新時間：2023年08月16日 15:53:42 作者：weixin_46949892

本文主要介紹了MyBatis中執(zhí)行相關SQL語句的方法,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

1.between... and...

<if test="(reportStartDate != null and reportStartDate != '') || (reportEndDate != null and reportEndDate != '')">
    and report_date between #{reportStartDate} AND #{reportEndDate}
</if>

2.and or

<if test="method != null">
    and ( Method like CONCAT('%', #{key ,jdbcType=VARCHAR}, '%')
    or EventCode like CONCAT('%', #{key ,jdbcType=VARCHAR}, '%')
    or EventName like CONCAT('%', #{key ,jdbcType=VARCHAR}, '%')
    )
</if>

3.like ---兩種寫法

<!--第一種寫法-->
<where>          
  <if test="reportRule != null and reportRule != ''">
      and report_rule like CONCAT('%',#{reportRule},'%')
  </if>           
</where>
<!--第二種寫法-->         
<where>
  <if test="custName != null and custName != ''">
      and cust_name like '%' #{custName} '%'
  </if>
  <if test="creater != null and creater != ''">
       and creater like '%' #{creater} '%'
  </if>
</where>

完整示例：

<select id="findByQueryIds"  parameterType="string" resultType="java.lang.Integer">
    select id from t_monitor_suspicion_custom
    <where>
      <if test="(reportStartDate != null and reportStartDate != '') || (reportEndDate != null and reportEndDate != '')">
         and report_date between #{reportStartDate} AND #{reportEndDate}
      </if>
      <if test="reportRule != null and reportRule != ''">
         and report_rule like CONCAT('%',#{reportRule},'%')
      </if>
      <if test="custNo != null and custNo != ''">
         and cust_no like CONCAT('%',#{custNo},'%')
      </if>
      <if test="custName != null and custName != ''">
          and cust_name like CONCAT('%',#{custName},'%')
      </if>
      <if test="customType != null and customType != ''">
          and custom_type = #{customType}
      </if>
   </where>
   and (suspicion_status = #{value} or suspicion_status = #{value1})
</select>

前端傳入cust_no為1019，后端實際查詢語句

[zl-aml-admin] DEBUG 2023-08-15 10:44:14.514 [http-nio-8081-exec-20] com.zlpay.modules.monitor.dao.SuspicionCustomDao.findByQueryIds [BaseJdbcLogger.java:137] - ==> Preparing: select id from t_monitor_suspicion_custom WHERE cust_no like CONCAT('%',?,'%') and (suspicion_status = ? or suspicion_status = ?)
[zl-aml-admin] DEBUG 2023-08-15 10:44:14.516 [http-nio-8081-exec-20] com.zlpay.modules.monitor.dao.SuspicionCustomDao.findByQueryIds [BaseJdbcLogger.java:137] - ==> Parameters: 1019(String), 0(String), 3(String)
[zl-aml-admin] DEBUG 2023-08-15 10:44:14.519 [http-nio-8081-exec-20] com.zlpay.modules.monitor.dao.SuspicionCustomDao.findByQueryIds [BaseJdbcLogger.java:137] - <== Total: 1

注意：由于一開始where語句只寫了 <if test="reportRule != null>沒有寫reportRule != ''" ，導致查詢結果出錯，所以我們如果用到動態(tài)查詢的話，一定要搞清楚前端傳的是空值還是null值，如果不確定的話，就都判斷一下<if test="reportRule != null and reportRule != ''">

到此這篇關于MyBatis中執(zhí)行相關SQL語句的方法的文章就介紹到這了,更多相關MyBatis 執(zhí)行SQL語句內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: