Hibernate implementation in MySQL recursive query Process

1. MySQL recursive query Process

Table:

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

 

Recursive query process:

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. The table corresponds to the XML file of hibernate.

<? 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 server implementation

/**
*
* @ 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. Reference

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

Query all Tree nodes in MySQL

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

MySQL -- recursive query

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