Hibernate 递归查询通常用于表的自连接/自指向(self association),如下面的表:
id | name | parent_id |
1 | 部门 | 0 |
2 | 技术部 | 1 |
3 | 账务部 | 1 |
那么相应的Domain为:
Class Department{
private long id;
private Department parentDepartment;
private List<Department> subDepartments;
//setter && getter
}
相应的Hibernate配置文件为:
<bag name="subDepartments" cascade="all-delete-orphan" lazy="true">
<key column="parentid"/>
<one-to-many class="domain.Department"/>
</bag>
<many-to-one name="parentDepartment" column="parent_id" not-null="false" class="domain.Department"/>
做好上面的准备工作之后,现在需要取出某个节点下所有的子结点,那么有以下做法:1)在程序中运用递归遍历;2)使用存储过程查询;3)支持递归查询的数据库等。
本例中,我们使用PostgreSQL 8.4,此版本支持递归查询,因此可以在DAO程序中如下操作,然后进行相应的处理(本例中将其状态与根节点进行同步):
String idTreeSql = " WITH RECURSIVE dep_tree(id) AS "
+ " ( " //
+ " SELECT sa.id "
+ " FROM department sa "
+ " WHERE sa.id=? " //
+ " UNION ALL " //
+ " SELECT sub.id "
+ " FROM department sub,dep_tree parent "
+ " WHERE sub.parentid = parent.id " //
+ " ) " //
+ " ( SELECT * FROM dep_tree ) ";
String updateActiveSql = " UPDATE department sa"
+ " SET sa.active=:active,sa.modifiedBy=:user,sa.modifiedDate=:date"
+ " WHERE sa.id IN (:ids) ";
session = getHibernateSession();
session.beginTransaction();
List<?> ids = session.createSQLQuery(idTreeSql).setParameter(0,
parentDepartment.getId()).list();// 此处取出的id类型为Big Decimal
List<Long> depIds = new ArrayList<Long>(ids.size());
logger.info("The entities to be updated: " + ids);
for (Object id : ids)
{
if (id instanceof Number)
{
depIds.add(((Number) id).longValue());
}
}
int result = session.createQuery(updateActiveSql).setParameter(
"active", parentDepartment.isActive()).setParameter("user",
parentDepartment.getModifiedBy()).setParameter("date",
parentDepartment.getModifiedDate()).setParameterList("ids",
depIds).executeUpdate();
logger.info("The entities updated: " + result);
session.getTransaction().commit();