Java查询MYSQL数据库商品分类表，得不到想要的数据集

Question

问题：我通过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=[]}]}]}

Answer 1

您好，请将最上面的resultset 初始化去掉，然后把 rs = ps.executeQuery(); 替换成ResultSet rs = ps.executeQuery();
因为你在递归的时候结果集变了