MySql遞迴查詢過程Hibernate實現

1、MySql遞迴查詢過程

表：

CREATE TABLE `torg` (
  `TorgID` varchar(50) NOT NULL,
  `TorgName` varchar(200) NOT NULL,
  `TorgCode` varchar(50) NOT NULL,
  `TorgRank` varchar(50) NOT NULL,
  `TorgFlag` varchar(5) NOT NULL,
  `TorgOrder` varchar(5) NOT NULL,
  `TorgPCode` varchar(50) NOT NULL,
  PRIMARY KEY (`TorgID`),
  UNIQUE KEY `Torg_PK` (`TorgID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

遞迴查詢過程：

CREATE DEFINER=`root`@`%` PROCEDURE `getOrgChildListByOrgPId`(in orgPId varchar(50))
BEGIN
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempChd VARCHAR(1000);

    #get org child Iplist
    SET sTemp = '#';
    SET sTempChd = orgPId;
   
    WHILE sTempChd is not null DO
         SET sTemp = concat(sTemp,',',sTempChd);
         SELECT group_concat(torgId) INTO sTempChd FROM torg where FIND_IN_SET(TorgPCode,sTempChd)>0;
    END WHILE;
    #SELECT getChildList(orgPId) into orgIdList ;

    # select all torg by orgIdList
    SET @str = CONCAT('select * from Torg where FIND_IN_SET(torgId,"', sTemp,'")');
    PREPARE stmt FROM @str;
    EXECUTE stmt;
   DEALLOCATE PREPARE stmt;

END

 

2、表對應Hibernate的xml檔案

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
    Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
    <class name="com.macren.model.Torg" table="torg" catalog="lzproject">
        <id name="torgId" type="java.lang.String">
            <column name="TorgID" length="50" />
            <generator class="uuid.hex" />
        </id>
        <property name="torgName" type="java.lang.String">
            <column name="TorgName" length="200"   />
        </property>
        <property name="torgCode" type="java.lang.String">
            <column name="TorgCode" length="50"   />
        </property>
        <property name="torgRank" type="java.lang.String">
            <column name="TorgRank" length="50"   />
        </property>
        <property name="torgFlag" type="java.lang.String">
            <column name="TorgFlag" length="5"  />
        </property>
          <property name="torgOrder" type="java.lang.String">
            <column name="TorgOrder" length="5"   />
        </property>
        <property name="torgPcode" type="java.lang.String">
            <column name="TorgPCode" length="50"   />
        </property>
    </class>
   

<!--  sql procedure Mapping     -->
    <sql-query name="getOrgChildListFromOrgId" callable="true">
    <return alias="torg" class="com.macren.model.Torg">
     <return-property name="torgId" column="torgId"/>
     <return-property name="torgName" column="torgName"/>
     <return-property name="torgCode" column="torgCode" />
     <return-property name="torgRank" column="torgRank" />
     <return-property name="torgFlag" column="torgFlag" />
     <return-property name="torgOrder" column="torgOrder" />
     <return-property name="torgPcode" column="torgPcode" />
    </return>
    {call getOrgChildListByOrgPId(?)}
  </sql-query>
   
</hibernate-mapping>

3、Java sever端實現

 /**
  *
  * @param userId
  * @return
  * @throws ParseException
  */
 @SuppressWarnings("unchecked")
 public List<Torg> getOrgListForUserId(String userId) throws ParseException{
  try {
   // get user
   List<Torg> retList = new ArrayList<Torg>();
      // get local
   String torgId = getOrgIdByUserId(userId);

   // getOrgChildListByOrgPId
   Query query = getSession().getNamedQuery("getOrgChildListByOrgPId");
   query.setParameter(0, torgId);
   retList = query.list();
   
   return retList;
  } catch (RuntimeException re) {
      throw re;
  }
 }

 

4、參考

http://blog.csdn.net/shangtongwei/archive/2010/04/17/5496319.aspx

MySQL中進行樹狀所有子節點的查詢

http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx

MYSQL--遞迴查詢

http://unix-cd.com/vc/www/26/2010-09/16547.html