mybatis的动态sql总结

置顶在武汉帮我推荐个工作吧

已于 2022-11-08 11:25:36 修改

阅读量1k

点赞数

分类专栏： mybatis 文章标签： mybatis

于 2019-07-01 16:41:45 首次发布

本文链接：https://blog.csdn.net/zhangshengqiang168/article/details/94397073

版权

mybatis 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1.选择排序
order by
  <choose>
      <when test="status == 0">
         end_time asc ,
      </when>
      <when test="status == 1">
         total_price desc ,
      </when>
      <when test="status == 2">
         amount desc ,
      </when>
      <when test="status == 3">
         `count` desc ,
      </when>
  </choose>
      sort DESC ,create_time DESC

2.模糊查询

product_name LIKE CONCAT('%',ltrim(rtrim(#{keyword})),'%') //去掉前后空格

3.foreach遍历

where id in

#{id}

</foreach>

4.mysql批量插入

<insert id="insertProductCoupon" parameterType="com.cncbox.mall.sales.model.Product">
    insert into sms_coupon_product_relation (id, coupon_id, product_id,
            product_name, product_sn)
    values
          <foreach collection="productList"  item="product" separator=",">
              (null, #{product.couponId,jdbcType=BIGINT}, #{product.productId,jdbcType=BIGINT},
              #{product.productName,jdbcType=VARCHAR}, #{product.productSn,jdbcType=VARCHAR})
          </foreach>
</insert>

5.批量修改 mybatis需要批量执行,需要再数据源连接url后加 &allowMultiQueries=true

<update id="updatePic">
        <foreach collection="productPics" item="list" index="index" open="" close="" separator=";">
            update pms_product
            <set>
                pic=#{list.pic},album_pics=#{list.pic}
            </set>
            where id=#{list.id}
        </foreach>
</update>

<update id="updateTemplate" parameterType="com.macro.mall.dto.CardTemplate">
        <foreach collection="cardTemplateList" item="list" index="index" open="" close="" separator=";">
            update sms_card_template
            <set>
                <if test="list.name != null">
                    `name` = #{list.name,jdbcType=VARCHAR},
                </if>
                <if test="list.pic != null">
                    pic = #{list.pic,jdbcType=VARCHAR},
                </if>
                    create_time = sysdate(),
            </set>
            where id = #{list.id,jdbcType=BIGINT}
        </foreach>
    </update>