MyBatis中相关SQL语句

程序猿小何

已于 2023-09-11 14:01:52 修改

阅读量343

点赞数 2

文章标签： sql mybatis 数据库

于 2023-08-15 11:06:49 首次发布

本文链接：https://blog.csdn.net/weixin_46949892/article/details/132294319

版权

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 = ?))

扩展：

// 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"));