Java 从数据库读取数据转换为层次型json数据

数据库数据

例如：

[
    {
        "children": [
            {
                "children": [
                    {
                        "children": [],
                        "name": "1"
                    },
                    {
                        "children": [],
                        "name": "2"
                    },
                    {
                        "children": [],
                        "name": "3"
                    },
                    {
                        "children": [
                            {
                                "children": [],
                                "name": "ss1"
                            },
                            {
                                "children": [],
                                "name": "ss2"
                            },
                            {
                                "children": [],
                                "name": "ss3"
                            },
                            {
                                "children": [],
                                "name": "ss4"
                            }
                        ],
                        "name": "4"
                    },
                   
                    {
                        "children": [],
                        "name": "9"
                    }
                ],
                "name": "北京"
            },
            {
                "children": [],
                "name": "天津"
            },
            {
                "children": [],
                "name": "河南"
            },
            {
                "children": [],
                "name": "广东"
            }
        ],
        "name": "中国"
    }
]

准备jar包

 <!-- https://mvnrepository.com/artifact/org.json/json -->
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-lang3</artifactId>
      <version>3.1</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.44</version>
    </dependency>
    <dependency>
      <groupId>commons-beanutils</groupId>
      <artifactId>commons-beanutils</artifactId>
      <version>1.8.3</version>
    </dependency>
    <dependency>
      <groupId>commons-logging</groupId>
      <artifactId>commons-logging</artifactId>
      <version>1.1.1</version>
    </dependency>
    <dependency>
      <groupId>commons-collections</groupId>
      <artifactId>commons-collections</artifactId>
      <version>3.2.1</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/net.sf.json-lib/json-lib -->
    <!--可以使用net.sf.json-lib  也可以使用com.alibaba-->
    <!--<dependency>-->
      <!--<groupId>net.sf.json-lib</groupId>-->
      <!--<artifactId>json-lib</artifactId>-->
      <!--<version>2.4</version>-->
      <!--<classifier>jdk15</classifier>-->
    <!--</dependency>-->
    <!--<dependency>-->
      <!--<groupId>net.sf.ezmorph</groupId>-->
      <!--<artifactId>ezmorph</artifactId>-->
      <!--<version>1.0.6</version>-->
    <!--</dependency>-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.13</version>
    </dependency>

JAVA代码

准备实体类

package constructor;
import net.sf.json.JSONObject;
import java.util.ArrayList;
import java.util.List;
/**
 * @Author zhaoxin
 * @Email 1272743926@qq.com
 * @Description //TODO
 * @Date 2018/12/6
 **/
public class SidebarTree {
    private int id;
    private String name;
    private int parent;
    private JSONObject attributes = new JSONObject();   //net.sf.json
    private List<SidebarTree> children= new ArrayList<>(); //存放子节点
    public SidebarTree() {
    }
    public SidebarTree(int id, String name, int parent) {
        this.id = id;
        this.name = name;
        this.parent = parent;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getParent() {
        return parent;
    }

    public void setParent(int parent) {
        this.parent = parent;
    }

    public JSONObject getAttributes() {
        return attributes;
    }

    public void setAttributes(JSONObject attributes) {
        this.attributes = attributes;
    }

    public List<SidebarTree> getChildren() {
        return children;
    }
    public void setChildren(List<SidebarTree> children) {
        this.children = children;
    }
}

准备连接数据库的工具类

package constructor;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**
 * @Author zhaoxin
 * @Email 1272743926@qq.com
 * @Description //TODO
 * @Date 2018/12/6
 **/
public class Connect {
    public  static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://127.0.0.1:3306/test";
        String user="root";
        String pwd="root";
        Connection connection=DriverManager.getConnection(url,user,pwd);
        return connection;
    }
    public static void relase(Connection connection, Statement statement, ResultSet resultSet)throws Exception{
        if (resultSet!=null){
            resultSet.close();
        }
        if (statement!=null){
            statement.close();
        }
        if (connection!=null){
            connection.close();
        }
    }
}

编写主类

package constructor;
//import net.sf.json.JSONArray;
//import net.sf.json.JSONObject;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
/**
 * @Author zhaoxin
 * @Email 1272743926@qq.com
 * @Description //TODO
 * @Date 2018/12/6
 **/
public class TreeTest {
    public static List<SidebarTree> formatTree(List<SidebarTree> list ){
        SidebarTree root=new SidebarTree();//根节点
        SidebarTree node=new SidebarTree();//叶子节点
        List<SidebarTree> treelist=new ArrayList<>(); //拼凑好的Json数据
        List<SidebarTree> parentNodes=new ArrayList<>(); // 存放所有父节点
        if(list!=null && list.size()>0){
            root=list.get(0); //第一个一定是根节点 0
            for(int i=1; i<list.size(); i++){
                node=list.get(i);
                if(node.getParent()==root.getId()){ //从跟节点开始遍历是不是子节点
                    parentNodes.add(node);
                    root.getChildren().add(node);
                }else{ //获取root子节点的孩子节点
                    getChildrenNodes(parentNodes, node);
                    parentNodes.add(node);
                }
            }
        }
        treelist.add(root);
        return treelist;
    }
    private static void getChildrenNodes(List<SidebarTree> parentNodes , SidebarTree node){
        for(int i=parentNodes.size()-1; i>=0; i--){
            SidebarTree pnode=parentNodes.get(i);
            if(pnode.getId()==node.getParent()){
                pnode.getChildren().add(node);
                return;
            }
        }
    }
    public static void  main(String[] args)throws Exception{
        Connection connection=Connect.getConnection();//获取数据库连接
        String sql="select * from tree";//查询数据
        PreparedStatement preparedStatement=connection.prepareStatement(sql);//sql语句预处理
        ResultSet resultSet=preparedStatement.executeQuery();//执行sql语句
        List<SidebarTree> list=new ArrayList<>();
        while(resultSet.next()){
            SidebarTree sidebarTree=new SidebarTree(resultSet.getInt("id"),resultSet.getString("name"),resultSet.getInt("parent"));
            list.add(sidebarTree);//将对象放入list集合中
        }
        JSONArray jsonArray=JSONArray.parseArray(JSON.toJSONString(formatTree(list)));
//        JSONArray jsonArray=JSONArray.fromObject(formatTree(list));//formatTree(list)  构造层次json字符串  使用net.sf.json包所使用的方法
        jsonArray=redel(jsonArray);//删除层次json字符中不用的属性
        System.out.println(jsonArray);
    }
    //消除json字符串中无用的属性
    public static JSONObject del(JSONObject jsonObject){
        jsonObject.remove("attributes");
        jsonObject.remove("id");
        jsonObject.remove("parent");
        return jsonObject;
    }
    //如果json字符串的属性是列表json字符串
    public static JSONArray redel(JSONArray jsonArray) {
        for (int i = 0; i < jsonArray.size(); i++) {

            JSONObject jsonObject = (JSONObject) jsonArray.get(i);

            if (jsonObject.getJSONArray("children").size() > 0) {
                del(jsonObject);
                redel(jsonObject.getJSONArray("children"));

            } else {
                del(jsonObject);
            }
        }
        return jsonArray;
    }
}