java - hibernate原生SQL查询错误

Question

我想在 hibernate 3.5 中执行 native sql 查询。

我使用 mysql 5 作为 dbms，在 mysqlworkbench 上测试了查询，我得到了我想要的数据。

所以我在我的 java 代码中创建了一个 SQLQuery 对象，设置查询字符串和所需的参数。

执行查询后，我得到一个异常说:

org.hibernate.exception.SQLGrammarException: could not execute query
....
Caused by: java.sql.SQLException: Column 'name' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1145)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5617)
at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:225)
at org.hibernate.type.StringType.get(StringType.java:41)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:210)

我的查询(直接在服务器上执行时工作正常):

select site.name as constructionSiteName, site.id as constructionSiteId, com.name,
        sum(pos.sum_checked_brutto) as sumCheckedBrutto, 
        sum(pos.sum_checked_netto) as sumCheckedNetto
    from constructionsite site
    inner join costunit cu on site.id = cu.constructionsite
    inner join costunit_position pos on pos.costunit_id = cu.id
    inner join company com on pos.company_id = com.id
    where com.id = 57
    group by site.id;

这是我在我的 java 代码中创建查询字符串的方法:

String queryString = "select site.name as constructionSiteName, site.id as constructionSiteId, " +
    "sum(pos.sum_checked_brutto) as sumCheckedBrutto, sum(pos.sum_checked_netto) as sumCheckedNetto " + 
    "from constructionsite site " +
    "inner join costunit cu on site.id = cu.constructionsite " +
    "inner join costunit_position pos on pos.costunit_id = cu.id " +
    "inner join company com on pos.company_id = com.id " +
    "where com.id = ? " +
    "group by site.id ";

然后我将它设置为 SQLQuery 对象并设置参数:

SQLQuery query = getSession().createSQLQuery(queryString);
query.setLong(0, companyId);

最后一步是“执行”查询:

List result = query.list();

我真的不知道我做错了什么，非常感谢一些帮助。

tnx 提前

编辑:

hibernate执行生成的sql语句:

2011-05-19 10:42:50,143 DEBUG SQL:111 - select site.name as constructionSiteName,
site.id as constructionSiteId, sum(pos.sum_checked_brutto) as sumCheckedBrutto,
sum(pos.sum_checked_netto) as sumCheckedNetto 
from constructionsite site 
inner join costunit cu on site.id = cu.constructionsite 
inner join costunit_position pos on pos.costunit_id = cu.id 
inner join company com on pos.company_id = com.id where com.id = ? 
group by site.id

我还在服务器输出中找到了这个:

2011-05-19 10:42:50,159  INFO StringType:203 - could not read column value from result set: name; Column 'name' not found.

Answer 1

请仔细检查该表 site有一个名为 name 的列. (注意:MySQL 表名可能区分大小写，具体取决于配置和底层操作系统。)

仔细检查您的应用程序使用的数据库是否与您从 SQL 客户端执行查询的数据库完全相同。也许你有一个测试和生产环境，有两个不同版本的表 site .