使用Criteria进行分组求和、排序、模糊查询的实例

使用Criteria进行分组求和、排序、模糊查询的实例

目录Criteria进行分组求和、排序、模糊查询1.Entity如下2.repository如下3.service如下4.顺便提及一个不经意间的小错误Criteria进行模糊查询实现站内搜索功能Dao层的方法如下

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,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 criteriaQuery = criteriaBuilder.createQuery(Tuple.class );

root root = criteriaQuery.from(TestEntity.class);

List 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 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进行模糊查询实现站内搜索功能

今天给网站新加入了一个站内搜索的功能http://，思想是：使用Criteria进行模糊查询。

Dao层的方法如下

//搜索方法

public List 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();

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。