使用Criteria進(jìn)行分組求和、排序、模糊查詢的實(shí)例
Criteria進(jìn)行分組求和、排序、模糊查詢
工程框架使用的是spring data,但是spring data 提供的 JpaRepository 以及覆寫(xiě)JpaSpecificationExecutor接口并不能滿足我的要求,我要進(jìn)行模糊查詢,并根據(jù)bookId對(duì)數(shù)量進(jìn)行分組聚合,并獲取數(shù)量最高的top n,由于模糊查詢并不是必填條件,所以直接使用@Query注解感覺(jué)也不是很合適,于是采用Criteria來(lái)實(shí)現(xiàn)。
1.Entity如下
package com.example.springdatatest.repository.entity;? import lombok.Data;? import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.Table; ? @Table @Data @Entity public class TestEntity { ? ? ? @Id ? ? String testId; ? ? ? @Column ? ? public String regionCode; ? ? ? @Column ? ? public long count; ? ? ? @Column ? ? public String bookId;? ? ? ? @Column ? ? public String libraryCode; }
2.repository如下
package com.example.springdatatest.repository;? import com.example.springdatatest.repository.entity.TestEntity; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.JpaSpecificationExecutor;? public interface TestRepository extends JpaRepository<TestEntity,String>,JpaSpecificationExecutor { }
3.service如下
package com.example.springdatatest.service;? import com.example.springdatatest.repository.TestRepository; import com.example.springdatatest.repository.entity.TestEntity; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; ? import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import javax.persistence.Tuple; import javax.persistence.criteria.*; import java.util.ArrayList; import java.util.List; ? @Service public class TestService { ? ? ? @Autowired ? ? TestRepository testRepository; ? ? ? @Autowired ? ? @PersistenceContext ? ? private EntityManager entityManager;? ? ? ? public void test(){ ? ? ? ? CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); ? ? ? ? CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createQuery(Tuple.class ?);? ? ? ? ? Root root = criteriaQuery.from(TestEntity.class); ? ? ? ? ? List<Predicate> predicateList = new ArrayList<>(); ? ? ? ? predicateList.add(criteriaBuilder.equal( root.get("libraryCode" ), "1" )); ? ? ? ? predicateList.add(criteriaBuilder.like(root.get("regionCode"),"%"+"6101"+"%")); ? ? ? ? Predicate[] p = new Predicate[predicateList.size()]; ? ? ? ? predicateList.toArray(p); ? ? ? ? ? Path bookId = root.get("bookId"); ? ? ? ? Path bookCount = root.get("count");? ? ? ? ? criteriaQuery.where(p) ? ? ? ? ? ? ? ? .multiselect(bookId,criteriaBuilder.sum(bookCount)) ? ? ? ? ? ? ? ? .groupBy(bookId) ? ? ? ? ? ? ? ? .orderBy(criteriaBuilder.desc(criteriaBuilder.sum(bookCount))); ? ? ? ? ? List<Tuple> list = entityManager.createQuery(criteriaQuery).setFirstResult(1) ? ? ? ? ? ? ? ? .setMaxResults(1) ? ? ? ? ? ? ? ? .getResultList();? ? ? } }
4.順便提及一個(gè)不經(jīng)意間的小錯(cuò)誤
也是由于粗心,沒(méi)有寫(xiě)這一句 predicateList.toArray(p); ,導(dǎo)致一直報(bào)空指針異常。
java.lang.NullPointerException
at org.hibernate.query.criteria.internal.predicate.CompoundPredicate.render(CompoundPredicate.java:166)
at org.hibernate.query.criteria.internal.predicate.CompoundPredicate.render(CompoundPredicate.java:115)
at org.hibernate.query.criteria.internal.predicate.CompoundPredicate.render(CompoundPredicate.java:105)
at org.hibernate.query.criteria.internal.QueryStructure.render(QueryStructure.java:248)
at org.hibernate.query.criteria.internal.CriteriaQueryImpl.interpret(CriteriaQueryImpl.java:292)
at org.hibernate.query.criteria.internal.compile.CriteriaCompiler.compile(CriteriaCompiler.java:149)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:3707)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:208)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:350)
at com.sun.proxy.$Proxy81.createQuery(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:309)
at com.sun.proxy.$Proxy81.createQuery(Unknown Source)
at com.example.springdatatest.service.TestService.test(TestService.java:50)
at com.example.springdatatest.SpringdatatestApplicationTests.contextLoads(SpringdatatestApplicationTests.java:19)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
這只是實(shí)現(xiàn)的一個(gè)小demo,具體入?yún)⒉](méi)有體現(xiàn),在此做一個(gè)記錄。
Criteria進(jìn)行模糊查詢實(shí)現(xiàn)站內(nèi)搜索功能
今天給網(wǎng)站新加入了一個(gè)站內(nèi)搜索的功能,思想是:使用Criteria進(jìn)行模糊查詢。
Dao層的方法如下
//搜索方法 public List<Question> findSearch(String scon) { ? ? ? ? Session s = ?getHibernateTemplate().getSessionFactory().openSession(); ? ? ? ? Criteria criteria =s.createCriteria(Question.class); ? ? ? ? criteria.add(Expression.or(Expression.like("qdesc","%"+scon+"%"),Expression.like("qname","%"+scon+"%"))); ? ? ? ? return criteria.list(); ? ? }
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
springBoot項(xiàng)目集成quartz開(kāi)發(fā)定時(shí)任務(wù)案例及注意事項(xiàng)
這篇文章主要介紹了springBoot項(xiàng)目集成quartz開(kāi)發(fā)定時(shí)任務(wù)案例及注意事項(xiàng),這些功能的主要接口(API)是Scheduler接口。它提供了簡(jiǎn)單的操作,例如:將任務(wù)納入日程或者從日程中取消,開(kāi)始/停止/暫停日程進(jìn)度,需要的朋友可以參考下2022-06-06記一次springboot服務(wù)凌晨無(wú)故宕機(jī)問(wèn)題的解決
這篇文章主要介紹了記一次springboot服務(wù)凌晨無(wú)故宕機(jī)問(wèn)題的解決,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-09-09java Swing基礎(chǔ)教程之圖形化實(shí)例代碼
這篇文章主要介紹了java Swing基礎(chǔ)教程之圖形化實(shí)例代碼的相關(guān)資料,需要的朋友可以參考下2017-02-02Spring Boot 實(shí)現(xiàn)Restful webservice服務(wù)端示例代碼
這篇文章主要介紹了Spring Boot 實(shí)現(xiàn)Restful webservice服務(wù)端示例代碼,非常不錯(cuò),具有參考借鑒價(jià)值,需要的朋友可以參考下2017-11-11Java實(shí)現(xiàn)插入公式到PPT的示例代碼
PPT中可插入公式,用于放映時(shí)演示相關(guān)內(nèi)容的論證、推算的依據(jù)。通過(guò)代碼,我們可借助特定的工具來(lái)實(shí)現(xiàn)在PPT中的插入公式,本文將對(duì)此作詳細(xì)介紹2022-07-07Mybatis自關(guān)聯(lián)查詢一對(duì)多查詢的實(shí)現(xiàn)示例
這篇文章主要介紹了Mybatis自關(guān)聯(lián)查詢一對(duì)多查詢的實(shí)現(xiàn)示例,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02