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(); } }