快捷導(dǎo)航

MyBatis中執(zhí)行相關(guān)SQL語(yǔ)句的方法

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

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

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 ---兩種寫(xiě)法

<!--第一種寫(xiě)法-->
<where>          
  <if test="reportRule != null and reportRule != ''">
      and report_rule like CONCAT('%',#{reportRule},'%')
  </if>           
</where>
<!--第二種寫(xiě)法-->         
<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，后端實(shí)際查詢(xún)語(yǔ)句

[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

注意：由于一開(kāi)始where語(yǔ)句只寫(xiě)了 <if test="reportRule != null>沒(méi)有寫(xiě)reportRule != ''" ，導(dǎo)致查詢(xún)結(jié)果出錯(cuò)，所以我們?nèi)绻玫絼?dòng)態(tài)查詢(xún)的話，一定要搞清楚前端傳的是空值還是null值，如果不確定的話，就都判斷一下<if test="reportRule != null and reportRule != ''">

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

您可能感興趣的文章: