如何使用Criteria進行分組求和、排序、模糊查詢

這篇文章主要為大家展示了“如何使用Criteria進行分組求和、排序、模糊查詢”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“如何使用Criteria進行分組求和、排序、模糊查詢”這篇文章吧。

Criteria進行分組求和、排序、模糊查詢

工程框架使用的是spring data,但是spring data 提供的 JpaRepository 以及覆寫JpaSpecificationExecutor接口并不能滿足我的要求，我要進行模糊查詢，并根據bookId對數量進行分組聚合，并獲取數量最高的top n，由于模糊查詢并不是必填條件，所以直接使用@Query注解感覺也不是很合適，于是采用Criteria來實現。

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.順便提及一個不經意間的小錯誤

也是由于粗心，沒有寫這一句  predicateList.toArray(p); ，導致一直報空指針異常。

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)

這只是實現的一個小demo，具體入參并沒有體現，在此做一個記錄。

Criteria進行模糊查詢實現站內搜索功能

今天給網站新加入了一個站內搜索的功能，思想是：使用Criteria進行模糊查詢。

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();
    }

以上是“如何使用Criteria進行分組求和、排序、模糊查詢”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！