Spring Data Jpa And 和 Or 组合使用的简单方式

最新推荐文章于 2024-07-20 20:09:56 发布

endxu

最新推荐文章于 2024-07-20 20:09:56 发布

阅读量1.1w

点赞数 2

文章标签： spring data jpa

本文链接：https://blog.csdn.net/weixin_37211894/article/details/88887809

版权

最近在做毕设，今天开始实现搜索功能，最开始的时候是打算上es的话，残酷的现实告诉我时间不够，所以还是简单的使用关系型数据库的模糊查询吧。

在对对应的Jpa接口进行测试的时候，我发现他的运行结果不同预期。

首先，根据文章的标题，摘要，内容进行模糊查询，其一包含关键字便是我们需要的数据。但是文章包含已发布和草稿两种状态，那我需要在查询条件上加上相关的字段，所以我最开始写的方法名，测试代码及输出结果如下：

方法名：
List<Article> findDistinctByArticleModeAndArticleTitleContainingOrArticleSummaryContainingOrArticleContentContaining(int mode,String titleKey,String summaryKey,String contentKey);

测试：
List<Article> articles = articleDao.findDistinctByArticleModeAndArticleTitleContainingOrArticleSummaryContainingOrArticleContentContaining(2,"吃","吃","吃");
for(Article article:articles){
    System.out.println(article.toString());
}

控制台输出：
2019-03-29 11:49:38.036  INFO 4216 --- [           main] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select distinct article0_.id as id1_0_, article0_.article_click as article_2_0_, article0_.article_content as article_3_0_, article0_.article_keyword as article_4_0_, article0_.article_like as article_5_0_, article0_.article_mode as article_6_0_, article0_.article_sort_id as article_7_0_, article0_.article_summary as article_8_0_, article0_.article_time as article_9_0_, article0_.article_title as article10_0_, article0_.articleuuid as article11_0_, article0_.last_update_time as last_up12_0_, article0_.user_id as user_id13_0_, article0_.user_name as user_na14_0_ from blog_article article0_ where article0_.article_mode=? and (article0_.article_title like ?) or article0_.article_summary like ? or article0_.article_content like ?
Article(id=842, articleUUID=b4511aa8b8e94fa1a82d9084944a5a8e, articleTitle=吃饭了吗, articleKeyword=没吃呢, articleSummary=没吃呢, articleContent=没吃呢, articleTime=2019-03-29 10:57:43.0, lastUpdateTime=2019-03-29 10:57:43.0, articleClick=0, articleLike=0, articleSortId=0, userId=217, userName=beyond.yang, articleMode=2, hasLike=false, hasClick=false, aNew=false)
Article(id=843, articleUUID=01a5198911014c48a7efc3caac42c559, articleTitle=吃饭了吗, articleKeyword=吃了吗, articleSummary=吃了个鬼, articleContent=没钱吃饭, articleTime=2019-03-29 10:58:56.0, lastUpdateTime=2019-03-29 11:45:36.0, articleClick=1, articleLike=0, articleSortId=0, userId=217, userName=beyond.yang, articleMode=1, hasLike=false, hasClick=false, aNew=false)

输出结果与预期不一致，输出了两篇文章，其中一篇articleMode为1，与预期不符，只要原因在于这个方法生成的sql条件为：

where article0_.article_mode=? and (article0_.article_title like ?) or article0_.article_summary like ? or article0_.article_content like ?

而我期望的结果为：

where article0_.article_mode=? and (article0_.article_title like ? or article0_.article_summary like ? or article0_.article_content like ?）

百度了一下and和or如何组合查询的解决方法，感觉比较复杂，如果通过直接写query的方法，在要支持分页的时候比较麻烦，查到通过jpa本身来解决的方法比较复杂，根据输出的SQL我想到了一种比较简单但是不是特别优雅的方法，方法名，测试代码及输出结果如下：

方法名：
List<Article> findDistinctByArticleModeAndArticleTitleContainingOrArticleModeAndArticleSummaryContainingOrArticleModeAndArticleContentContaining(int mode,String titleKey,int mode1,String summaryKey,int mode2,String contentKey);

测试代码：
List<Article> articles = articleDao.findDistinctByArticleModeAndArticleTitleContainingOrArticleModeAndArticleSummaryContainingOrArticleModeAndArticleContentContaining(1,"吃",1,"吃",1,"吃");
for(Article article:articles){
    System.out.println(article.toString());
}

输出结果：
2019-03-29 11:56:54.516  INFO 12008 --- [           main] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select distinct article0_.id as id1_0_, article0_.article_click as article_2_0_, article0_.article_content as article_3_0_, article0_.article_keyword as article_4_0_, article0_.article_like as article_5_0_, article0_.article_mode as article_6_0_, article0_.article_sort_id as article_7_0_, article0_.article_summary as article_8_0_, article0_.article_time as article_9_0_, article0_.article_title as article10_0_, article0_.articleuuid as article11_0_, article0_.last_update_time as last_up12_0_, article0_.user_id as user_id13_0_, article0_.user_name as user_na14_0_ from blog_article article0_ where article0_.article_mode=? and (article0_.article_title like ?) or article0_.article_mode=? and (article0_.article_summary like ?) or article0_.article_mode=? and (article0_.article_content like ?)
Article(id=843, articleUUID=01a5198911014c48a7efc3caac42c559, articleTitle=吃饭了吗, articleKeyword=吃了吗, articleSummary=吃了个鬼, articleContent=没钱吃饭, articleTime=2019-03-29 10:58:56.0, lastUpdateTime=2019-03-29 11:45:36.0, articleClick=1, articleLike=0, articleSortId=0, userId=217, userName=beyond.yang, articleMode=1, hasLike=false, hasClick=false, aNew=false)

可以看到，这个方法生成的查询条件：where article0_.article_mode=? and (article0_.article_title like ?) or article0_.article_mode=? and (article0_.article_summary like ?) or article0_.article_mode=? and (article0_.article_content like ?)

虽然这样的写法有点奇怪，不过足够简单的解决了问题，之后要加上分页支持只需要在方法参数加上Pageable

==================================分割线==================================

使用Specification正儿八经的解决方法

具体做法是实现Specification接口的toPredicate方法

通过CriteraBuilder创建需要的Predicate

首先，生成三个需要like的条件生成Predicate，由于有多个条件，你并不能保障每个条件都传进来，所以使用List容器转起来

我们需要的where限制是where article0_.article_mode=? and (article0_.article_title like ? or article0_.article_summary like ? or article0_.article_content like ?），也就是三个模糊匹配like的条件，需要先进行and操作，由于有多个条件，你并不能保障每个条件都传进来，所以使用List容器转起来，生成and条件的Predicate的时候转换为应用类型对应的数据

接下来，生成article_mode的Predicate，直接用equal

最后直接将两个Predicate通过query.where返回，生成的查询调价便是我们期望的

class ArticleSearchSpecification implements Specification{
    private Article article;

    public ArticleSearchSpecification(Article article){
        this.article = article;
    }

    @Override
    public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {
        List<Predicate> predicates = new ArrayList<>();
        if(article.getArticleTitle() != null && !article.getArticleTitle().equals("")){
            predicates.add(cb.like(root.get("articleTitle").as(String.class),"%"+article.getArticleTitle()+"%"));
        }
        if(article.getArticleSummary() != null && !article.getArticleSummary().equals("")){
            predicates.add(cb.like(root.get("articleSummary").as(String.class),"%"+article.getArticleSummary()+"%"));
        }
        if(article.getArticleContent() != null && !article.getArticleContent().equals("")){
            predicates.add(cb.like(root.get("articleContent").as(String.class),"%"+article.getArticleContent()+"%"));
        }

        Predicate predicateOr = cb.or(predicates.toArray(new Predicate[predicates.size()]));
        predicateOr = cb.and(predicateOr);

        Predicate predicateAnd;
        if(article.getArticleMode() != ArticleConstant.ARTICLE_DRAFT
                && article.getArticleMode()!= ArticleConstant.ARTICLE_PUBLIC){
            predicateAnd = cb.equal(root.get("articleMode"),ArticleConstant.ARTICLE_PUBLIC);
        }else{
            predicateAnd = cb.equal(root.get("articleMode"),article.getArticleMode());
        }

        return query.where(predicateOr,predicateAnd).getRestriction();
    }
}