java - unique(true) - 无法准备语句

Question

我正在使用 hibernate 和 spring。但是，当添加 .distinct(true) 时，例如 criteria.select(cb.construct(Customer.class, root.get("Name"))).distinct(true);.然后我收到以下异常:

Exception in thread "AWT-EventQueue-0" javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:273)
    at org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getResultList(CriteriaQueryCompiler.java:254)
    at com.limitCalculator.dao.FilterDaoImpl.getFilter(FilterDaoImpl.java:83)
    at com.limitCalculator.dao.FilterDaoImpl$$FastClassByCGLIB$$a62bf500.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:698)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:631)
    at com.limitCalculator.dao.FilterDaoImpl$$EnhancerByCGLIB$$6ca0bd06.getFilter(<generated>)
    at com.limitCalculator.service.FilterServiceImpl.getFilter(FilterServiceImpl.java:37)
    at com.limitCalculator.gui.timerSelection.MainTabPanel.placeSelectionWithButtons(MainTabPanel.java:137)
    at com.limitCalculator.gui.timerSelection.MainTabPanel.createLayout(MainTabPanel.java:111)
    at com.limitCalculator.gui.timerSelection.MainWindow.createTabBar(MainWindow.java:132)
    at com.limitCalculator.gui.timerSelection.MainWindow.makeLayout(MainWindow.java:182)
    at com.limitCalculator.gui.timerSelection.MainWindow.access$1(MainWindow.java:172)
    at com.limitCalculator.gui.timerSelection.MainWindow$4.run(MainWindow.java:197)
    at java.awt.event.InvocationEvent.dispatch(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$400(Unknown Source)
    at java.awt.EventQueue$2.run(Unknown Source)
    at java.awt.EventQueue$2.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:188)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:159)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1854)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1831)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
    at org.hibernate.loader.Loader.doQuery(Loader.java:899)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
    at org.hibernate.loader.Loader.doList(Loader.java:2516)
    at org.hibernate.loader.Loader.doList(Loader.java:2502)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2332)
    at org.hibernate.loader.Loader.list(Loader.java:2327)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:490)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1268)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:264)
    ... 33 more
Caused by: java.sql.SQLSyntaxErrorException: invalid ORDER BY expression
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
    at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:161)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
    ... 49 more

我的 hibernate 查询如下所示:

hibernate :

select distinct customer0_.Name as col_0_0_ 
from Customer customer0_ 
where (upper(customer0_.Name) like ?) and (upper(customer0_.City) like ?) and
    (upper(customer0_.Country) like ?) 
order by customer0_.customerNr asc

这就是我正在使用的代码:

@Transactional
@SuppressWarnings("all")
public List<Customer> getFilter(String city, String country) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Customer> criteria = cb.createQuery(Customer.class);
    //which table we want to fetch
    final Root root = criteria.from(Customer.class);
    List criteriaList = createWhereCritera(city, country, cb, root);

    //create statement
    criteria.select(cb.construct(Customer.class, root.get("Name"))).distinct(true);
    // Pass the criteria list to the where method of criteria query
    criteria.where(cb.and((Predicate[]) criteriaList.toArray(new Predicate[0])));
    // Order by clause
    criteria.orderBy(cb.asc(root.get("customerNr")));
    return em.createQuery(criteria).getResultList();
}

public List createWhereCritera(String city, String country, CriteriaBuilder cb,
        final Root root) {
    // This list will contain all Predicates (where clauses)
    List criteriaList = new ArrayList();
    // City: 
    Predicate predicateCity = cb.like(cb.upper(root.get("City")),city);
    criteriaList.add(predicateCity); 
    // Country: 
    Predicate predicateCountry = cb.like(cb.upper(root.get("Country")),country);
    criteriaList.add(predicateCountry);
    return criteriaList;
}   

有什么建议吗？为什么会出现此问题以及如何获取所有不同的值？

感谢您的回答!

Answer 1

您的查询有误。Order By 中的任何内容都必须是 Select 子句 的一部分。

当存在 ORDER BY 子句时，它是最后执行的。首先，FROM 子句生成用于检查的对象，然后 WHERE 子句选择要收集哪些对象作为结果。然后，SELECT 子句通过计算结果表达式来构建结果。最后，通过计算 ORDER BY 表达式对结果进行排序。

ORDER BY 子句中只允许使用直接从 SELECT 子句中的表达式派生的表达式。以下查询无效，因为 ORDER BY 表达式不是结果的一部分:

SELECT c.name
FROM Country c
WHERE c.population > 1000000
ORDER BY c.population

另一方面，以下查询是有效的，因为给定国家/地区 c，可以从 c 计算 c.population 表达式:

SELECT c
FROM Country c
WHERE c.population > 1000000
ORDER BY c.population

因此将您的查询更改为如下所示。

select distinct (customer0_.Name as col_0_0_), customer0_.customerNr from Customer customer0_ where (upper(customer0_.Name) like ?) and (upper(customer0_.City) like ?) and (upper(customer0_.Country) like ?) order by customer0_.customerNr asc