添加根类别
//添加顶级节点
if(action != null && action.trim().equals("add")) {
String name = request.getParameter("name");
String descr = request.getParameter("descr");
Category c = new Category();
c.setPid(0);
c.setName(name);
c.setDescr(descr);
c.setGrade(1);
CategoryService service = CategoryService.getInstance();
//调用方法
service.add(c);
out.println("类别添加成功!恭喜!");
return;
}
//添加子类别
int pid = Integer.parseInt(request.getParameter("pid"));
int grade = Integer.parseInt(request.getParameter("grade"));
if(action != null && action.trim().equals("add")) {
String name = request.getParameter("name");
String descr = request.getParameter("descr");
Category c = new Category();
c.setPid(pid);
c.setName(name);
c.setDescr(descr);
c.setGrade(grade + 1);
CategoryService service = CategoryService.getInstance();
service.add(c);
out.println("类别添加成功!恭喜!");
return;
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 {
//获取要添加的cno号,
//调用方法
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);
}
}
private int getNextCno(Connection conn, Category c) {
int cno = -1;
//拿到上层节点最大的cno
String sqlMax = "select max(cno) from category where pid = " + c.getPid();
Statement stmtMax = DB.getStatement(conn);
ResultSet rsMax = DB.getResultSet(stmtMax, sqlMax);
try {
rsMax.next();
//因为上面select max(cn0) 返回只有一列所以getInt(1)
int cnoMax = rsMax.getInt(1);
System.out.println(cnoMax);
//计算节点的基数,如用两位表示就是100,三位表示就是1000等
//Math.pow返回第一个参数的第二个参数次幂的值a - 底数,b - 指数
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;
}