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 != ''">
补充:如果数据表中主键自增,然后我们在插入语句时需要同时知道该条语句的主键值,可以使用:
<insert id="add" parameterType="com.health.entity.CheckGroup">
<!--通过mybatis框架提供的selectKey标签获得自增产生的id值,其中keyProperty中的值需的和
CheckGroup数据库中的值保持一致,order中的值是在插入语句完成后再获取idde值!-->
<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
select LAST_INSERT_ID()
</selectKey>
insert into t_checkgroup (code,name,helpCode,sex,remark,attention)
values
(#{code},#{name},#{helpCode},#{sex},#{remark},#{attention})
</insert>
补充:mybatisplus中的and or 用法
QueryWrapper<LargeCustomEntity> douAddLarRepWrapper = new QueryWrapper<>();
douAddLarRepWrapper.eq("is_add_report",YesOrNoEnum.NO.getValue())
.and(i -> i.eq("replenish_status",ReplenishStatusEnum.REPLENISH_STATUS_0.getValue())
.or()
.eq("replenish_status",ReplenishStatusEnum.REPLENISH_STATUS_1.getValue())
.or()
.eq("replenish_status",ReplenishStatusEnum.REPLENISH_STATUS_2.getValue())
.or()
.eq("replenish_status",ReplenishStatusEnum.REPLENISH_STATUS_3.getValue()));
Long douAddLarRep = largeCustomDao.selectCount(douAddLarRepWrapper);
jsons.set("douAddLarRep",douAddLarRep);
实际SQL:SELECT COUNT( * ) FROM t_monitor_large_custom WHERE (is_add_report = ? AND (replenish_status = ? OR replenish_status = ? OR replenish_status = ? OR replenish_status = ?))文章来源:https://www.toymoban.com/news/detail-650043.html
扩展:文章来源地址https://www.toymoban.com/news/detail-650043.html
// A or (B and C)
.eq("a", "A").or(i -> i.eq("b", "B").eq("c", "C"));
// A or (B or C)
.eq("a", "A").or(i -> i.eq("b", "B").or().eq("c", "C"));
// A and (B and C)
.eq("a", "A").and(i -> i.eq("b", "B").eq("c", "C"));
// A and (B or C)
.eq("a", "A").and(i -> i.eq("b", "B").or().eq("c", "C"));
到了这里,关于MyBatis中相关SQL语句的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!