CODEWITHSUNDEEP
sqlhibernatespringspring-mvchql
Sagar
Sagar

Reputation: 1262

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

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.

Upvotes: 1

Views: 20691

Answers (3)

danny.lesnik
danny.lesnik

Reputation: 18639

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.

Upvotes: 2

Sab Than
Sab Than

Reputation: 171

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.

Upvotes: 0

Slava Semushin
Slava Semushin

Reputation: 15204

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).

Upvotes: 1

Related Questions