jdbc中递归树封装查询数据

jdbc中递归树封装查询

• 一、递归树封装多次查询
• • • 数据库驱动包.jar文件：
    • ①数据库数据准备(使用IDEA(64位)软件和phpstudy软件，即小皮系统(64位))
    • ②实体：为了使用通用 Dao 将 children 属性放入 ParentCategory 中。
    • ③实体Category类
    • ④需要用到的工具类
    • • ①StringUtil类
      • ②DBUtil类
      • ③DataSource类
      • ④db.properties配置文件(记住要放在src目录下，数据根据个人情况而定)
    • ⑤测试类MainTest类
    • ⑥测试查询结果如下：
• 二、单次查询，内存中拆分(这是目录一的优化)
• • • ①起始条件是目录一中的条件：测试类如下：
    • ②单次查询，内存中拆分测试结果如下：

一、递归树封装多次查询 数据库驱动包.jar文件：

链接：https://pan.baidu.com/s/1zrA87rSYLA_A09hyZ9mieA
提取码：uh6r

①数据库数据准备(使用IDEA(64位)软件和phpstudy软件，即小皮系统(64位))

CREATE TABLE `category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '分类id',
  `name` char(50) DEFAULT NULL COMMENT '分类名称',
  `parent_id` bigint(20) DEFAULT NULL COMMENT '父分类id',
  `level` int(11) DEFAULT NULL COMMENT '层级',
  `show_status` tinyint(4) DEFAULT '1' COMMENT '是否显示[0-不显示，1显示]',
  `sort_number` int(11) DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1433 DEFAULT CHARSET=utf8mb4 COMMENT='商品三级分类';

INSERT INTO `category` VALUES (1,'图书、音像、电子书刊',0,1,1,0);
INSERT INTO `category` VALUES (2,'手机',0,1,1,0);
INSERT INTO `category` VALUES (22,'电子书刊',1,2,1,2);
INSERT INTO `category` VALUES (23,'音像',1,2,1,6);
INSERT INTO `category` VALUES (24,'英文原版',1,2,1,1);
INSERT INTO `category` VALUES (25,'文艺',1,2,1,12);
INSERT INTO `category` VALUES (34,'手机通讯',2,2,1,11);
INSERT INTO `category` VALUES (35,'运营商',2,2,1,7);
INSERT INTO `category` VALUES (36,'手机配件',2,2,1,2);
INSERT INTO `category` VALUES (165,'电子书',22,3,1,102);
INSERT INTO `category` VALUES (166,'网络原创',22,3,1,153);
INSERT INTO `category` VALUES (167,'数字杂志',22,3,1,129);
INSERT INTO `category` VALUES (168,'多媒体图书',22,3,1,17);
INSERT INTO `category` VALUES (169,'音乐',23,3,1,32);
INSERT INTO `category` VALUES (170,'影视',23,3,1,108);
INSERT INTO `category` VALUES (171,'教育音像',23,3,1,106);
INSERT INTO `category` VALUES (172,'少儿',24,3,1,35);
INSERT INTO `category` VALUES (173,'商务投资',24,3,1,27);
INSERT INTO `category` VALUES (174,'英语学习与考试',24,3,1,29);
INSERT INTO `category` VALUES (175,'文学',24,3,1,64);
INSERT INTO `category` VALUES (176,'传记',24,3,1,58);
INSERT INTO `category` VALUES (177,'励志',24,3,1,98);
INSERT INTO `category` VALUES (178,'小说',25,3,1,141);
INSERT INTO `category` VALUES (179,'文学',25,3,1,55);
INSERT INTO `category` VALUES (180,'青春文学',25,3,1,26);
INSERT INTO `category` VALUES (181,'传记',25,3,1,149);
INSERT INTO `category` VALUES (182,'艺术',25,3,1,122);
INSERT INTO `category` VALUES (225,'手机',34,3,1,201);
INSERT INTO `category` VALUES (226,'对讲机',34,3,1,103);
INSERT INTO `category` VALUES (227,'合约机',35,3,1,134);
INSERT INTO `category` VALUES (228,'选号中心',35,3,1,137);
INSERT INTO `category` VALUES (229,'装宽带',35,3,1,52);
INSERT INTO `category` VALUES (230,'办套餐',35,3,1,77);
INSERT INTO `category` VALUES (231,'移动电源',36,3,1,0);
INSERT INTO `category` VALUES (232,'电池/移动电源',36,3,1,1);
INSERT INTO `category` VALUES (233,'蓝牙耳机',36,3,1,2);
INSERT INTO `category` VALUES (234,'充电器/数据线',36,3,1,10);
INSERT INTO `category` VALUES (235,'苹果周边',36,3,1,42);
INSERT INTO `category` VALUES (236,'手机耳机',36,3,1,184);
INSERT INTO `category` VALUES (237,'手机贴膜',36,3,1,86);
INSERT INTO `category` VALUES (238,'手机存储卡',36,3,1,113);
INSERT INTO `category` VALUES (239,'充电器',36,3,1,69);
INSERT INTO `category` VALUES (240,'数据线',36,3,1,7);
INSERT INTO `category` VALUES (241,'手机保护套',36,3,1,65);
INSERT INTO `category` VALUES (242,'车载配件',36,3,1,64);
INSERT INTO `category` VALUES (243,'iPhone 配件',36,3,1,127);
INSERT INTO `category` VALUES (244,'手机电池',36,3,1,198);
INSERT INTO `category` VALUES (245,'创意配件',36,3,1,123);
INSERT INTO `category` VALUES (246,'便携/无线音响',36,3,1,18);
INSERT INTO `category` VALUES (247,'手机饰品',36,3,1,213);
INSERT INTO `category` VALUES (248,'拍照配件',36,3,1,22);
INSERT INTO `category` VALUES (249,'手机支架',36,3,1,217);

②实体：为了使用通用 Dao 将 children 属性放入 ParentCategory 中。

import java.util.List;

public class ParentCategory {
    private List children;

    public List getChildren() {
        return children;
    }

    public void setChildren(List children) {
        this.children = children;
    }
}

③实体Category类

public class Category extends ParentCategory{

  private long id;
  private String name;
  private long parentId;
  private Integer level;
  private Integer showStatus;
  private Integer sortNumber;

  @Override
  public String toString() {
    StringBuffer s = new StringBuffer();
    for (int i = 1; i < level; i++) {
      s.append("t");
    }
    return "n"+s+"Category{" +
            "id=" + id +
            ", name='" + name + ''' +
            ", parentId=" + parentId +
            ", level=" + level +
            ", showStatus=" + showStatus +
            ", sortNumber=" + sortNumber +
            ",  nt-->children=" + getChildren() +
            '}';
  }

  public long getId() {
    return id;
  }

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

  public String getName() {
    return name;
  }

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

  public long getParentId() {
    return parentId;
  }

  public void setParentId(long parentId) {
    this.parentId = parentId;
  }

  public Integer getLevel() {
    return level;
  }

  public void setLevel(Integer level) {
    this.level = level;
  }

  public Integer getShowStatus() {
    return showStatus;
  }

  public void setShowStatus(Integer showStatus) {
    this.showStatus = showStatus;
  }

  public Integer getSortNumber() {
    return sortNumber;
  }

  public void setSortNumber(Integer sortNumber) {
    this.sortNumber = sortNumber;
  }
}

④需要用到的工具类 ①StringUtil类

public class StringUtil {
    
    public static String underscoreName(String camelCaseName) {
        StringBuffer result = new StringBuffer();
        if (camelCaseName != null && camelCaseName.length() > 0) {
            result.append(camelCaseName.substring(0, 1).toLowerCase());
            //拿到第一字母，直接转小写
            for (int i = 1; i < camelCaseName.length(); i++) {
                char ch = camelCaseName.charAt(i);
                //取第i个位置的字符
                if (Character.isUpperCase(ch)) {//判断该字符是不是大写字母
                    result.append("_");//如果是大写，则拼接下划线
                    result.append(Character.toLowerCase(ch));
                    //将自己转为小写加入result
                } else {
                    result.append(ch);
                }
            }
        }
        return result.toString();
    }
}

②DBUtil类

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DBUtil {
    //8.where 条件和 条件中的参数一并传入，完成相应的查询效果
    public static  List select(Connection connection, Class clazz, String sql, Object[] objects) throws SQLException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException {
        StringBuffer stringBuffer = new StringBuffer("select ");
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            stringBuffer.append("`"+StringUtil.underscoreName(field.getName())+"`,");
        }
        stringBuffer.deleteCharAt(stringBuffer.length()-1);
        stringBuffer.append(" from `"+StringUtil.underscoreName(clazz.getSimpleName())+"` "+sql);
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
        for (int i = 0; i < objects.length; i++) {
            System.out.println(i + ":" + objects[i]);
            statement.setObject(i+1,objects[i]);
        }
        ResultSet resultSet = statement.executeQuery();
        List list = new ArrayList<>();
        while (resultSet.next()) {
            T tt = clazz.getDeclaredConstructor().newInstance();
            for (Field field : fields) {
                field.setAccessible(true);
                Object object = resultSet.getObject(StringUtil.underscoreName(field.getName()));
                field.set(tt,object);
            }
            list.add(tt);
        }
        System.out.println(stringBuffer);
        resultSet.close();
        statement.close();
        return list;
    }
    
    //7.查询第1页的10条数据
    public static  List pageData(Connection connection, Class clazz , int page, int number) throws SQLException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException {
        StringBuffer stringBuffer = new StringBuffer("select ");
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            stringBuffer.append("`"+StringUtil.underscoreName(field.getName())+"`,");
        }
        stringBuffer.deleteCharAt(stringBuffer.length()-1);
        stringBuffer.append(" from `"+StringUtil.underscoreName(clazz.getSimpleName())+"` limit "+(page-1)*number+","+number);
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
        ResultSet resultSet = statement.executeQuery();
        List list = new ArrayList<>();
        while (resultSet.next()) {
            T tt = clazz.getDeclaredConstructor().newInstance();
            for (Field field : fields) {
                field.setAccessible(true);
                Object object = resultSet.getObject(StringUtil.underscoreName(field.getName()));
                field.set(tt,object);
            }
            list.add(tt);
        }
        System.out.println(stringBuffer.toString());
        resultSet.close();
        statement.close();
        return list;
    }

    //6.删除id为2的数据
    public static boolean deleteById(Connection connection, Class clazz, int id) throws SQLException {
        StringBuffer stringBuffer = new StringBuffer("delete from `"+StringUtil.underscoreName(clazz.getSimpleName())+"` where id="+id);
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
        int i = statement.executeUpdate();
        statement.close();
        System.out.println(stringBuffer);
        return i!=0;
    }

    //5.获取id为2的数据
    public static  T findById(Connection connection, Class clazz, int i) throws SQLException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException {
        StringBuffer stringBuffer = new StringBuffer("select ");
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            stringBuffer.append("`"+StringUtil.underscoreName(field.getName())+"`,");
        }
        stringBuffer.deleteCharAt(stringBuffer.length()-1);
        stringBuffer.append(" from `"+StringUtil.underscoreName(clazz.getSimpleName())+"` where id="+i);
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
        ResultSet resultSet = statement.executeQuery();
        T tt=null;
        while (resultSet.next()) {
            tt = clazz.getDeclaredConstructor().newInstance();
            for (Field field : fields) {
                field.setAccessible(true);
                Object object = resultSet.getObject(StringUtil.underscoreName(field.getName()));
                field.set(tt,object);
            }
        }
        resultSet.close();
        statement.close();
        System.out.println(stringBuffer);//检验SQL语句是否正确
        return tt;
    }

    //4.返回数据库的总条数
    public static long allCount(Connection connection, Class clazz) throws SQLException {
        StringBuffer stringBuffer = new StringBuffer("select count(*) from "+"`"+StringUtil.underscoreName(clazz.getSimpleName())+"`");
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
        ResultSet resultSet = statement.executeQuery();
        long allCount=0;
        while (resultSet.next()) {
             allCount = resultSet.getLong(1);
        }
        resultSet.close();
        statement.close();
        return allCount;
    }

    //3.反射编写通用查询
    public static  List findAll(Class clazz, Connection connection) throws SQLException, IllegalAccessException, InstantiationException, NoSuchMethodException, InvocationTargetException {
        StringBuffer stringBuffer = new StringBuffer("select ");
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(true);
            stringBuffer.append("`"+StringUtil.underscoreName(field.getName())+"`,");
        }
        stringBuffer.deleteCharAt(stringBuffer.length()-1);
        stringBuffer.append(" from `"+StringUtil.underscoreName(clazz.getSimpleName())+"`");
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
        ResultSet resultSet = statement.executeQuery();
        List list=new ArrayList<>();
        while (resultSet.next()) {
        //创建对象
            T obj = clazz.getDeclaredConstructor().newInstance();
            for (Field field : fields) {
                Object object = resultSet.getObject(StringUtil.underscoreName(field.getName()));
                field.set(obj,object);
            }
            list.add(obj);
        }
        System.out.println(stringBuffer.toString());
        resultSet.close();
        statement.close();
        return list;
    }

    //2.反射编写通用修改数据
    public static boolean updateData(Object obj, Connection connection) throws SQLException, IllegalAccessException, NoSuchFieldException {
        Class clazz = obj.getClass();
        StringBuffer stringBuffer = new StringBuffer("update `"+StringUtil.underscoreName(clazz.getSimpleName()+"` set"));
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(true);
            if (field.getName().equals("id")) {
                continue;
            }
            stringBuffer.append(" `"+StringUtil.underscoreName(field.getName())+"`=?,");
        }
        stringBuffer.deleteCharAt(stringBuffer.length()-1);
        stringBuffer.append(" where id=?");
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
        for (int i = 0; i < fields.length; i++) {
            fields[i].setAccessible(true);
            if (fields[i].getName().equals("id")) {
                continue;
            }
            statement.setObject(i,fields[i].get(obj));
        }
        Field id=clazz.getDeclaredField("id");
        id.setAccessible(true);
        if (id.get(obj)==null) {
            throw new RuntimeException("id不能为空");
        }
        statement.setObject(fields.length,id.get(obj));
        int i = statement.executeUpdate();
        System.out.println(stringBuffer.toString());
        statement.close();
        return i!=0;
    }

    //1.反射编写通用插入数据
    public static boolean insertData(Object obj, Connection connection) throws SQLException, IllegalAccessException {
        Class clazz = obj.getClass();
        StringBuffer stringBuffer = new StringBuffer("insert into `"+StringUtil.underscoreName(clazz.getSimpleName())+"`(");
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(true);
            if(field.getName().equals("id")){
                continue;
            }
            stringBuffer.append("`"+StringUtil.underscoreName(field.getName())+"`,");
        }
        stringBuffer.deleteCharAt(stringBuffer.length()-1);
        stringBuffer.append(") values (");
        for (int i = 0; i < fields.length-1; i++) {
            stringBuffer.append("?,");
        }
        stringBuffer.deleteCharAt(stringBuffer.length()-1);
        stringBuffer.append(")");
        PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
        for (int i = 0; i < fields.length; i++) {
            if(fields[i].getName().equals("id")){
                continue;
            }
            Object o = fields[i].get(obj);
            statement.setObject(i,o);
            System.out.println(i + "-->" + o);
        }
        int i = statement.executeUpdate();
        System.out.println(stringBuffer.toString());
        statement.close();//关闭资源,释放内存
        return i!=0;
    }
}

③DataSource类

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.linkedList;
import java.util.Properties;

public class DataSource {
    private static linkedList pool = new linkedList<>();
    private static final int CONNECTION_COUNT = 5;
    private  DataSource(){
    }
    static {
        try {
            InputStream stream = DataSource.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(stream);
            String driverClassName = properties.getProperty("jdbc.driverClassName");
            String url = properties.getProperty("jdbc.url");
            String username = properties.getProperty("jdbc.username");
            String password = properties.getProperty("jdbc.password");
            Class.forName(driverClassName);

            for (int i = 0; i < CONNECTION_COUNT; i++) {
                Connection connection = DriverManager.getConnection(url, username, password);
                pool.add(connection);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        return pool.getFirst();
    }
    
    public static void closeConnection(Connection connection) {
        pool.addLast(connection);
    }
}

④db.properties配置文件(记住要放在src目录下，数据根据个人情况而定)

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mydb?characterEncoding=utf8&useSSL=false
jdbc.username=root
jdbc.password=123456

⑤测试类MainTest类

import cn.hxzy.demo1124.entity.Category;
import cn.hxzy.demo1124.util.DBUtil;
import cn.hxzy.demo1124.util.DataSource;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;

public class MainTest {
    public static void main(String[] args) throws InvocationTargetException, SQLException, InstantiationException, IllegalAccessException, NoSuchMethodException {
        Connection connection = DataSource.getConnection();
        List selectData = DBUtil.select(connection,Category.class,"where parent_id =?",new Object[]{0});
        for (Category category : selectData) {
           setChildren(connection,category);
        }
        for (Category category : selectData) {
            System.out.println(category);
        }}
    private static void setChildren(Connection connection, Category category) throws InvocationTargetException, SQLException, InstantiationException, IllegalAccessException, NoSuchMethodException {
        List children = DBUtil.select(connection,Category.class,"where parent_id =?",new Object[]{category.getId()});
        for (Category child : children) {
        //反复调用它自身的方法(递归调用)
            setChildren(connection,child);
        }
        category.setChildren(children);
    }
}

⑥测试查询结果如下：

F:softwarejdk-11binjava.exe -javaagent:F:softwareideaIU-2020.1libidea_rt.jar=11170:F:softwareideaIU-2020.1bin -Dfile.encoding=UTF-8 -classpath E:IdeaProjectsjdbcoutproductionjdbc;E:IdeaProjectsjdbcmysql-connector-java-5.1.48.jar;E:IdeaProjectsjdbcdruid-1.2.8.jar cn.hxzy.demo1124.MainTest
0:0
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:1
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:22
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:165
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:166
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:167
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:168
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:23
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:169
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:170
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:171
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:24
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:172
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:173
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:174
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:175
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:176
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:177
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:25
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:178
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:179
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:180
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:181
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:182
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:2
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:34
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:225
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:226
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:35
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:227
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:228
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:229
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:230
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:36
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:231
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:232
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:233
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:234
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:235
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:236
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:237
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:238
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:239
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:240
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:241
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:242
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:243
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:244
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:245
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:246
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:247
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:248
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?
0:249
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category` where parent_id =?

Category{id=1, name='图书、音像、电子书刊', parentId=0, level=1, showStatus=1, sortNumber=0,  
	-->children=[
	Category{id=22, name='电子书刊', parentId=1, level=2, showStatus=1, sortNumber=2,  
	-->children=[
		Category{id=165, name='电子书', parentId=22, level=3, showStatus=1, sortNumber=102,  
	-->children=[]}, 
		Category{id=166, name='网络原创', parentId=22, level=3, showStatus=1, sortNumber=153,  
	-->children=[]}, 
		Category{id=167, name='数字杂志', parentId=22, level=3, showStatus=1, sortNumber=129,  
	-->children=[]}, 
		Category{id=168, name='多媒体图书', parentId=22, level=3, showStatus=1, sortNumber=17,  
	-->children=[]}]}, 
	Category{id=23, name='音像', parentId=1, level=2, showStatus=1, sortNumber=6,  
	-->children=[
		Category{id=169, name='音乐', parentId=23, level=3, showStatus=1, sortNumber=32,  
	-->children=[]}, 
		Category{id=170, name='影视', parentId=23, level=3, showStatus=1, sortNumber=108,  
	-->children=[]}, 
		Category{id=171, name='教育音像', parentId=23, level=3, showStatus=1, sortNumber=106,  
	-->children=[]}]}, 
	Category{id=24, name='英文原版', parentId=1, level=2, showStatus=1, sortNumber=1,  
	-->children=[
		Category{id=172, name='少儿', parentId=24, level=3, showStatus=1, sortNumber=35,  
	-->children=[]}, 
		Category{id=173, name='商务投资', parentId=24, level=3, showStatus=1, sortNumber=27,  
	-->children=[]}, 
		Category{id=174, name='英语学习与考试', parentId=24, level=3, showStatus=1, sortNumber=29,  
	-->children=[]}, 
		Category{id=175, name='文学', parentId=24, level=3, showStatus=1, sortNumber=64,  
	-->children=[]}, 
		Category{id=176, name='传记', parentId=24, level=3, showStatus=1, sortNumber=58,  
	-->children=[]}, 
		Category{id=177, name='励志', parentId=24, level=3, showStatus=1, sortNumber=98,  
	-->children=[]}]}, 
	Category{id=25, name='文艺', parentId=1, level=2, showStatus=1, sortNumber=12,  
	-->children=[
		Category{id=178, name='小说', parentId=25, level=3, showStatus=1, sortNumber=141,  
	-->children=[]}, 
		Category{id=179, name='文学', parentId=25, level=3, showStatus=1, sortNumber=55,  
	-->children=[]}, 
		Category{id=180, name='青春文学', parentId=25, level=3, showStatus=1, sortNumber=26,  
	-->children=[]}, 
		Category{id=181, name='传记', parentId=25, level=3, showStatus=1, sortNumber=149,  
	-->children=[]}, 
		Category{id=182, name='艺术', parentId=25, level=3, showStatus=1, sortNumber=122,  
	-->children=[]}]}]}

Category{id=2, name='手机', parentId=0, level=1, showStatus=1, sortNumber=0,  
	-->children=[
	Category{id=34, name='手机通讯', parentId=2, level=2, showStatus=1, sortNumber=11,  
	-->children=[
		Category{id=225, name='手机', parentId=34, level=3, showStatus=1, sortNumber=201,  
	-->children=[]}, 
		Category{id=226, name='对讲机', parentId=34, level=3, showStatus=1, sortNumber=103,  
	-->children=[]}]}, 
	Category{id=35, name='运营商', parentId=2, level=2, showStatus=1, sortNumber=7,  
	-->children=[
		Category{id=227, name='合约机', parentId=35, level=3, showStatus=1, sortNumber=134,  
	-->children=[]}, 
		Category{id=228, name='选号中心', parentId=35, level=3, showStatus=1, sortNumber=137,  
	-->children=[]}, 
		Category{id=229, name='装宽带', parentId=35, level=3, showStatus=1, sortNumber=52,  
	-->children=[]}, 
		Category{id=230, name='办套餐', parentId=35, level=3, showStatus=1, sortNumber=77,  
	-->children=[]}]}, 
	Category{id=36, name='手机配件', parentId=2, level=2, showStatus=1, sortNumber=2,  
	-->children=[
		Category{id=231, name='移动电源', parentId=36, level=3, showStatus=1, sortNumber=0,  
	-->children=[]}, 
		Category{id=232, name='电池/移动电源', parentId=36, level=3, showStatus=1, sortNumber=1,  
	-->children=[]}, 
		Category{id=233, name='蓝牙耳机', parentId=36, level=3, showStatus=1, sortNumber=2,  
	-->children=[]}, 
		Category{id=234, name='充电器/数据线', parentId=36, level=3, showStatus=1, sortNumber=10,  
	-->children=[]}, 
		Category{id=235, name='苹果周边', parentId=36, level=3, showStatus=1, sortNumber=42,  
	-->children=[]}, 
		Category{id=236, name='手机耳机', parentId=36, level=3, showStatus=1, sortNumber=184,  
	-->children=[]}, 
		Category{id=237, name='手机贴膜', parentId=36, level=3, showStatus=1, sortNumber=86,  
	-->children=[]}, 
		Category{id=238, name='手机存储卡', parentId=36, level=3, showStatus=1, sortNumber=113,  
	-->children=[]}, 
		Category{id=239, name='充电器', parentId=36, level=3, showStatus=1, sortNumber=69,  
	-->children=[]}, 
		Category{id=240, name='数据线', parentId=36, level=3, showStatus=1, sortNumber=7,  
	-->children=[]}, 
		Category{id=241, name='手机保护套', parentId=36, level=3, showStatus=1, sortNumber=65,  
	-->children=[]}, 
		Category{id=242, name='车载配件', parentId=36, level=3, showStatus=1, sortNumber=64,  
	-->children=[]}, 
		Category{id=243, name='iPhone 配件', parentId=36, level=3, showStatus=1, sortNumber=127,  
	-->children=[]}, 
		Category{id=244, name='手机电池', parentId=36, level=3, showStatus=1, sortNumber=198,  
	-->children=[]}, 
		Category{id=245, name='创意配件', parentId=36, level=3, showStatus=1, sortNumber=123,  
	-->children=[]}, 
		Category{id=246, name='便携/无线音响', parentId=36, level=3, showStatus=1, sortNumber=18,  
	-->children=[]}, 
		Category{id=247, name='手机饰品', parentId=36, level=3, showStatus=1, sortNumber=213,  
	-->children=[]}, 
		Category{id=248, name='拍照配件', parentId=36, level=3, showStatus=1, sortNumber=22,  
	-->children=[]}, 
		Category{id=249, name='手机支架', parentId=36, level=3, showStatus=1, sortNumber=217,  
	-->children=[]}]}]}

Process finished with exit code 0

注意：过程当中导包不要导错

二、单次查询，内存中拆分(这是目录一的优化) ①起始条件是目录一中的条件：测试类如下：

import cn.hxzy.demo1124.entity.Category;
import cn.hxzy.demo1124.util.DBUtil;
import cn.hxzy.demo1124.util.DataSource;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;

public class MainTest {
    public static void main(String[] args) throws InvocationTargetException, SQLException, InstantiationException, IllegalAccessException, NoSuchMethodException {
        Connection connection = DataSource.getConnection();
        List list = DBUtil.findAll(Category.class, connection);
        List weNeed=new ArrayList<>();
        list.forEach(c->{
            if(c.getParentId()==0){
                setChildren(c,list);
                weNeed.add(c);
            }
        });
        for (Category category : weNeed) {
            System.out.println(category);
        }
        DataSource.closeConnection(connection);
    }

    private static void setChildren(Category c, List list) {
        List weNeed=new ArrayList<>();
        for (Category category : list) {
            if (c.getId()==category.getParentId()) {
             //反复调用它自身的方法(递归调用)
                setChildren(category,list);
                weNeed.add(category);
            }
        }
        weNeed.sort(new Comparator() {
            @Override
            public int compare(Category o1, Category o2) {
                return o1.getSortNumber()-o2.getSortNumber();
            }
        });
        //上述排序第二种简化方式
        //weNeed.sort((o1, o2) -> o1.getSortNumber()-o2.getSortNumber());
        //上述排序第三种简化方式
        // weNeed.sort(Comparator.comparingInt(Category::getSortNumber));
        c.setChildren(weNeed);
    }
}

②单次查询，内存中拆分测试结果如下：

F:softwarejdk-11binjava.exe -javaagent:F:softwareideaIU-2020.1libidea_rt.jar=11397:F:softwareideaIU-2020.1bin -Dfile.encoding=UTF-8 -classpath E:IdeaProjectsjdbcoutproductionjdbc;E:IdeaProjectsjdbcmysql-connector-java-5.1.48.jar;E:IdeaProjectsjdbcdruid-1.2.8.jar cn.hxzy.demo1124.MainTest
select `id`,`name`,`parent_id`,`level`,`show_status`,`sort_number` from `category`

Category{id=1, name='图书、音像、电子书刊', parentId=0, level=1, showStatus=1, sortNumber=0,  
	-->children=[
	Category{id=24, name='英文原版', parentId=1, level=2, showStatus=1, sortNumber=1,  
	-->children=[
		Category{id=173, name='商务投资', parentId=24, level=3, showStatus=1, sortNumber=27,  
	-->children=[]}, 
		Category{id=174, name='英语学习与考试', parentId=24, level=3, showStatus=1, sortNumber=29,  
	-->children=[]}, 
		Category{id=172, name='少儿', parentId=24, level=3, showStatus=1, sortNumber=35,  
	-->children=[]}, 
		Category{id=176, name='传记', parentId=24, level=3, showStatus=1, sortNumber=58,  
	-->children=[]}, 
		Category{id=175, name='文学', parentId=24, level=3, showStatus=1, sortNumber=64,  
	-->children=[]}, 
		Category{id=177, name='励志', parentId=24, level=3, showStatus=1, sortNumber=98,  
	-->children=[]}]}, 
	Category{id=22, name='电子书刊', parentId=1, level=2, showStatus=1, sortNumber=2,  
	-->children=[
		Category{id=168, name='多媒体图书', parentId=22, level=3, showStatus=1, sortNumber=17,  
	-->children=[]}, 
		Category{id=165, name='电子书', parentId=22, level=3, showStatus=1, sortNumber=102,  
	-->children=[]}, 
		Category{id=167, name='数字杂志', parentId=22, level=3, showStatus=1, sortNumber=129,  
	-->children=[]}, 
		Category{id=166, name='网络原创', parentId=22, level=3, showStatus=1, sortNumber=153,  
	-->children=[]}]}, 
	Category{id=23, name='音像', parentId=1, level=2, showStatus=1, sortNumber=6,  
	-->children=[
		Category{id=169, name='音乐', parentId=23, level=3, showStatus=1, sortNumber=32,  
	-->children=[]}, 
		Category{id=171, name='教育音像', parentId=23, level=3, showStatus=1, sortNumber=106,  
	-->children=[]}, 
		Category{id=170, name='影视', parentId=23, level=3, showStatus=1, sortNumber=108,  
	-->children=[]}]}, 
	Category{id=25, name='文艺', parentId=1, level=2, showStatus=1, sortNumber=12,  
	-->children=[
		Category{id=180, name='青春文学', parentId=25, level=3, showStatus=1, sortNumber=26,  
	-->children=[]}, 
		Category{id=179, name='文学', parentId=25, level=3, showStatus=1, sortNumber=55,  
	-->children=[]}, 
		Category{id=182, name='艺术', parentId=25, level=3, showStatus=1, sortNumber=122,  
	-->children=[]}, 
		Category{id=178, name='小说', parentId=25, level=3, showStatus=1, sortNumber=141,  
	-->children=[]}, 
		Category{id=181, name='传记', parentId=25, level=3, showStatus=1, sortNumber=149,  
	-->children=[]}]}]}

Category{id=2, name='手机', parentId=0, level=1, showStatus=1, sortNumber=0,  
	-->children=[
	Category{id=36, name='手机配件', parentId=2, level=2, showStatus=1, sortNumber=2,  
	-->children=[
		Category{id=231, name='移动电源', parentId=36, level=3, showStatus=1, sortNumber=0,  
	-->children=[]}, 
		Category{id=232, name='电池/移动电源', parentId=36, level=3, showStatus=1, sortNumber=1,  
	-->children=[]}, 
		Category{id=233, name='蓝牙耳机', parentId=36, level=3, showStatus=1, sortNumber=2,  
	-->children=[]}, 
		Category{id=240, name='数据线', parentId=36, level=3, showStatus=1, sortNumber=7,  
	-->children=[]}, 
		Category{id=234, name='充电器/数据线', parentId=36, level=3, showStatus=1, sortNumber=10,  
	-->children=[]}, 
		Category{id=246, name='便携/无线音响', parentId=36, level=3, showStatus=1, sortNumber=18,  
	-->children=[]}, 
		Category{id=248, name='拍照配件', parentId=36, level=3, showStatus=1, sortNumber=22,  
	-->children=[]}, 
		Category{id=235, name='苹果周边', parentId=36, level=3, showStatus=1, sortNumber=42,  
	-->children=[]}, 
		Category{id=242, name='车载配件', parentId=36, level=3, showStatus=1, sortNumber=64,  
	-->children=[]}, 
		Category{id=241, name='手机保护套', parentId=36, level=3, showStatus=1, sortNumber=65,  
	-->children=[]}, 
		Category{id=239, name='充电器', parentId=36, level=3, showStatus=1, sortNumber=69,  
	-->children=[]}, 
		Category{id=237, name='手机贴膜', parentId=36, level=3, showStatus=1, sortNumber=86,  
	-->children=[]}, 
		Category{id=238, name='手机存储卡', parentId=36, level=3, showStatus=1, sortNumber=113,  
	-->children=[]}, 
		Category{id=245, name='创意配件', parentId=36, level=3, showStatus=1, sortNumber=123,  
	-->children=[]}, 
		Category{id=243, name='iPhone 配件', parentId=36, level=3, showStatus=1, sortNumber=127,  
	-->children=[]}, 
		Category{id=236, name='手机耳机', parentId=36, level=3, showStatus=1, sortNumber=184,  
	-->children=[]}, 
		Category{id=244, name='手机电池', parentId=36, level=3, showStatus=1, sortNumber=198,  
	-->children=[]}, 
		Category{id=247, name='手机饰品', parentId=36, level=3, showStatus=1, sortNumber=213,  
	-->children=[]}, 
		Category{id=249, name='手机支架', parentId=36, level=3, showStatus=1, sortNumber=217,  
	-->children=[]}]}, 
	Category{id=35, name='运营商', parentId=2, level=2, showStatus=1, sortNumber=7,  
	-->children=[
		Category{id=229, name='装宽带', parentId=35, level=3, showStatus=1, sortNumber=52,  
	-->children=[]}, 
		Category{id=230, name='办套餐', parentId=35, level=3, showStatus=1, sortNumber=77,  
	-->children=[]}, 
		Category{id=227, name='合约机', parentId=35, level=3, showStatus=1, sortNumber=134,  
	-->children=[]}, 
		Category{id=228, name='选号中心', parentId=35, level=3, showStatus=1, sortNumber=137,  
	-->children=[]}]}, 
	Category{id=34, name='手机通讯', parentId=2, level=2, showStatus=1, sortNumber=11,  
	-->children=[
		Category{id=226, name='对讲机', parentId=34, level=3, showStatus=1, sortNumber=103,  
	-->children=[]}, 
		Category{id=225, name='手机', parentId=34, level=3, showStatus=1, sortNumber=201,  
	-->children=[]}]}]}

Process finished with exit code 0