MyBatis常用语法

1 将查询出的结果为null 的转换为0

   COALESCE(SUM(s.num),0)

2 null 赋值

	IFNULL(name,0)

2 对二进制位判断位数

status : 二进制字段

 	 AND
     s.status & 1 > 0    ( 第一位为1)
     AND
     s.status & 2 > 0    ( 第二位为1)
     AND
     s.status & 4 > 0    (第三位为1)
     AND
     s.status & 8 > 0    (第四位为1)
     AND
     s.status & 16 > 0   (第五位为1)

3 排除结果null 值

	SELECT
	filed
	FROM
	表
	WHERE
	filed  IS NOT  null

4 批量更新

	<update id="updateBatch">
       <foreach collection="list" separator=";" item="item">
            UPDATE t_product_stock_warn
            <set>
                <if test="item.filed1 != null">
                    filed1 = #{item.filed1,jdbcType=INTEGER},              
                </if>
                <if test="item.filed2 != null">
                    filed1 = #{item.filed2,jdbcType=INTEGER},              
                </if>
            </set>
            WHERE id = #{item.id,jdbcType=INTEGER}
        </foreach>
	</update>

5 批量插入

	INSERT INTO 表(filed1,filed2)
	        VALUES
	        <foreach collection="list" item="item" separator=",">
	            (#{item.filed1,jdbcType=INTEGER},
	            #{item.filed2,jdbcType=VARCHAR})
	        </foreach>

6 模糊查询

	name LIKE concat('%',#{name,jdbcType=VARCHAR},'%')

7 格式化日期

	DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
	DATE_FORMAT(NOW(),'%m-%d-%Y')
	DATE_FORMAT(NOW(),'%d %b %y')
	DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

8 特殊符号

	&            &
    <            &lt;
    >        	 &gt;
    <=			 &lt;=
    >=        	 &gt;=

9 判断集合 !=null 并且 != Empty

<if test="list !=null and list.size()>0 ">
            AND od.supplier_id IN
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{item,jdbcType=INTEGER}
            </foreach>
</if>

10 if else

<choose>
    <when test="brandId == 1 ">
        .....
    </when>
    <otherwise>
        ....
    </otherwise>
</choose>

11 分组统计

SELECT		
		CASE
		WHEN real_amount BETWEEN 10 AND 50 THEN '10-50'
		WHEN real_amount BETWEEN 60 AND 80 THEN '60-80'
		WHEN real_amount BETWEEN 80 AND 200 THEN '80-100'
		ELSE 'other'
		END  AS  'price',
		COUNT(real_amount) total
	FROM
		t_order 
	WHERE
		order_status IN ( '03', '04' ) 
	GROUP BY
		price	

12 截取字符串

• 10-20 -> 20

substring_index(#{priceRange},'-',-1)

• 10-20 -> 10

substring_index(#{priceRange},'-',1)

13 Mapper中获取当前时间戳(毫秒)

REPLACE(UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)),'.','')

14 获取当前时间戳(秒级)

unix_timestamp(now()) = 1346836984

15 时间戳转为日期格式

FROM_UNIXTIME(ROUND(create_time/1000))