MySQL虽然也可以实现Oracle的sys_connect_by_path功能.但是性能很差.
详见:
http://blog.itpub.net/29254281/viewspace-1851457/
其实这种还是用程序算比较好.
本质还是用了递归,用程序做递归,比用SQL做递归真是简单多了.
JAVA结果和数据库运算结果比对:
ITPUB的代码粘贴不支持泛型,估计是根据正则过滤了...
需要补全泛型才能运行.
详见:
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结果和数据库运算结果比对:
![](http://img.blog.itpub.net/blog/attachment/201512/7/29254281_14494672751WJf.png?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201512/7/29254281_1449467320TN16.png?x-oss-process=style/bb)
ITPUB的代码粘贴不支持泛型,估计是根据正则过滤了...
需要补全泛型才能运行.
![](http://img.blog.itpub.net/blog/attachment/201512/7/29254281_1449467561jdsF.png?x-oss-process=style/bb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-1867455/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-1867455/