HQL query not working, Incorrect syntax near ',' error. Using Spring and Hibernate

Question

I am trying to execute following update query and getting error,

Query is=

@Transactional
public List<Order> getClosedOrders(String userID) throws DataAccessException {
try { 

String SQL_SELECT_QUERY = "from Order as o where o.orderStatus='closed' and o.account.profile.userId='"+userID+"'";

String SQL_UPDATE_QUERY = "update Order set orderStatus=completed where orderStatus=closed and account.profile.userId='"+userID+"'";

List<Order> orderList = (List<Order>) list(SQL_SELECT_QUERY); 

if(!orderList.isEmpty()) {

batchUpdate(SQL_UPDATE_QUERY);
return orderList;
}
return null;
} catch(Exception ex) {

ex.printStackTrace();
throw new DataAccessException(errorMessage);
} 
}

However Select query is working but for Update query it is giving Following error:

WARN [http-8080-2] (JDBCExceptionReporter.java:71) - SQL Error: 102, SQLState: S0001

ERROR [http-8080-2] (JDBCExceptionReporter.java:72) - Incorrect syntax near ','.

org.hibernate.exception.SQLGrammarException: could not execute update query

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)

at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:334)

at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:209)

I don't understand why this is happening. I am not using "," anywhere in my query but still it says that incorrect syntax near',' Why it is so? How to solve this? Thank you in advance.

That is indeed strange. Only thing that comes to mind is that userID contains a comma, but i'm sure you checked that. — Till, May 11 '11 at 11:11

score 2 · Answer 1 · edited Apr 01 '16 at 10:41

2

First of all:

<property name="hibernate.show.sql" value="true"></property>

It will help you a lot.

Second of all:

String SQL_UPDATE_QUERY = "update Order set orderStatus=completed where orderStatus=closed and account.profile.userId=:userId";

and use

addString("userId",userId);

May be these changes will help you to eliminate problem.

edited Apr 01 '16 at 10:41

N00b Pr0grammer

4,503
5
32
46

answered May 11 '11 at 11:18

danny.lesnik

18,479
29
135
200

Added that property but don't know why its not showing queries. – Sagar May 11 '11 at 12:09
"update Order set orderStatus=completed where orderStatus=closed and account.profile.userId='"+userID+"'"; This query I want to execute. – Sagar May 11 '11 at 12:40
@Sugar, this is HQL query, and what about SQL query? you shoul enable hibernate.show.sql and then see original SQL query in console. – danny.lesnik May 11 '11 at 12:45

score 1 · Answer 2 · answered May 11 '11 at 18:11

1

I not sure but try to escape Order by backticks (for MySQL) or double quotes (for PostgreSQL) or similar. If your query uses as raw SQL then database may recognize it as reserved keyword (like ORDER BY).

answered May 11 '11 at 18:11

Slava Semushin

14,904
7
53
69

score 0 · Answer 3 · answered May 12 '11 at 00:24

You are missing the quotes in this one

String SQL_UPDATE_QUERY = "update Order set orderStatus=completed where orderStatus=closed and account.profile.userId='"+userID+"'";

Shouldn't it actually be

String SQL_UPDATE_QUERY = "update Order set orderStatus='completed' where orderStatus='closed' and account.profile.userId='"+userID+"'";

Quotes for all orderStatus expressions.

HQL query not working, Incorrect syntax near ',' error. Using Spring and Hibernate

3 Answers3