问题:我通过CategoryDaoImpl查询数据库中的category表的数据,以树存储返回一个跟,但结果却只包含以及分类根节点,以及根节点一个子节点和后面的孙节点。
代码如下:实体类**Category**
package com.liao.sm.entity;
import java.util.List;
public class Category {
private int id;//编号
private String title;//分类名称
private int pid;//父类编号
private String info;//备注
private List childs;//子分类集合
public Category(int id, String title, int pid, String info) {
this.id = id;
this.title = title;
this.pid = pid;
this.info = info;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public int getPid() {
return pid;
}
public void setPid(int pid) {
this.pid = pid;
}
public String getInfo() {
return info;
}
public void setInfo(String info) {
this.info = info;
}
public List<Category> getChilds() {
return childs;
}
public void setChilds(List<Category> childs) {
this.childs = childs;
}
@Override
public String toString() {
return "Category{" +
"id=" + id +
", title='" + title + '\'' +
", pid=" + pid +
", info='" + info + '\'' +
", childs=" + childs +
'}';
}
}
CategoryDaoImpl如下
package com.liao.sm.dao.impl;
import com.liao.sm.dao.CategoryDao;
import com.liao.sm.entity.Category;
import com.liao.sm.util.DBManageUtil;
import com.mysql.jdbc.Connection;
import org.junit.Test;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class CategoryDaoImpl implements CategoryDao {
private Connection conn = null;
private PreparedStatement ps = null;
private ResultSet rs = null;
public CategoryDaoImpl() {
conn = DBManageUtil.getConnection();
}
public Category getRoot() {
int id = 10000;//根节点ID
String sql = "select* from product_category where id=?";
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
rs = ps.executeQuery();
if (rs.next()) {
Category category = new Category(rs.getInt("id"),
rs.getString("title"),
rs.getInt("pid"),
rs.getString("info"));
category.setChilds(selectChildsById(id));
return category;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBManageUtil.close(conn,ps,rs);
}
return null;
}
private List<Category> selectChildsById(int id) {
String sql = "select * from product_category where pid=?";
List<Category> list = new ArrayList<Category>();
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
rs = ps.executeQuery();
while (rs.next()) {
Category category = new Category(rs.getInt("id"),
rs.getString("title"),
rs.getInt("pid"),
rs.getString("info"));
/** 此处通过递归实现查询所有子类的各个子类*/
category.setChilds(selectChildsById(rs.getInt("id")));
list.add(category);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
数据表category设计:
create table product_category(
id int not null auto_increment comment '分类编号',
title varchar(16) not null comment '分类名称',
pid int comment '父类编号',
info varchar(255) comment '备注',
primary key(id)
)engine=innodb,default charset=utf8;
/*pid int comment '父类编号 这里不能设置非空,否则不能插入根' */
/*添加外键*/
alter table product_category
add constraint FK_category_self foreign key(pid) references product_category(id);
insert into product_category values(10000,"商品",null,null);
insert into product_category(title,pid,info) values("家用电器",10000,null);
insert into product_category(title,pid,info) values("一般食品",10000,null);
insert into product_category(title,pid,info) values("家用百货",10000,null);
insert into product_category(title,pid,info) values("厨卫用具",10001,null);
测试结果如下
Category{id=10000, title='商品', pid=0, info='null', childs=[Category{id=10001, title='家用电器', pid=10000, info='null', childs=[Category{id=10004, title='厨卫用具', pid=10001, info='null', childs=[]}]}]}