categorymysqldao.java

基于mysql5.0完成的shopping网站后台

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.shopping.dao;

import com.shopping.model.Category;
import com.shopping.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Administrator
 */
public class CategoryMysqlDAO implements CategoryDAO {

    ///新建;删除；浏览;;
    //由于我们希望，对象一个够;
    public int save(Category c) {
        //
        Connection conn = null;
        PreparedStatement pstmt = null;
        String sql = "insert into category values(null,0,?,?,0,0)";
        System.out.println(sql);
        int rs = -1;
        try {
            conn = DBUtil.getConnection();
            pstmt = DBUtil.getPStatement(conn, sql);
            pstmt.setString(1, c.getName());
            pstmt.setString(2, c.getDescr());
            rs = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.getMessage();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }

        return rs;

    }

    public void del(Category c) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<Category> getCategorys() {
        List<Category> list = new ArrayList<Category>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select * from category";
        System.out.println(sql);
        try {
            conn = DBUtil.getConnection();
            pstmt = DBUtil.getPStatement(conn, sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Category c = new Category();
                c.setId(rs.getInt("id"));
                c.setName(rs.getString("name"));
                c.setDescr(rs.getString("descr"));
                c.setGrade(rs.getInt("grade"));
                c.setIsleaf(rs.getInt("cno") == 0 ? true : false);
                c.setPid(rs.getInt("pid"));
                list.add(c);
            }
        } catch (SQLException e) {
            e.getMessage();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return list;
    }

    public void addChildCategory(int pid, String cname, String cdescr, int grade) {

        //连接上数据库
        Connection conn = null;
        Statement sstmt = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Statement stmt = null;
        try {
            conn = DBUtil.getConnection();
            sstmt = DBUtil.getStatement(conn);

            //...........
            conn.setAutoCommit(false);


            //插入一个新的类别
            String sql = "insert into category values(null,?,?,?,?,?)";


            pstmt = DBUtil.getPStatement(conn, sql);


            pstmt.setInt(1, pid);
            pstmt.setString(2, cname);
            pstmt.setString(3, cdescr);
            pstmt.setInt(4, 0);//0代表是新添加是叶子节点.....
            pstmt.setInt(5, grade + 1);//在你类级别上加一..
            pstmt.executeUpdate();

            //将父类别isleaf 改成 1　父类另不再是叶子节点...
            sql = "update  category  set cno = 1 where id = " + pid;
            stmt = DBUtil.getStatement(conn);
            stmt.executeUpdate(sql);


            conn.commit();//手动提交
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            try {
                conn.rollback();
                e.printStackTrace();
            } catch (SQLException ex) {
                Logger.getLogger(CategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

    }

   

    public Category loadById(int id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    private void tree(List<Category> list, Connection conn, int id, int grade) {
        String sql = "select * from category where pid = " + id;
        Statement stmt = DBUtil.getStatement(conn);
        ResultSet rs = DBUtil.query(stmt, sql);


        //rs = 2   id 9 id 13
        try {
            while (rs.next()) {
                Category c = new Category();
                //注意这个值是传进来的
                c.init(rs);


                c.setGrade(grade);//1
                list.add(c);//3

                //如果不是页子节点，再迭代一次
                if (!c.isIsleaf()) {
                    tree(list, conn, c.getId(), grade + 1);
                }

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, stmt, rs);
        }

    }
    
    
    
    
    
     public void delete(int id, int pid) {
       //删除id delete
       //pid isleaf; update
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Statement stmt2 = null;


        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);

            //删除产品类别及子类别;;
            delete(conn, id);

            stmt = DBUtil.getStatement(conn);

            //查看父节点，有没有其它子结点
            String sql = "select count(*) from category where pid = " + pid;
System.out.println("............."+sql);
            rs = DBUtil.query(stmt, sql);
            rs.next();
            int count = rs.getInt(1);

  
                //没有其它的子贴
                if(count==0){ 
                stmt2 = DBUtil.getStatement(conn);
                String sql2 = "update category set cno = 0 where id = " + pid;
System.out.println("________"+sql2);
                DBUtil.update(stmt2, sql2);
                }
         
            conn.commit();
            conn.setAutoCommit(true);

        } catch (SQLException e) {
            try {
                conn.rollback();
                e.printStackTrace();
            } catch (SQLException ex) {
                Logger.getLogger(CategoryMysqlDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        } finally {
            DBUtil.close(null, stmt2, rs);
            DBUtil.close(conn, stmt, null);
        }


    }
    
//删除类别功能
//将自己和所有子节点删除
//迭代删除所有
    private void delete(Connection conn, int id) {

        //有没有子节点；
        String sql1 = "select * from category where pid = " + id;
System.out.println("******"+sql1);
        Statement stmt1 = DBUtil.getStatement(conn);
        ResultSet rs1 = DBUtil.query(stmt1, sql1);

        try {
            while (rs1.next()) {
                //能进来就是有子贴
                delete(conn, rs1.getInt("id"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, stmt1, rs1);
        }


        //....................................
        //如果没有子贴删除
      
        Statement stmt2 = DBUtil.getStatement(conn);
        String sql2 = "delete from category where id = " + id;
        
        try {
            DBUtil.update(stmt2, sql2);
        } finally {
            DBUtil.close(null, stmt2, null);
        }
        }
public static void main(String args[]){
 CategoryMysqlDAO c = new CategoryMysqlDAO();
 c.delete(10, 0);
} 
   
}