Criteria进行分组求和、排序、模糊查询
工程框架使用的是spring data,但是spring data 提供的 JpaRepository 以及覆写JpaSpecificationExecutor接口并不能满足我的要求，我要进行模糊查询，并根据bookId对数量进行分组聚合，并获取数量最高的top n，由于模糊查询并不是必填条件，所以直接使用@Query注解感觉也不是很合适，于是采用Criteria来实现。
1.Entity如下
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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如下
|
1 2 3 4 5 6 |
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如下
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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层的方法如下
|
1 2 3 4 5 6 7 |
//搜索方法 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(); } |
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。
原文链接：https://blog.csdn.net/qq_27707957/article/details/99709429
查看更多关于使用Criteria进行分组求和、排序、模糊查询的实例的详细内容...