java Hibernate createSQLquery 的无效列名错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17197246/
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
Invalid Column Name error with Hibernate createSQLquery
提问by Ginja Ninja
This is a smaller version of a larger query I'm trying to get working. When I run the query I get the following error. I've tried removing as much of the query as I can to try to find the guilty column, but haven't had any success. I'm using Spring 3.2.1, Hibernate 3.6, and Oracle 11g
这是我试图开始工作的较大查询的较小版本。当我运行查询时，出现以下错误。我尝试删除尽可能多的查询以尝试找到有罪的列，但没有任何成功。我使用的是 Spring 3.2.1、Hibernate 3.6 和 Oracle 11g
Caused by: java.sql.SQLException: Invalid column name
at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3711)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2763)
at oracle.jdbc.driver.OracleResultSet.getBigDecimal(OracleResultSet.java:368)
at org.hibernate.type.descriptor.sql.DecimalTypeDescriptor.doExtract(DecimalTypeDescriptor.java:62)
at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)
Query:
询问：
String query = "SELECT d.ID as {d.id}, d.DETAIL_ORDER as {d.detailOrder}, g.ID as {g.id}, g.NAME as {g.name} " +
"FROM EVAL_MASTER_EVAL_DETAIL d " +
"JOIN EVAL_QUESTION_GROUP g ON d.GROUP_ID = g.ID " +
"WHERE d.ACTIVE = 'Y' " +
"AND d.MASTER_EVAL_ID = :evalId" +
" ORDER BY d.DETAIL_ORDER ASC";
details = session.createSQLQuery(query).addEntity("d", EvalMasterEvalDetail.class)
.addEntity("g", EvalQuestionGroup.class).setParameter("evalId", evalId).list();
Resulting Hibernate Query:
结果休眠查询：
Hibernate: SELECT d.ID as ID29_0_, d.DETAIL_ORDER as DETAIL5_29_0_, g.ID as ID34_1_, g.NAME as NAME34_1_ FROM EVAL_MASTER_EVAL_DETAIL d JOIN EVAL_QUESTION_GROUP g ON d.GROUP_ID = g.ID WHERE d.ACTIVE = 'Y' AND d.MASTER_EVAL_ID = ? ORDER BY d.DETAIL_ORDER ASC
Mappings (to verify names)
映射（验证名称）
<hibernate-mapping>
<class name="org.anes.surveys.domain.EvalMasterEvalDetail" table="EVAL_MASTER_EVAL_DETAIL">
<id name="id" type="big_decimal">
<column name="ID" precision="22" scale="0" />
<generator class="sequence-identity" >
<param name="sequence">EVAL_MASTER_EVAL_DETAIL_SEQ</param>
</generator>
</id>
<many-to-one name="evalQuestionGroup" class="org.anes.surveys.domain.EvalQuestionGroup" fetch="select">
<column name="GROUP_ID" precision="22" scale="0" />
</many-to-one>
<many-to-one name="evalMasterEvaluation" class="org.anes.surveys.domain.EvalMasterEvaluation" fetch="select">
<column name="MASTER_EVAL_ID" precision="22" scale="0" not-null="true" />
</many-to-one>
<property name="detailOrder" type="big_decimal">
<column name="DETAIL_ORDER" precision="22" scale="0" not-null="true" />
</property>
<property name="active" type="string">
<column name="ACTIVE" length="1" not-null="true" />
</property>
</class>
</hibernate-mapping>
<hibernate-mapping>
<class name="org.anes.surveys.domain.EvalQuestionGroup" table="EVAL_QUESTION_GROUP">
<id name="id" type="big_decimal">
<column name="ID" precision="22" scale="0" />
<generator class="sequence-identity" >
<param name="sequence">EVAL_QUESTION_GROUP_SEQ</param>
</generator>
</id>
<property name="name" type="string">
<column name="NAME" not-null="true" />
</property>
<set name="evalMasterEvalDetails" table="EVAL_MASTER_EVAL_DETAIL" inverse="true" lazy="true" fetch="select">
<key>
<column name="GROUP_ID" precision="22" scale="0" />
</key>
<one-to-many class="org.anes.surveys.domain.EvalMasterEvalDetail" />
</set>
</class>
</hibernate-mapping>
EDIT: Tried the following and still get invalid column error. ACTIVE & d.active are strings.
编辑：尝试了以下操作，仍然得到无效的列错误。ACTIVE & d.active 是字符串。
String query = "SELECT ACTIVE as {d.active} " +
"FROM EVAL_MASTER_EVAL_DETAIL";
details = session.createSQLQuery(query).addEntity("d", EvalMasterEvalDetail.class).list();
String query = "SELECT d.ID " +
"FROM EVAL_MASTER_EVAL_DETAIL d ";
details = session.createSQLQuery(query).addEntity("d", EvalMasterEvalDetail.class).list();
String query = "SELECT ID " +
"FROM EVAL_MASTER_EVAL_DETAIL";
details = session.createSQLQuery(query).addEntity("d", EvalMasterEvalDetail.class).list();
This works, but I only get scalar values:
这有效，但我只得到标量值：
String query = "SELECT ID " +
"FROM EVAL_MASTER_EVAL_DETAIL";
details = session.createSQLQuery(query).list();
回答by Ginja Ninja
Here's what I ended up doing.
这就是我最终要做的。
-I used HQL instead of my native SQL (reference the object fields instead of db columns)
- 我使用 HQL 而不是我的本机 SQL（引用对象字段而不是 db 列）
-Created a new domain class with getters and setters to define/hold the results of my query (class contains fields from 5 different objects/tables)
- 创建了一个带有 getter 和 setter 的新域类来定义/保存我的查询结果（类包含来自 5 个不同对象/表的字段）
public class EvalForm {
private BigDecimal detailId;
private BigDecimal detailOrder;
private BigDecimal groupId;
private String groupName;
}
-Used my new class' field names as the column aliases
- 使用我的新类的字段名称作为列别名
String query = "SELECT d.id as detailId, d.detailOrder as detailOrder, g.id as groupId, g.name as groupName....
-Added setResultTransformer to point to my new class
- 添加了 setResultTransformer 以指向我的新类
details = session.createQuery(query).setParameter("evalId", evalId).setResultTransformer(Transformers.aliasToBean(EvalForm.class)).list();
The result? Perfect for JSON output.
结果？非常适合 JSON 输出。
[{"detailId":44,"detailOrder":0,"groupId":128,"groupName":"My dope name"},{"detailId":42,"detailOrder":1,"groupId":68,"groupName":"qGroup AJAX"},{"detailId":81,"detailOrder":2,"groupId":68,"groupName":"qGroup AJAX"}]
回答by Bohemian
Remove all column aliases. Ie change:
删除所有列别名。即改变：
d.ID as {d.id}
to just
只是
d.ID
etc.
等等。
I've never seen such HQL syntax. It might be valid, but it's certainly unnecessary.
我从未见过这样的 HQL 语法。这可能是有效的，但肯定是不必要的。
