树形结构之五 数据提取-递归查询

 

也是关于树型结构的内容 ，就是数据库递归查询，我在项目中用的是oralce数据库 ，它提供了递归查询的功能，

像普通的查询一样，在网上了解了一下，发现很多数据库都提供了递归查询的实现，如mysql,db2,oralce等。

 

下面贴出来一个角色信息的hibernate配置文件来，里面用到了这种查询。

 

 

<?xml version="1.0" encoding="GBK"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
	<class name="com.ylsoft.power.domain.entity.Catalog" 
		table="PLATFORM_POWER_CATALOG">
		<cache usage="read-write" region="power_catalog"/>
		<id name="catalogId" type="java.lang.Integer">
			<column name="catalogId" not-null="true" length="20" />
			<generator class="sequence">
				<param name="sequence">PLATFORM_CATALOG_SEQ</param>
			</generator>
		</id>
		<property name="label" type="java.lang.String" column="label"
			length="20" />
		<property name="url" type="java.lang.String" column="url"
			length="500" />
		<property name="enable" type="java.lang.Boolean" update="true"
			insert="true" column="enable" />
		<property name="corder" type="java.lang.Integer" update="true"
			insert="true" column="corder" />

		<many-to-one name="parent" column="parentId"
			class="com.ylsoft.power.domain.entity.Catalog">
		</many-to-one>
		<!-- roles -->
		<set name="actions" cascade="all">
			<key column="catalogId" />
			<one-to-many entity-name="com.ylsoft.power.domain.entity.Action" />
		</set>

		<!-- departs -->
		<set name="fields" cascade="all" >
			<key column="catalogId" />
			<one-to-many entity-name="com.ylsoft.power.domain.entity.Field" />
		</set>

	</class>
	<sql-query name="com.ylsoft.power.domain.entity.Catalog.Descendants">
		<return alias="catalog"
			class="com.ylsoft.power.domain.entity.Catalog">
       		<return-property name="parent">
            	<return-column name="parentCatalogId"/>
        	</return-property>
		</return>
		select 
		c.catalogId as {catalog.catalogId},
		c.label as {catalog.label},
		c.enable as {catalog.enable},
		c.url as {catalog.url},
		c.parentId as parentCatalogId,
		c.corder as {catalog.corder}
		from platform_power_catalog c
		start with catalogId=:catalogId 
		connect by prior catalogId=parentId
		order by c.catalogid
	</sql-query>
	<!-- 得到指定部门的所有上级部门 -->
	<sql-query name="com.ylsoft.power.domain.entity.Catalog.Ancestors">
		<return alias="catalog"
			class="com.ylsoft.power.domain.entity.Catalog">
       		<return-property name="parent">
            	<return-column name="parentCatalogId"/>
        	</return-property>
		</return>
		select 
		c.catalogId as {catalog.catalogId},
		c.label as {catalog.label},
		c.enable as {catalog.enable},
		c.url as {catalog.url},
		c.parentId as parentCatalogId,
		c.corder as {catalog.corder}
		from platform_power_catalog c
		start with catalogId=:catalogId 
		connect by prior parentId=catalogId
		order by c.catalogid
	</sql-query>
</hibernate-mapping>

 

 

这里用了hibernate，其实就是一个普通的查询语句。