hibernate native SQL query error

i want to execute a native sql query in hibernate 3.5.

i'm using mysql 5 as dbms, tested the query at the mysqlworkbench and i get the data i want.

so i created a SQLQuery object in my java code, set the query string and the needed parameter.

after executing the query i get an exception saying:

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)

my query (which works fine when directly executing at the server):

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.c开发者_如何学Pythononstructionsite
    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;

here's how i create the query string in my java code:

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 ";

then i set it to the the SQLQuery object and set the param:

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

The last step is to "execute" the query with:

List result = query.list();

i really don't know what i'm doing wrong and would really appreciate some help.

tnx in advance

EDIT:

the generated sql statement executed by hibernate:

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

i also found this one in the server output:

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

Please double check that table site has a column named name. (Be careful: MySQL table names can be case-sensitive depending on configuration and the underlying OS.)

Double check that your application is using the very same database where you execute your query from the SQL client. Maybe you have a testing and production environment with two different versions of table site.

Try removing the column alias names from the SQL. I faced a similar problem earlier and that fixed it for me. Some how native SQL queries when run on hibernate overlook the column alias names. I am not sure why this happens.

Your Query would be select site.name , site.id , com.name, sum(pos.sum_checked_brutto) , sum(pos.sum_checked_netto) 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;

It's related to MySQL-JDBC. Add [useOldAliasMetadataBehavior=true] to the JDBC url in the configuration file. Source: http://bugs.mysql.com/bug.php?id=31499

yes, removing the alias worked but is a problem if you have 2 tables, each with a column called name.

However if I put a ifnull(site.name), this seemed to work. No idea why.