weixin_40268056 2019-01-08 01:17 采纳率: 100%
浏览 545
已采纳

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

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

  • 写回答

3条回答 默认 最新

  • qq_31290353 2019-01-08 10:10
    关注

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥115 用Java解决探地雷达dzt文件的解析过程
  • ¥20 有关神经网络这道(b)determine the parameters of neural network
  • ¥25 annaconda jvpyter
  • ¥20 来一个会抓包app支付接口的
  • ¥30 vivado2017.4的sdk打不开
  • ¥50 matlab,文献复现
  • ¥20 寻找Flink CDC指导,有偿咨询
  • ¥15 cruise如何仿真不同温度下车辆的经济性?
  • ¥20 扑克牌游戏的完整代码
  • ¥88 求帮安装Python架构的vnpy