MySQL虽然也可以实现Oracle的sys_connect_by_path功能.但是性能很差.
详见:
http://blog.itpub.net/29254281/viewspace-1851457/

其实这种还是用程序算比较好.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
public class TreeScan {
private static String URL = "jdbc:mysql://127.0.0.1:3306/mvbox";
private static String USERNAME = "xx";
private static String PWD = "xx";
public static void main(String[] args) throws ClassNotFoundException, SQLException, CloneNotSupportedException {
List list = new ArrayList();
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(URL, USERNAME, PWD);
ResultSet rs = con.createStatement().executeQuery("select employee_id,manager_id from employees");
while (rs.next()) {
Employee e = new Employee();
e.setEmployeeId(rs.getInt("employee_id"));
e.setManagerId(rs.getInt("manager_id"));
list.add(e);
}
rs.close();
con.close();
treeScan(list);
}
private static void treeScan(List list) throws CloneNotSupportedException {
for (Employee e : list) {
Employee e1 = findManager(list, e);
while (e1 != null) {
e1 = findManager(list, e1);
}
}
Collections.sort(list);
for (Employee ee : list) {
System.out.println(ee.toString());
}
}
private static Employee findManager(List list, Employee e) throws CloneNotSupportedException {
Employee manager = null;
for (Employee em : list) {
if (em.getEmployeeId().equals(e.getManagerId())) {
manager = new Employee();
manager.setEmployeeId(em.getEmployeeId());
manager.setManagerId(em.getManagerId());
e.setManager(manager);
return manager;
}
}
return manager;
}
}
class Employee implements Comparable {
private Integer employeeId = null;
private Integer managerId = null;
private Employee manager = null;
public Integer getEmployeeId() {
return employeeId;
}
public void setEmployeeId(Integer employeeId) {
this.employeeId = employeeId;
}
public Integer getManagerId() {
return managerId;
}
public void setManagerId(Integer managerId) {
this.managerId = managerId;
}
public Employee getManager() {
return manager;
}
public void setManager(Employee manager) {
this.manager = manager;
}
public String toString() {
if (this.manager != null) {
return this.manager.toString() + "/" + this.getEmployeeId();
}
return this.getEmployeeId().toString();
}
@Override
public int compareTo(Employee o) {
return this.toString().compareTo(o.toString());
}
}

本质还是用了递归,用程序做递归,比用SQL做递归真是简单多了.
JAVA结果和数据库运算结果比对:

ITPUB的代码粘贴不支持泛型,估计是根据正则过滤了...
需要补全泛型才能运行.

ITPub博客

MySQL树形遍历(三)

最新文章