mybatis

常用xml

<!-- 模糊查询 -->
<if test="name != null and name != ''">
  and o.name like concat('%', #{name,jdbcType=VARCHAR},'%')
</if>

<!-- mybatis 循环查询 -->
<if test="name != null">
  <choose>
    <when test="nameIds != null and nameIds.size() > 0">
        <foreach collection="nameIds" open="(" close=")" separator="," item="item">
          #{item,jdbcType=BIGINT}
        </foreach>
    </when>
    
    <otherwise>
            ('')
    </otherwise>
  </choose>
</if>

<if test = "nameIds != null and nameIds.size() > 0 ">
  and r.id in
  <foreach collection="nameIds" item="id" index="index" open="(" close=")" separator=",">
    #{id}
  </foreach>
</if>

<!-- 排序 -->
<choose>
  <when test="query.sortField == null">
    order by r.id desc
  </when>

  <!-- 0-app排序-->
  <otherwise>
    <choose>
      <when test="query.sortField == 0">
          order by (case r.state when 2 then 0 else 1 end) asc, r.id asc
      </when>
    </choose>
  </otherwise>
</choose>

<!-- 时间跨度 -->
<if test="createTimeStart != null">
  and a.create_time <![CDATA[ >= ]]> #{createTimeStart,jdbcType=TIMESTAMP}
</if>
     
<if test="createTimeEnd != null">
  and a.create_time <![CDATA[ <= ]]> #{createTimeStart,jdbcType=TIMESTAMP}
</if>

<!-- 批量插入 -->
<insert id="insertList">
  INSERT INTO demo( id, standard_price, bottom_price, modifer, modify_time )VALUES
  <foreach collection="list" item="element" index="index" separator=",">
    (
      #{element.id,jdbcType=BIGINT},
      #{element.standardPrice,jdbcType=DECIMAL},
      #{element.bottomPrice,jdbcType=DECIMAL},
      #{element.modifer,jdbcType=VARCHAR},
      #{element.modifyTime,jdbcType=TIMESTAMP}
    )
  </foreach>
</insert>

<!-- 批量更新 -->
<update id="updateBatch" parameterType="java.util.List">
    <!--@mbg.generated-->
    update demo
    <trim prefix="set" suffixOverrides=",">
      <trim prefix="company_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.companyId,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="project_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.projectId,jdbcType=BIGINT}
        </foreach>
      </trim>
    </trim>
    where id in
    <foreach close=")" collection="list" item="item" open="(" separator=", ">
      #{item.id,jdbcType=BIGINT}
    </foreach>
</update>

MyBatis Plus 查询方法

函数名	说明
eq	=
ne	<>
gt	>
ge	>=
lt	<
le	<=
between	between
notBetween	not between
like	like %key%
notLike	not like %key%
likeLeft	like %key
likeRight	like key%
isNull	is null
isNotNull	is not null
in	in
notIn	not in
inSql	in (sql语句)
notInSql	not in (sql语句)
groupBy	group by
orderByAsc	order by 字段 asc
orderByDesc	order by 字段 desc
orderBy	order by 字段 asc/desc
having	having(sql语句)
or	a or b
and	a and b
apply	拼接sql，消除sql注入风险
last	无视优化规则，直接拼接到sql的最后（有sql注入的风险）
exists	exists(sql语句)
notExists	not exists(sql语句)
nested	正常嵌套，不带and或者or