org.hibernate.exception.SQLGrammarException: could not extract ResultSet &&&&&Incorrect syntax near '@P0'.

这个故障的原因比较多：

1.如数据库中的字段和类中的字段类型不一致；

2.数据库dialect不够具体

myeclispe自动生成的是  org.hibernate.dialect.SQLServerDialect

如果连接的是SQLSERVER 2008 则可以写成org.hibernate.dialect.SQLServer2008Dialect

 

 

最近遇到这个报错：

现象就是 hql 的分页查询语句转换出错

select TOP ? employee0_.id as id1_2_, employee0_.EmpNr as EmpNr2_2_, employee0_.EmpChName as EmpChNam3_2_, employee0_.EmpName as EmpName4_2_, employee0_.Section as Section5_2_, employee0_.Shift as Shift6_2_, employee0_.OnDuty as OnDuty7_2_, employee0_.Title as Title8_2_ from database.dbo.employee employee0_ order by employee0_.id

应该？格式是（？）,对应的错误提示是：Incorrect syntax near '@P0'.，

对应的是hql的分页查询语句query.setMaxResults(5);query.setFirstResult(0);由于这是自动生成的，所以不应该出错。

所以排查了很久，就想着看看hibernate.xml文件配置，发现Dialect之前动过了。

<property name="dialect">

org.hibernate.dialect.SQLServerDialect

</property>

应该改成

<property name="dialect">

org.hibernate.dialect.SQLServer2008Dialect

</property>

这样转换的时候，sql语句会变成下图所示：

from Employee emp order by emp.id

Hibernate: select TOP(?) employee0_.id as id1_2_, employee0_.EmpNr as EmpNr2_2_, employee0_.EmpChName as EmpChNam3_2_, employee0_.EmpName as EmpName4_2_, employee0_.Section as Section5_2_, employee0_.Shift as Shift6_2_, employee0_.OnDuty as OnDuty7_2_, employee0_.Title as Title8_2_ from database.dbo.employee employee0_ order by employee0_.id