CODEWITHSUNDEEP
javaspringspring-dataspring-data-jpa
levi
levi

Reputation: 191

spring jpa @Query error, expecting CLOSE, found '(' near

I have following JPQL:

@Query("SELECT su.id, su.nameCn, count(b.id), avg(s.rate), count(concat(b.id, '@', s.user.id)) "
            + "FROM S su, B b, S s where b.st.id = su.id and s.bd.id = b.id and su.mt.id = ?1 group by su.id")

When I add concat(b.id, '@', s.user.id), it shows me:

org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '(' near line 1, at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:288) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:187) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:115) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:76) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1907) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291) ~[hibernate-entitymanager-5.1.0.Final.jar:5.1.0.Final]

Upvotes: 9

Views: 28480

Answers (2)

Shaan
Shaan

Reputation: 598

Problem is using both count and concatenate function together.

If you write your query without count() like below, should work. This is just for testing to know who is causing the issue exactly.

Try writing query different way using count and concatenate seperatly. 

...concat(b.id, '@', s.user.id)....

Upvotes: 1

OrangeDog
OrangeDog

Reputation: 38769

expecting CLOSE, found (

The term CLOSE here refers to a closing bracket, ).

It would appear that the query parser does not support calling another function inside the count(...).

In other words, the syntax error is here:

SELECT su.id, su.nameCn, count(b.id), avg(s.rate), count(concat(b.id, '@', s.user.id))
                                                               ^

Perhaps you mean to be doing some kind of join, and counting the rows there?

Upvotes: 20

Related Questions