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
部分代码:....
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},from Sysmodule sm where sm.sysmoduleParentId =0";
Query query = s.createSQLQuery(sql).addEntity("sm", cls);这样我是可以正常执行的。
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 =0Query query = s.createSQLQuery(sql).addEntity("sm", cls).addScalar("nSonCount", Hibernate.INTEGER);
List items = query.list();
还是抱错 [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]列名 'row1_0_' 无效。
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 =0Query 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.*} from Sysmodule sm where sm.sysmodule_parent_id="+sysmoduleParentId;Query query = s.createSQLQuery(sql).addEntity("sm", cls).addScalar("nSonCount", Hibernate.INTEGER);
List items = query.list();可以正常通过这样,只取一个字段却不行
sql = "select (select count(subsm.row_id) from Sysmodule subsm where subsm.sysmodule_parent_id = sm.row_id) as nSonCount,{sm.rowId} from Sysmodule sm where sm.sysmodule_parent_id="+sysmoduleParentId;Query query = s.createSQLQuery(sql).addEntity("sm", cls).addScalar("nSonCount", Hibernate.INTEGER);
List items = query.list();为什么,我不想取出所有的字段。
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();