JavaWeb网上图书商城完整项目--day02-26.查询所有分类功能之DAO层实现

我们按照表示的设计

 

以及：

package com.weiyuan.goods.category.domain;

import java.util.List;

public class Category {

    private String cid;//主键
    private String cname;//分类的名称
    private String desc;//分类的描述
    private Category parent ;//父分类，对应表的pid字段，pid是一个外键
    private List<Category> children;//子分类
    public String getCid() {
        return cid;
    }
    public void setCid(String cid) {
        this.cid = cid;
    }
    public String getCname() {
        return cname;
    }
    public void setCname(String cname) {
        this.cname = cname;
    }
    public String getDesc() {
        return desc;
    }
    public void setDesc(String desc) {
        this.desc = desc;
    }
    public Category getParent() {
        return parent;
    }
    public void setParent(Category parent) {
        this.parent = parent;
    }
    public List<Category> getChildren() {
        return children;
    }
    public void setChildren(List<Category> children) {
        this.children = children;
    }
    @Override
    public String toString() {
        return "Category [cid=" + cid + ", cname=" + cname + ", desc=" + desc
                + ", parent=" + parent + ", children=" + children + "]";
    }
    
}

我们来获得数据模型层的代码：

package com.weiyuan.goods.category.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.handlers.MapListHandler;

import com.weiyuan.goods.category.domain.Category;

import cn.itcast.commons.CommonUtils;
import cn.itcast.jdbc.TxQueryRunner;

public class CategoryDao {
      //操作数据库
        private TxQueryRunner qr = new TxQueryRunner();
        

        /**
         * 查询出所有的一级分类
         * @throws SQLException 
         * */
    public List<Category> findAll() throws SQLException{
        //对应一级分类是对应的数据库表中的pid字段的值是null
        String sql = "select * from t_category where pid is null";
        List<Map<String, Object>> list = qr.query(sql, new MapListHandler());
        //获得所有的父节点元素
        List<Category> listCategory = toListCategory(list);
        //查找每一个父分类的子节点元素
        for(Category parent:listCategory){
            List<Category> childrenCategory = getChildrenCategory(parent.getCid());
            parent.setChildren(childrenCategory);
        }
        return listCategory;
    }
    
    //编写一个函数将List<Map<String, Object>>封装成一个List<Category>对象
    public List<Category> toListCategory(List<Map<String, Object>> mapList){
        List<Category> categories = new ArrayList();
        for(Map<String, Object> obj:mapList){
            Category c = toCategory(obj);
            categories.add(c);
        }
        
        return categories;
    }

    private Category toCategory(Map<String, Object> obj) {
        // TODO Auto-generated method stub
        Category bean = CommonUtils.toBean(obj, Category.class);
        String pid = (String) obj.get("pid");
        if(pid != null){//说明存在父节点
            Category parent = new Category();
            parent.setCid(pid);
            bean.setParent(parent);
            
        }
        return bean;
    }
    
    /*获得一级分类的子分类*/
    public List<Category> getChildrenCategory(String cid) throws SQLException{
        
        String sql = "select * from t_category where pid =?";
        List<Map<String, Object>> list = qr.query(sql, new MapListHandler(),cid);
        return toListCategory(list);
                
        
    }
}