在hibernate中使用native sql问题，急啊，今天明天解决不了就要被炒了！
首先我申明我用native sql而没有用hql的原因是我要使用SQL SERVER2000的全文检索功能也就是CONTAINS(),或者FREETEXT()函数，在hibernate中如果用HQL的话就会出现QueryException:unexpected AST node错误，虽然我也不知道这个是什么错误，但是没有用全文检索的时候就不会出现这个问题，说明hibernate好象不支持sqlserver的全文检索函数。于是我决定改用native sql来试一试，但是最郁闷的事情发生了，请求各位帮忙解决一下。我的NATIVE SQL语句如下：
SELECT {engineer.id},{engineer.name},{engineer.statement},{engineer.skypeAcc},{engineer.avecommAcc},{engineer.city},{service.servicetype},{service.lev},{service.overview}
FROM supportengineer AS engineer,seservice AS service
WHERE {engineer.id} = {service.engineerid}
在执行了以上语句后：会出现该错误：
org.hibernate.QueryException: No column name found for property [engineerid] for alias [service]
看到这个错误，我先认为可能是在seservice这个表中不存在engineerid字段，但是我查询了一下该表存在这个字段，它是一个外键和supportengineer表进行关联。于是我又把这个native sql去掉{}后在SQL SERVER2000中运行了一下，能够正常查询数据。我又检查了我的HBM。XML文件发现一切配置正常。这下就搞不懂了，超郁闷。
我说一下这两张表的关系：是一对多关系。seservice代表服务，supportengineer代表工程师，它表是一个工程师可以提供多中服务。我把我的部分dao源代码公布出来让大家帮我解决一下问题：
String sql = "SELECT {engineer.id},{engineer.name},{engineer.statement}, " +
"{engineer.skypeAcc},{engineer.avecommAcc},{engineer.city}, " +
"{service.servicetype},{service.lev},{service.overview} FROM " +
" supportengineer AS engineer,seservice AS service WHERE " +
" {engineer.id} = {service.engineerid} ";
if(name != null && !name.equals( " "))
{
String engineerName = name.trim();
sql += " AND {engineer.name} = ' " + engineerName + " ' ";
}
if(category != null && !category.equals( " "))
{
String serviceType = category.trim();
sql += " AND {service.servicetype} = ' " + serviceType + " ' ";
}
if(level != null && !level.equals( " "))
{
String serviceLevel = level.trim();
sql += " AND {service.lev} = ' " + serviceLevel + " ' ";
}
if(city != null && !city.equals( " "))
{
String engineerCity = city.trim();
sql += " AND {engineer.city} = ' " + engineerCity + " ' ";
}
if(detail != null && !detail.equals( " "))
{
String engineerDetail = detail.trim();
sql += " AND FREETEXT({engineer.detail}, ' " + engineerDetail + " ') ";
}
if(serviceDetail != null && !serviceDetail.equals( " "))
{
String serviceDetailStr = serviceDetail.trim();
sql += " AND FREETEXT({service.overview}, ' " + serviceDetailStr + " ') ";
}
List tempList = session.createSQLQuery(sql).addEntity( "service ", Seservice.class).addJoin( "engineer ", "service.supportengineer ").list();
在此感谢了！
[解决办法]
把你的Seservice.class贴出来？
猜测原因：结果集的列名与HBM上配置的不一致
把
SELECT {engineer.id},。。。
FROM supportengineer AS engineer,seservice AS service
WHERE {engineer.id} = {service.engineerid}
修改成
SELECT {engineer.id AS engineerid},。。。
FROM supportengineer AS engineer,seservice AS service
WHERE {engineer.id} = {service.engineerid}
试试看？
