高分请教：hibernate 采用nativesql进行查询的问题
select   {sm.rowId},{sm.sysmoduleName},(select   count({subsm.rowId})   from   Sysmodule   subsm   where   {subsm.sysmoduleParentId}   =   {sm.rowId})   as   nSonCount     from   Sysmodule   sm   where   sm.sysmoduleParentId   =0


部分代码：

....
Query   query   =   s.createSQLQuery(sql).addEntity( "sm ",   cls).addEntity( "subsm ",   cls).addScalar( "nSonCount ",   Hibernate.INTEGER);
List   items   =   query.list();

....

这条语句在执行时总是无法通过，抱错：
org.hibernate.exception.SQLGrammarException:   could   not   execute   query


其实我只是想实现的在普通sql中是：
select   row_id,sysmodule_name   ,(select   count(*)   from   sysmodule   where   sysmodule_parent_id   =a.row_id)   as   nSonCount   from   sysmodule   a   where   a.sysmoduleParentId   =0


------解决方案--------------------
还不如直接用SQL查询算了
------解决方案--------------------
这样试试
select {sm.rowId},{sm.sysmoduleName},(select count(subsm.rowId) from Sysmodule subsm where subsm.sysmoduleParentId = {sm.rowId}) as nSonCount from Sysmodule sm where sm.sysmoduleParentId =0

Query query = s.createSQLQuery(sql).addEntity( "sm ", cls).addScalar( "nSonCount ", Hibernate.INTEGER);
List items = query.list();
------解决方案--------------------
这样改就可以了
select {sm.rowId},{sm.sysmoduleName},(select count(subsm.rowId) from Sysmodule subsm where subsm.sysmoduleParentId = sm.rowId) as nSonCount from Sysmodule sm where sm.sysmoduleParentId =0

Query query = s.createSQLQuery(sql).addEntity( "sm ", cls).addScalar( "nSonCount ", Hibernate.INTEGER);
List items = query.list();
如果你的登录用户的默认数据库不是你要用的数据要用全名，如数据库名为data，就要把Sysmodule
改为data.dbo.Sysmodule
------解决方案--------------------
你可以只取出你想要的，这样写
sql = "select (select count(subsm.row_id) from Sysmodule subsm where subsm.sysmodule_parent_id = sm.row_id) as nSonCount,sm.rowId as {sm.rowId} from Sysmodule sm where sm.sysmodule_parent_id= "+sysmoduleParentId;
以上的写法用的时候要注意了，首先你要保证你的实体类中除了rowId属性外，其它的属性都可以为空，否则将提示错误。一般没有这么写，如果你要完成你想要的结果可以这样写：
sql = "select (select count(subsm.row_id) from Sysmodule subsm where subsm.sysmodule_parent_id = sm.row_id) as nSonCount,sm.rowId as ROWID from Sysmodule sm where sm.sysmodule_parent_id= "+sysmoduleParentId;

Query query = s.createSQLQuery(sql).addScalar( "nSonCount ", Hibernate.INTEGER).addScalar( "ROWID ", Hibernate.INTEGER);
List items = query.list();