树形结构可以清楚地呈现数据的从属关系,在数据库中我们可以用自关联来保存这样的关系。但是在取出数据的时候,如果采用以往的手段,免不了要进行递归操作。递归在理论上是可以解决树形结构的问题,但是如果数据量够大,目录层次够深,我们递归出层次关系是比较消耗资源的。我们可以从数据结构入手,树有它的分支,每个分支又可以延伸出新的分支,那么我们可以在每个节点中增加它的左支和右支,左支和右支良好的维护了树形体系。父节点的左支是永远小于子节点的左支,父节点的右支是永远大于子节点的右支,有了这样的关系,我们想要表现出树形结构是很简单的。所以我们将数据库表设计为自关联表,每个记录中都维护着一个坐支和右支。不必在表中添加父节点列也可以轻松搞定树状关系。
效果图:
Dao层
- package cn.dk.dao;
- import java.sql.SQLException;
- import java.util.List;
- import org.apache.commons.dbutils.QueryRunner;
- import org.apache.commons.dbutils.handlers.BeanHandler;
- import org.apache.commons.dbutils.handlers.BeanListHandler;
- import cn.dk.domain.Category;
- import cn.dk.utils.JdbcUtils;
- public class CategoryDao {
- @SuppressWarnings("unchecked")
- public List<Category> selectAllCategory() {
- QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
- String sql = "select c.id,c.name,c.left_hand,c.right_hand,count(c.name) depth from category p,category c where p.left_hand<=c.left_hand and p.right_hand>=c.right_hand group by c.name order by c.left_hand";
- try {
- return (List<Category>) runner.query(sql, new BeanListHandler(
- Category.class));
- } catch (SQLException e) {
- throw new RuntimeException(e);
- }
- }
- public Category selectCategory(String id) {
- QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
- String sql = "select id,name,left_hand,right_hand from category where id=?";
- Object[] params = { id };
- try {
- return (Category) runner.query(sql,
- new BeanHandler(Category.class), params);
- } catch (SQLException e) {
- throw new RuntimeException(e);
- }
- }
- public void insertCategory(Category category) {
- QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
- String sql = "insert into category values(?,?,?,?)";
- Object[] params = { category.getId(), category.getName(),
- category.getLeft_hand(), category.getRight_hand() };
- try {
- runner.update(sql, params);
- } catch (SQLException e) {
- throw new RuntimeException(e);
- }
- }
- @SuppressWarnings("unchecked")
- public List<Category> selectParent(String id) {
- QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
- String sql = "select p.id,p.name,p.left_hand,p.right_hand from category p,category c where p.left_hand <= c.left_hand and p.right_hand >= c.right_hand and c.id=? order by p.left_hand";
- Object[] params = { id };
- try {
- return (List<Category>) runner.query(sql, new BeanListHandler(
- Category.class), params);
- } catch (SQLException e) {
- throw new RuntimeException(e);
- }
- }
- public void updateCategory(int left_hand) {
- String sql = "update category set left_hand=left_hand+2 where left_hand>? ";
- QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
- try {
- runner.update(sql, left_hand);
- } catch (SQLException e) {
- throw new RuntimeException(e);
- }
- sql = "update category set right_hand=right_hand+2 where right_hand>=? ";
- try {
- runner.update(sql, left_hand);
- } catch (SQLException e) {
- throw new RuntimeException(e);
- }
- }
- }