mybatis-mysql常用操作

简介：

       使用mysql时，mybatis常用拼接

时间范围查询：

controller：

@ApiParam(name = "startTime", value = "开始时间", required = false) @RequestParam(name = "startTime", required = false) String startTime,
@ApiParam(name = "endTime", value = "结束时间", required = false) @RequestParam(name = "endTime", required = false) String endTime,

map.put("startTime", startTime);
map.put("endTime", endTime);

server：

String startTime = null;
if (map.get("startTime") != null) {
    startTime = map.get("startTime").toString();
}
String endTime = null;
if (map.get("endTime") != null) {
    endTime = map.get("endTime").toString();
}

dao：

@Param("startTime") String startTime,@Param("endTime") String endTime,

mybatis方式一：

<if test="startTime!=null and startTime!='' and endTime!=null and endTime!=''">
    AND
        <![CDATA[ DATE_FORMAT(t.create_time,'%Y-%m-%d')>=DATE_FORMAT(#{startTime},'%Y-%m-%d') ]]>
    AND
        <![CDATA[ DATE_FORMAT(t.create_time,'%Y-%m-%d')<=DATE_FORMAT(#{endTime},'%Y-%m-%d') ]]>
</if>

mybatis方式二： 

<if test="startTime!=null and startTime!='' and endTime!=null and endTime!=''">
    and (DATE_FORMAT(t.create_time,'%Y-%m-%d')  between  #{startTime} and  #{endTime} )
</if>

模糊查询like:

<if test="name!=null">
    and m.name LIKE CONCAT('%', #{name}, '%')
</if>

多字段模糊查询like:       

<if test="name!=null">
    AND (m.`name` LIKE CONCAT('%', #{name}, '%') OR m.`tags` LIKE CONCAT('%',#{name},'%') OR m.code LIKE CONCAT('%',#{name},'%') )
</if>

通过集合查询

@ApiParam(name = "listxxx", value = "查询人员列表", required = false) @RequestParam(name = "listxxx", required = false) List<Long> listxxx,

mybatis:

<if test="listxxx!=null and listxxx.size > 0 ">
   and accountId in
        <foreach collection="listxxx" open="(" close=")" separator="," item="itemId">
            #{itemId}
        </foreach>
</if>