java SQL 错误：933，SQLState：42000 和 ORA-00933：SQL 命令未正确结束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11852223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Error: 933, SQLState: 42000 and ORA-00933: SQL command not properly ended
提问by Wires77
I'm using Hibernate for database access. I'm using the following query in my code to fetch the data I need:
我正在使用 Hibernate 进行数据库访问。我在我的代码中使用以下查询来获取我需要的数据：
SELECT proasset
FROM com.company.claims.participant.AbstractBeneficiary bene
JOIN bene.approvals approval
JOIN bene.proassetkey proasset
join proasset.relatedparties proassetparties
WHERE approval.user_dt > :currentDate
AND approval.user_type = :userType
I'm using it as queryin the following:
我使用它query如下：
Query q = this.getSessionFactory().getCurrentSession().createSQLQuery(query.toString())
q.setDate("currentDate", new Date());
q.setString("userType", APPROVER_USER_TYPE);
List<ProAsset> proassets = q.list();
However, I encounter the following when trying to run it:
但是，我在尝试运行它时遇到以下问题：
SQL Error: 933, SQLState: 42000
ORA-00933: SQL command not properly ended
If it matters, the query is being constructed using a StringBuilderand it uses \nto break the lines
如果重要，查询是使用 a 构造的StringBuilder，它用于\n断行
Any thoughts on the problem?
对这个问题有什么想法吗？
回答by Bill Rosmus
It looks like you are trying to mix ORM with a native (plain old SQL) query.
看起来您正在尝试将 ORM 与本机（普通旧 SQL）查询混合使用。
createSQLQuery requires native SQL. You are using classes instead of table names. Try a read of this:
createSQLQuery 需要本机 SQL。您正在使用类而不是表名。尝试阅读以下内容：
http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html
http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html
In short you need to write a query like:
简而言之，您需要编写如下查询：
select fu
from bar
where situation = 'snafu'
Perhaps you are really wanting to write a namedQuery? They use ORM syntax where you join entities as it seems you are doing in your example.
也许你真的想写一个namedQuery？他们使用 ORM 语法，您在其中加入实体，就像您在示例中所做的那样。
Check these examples out:
查看这些示例：
http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#querysql-namedqueries
http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#querysql-namedqueries
回答by Sai Ye Yan Naing Aye
Correct the syntax by removing the inappropriate clauses. It may be possible to duplicate the removed clause with another SQL statement. For example, to order the rows of a view, do so when querying the view and not when creating it. This error can also occur in SQL*Forms applications if a continuation line is indented. Check for indented lines and delete these spaces.
通过删除不适当的子句来更正语法。可以用另一个 SQL 语句复制已删除的子句。例如，要对视图的行进行排序，请在查询视图时而不是在创建视图时这样做。如果连续行缩进，该错误也可能出现在 SQL*Forms 应用程序中。检查缩进的行并删除这些空格。
回答by Wires77
Alright, so I used createSQLQuery() instead of createQuery() and I was using the column names instead of the variable names.
好的，所以我使用 createSQLQuery() 而不是 createQuery() 并且我使用的是列名而不是变量名。
Here's what my code looks like now:
这是我的代码现在的样子：
StringBuilder query = new StringBuilder();
query.append("SELECT proasset\n" +
"FROM com.avivausa.claims.participant.AbstractBeneficiary bene\n" +
"JOIN bene.approvals approval\n" +
"JOIN bene.proAsset proasset\n" +
"join proasset.additionalParties proassetparties\n" +
"WHERE approval.userDate < current_date()\n");
query.append("AND approval.userType = ").append(UserAuditType.APPROVER);
Query q = this.getSessionFactory().getCurrentSession().createQuery(query.toString());
List<ProAsset> proassets = q.list();
Obviously I made some refactoring changes too, but the main changes were what I stated above. Thank you for your responses though!
显然我也进行了一些重构更改，但主要更改是我上面所说的。不过还是谢谢你的回复！
