I am using hibernate with spring. However, when adding .distinct(true) like criteria.select(cb.construct(Customer.class, root.get("Name"))).distinct(true);. Then I am receiving the following exception:
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
My hibernate query looks like that:
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
Thats the code I am using:
@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;
}
Any recommendation, why this issue occurs and how to get all distinct values?
I appreciate your answer!
Your query is wrong.
Whatever is there in Order By has to be a part of Select clause.
When an ORDER BY clause exists it is the last to be executed. First the FROM clause produces objects for examination and the WHERE clause selects which objects to collect as results. Then the SELECT clause builds the results by evaluating the result expressions. Finally the results are ordered by evaluation of the the ORDER BY expressions.
Only expressions that are derived directly from expressions in the SELECT clause are allowed in the ORDER BY clause. The following query is invalid because the ORDER BY expression is not part of the results:
SELECT c.name
FROM Country c
WHERE c.population > 1000000
ORDER BY c.population
On the other hand, the following query is valid because, given a Country c, the c.population expression can be evaluated from c:
SELECT c
FROM Country c
WHERE c.population > 1000000
ORDER BY c.population
so change your query to something like below.
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