CategoryManager

package com.ldc.shopping.category;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 com.ldc.shopping.category.Category;import com.ldc.shopping.util.DB;public class CategoryService { private static CategoryService service; private CategoryService() {} public static CategoryService getInstance(){ if(service ==null){ service = new CategoryService(); } return service; } //保存分类 public void add(Category c) {Connection conn = DB.getConn();try {conn.setAutoCommit(false);} catch (SQLException e1) {e1.printStackTrace();} String sql = "insert into category values (null, ?, ?, ?, ?, ?)"; PreparedStatement pstmt = DB.prepare(conn, sql); try {int cno = getNextCno(conn, c);pstmt.setInt(1, c.getPid());pstmt.setString(2, c.getName());pstmt.setString(3, c.getDescr());pstmt.setInt(4, cno);pstmt.setInt(5, c.getGrade());pstmt.executeUpdate();conn.commit();} catch (SQLException e) {try {conn.rollback();} catch (SQLException e1) {e1.printStackTrace();}e.printStackTrace();} finally {try {conn.setAutoCommit(true);} catch (SQLException e) {e.printStackTrace();}DB.close(pstmt);DB.close(conn);}} public int getCnoById(int id){ int cno =0; Connection conn = null; String sql = "select * from category where id="+id; Statement stmt = null; ResultSet rs = null; try{ conn = DB.getConn(); stmt = DB.getStmt(conn); rs = stmt.executeQuery(sql); while(rs.next()) { cno =rs.getInt("cno"); } } catch (SQLException e) {e.printStackTrace();} finally {DB.close(rs);DB.close(stmt);DB.close(conn);}return cno; } /* * 计算下一个分类的编号 */private int getNextCno(Connection conn, Category c) {int cno = -1;String sqlMax = "select max(cno) from category where pid = " + c.getPid();Statement stmtMax = DB.getStmt(conn);ResultSet rsMax = DB.getResultSet(stmtMax, sqlMax);try {rsMax.next();int cnoMax = rsMax.getInt(1); //System.out.println(cnoMax);//计算节点的基数,如用两位表示就是100，三位表示就是1000等int baseNumber = (int)Math.pow(10, Category.LEVEL_LENGTH);//构建cstr要加上的数字int numberToAdd = (int)Math.pow(baseNumber, Category.MAX_GRADE - c.getGrade());if(cnoMax == 0) { //要加入的节点是该父亲下面的第一个子节点 if(c.getPid() == 0) { //最顶层节点cno = numberToAdd; } else { //其他层节点int parentCno = getParentCno(conn, c); //拿到父亲代表串cno = parentCno + numberToAdd;}} else { //加入的节点不是该父亲下面的第一个cno = cnoMax + numberToAdd;}} catch (SQLException e) {e.printStackTrace();} finally {DB.close(rsMax);DB.close(stmtMax);}return cno;}/*获取父亲类别的Cno * */private int getParentCno(Connection conn, Category child) {String sql = "select cno from category where id = " + child.getPid();Statement stmt = DB.getStmt(conn);ResultSet rs = DB.getResultSet(stmt, sql);int cno = -1;try {rs.next();cno = rs.getInt(1);} catch (SQLException e) {e.printStackTrace();} finally {DB.close(rs);DB.close(stmt);}return cno; }public void update(Category c) {c.update();} public Category getCategoryById(int id){ Category c = new Category(); Connection conn = DB.getConn();String sql = "select * from category where id=" + id;Statement stmt = DB.getStmt(conn);ResultSet rs = DB.getResultSet(stmt, sql);try {while (rs.next()) {c = this.getCategoryFromRs(rs);}} catch (SQLException e) {e.printStackTrace();} finally {DB.close(rs);DB.close(stmt);DB.close(conn);} return c; } /** * * @param id * @return */ public Category getCategoryByCno(int cno){ Category c = new Category(); Connection conn = DB.getConn();String sql = "select * from category where cno=" + cno;Statement stmt = DB.getStmt(conn);ResultSet rs = DB.getResultSet(stmt, sql);try {while (rs.next()) {c = this.getCategoryFromRs(rs);}} catch (SQLException e) {e.printStackTrace();} finally {DB.close(rs);DB.close(stmt);DB.close(conn);} return c; } public List<Category> read(){Connection conn = null;List<Category> sortsTree = new ArrayList<Category>();try{ conn = DB.getConn(); read(sortsTree,conn,0,0);}catch(Exception e){e.printStackTrace(); }finally{ DB.close(conn); } return sortsTree; } /* * 递归分类 * @param conn * @param id * @param level * @throws SQLException */ private void read(List<Category> sortsTree,Connection conn,int id, int level) throws SQLException { String sql ="select * from category where pid = ?"; PreparedStatement pstmt = null; ResultSet rs = null; try{ pstmt = conn.prepareStatement(sql); pstmt.setInt(1, id); rs = pstmt.executeQuery(); while(rs. next()){ Category c = this.getCategoryFromRs(rs);sortsTree.add(c); if(rs.getInt("grade")!=3){ read(sortsTree,conn, rs.getInt("id"),level); } } }finally{ DB.close(rs); DB.close(pstmt); } } public List<Category> getCategories() {List<Category> categories = new ArrayList<Category>();Connection conn = DB.getConn();String sql = "select * from category order by cno";Statement stmt = DB.getStmt(conn);ResultSet rs = DB.getResultSet(stmt, sql);try {while (rs.next()) {Category c = this.getCategoryFromRs(rs);categories.add(c);}} catch (SQLException e) {e.printStackTrace();} finally {DB.close(rs);DB.close(stmt);DB.close(conn);}return categories;}private Category getCategoryFromRs(ResultSet rs) {Category c = new Category();try {c.setId(rs.getInt("id"));c.setPid(rs.getInt("pid"));c.setName(rs.getString("name"));c.setDescr(rs.getString("descr"));c.setCno(rs.getInt("cno"));c.setGrade(rs.getInt("grade"));} catch (SQLException e) {e.printStackTrace();}return c;} /*通过ID删除类别极其子类别 * */ public void delete(int id){Connection conn = null; try{ conn = DB.getConn(); delete(conn,id);}catch(Exception e){e.printStackTrace(); }finally{ DB.close(conn); }}/* * * @throws Exception */ private void delete(Connection conn,int id) throws Exception { Statement stmt = null; ResultSet rs = null; try{ stmt = DB.getStmt(conn); String sql = "select * from category where pid = " + id; rs = stmt.executeQuery(sql); while(rs.next()){ delete(conn,rs.getInt("id")); } stmt.executeUpdate("delete from category where id = " + id); } finally{DB.close(rs);DB.close(stmt); }} /* * 返回商品分类字符串 * @return */ public String getCategoryTree(){ CategoryTreeReader reader = new CategoryTreeReader(); return reader.read(); }}