代码之家 › 专栏 › 技术社区 › Carol.Kar

distinct(true)-无法准备语句

entitymanager hibernate java

Carol.Kar · 技术社区 · 11 年前

我正在使用春季冬眠。但是,当添加 .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

我的休眠查询如下所示:

冬眠:

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

有什么建议,为什么会出现这个问题,以及如何获得所有不同的值?

谢谢你的回答!

2 回复 | 直到 11 年前

Ankur Singhal 11 年前

您的查询错误。里面有什么 Order By has to be a part of Select clause .

当 ORDER BY 子句存在,它是最后执行的。首先 FROM 子句生成要检查的对象,WHERE子句选择要收集哪些对象作为结果。然后 SELECT 子句通过计算结果表达式来生成结果。最后,通过对 订购者 表达。

仅从 选择 ORDERBY子句中允许使用子句。以下内容 查询无效 因为 订购者 表达式不是结果的一部分:

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

luboskrnac 8 年前

对于条件查询,您可以通过 group by 基于根表的主键列:

criteria.groupBy(root.get("id")); // Assuming that Customer.id is primary key column