MySQL recursive instance

Available in OracleConnectClause can easily implement recursive queries, which can be used in MSSQLServer and DB2WithMySQL does not support the connect by clause or the with clause. Therefore, you must use other methods to implement recursive queries, the implementation scheme varies with the requirements.

The recursive instances used by MySQL Program (sorted by the Internet ),

1. Create a table

DROP TABLE IF EXISTS `item_category`;
CREATE TABLE `item_category` (
`id` int(11) NOT NULL auto_increment,
`catId` int(11) default NULL,
`parentId` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT;

2. Insert data

INSERT INTO `item_category` VALUES (1,1,0);
INSERT INTO `item_category` VALUES (2,2,1);
INSERT INTO `item_category` VALUES (3,3,1);
INSERT INTO `item_category` VALUES (4,4,2);
INSERT INTO `item_category` VALUES (5,5,3);

3. Create a stored procedure

DELIMITER //
drop procedure if exists findLChild//
CREATE PROCEDURE findLChild(iid bigint(20),layer bigint(20))
BEGIN
create temporary table if not exists tmp_table (id bigint(20));
SET@@max_sp_recursion_depth=99;
call iterative(iid,layer);
select * from tmp_table;
drop temporary table if exists tmp_table;
END;//
DELIMITER ;
DELIMITER //
drop procedure if exists iterative//
CREATE PROCEDURE iterative(iid bigint(20),layer bigint(20))
BEGIN
declare tid bigint(20) default -1;
declare cur1 CURSOR FOR select catId from item_category where parentId=iid;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tid = null;
if layer > 0 then
OPEN cur1;
FETCH cur1 INTO tid;
WHILE(tid is not null)
DO
insert into tmp_table values(tid);
call iterative(tid,layer-1);
FETCH cur1 INTO tid;
END WHILE;
end if;
END;//
DELIMITER ;

4. Call

call findLChild(1,50);

5 Java call

The code for executing the stored procedure and obtaining the result set in Java is as follows:
Connection con = new connection (); ---- assume that a valid connection has been obtained.
Resultset rs = NULL;
Callablestatement CSTM = con. preparecall ("{call testrs (?)} ");
CSTM. setint (); ---- fill in the first parameter value of the stored procedure 2.
Boolean BL = c0000.exe cute (); ----- executes the Stored Procedure
While (BL) {---- if the stored procedure is successfully executed and a result set is returned, BL is true.
Rs = CSTM. getresultset () ---- get a result set,
While (Rs. Next ()){
System. Out. println (Rs. getint (1); --- output a random value.
}
BL = CSTM. getmoreresultset (); ---- if the result set can be obtained, BL = true. In this way, return to the loop header and continue to obtain the result set for processing. If no result set exists, BL = false. Result loop.
In this way, as long as three cycles are passed, the three result sets of the stored procedure can be processed separately.