我希望您能在Hibernate 3.5中执行本地SQL查询。
我使用MySQL 5作为数据库管理系统,在MySQL Workbench中测试了查询并获得了所需的数据。
因此,我在Java代码中创建了一个SQLQuery对象,设置了查询字符串和所需的参数。
执行查询后,我收到了一个异常消息:
最后一步是使用以下命令“执行”查询:
我真的不知道我做错了什么,非常感谢您的帮助。
提前感谢。
编辑:
由Hibernate执行的生成的SQL语句:
我在服务器输出中也找到了这个:
我使用MySQL 5作为数据库管理系统,在MySQL Workbench中测试了查询并获得了所需的数据。
因此,我在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();
我真的不知道我做错了什么,非常感谢您的帮助。
提前感谢。
编辑:
由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.
.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'name' in field list is ambiguous
。 - theFriedC