hibernate mysql arraylist_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文件

/p>

"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

{call getOrgChildListByOrgPId(?)}

3、Java sever端实现

/**

*

* @param userId

* @return

* @throws ParseException

*/

@SuppressWarnings("unchecked")

public List getOrgListForUserId(String userId) throws ParseException{

try {

// get user

List retList = new ArrayList();

// 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、参考

MySQL中进行树状所有子节点的查询

MYSQL--递归查询