HQL查询不起作用，'，'错误附近的语法不正确。使用Spring和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

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

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

错误[http-8080-2](JDBCExceptionReporter.java:72) - ','附近的语法不正确。

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

org.hibernate.exception.SQLGrammarException:无法执行更新查询

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.

我不明白为什么会这样。我在查询中的任何地方都没有使用“,”但它仍然说“','为什么会出现错误的语法?怎么解决这个?先谢谢你。

3 个解决方案

#1

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.

可能这些变化将帮助您消除问题。

#2

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

我不确定但是试图通过反引号(对于MySQL)或双引号(对于PostgreSQL)或类似来逃避Order。如果您的查询使用原始SQL,那么数据库可能会将其识别为保留关键字(如ORDER BY)。

#3

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.

所有orderStatus表达式的引号。

#1