Mybatis的分页条件查询sql_mybatis mapper 分页条件查询-CSDN博客

本文链接：https://blog.csdn.net/java_zhangfei/article/details/113182945

本文介绍了一种使用MyBatis实现的分页查询方法，通过XML映射文件和Java接口结合的方式，实现了带有关键词和媒体ID过滤的动态SQL查询。文章提供了具体的XML配置示例和Java服务实现代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 ?,?