Mapper的常用写法

1.循环

<foreach collection="userIdList" open="(" close=")" separator=" , " item="userId">
    #{userId}
</foreach>

2.模糊查询

<if test="query.goodsName != null and query.goodsName != ''">
    and gml.goods_name like CONCAT('%',#{query.goodsName},'%')
</if>

3.时间格式化

<if test="query.endQueryTime != null and query.endQueryTime != ''">
    and DATE_FORMAT(gml.modify_time,'%Y-%m-%d %H:%i:%s') &lt;= #{query.endQueryTime}
</if>

4.IFNULL的使用
IFNULL(dg.my_favorite,0)
5.去重复尽量使用嵌套select分组去重

SELECT
*
FROM
goods g
LEFT JOIN (select id,goods_id,my_favorite,driver_code,driver_name from driver_goods where driver_code = #{driverCode}
GROUP BY goods_id) dg ON g.id = dg.goods_id
LEFT JOIN settlement_config sc ON sc.goods_code = g.goods_code
WHERE 1=1
AND g.is_deleted = 0
AND g.is_open=1
AND g.consigner_id = #{orgId}
ORDER BY g.updated_time DESC

Mybatis传入参数0 ,会成为空

从页面传参数 is_send= 0 到mapper.xml
传入的这个参数类型如果不是字符串类型的话
在xml 经过if(test="is_send!=null and is_send !=''") 这样判断的话，那么这个0就会是空
if(test="is_send!=null ) 如果去掉and is_send !=''" 这个,那么就会识别成为0
<result column="is_sent" property="isSent" jdbcType="INTEGER" />