200分问一个简单hibernate的isnull问题

[peihexian]

我用的sql server 2000,这样的HSQL为什么hibernate总是报错？

String HSQL="select isnull(count(a),0) from CustomerUnitInfo a where a.unitId="+UnitId;
result=((Integer)DbSession.iterate(HSQL).next()).intValue();

抱错如下:


net.sf.hibernate.QueryException: undefined alias: isnull [select isnull(count(a),0) from com.bkgd.water.dao.tables.CustomerUnitInfo a where a.unitId=5]

at net.sf.hibernate.hql.PathExpressionParser.token(PathExpressionParser.java:103)

at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)

at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:170)

at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)

at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114)

at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143)

at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)

at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)

at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)

at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:295)

at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1572)

at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1602)

at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1592)

at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1584)


看错误提示大概是hibernate认为isnull是一个别名，可这个是sql server的关键字啊？
hibernate的配置文件中我已经设置为:

<property name="dialect">net.sf.hibernate.dialect.SQLServerDialect</property>

我用的jtds的sql server jdbc 驱动，用ms的那三个文件的jdbc驱动也是一样报错，hibernate版本2.1.8。

[sgdb]

说明hql不支持isnul，你可以使用原始的sql语句来做

[peihexian]

可是hibernate的文档中明明就用了isnull函数啊？

The next query uses the MS SQL Server isNull() function to return all the accounts and unpaid payments for the organization to which the current user belongs. It translates to an SQL query with three inner joins, an outer join and a subselect against the ACCOUNT, PAYMENT, PAYMENT_STATUS, ACCOUNT_TYPE, ORGANIZATION and ORG_USER tables.

select account, payment
from Account as account
left outer join account.payments as payment
where :currentUser in elements(account.holder.users)
and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID)
order by account.type.sortOrder, account.accountNumber, payment.dueDate

[jihanzhong]

count(a) 不可能出现null的8！

[lippea]

up sgdb(神天月晓)
照说明看来，可能是在select中不支持，但在where条件中支持。

[peihexian]

我自己解决了，结帐。