/* * 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);}    }