hibernate native SQL query error

Question

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

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.

ps: please don't get confused by the table and column names. :D — theFriedC
– theFriedC, Commented May 19, 2011 at 8:26
can you print the actual value of queryString after the assignment — Suraj Chandran
– Suraj Chandran, Commented May 19, 2011 at 8:34
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. — theFriedC
– theFriedC, Commented May 19, 2011 at 8:46
The query you executed includes "com.name", but not the query you executed via java.I ma not sure whether that is the problem though — Suraj Chandran
– Suraj Chandran, Commented May 19, 2011 at 8:52

jabal · Accepted Answer · 2011-05-19 09:03:46Z

0

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.

answered May 19, 2011 at 9:03

jabal

12.5k13 gold badges55 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

theFriedC Over a year ago

i tried the same with a simple query like select site.name from constructionsite site and i get the expected resultset without errors.

jabal Over a year ago

i see. try to build up your desired query step-by-step and see where does it go wrong.. :-)

jabal Over a year ago

ok. please don't forget to answer you question if you have the solution, this is how things go on here. good luck.

theFriedC Over a year ago

wanted to answer my own question but "computer says no ..." i have to wait 8 hours until i can post an answer :D the problem was the alias constructionSiteName if i leave it - everything works fine

Tim McJilton Over a year ago

What was the problem? I am running into a similar error where it works when I run it on sqlplus, but not in my hibernate query in java.

|

user1783435 · Accepted Answer · 2014-03-04 18:04:40Z

0

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;

edited Mar 4, 2014 at 18:04

answered Mar 4, 2014 at 17:55

user1783435

12 bronze badges

Comments

jasxir · Accepted Answer · 2014-03-14 03:30:06Z

0

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

answered Mar 14, 2014 at 3:30

jasxir

91810 silver badges21 bronze badges

Comments

piyushj · Accepted Answer · 2016-08-11 08:09:55Z

0

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.

edited Aug 11, 2016 at 8:09

piyushj

1,5465 gold badges21 silver badges29 bronze badges

answered Aug 11, 2016 at 6:45

Firoz

12 bronze badges

1 Comment

piyushj Over a year ago

"No idea why" wont help, the answer need a clarification.

Collectives™ on Stack Overflow

hibernate native SQL query error

4 Answers 4

6 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related