递归查询

public class CategoryDTO{

private Integer id;

private String text;

private Integer childrenCount;

private List<CategoryDTO> children = new ArrayList<CategoryDTO>();

set ...   get....

}

public List<CategoryDTO> findAllCategories(Integer parentId) throws Exception{

List<CategoryDTO> cds = new ArrayList<CategoryDTO>();

List<Object[]> types = tcd.findByParentId(-1);

for(Object[] type : types){

CategoryDTO cd = new CategoryDTO();

cd.setId(Integer.parseInt(type[0].toString()));
cd.setText(type[1].toString());
Integer childrenCount = (null == type[2] ? 0 : Integer.parseInt(type[2].toString()));

if(childrenCount == 0){

cd.setChildren(null);

} else {

cd.setChildren(getTypes(cd, childrenCount))

}

cds.add(cd);

}

return cds;

}

//递归调用查询子类类别

private List<CategoryDTO> getTypes(CategoryDTO cd, Integer childCount) throws Exception{

List<CategoryDTO> cds = null;

if(childrenCount != 0){

cds = new ArrayList<CategoryDTO>();
List<Object[]> types = tcd.findByParentId(-1);

for(Object[] type : types){

CategoryDTO children = new CategoryDTO();

children .setId(Integer.parseInt(type[0].toString()));
children .setText(type[1].toString());
Integer childrenCount = (null == type[2] ? 0 : Integer.parseInt(type[2].toString()));

children.setChildrenCount(childrenCount);

cds.add(children);

List<CategoryDTO> types2 = getTypes(children,children.getChildrenCount());

children.setChildren(types2);

}

return cds;

}

}

查询的sql语句: select t1.cid, t1.cname, count(c2.id) from (select c1.id as cid, c1.name as cname from category where pid=?) as t1 left join category c2 on c1.cid = c2.pid group by t1.cid, t1.cname