在hibernate中使用native sql问题，急啊，今天明天解决不了就要被炒了！

[davidforever]

首先我申明我用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();
在此感谢了！

[zqrqq]

把你的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}

试试看？

[davidforever]

我怀疑是不是session.createSQLQuery(sql).addEntity("service", Seservice.class).addJoin("engineer", "service.supportengineer").list()这句出了问题！
因为在实体Seservice.class中存在一个supportengineer字段指向supportengineer，这个类表示一对多，多的那一端。
在engineer那一端也没有什么set和list来装service

[davidforever]

我顺便问一下：unexpected AST NODE是什么异常？

[zqrqq]

session.createSQLQuery(sql).addEntity("service", Seservice.class).list()

去掉.addJoin("engineer", "service.supportengineer")试试