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=/span>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<Category> children;
public List<Category> getChildren() {
return children;
}
public void setChildren(List<Category> 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 +
", \n\t-->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 {
/** * 驼峰转下划线 * @param camelCaseName 驼峰字符串 * @return 下划线字符串 */
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