Spring Data Jpa And 和 Or 组合使用的简单方式 |
您所在的位置：网站首页 › jpa连接查询 › Spring Data Jpa And 和 Or 组合使用的简单方式 |
|
最近在做毕设，今天开始实现搜索功能，最开始的时候是打算上es的话，残酷的现实告诉我时间不够，所以还是简单的使用关系型数据库的模糊查询吧。 在对对应的Jpa接口进行测试的时候，我发现他的运行结果不同预期。 首先，根据文章的标题，摘要，内容进行模糊查询，其一包含关键字便是我们需要的数据。但是文章包含已发布和草稿两种状态，那我需要在查询条件上加上相关的字段，所以我最开始写的方法名，测试代码及输出结果如下： 方法名： List findDistinctByArticleModeAndArticleTitleContainingOrArticleSummaryContainingOrArticleContentContaining(int mode,String titleKey,String summaryKey,String contentKey); 测试： List 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 findDistinctByArticleModeAndArticleTitleContainingOrArticleModeAndArticleSummaryContainingOrArticleModeAndArticleContentContaining(int mode,String titleKey,int mode1,String summaryKey,int mode2,String contentKey); 测试代码： List 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 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(); } }
|
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |