MyBatis中相关SQL语句
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
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 != ''" 导致查询结果出错所以我们如果用到动态查询的话一定要搞清楚前端传的是空值还是null值如果不确定的话就都判断一下<if test="reportRule != null and reportRule != ''">