Mybatis的分页条件查询sql

Mybatis 分页条件查询记录

• ArticleMapper.xml 展示

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ls.mapper.ArticleMapper">

    <select id="findByPage" parameterType="java.util.HashMap" resultType="com.ls.entity.Article">
        SELECT
            DATE_FORMAT(article_date,'%Y-%m-%d') as articleDate,article_id as articleId,
            del_flag as delFlag,item_id as itemId,user_id as userId,media_id as mediaId,
            emotional,article_content as articleContent,title,url
        FROM
            article
        WHERE
            del_flag = #{delFlag} AND item_id = #{itemId}
            <!-- 关键词查询 -->
            <if test="keys != null">
                AND
                <foreach collection="keys" item="item" open="(" close=")" separator="or">
                    title LIKE "%"#{item}"%" OR article_content LIKE "%"#{item}"%"
                </foreach>
            </if>
            <!-- 媒体查询 -->
            <if test="medias != null">
                AND
                <foreach collection="medias" item="media" open="(" close=")" separator="or">
                    media_id = #{media}
                </foreach>
            </if>
        LIMIT #{currentPage},#{pageSize}
    </select>

</mapper>

• ArticleMapper

public interface ArticleMapper extends BaseMapper<Article> {

    List<Article> findByPage(Map<String, Object> articleMap);

}

• ArticleServiceImpl

@Override
    public Map<String, Object> getArticleSearch(Long itemId,String[] keys, String[] medias, Integer currentPage) {
        Map<String, Object> map = new HashMap<>();

        Map<String, Object> articleMap = new HashMap<>();
        articleMap.put("delFlag",1);
        articleMap.put("itemId",itemId);
        if(keys.length > 0){
            articleMap.put("keys",keys);
        }
        if(medias.length > 0){
            articleMap.put("medias",medias);
        }

        articleMap.put("currentPage",(currentPage-1)*Consts.PAGE_SIZE);
        articleMap.put("pageSize",Consts.PAGE_SIZE);
        List<Article> list = articleMapper.findByPage(articleMap);

        map.put("articleList",parseArticle(list));
        map.put("count",list.size());
        return map;
    }

• 执行之后的SQL语句

SELECT DATE_FORMAT(article_date,'%Y-%m-%d') as articleDate,article_id as articleId, del_flag as delFlag,item_id as itemId,user_id as userId,media_id as mediaId, emotional,article_content as articleContent,title,url FROM article WHERE del_flag = ? AND item_id = ? AND ( title LIKE "%"?"%" OR article_content LIKE "%"?"%" or title LIKE "%"?"%" OR article_content LIKE "%"?"%" ) AND ( media_id = ? or media_id = ? ) LIMIT ?,? 

• 控制台输出
  

• 数据库查询展示
  

此文，我是使用map来封装参数，只记录自己的经历。。。