Mysql recursive query

In Oracle, we know that there is a HierarchicalQueries. Through CONNECTBY, we can easily query all the subnodes under all the current nodes. However, there are no corresponding functions in the current MySQL version !!! The following shows how to implement mysql recursive query in java: 1. Recursive class packagecn.edu. nuc. maven. MavenTest;

In Oracle, we know that there is a Hierarchical Queries. Using connect by, we can easily query all the subnodes under all the current nodes. However, there are no corresponding functions in the current MySQL version !!! The following describes the recursive query of mysql in java: 1. Recursive class package cn.edu. nuc. maven. MavenTest;

In Oracle, we know that there is a Hierarchical Queries. Using connect by, we can easily query all the subnodes under all the current nodes. However, there are no corresponding functions in the current MySQL version !!!

The following describes how to implement recursive mysql query in java:

1. Recursive class

Package cn.edu. nuc. maven. MavenTest;


Import java. SQL. Connection;
Import java. SQL. Statement;
Import java. util. ArrayList;
Import java. util. List;


Public class HQuery {
Static Connection conn;

Static Statement st;
/**
* Hierarchical Query
*/
Public static void main (String [] args ){
Test_getSortList ();
}

Public static void test_getSortList (){
List SortList = new ArrayList ();
GetSortList (sortList, 0L, 0); // the id of the starting root node is 0 and the level is 0.
For (Product bean: sortList ){
String str = "";
For (int I = 0; I Str + = "--";
}
System. out. println (str + bean. getId () + "" + bean. getParentId () + "" + bean. getName ());
}
}

/**
* Query the tree structure of a category
*/
Public static void getSortList (List SortList, Long parentId, int level ){


Product bean = null;
List List = new ArrayList ();
String SQL = "select * from product s where s. parentId =" + parentId;
Try {
System. out. println ("SQL:" + SQL );
List = (List ) DBUtils. query4ListBean (SQL, new Product (). getClass (); // The classification of the parent class is queried each time.
System. out. println (list. size ());
If (list! = Null & list. size ()> 0 ){
For (int I = 0; I Bean = (Product) list. get (I );
Bean. setLevel (level + 1); // Add a level field
SortList. add (bean );
GetSortList (sortList, bean. getId (), level + 1); // recursive query
}
} Else {
Level --;
}
} Catch (Exception e ){
E. printStackTrace ();
}
}
}

2. DBUtils encapsulation class:

Package cn.edu. nuc. maven. MavenTest;


Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. SQLException;
Import java. util. List;
Import java. util. Map;


Import org. apache. commons. dbutils. DbUtils;
Import org. apache. commons. dbutils. QueryRunner;
Import org. apache. commons. dbutils. handlers. ArrayHandler;
Import org. apache. commons. dbutils. handlers. ArrayListHandler;
Import org. apache. commons. dbutils. handlers. BeanHandler;
Import org. apache. commons. dbutils. handlers. BeanListHandler;
Import org. apache. commons. dbutils. handlers. ColumnListHandler;
Import org. apache. commons. dbutils. handlers. KeyedHandler;
Import org. apache. commons. dbutils. handlers. MapHandler;
Import org. apache. commons. dbutils. handlers. MapListHandler;
Import org. apache. commons. dbutils. handlers. ScalarHandler;


Public class DBUtils {
Static Connection conn = null;
Static String jdbcURL = "jdbc: mysql: // 172.30.204.163: 3306/crm ";
Static String jdbcDriver = "com. mysql. jdbc. Driver ";
Static QueryRunner qRunner = null;

Static {
DbUtils. loadDriver (jdbcDriver );
Try {
Conn = DriverManager. getConnection (jdbcURL, "crm", "crm ");
Conn. setAutoCommit (false); // disable automatic submission
QRunner = new QueryRunner ();
} Catch (Exception e ){
E. printStackTrace ();
}
}

Public static List query4ListBean (String SQL, Class clazz) throws ClassNotFoundException, SQLException {
List lBean = (List) qRunner. query (conn,
SQL,
New BeanListHandler (clazz); // "cn.edu. nuc. maven. MavenTest. Product"

Return lBean;
}

}

3. entity class: (Note: There is no level column in the table)

Package cn.edu. nuc. maven. MavenTest;


Public class Product {
Private Long id;
Private Long parentId;
Private String name;
Private String note;
Private int level;

...

}

4. Table Structure:

Running result:

-- 1 0 product
---- 2 1 product A-1
---- 3 1 product A-2
-- 4 0 product B
---- 5 4 B-1 Products
---- 6 4 Santa Fe