hibernate原生SQL查询错误

问题描述

我使用mysql 5作为dbms，在mysqlworkbench中测试了查询并获取了数据我想。

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

在执行查询后，我收到一个异常说：

  org.hibernate.exception.SQLGrammarException：无法执行查询
 .... 
引起：java.sql.SQLException：未找到列'name'。 
 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）
在org.hibernate.type.StringType.get（StringType.java:41）
在org.hibernate.type.NullableType.nullSafeGet（NullableType.java:184）
在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 sumCheckedBrutt o，
 sum（pos.sum_checked_netto）as sumCheckedNetto 
 from constructionsite site 
 inner join costunit cu on site.id = cu.constructionsite 
内部连接costunit_position pos pos.costunit_id = cu.id 
内部加入公司com在pos.company_id = com.id 
其中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 constructions站点+ 
inner join costunit cu on site.id = cu.constructionsite+ 
inner join costunit_position pos pos.costunit_id = cu.id+ 
内部加入公司com在pos.company_id = com.id+ 
其中com.id =？+ 
group by site.id; 
  

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

  SQLQuery query = getSession（）。createSQLQuery（queryString）; 
 query.setLong（0，companyId）; 
  

最后一步是执行查询：

 列表结果= query.list（）; 
  

我真的不知道自己做错了什么，并且非常感谢您的帮助。

tnx提前

编辑：

生成由hibernate执行的sql语句：

  2011-05-19 10：42：50,143 DEBUG SQL：111  -  select site.name as施工站点名称，
 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 
内部连接costunit_position pos pos.costunit_id = cu.id 
内部连接公司com在pos.company_id = com.id其中com.id =？ 
 group by site.id 
  

我也在服务器输出中找到了这个：

  2011-05-19 10：42：50,159 INFO StringType：203  - 无法从结果集中读取列值：name;未找到列'名称'。 
  

解决方案

你有2个表，每个表都有一个名为列的列。

然而如果我把一个 ifnull（site.name），这似乎工作。不知道为什么。

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.

解决方案

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.

这篇关于hibernate原生SQL查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！