mysql的动态sql总结

缺乏、安全感 2021-12-18 22:45 229阅读 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>