1 <%@ page language="java" contentType="text/html; charset=UTF-8" 2 pageEncoding="UTF-8"%> 3 4 5 6 7数据表格 8 <% 9 String pid = request.getParameter("pid"); 10 if(pid == null || pid.trim().length() == 0 ) 11 { 12 pid = "0"; 13 } 14 15 %> 16 17 18 19 20 21 22 177 178 179 192
195 216
217 218 查询Servlet
1 package com.hanqi; 2 3 import java.io.IOException; 4 import java.util.ArrayList; 5 6 import javax.servlet.ServletException; 7 import javax.servlet.annotation.WebServlet; 8 import javax.servlet.http.HttpServlet; 9 import javax.servlet.http.HttpServletRequest;10 import javax.servlet.http.HttpServletResponse;11 12 import com.alibaba.fastjson.JSON;13 14 /**15 * Servlet implementation class jsonMembers16 */17 @WebServlet("/jsonMembers")18 public class jsonMembers extends HttpServlet {19 private static final long serialVersionUID = 1L;20 21 /**22 * @see HttpServlet#HttpServlet()23 */24 public jsonMembers() {25 super();26 // TODO Auto-generated constructor stub27 }28 29 /**30 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)31 */32 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {33 // TODO Auto-generated method stub34 //接收地区父id35 String pid = request.getParameter("pid");36 37 //如果没有收到,赋初值-138 if(pid == null || pid.trim().length() == 0)39 {40 pid = "-1";41 }42 43 try {44 45 //实例化集合,接收查询结果46 ArrayList arr = new ArrayList ();47 48 //实例化数据库操作类49 AreaDao ad = new AreaDao();50 51 //调用查询方法52 arr = ad.selArea(Integer.parseInt(pid));53 54 //初始化向前台输出的json字符串55 String str = "";56 57 //如果查询到数据58 if(arr != null)59 {60 //转换成json61 str = JSON.toJSON(arr).toString();62 }63 64 //str = JSON.toJSONString(arr);65 66 //输出json67 response.getWriter().print(str);68 69 }catch (Exception e) {70 71 response.getWriter().append(e.getMessage());72 }73 74 75 //response.getWriter().append("Served at: ").append(request.getContextPath());76 }77 78 /**79 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)80 */81 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {82 // TODO Auto-generated method stub83 doGet(request, response);84 }85 86 } 增加/修改的Servlet
1 package com.hanqi; 2 3 import java.io.IOException; 4 import javax.servlet.ServletException; 5 import javax.servlet.annotation.WebServlet; 6 import javax.servlet.http.HttpServlet; 7 import javax.servlet.http.HttpServletRequest; 8 import javax.servlet.http.HttpServletResponse; 9 10 /** 11 * Servlet implementation class AddMem 12 */ 13 @WebServlet("/AddMem") 14 public class AddMem extends HttpServlet { 15 private static final long serialVersionUID = 1L; 16 17 /** 18 * @see HttpServlet#HttpServlet() 19 */ 20 public AddMem() { 21 super(); 22 // TODO Auto-generated constructor stub 23 } 24 25 /** 26 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) 27 */ 28 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 29 30 //接收表单数据 31 String name = request.getParameter("name"); 32 String postcode = request.getParameter("postcode"); 33 String parentid = request.getParameter("parentid"); 34 //id用来判断是添加还是修改 35 String id = request.getParameter("id"); 36 37 //判断数据是否合法 38 if(name == null || name.trim().length() == 0) 39 { 40 response.getWriter().append("{\"success\":false, \"message\":\"名称不能为空\"}"); 41 } 42 else if(parentid == null || parentid.trim().length() == 0) 43 { 44 response.getWriter().append("{\"success\":false, \"message\":\"父id不能为空\"}"); 45 } 46 else 47 { 48 //转换数据类型 49 int pid = Integer.parseInt(parentid); 50 51 //实例化实体类 52 Member mem = new Member(); 53 54 //向实体类对象添加参数 55 mem.setName(name); 56 mem.setPostcode(postcode); 57 mem.setParentid(pid); 58 59 //实例化数据库操作类 60 AreaDao ad = new AreaDao(); 61 62 try { 63 //影响行数 64 int row = -1; 65 66 //如果收到id,说明是修改请求 67 if(id != null && id.trim().length() > 0) 68 { 69 int iid = Integer.parseInt(id); 70 //向实体类对象添加id参数 71 mem.setId(iid); 72 //调用修改方法 73 row = ad.updateArea(mem); 74 //输出成功信息 75 response.getWriter().append("{\"success\":true, \"message\":\"成功修改"+row +"条数据\"}"); 76 } 77 else 78 { 79 //没有收到id,则添加数据 80 row = ad.addArea(mem); 81 //输出成功信息 82 response.getWriter().append("{\"success\":true, \"message\":\"成功添加"+row +"条数据\"}"); 83 } 84 85 } catch (Exception e) { 86 //异常处理,输出错误信息 87 response.getWriter().append("{\"success\":false, \"message\":\"错误信息:"+ e.getMessage()+"\"}"); 88 } 89 90 } 91 92 //response.setHeader("refresh", "1;URL=memList.jsp?pid=" + parentid); 93 //response.getWriter().append("Served at: ").append(request.getContextPath()); 94 } 95 96 /** 97 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) 98 */ 99 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {100 // TODO Auto-generated method stub101 doGet(request, response);102 }103 104 } 删除的Servlet
1 package com.hanqi; 2 3 import java.io.IOException; 4 import javax.servlet.ServletException; 5 import javax.servlet.annotation.WebServlet; 6 import javax.servlet.http.HttpServlet; 7 import javax.servlet.http.HttpServletRequest; 8 import javax.servlet.http.HttpServletResponse; 9 10 /** 11 * Servlet implementation class AddMem 12 */ 13 @WebServlet("/AddMem") 14 public class AddMem extends HttpServlet { 15 private static final long serialVersionUID = 1L; 16 17 /** 18 * @see HttpServlet#HttpServlet() 19 */ 20 public AddMem() { 21 super(); 22 // TODO Auto-generated constructor stub 23 } 24 25 /** 26 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) 27 */ 28 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 29 30 //接收表单数据 31 String name = request.getParameter("name"); 32 String postcode = request.getParameter("postcode"); 33 String parentid = request.getParameter("parentid"); 34 //id用来判断是添加还是修改 35 String id = request.getParameter("id"); 36 37 //判断数据是否合法 38 if(name == null || name.trim().length() == 0) 39 { 40 response.getWriter().append("{\"success\":false, \"message\":\"名称不能为空\"}"); 41 } 42 else if(parentid == null || parentid.trim().length() == 0) 43 { 44 response.getWriter().append("{\"success\":false, \"message\":\"父id不能为空\"}"); 45 } 46 else 47 { 48 //转换数据类型 49 int pid = Integer.parseInt(parentid); 50 51 //实例化实体类 52 Member mem = new Member(); 53 54 //向实体类对象添加参数 55 mem.setName(name); 56 mem.setPostcode(postcode); 57 mem.setParentid(pid); 58 59 //实例化数据库操作类 60 AreaDao ad = new AreaDao(); 61 62 try { 63 //影响行数 64 int row = -1; 65 66 //如果收到id,说明是修改请求 67 if(id != null && id.trim().length() > 0) 68 { 69 int iid = Integer.parseInt(id); 70 //向实体类对象添加id参数 71 mem.setId(iid); 72 //调用修改方法 73 row = ad.updateArea(mem); 74 //输出成功信息 75 response.getWriter().append("{\"success\":true, \"message\":\"成功修改"+row +"条数据\"}"); 76 } 77 else 78 { 79 //没有收到id,则添加数据 80 row = ad.addArea(mem); 81 //输出成功信息 82 response.getWriter().append("{\"success\":true, \"message\":\"成功添加"+row +"条数据\"}"); 83 } 84 85 } catch (Exception e) { 86 //异常处理,输出错误信息 87 response.getWriter().append("{\"success\":false, \"message\":\"错误信息:"+ e.getMessage()+"\"}"); 88 } 89 90 } 91 92 //response.setHeader("refresh", "1;URL=memList.jsp?pid=" + parentid); 93 //response.getWriter().append("Served at: ").append(request.getContextPath()); 94 } 95 96 /** 97 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) 98 */ 99 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {100 // TODO Auto-generated method stub101 doGet(request, response);102 }103 104 } 数据库操作类
1 package com.hanqi; 2 3 import java.sql.*; 4 import java.util.*; 5 6 //数据操作类 7 public class AreaDao { 8 9 //增 10 public int addArea(Member mem) throws Exception 11 { 12 int row = -1; 13 Connection conn = DBHelper.getConnection(); 14 PreparedStatement ps = null; 15 16 if(conn != null) 17 { 18 try 19 { 20 String sql = "insert into MEMBERS(id, parentid, name, postcode) values(sq_members_id.nextval,?,?,?)"; 21 22 ps = conn.prepareStatement(sql); 23 24 ps.setInt(1, mem.getParentid()); 25 ps.setString(2, mem.getName()); 26 ps.setString(3, mem.getPostcode()); 27 28 row = ps.executeUpdate(); 29 } 30 catch(Exception e) 31 { 32 throw e; 33 } 34 finally 35 { 36 ps.close(); 37 } 38 39 } 40 conn.close(); 41 return row; 42 } 43 44 //修改 45 public int updateArea(Member mem) throws Exception 46 { 47 int row = -1; 48 Connection conn = DBHelper.getConnection(); 49 PreparedStatement ps = null; 50 51 if(conn != null) 52 { 53 try 54 { 55 String sql = "update MEMBERS set parentid=?, name=?, postcode=? where id = ?"; 56 57 ps = conn.prepareStatement(sql); 58 59 ps.setInt(1, mem.getParentid()); 60 ps.setString(2, mem.getName()); 61 ps.setString(3, mem.getPostcode()); 62 ps.setInt(4, mem.getId()); 63 64 row = ps.executeUpdate(); 65 } 66 catch(Exception e) 67 { 68 throw e; 69 } 70 finally 71 { 72 ps.close(); 73 } 74 75 } 76 conn.close(); 77 return row; 78 } 79 80 //删除 81 public int delArea(int id) throws Exception 82 { 83 int row = -1; 84 Connection conn = DBHelper.getConnection(); 85 PreparedStatement ps = null; 86 87 if(conn != null) 88 { 89 try 90 { 91 String sql = "delete from MEMBERS where id = ? or parentid = ?"; 92 93 ps = conn.prepareStatement(sql); 94 95 ps.setInt(1, id); 96 ps.setInt(2, id); 97 98 row = ps.executeUpdate(); 99 }100 catch(Exception e)101 {102 throw e;103 }104 finally105 {106 ps.close();107 }108 109 }110 conn.close();111 return row;112 }113 114 //递归方法115 private int dg(Connection conn, int id, int row) throws Exception116 {117 118 //遍历子节点119 PreparedStatement ps = null;120 121 PreparedStatement ps1 = null;122 123 ResultSet rs =null;124 125 try{126 127 String sql = "delete from MEMBERS where id = ?";128 129 ps1 = conn.prepareStatement(sql);130 131 ps1.setInt(1, id);132 133 row = row + ps1.executeUpdate();134 135 sql = "select * from MEMBERS where parentid = ?";136 137 ps = conn.prepareStatement(sql);138 139 ps.setInt(1, id);140 141 rs = ps.executeQuery();142 143 if(rs != null)144 {145 146 while(rs.next())147 {148 row = dg(conn, rs.getInt("id"),row);//递归调用149 }150 }151 152 }153 154 catch(Exception e)155 {156 e.printStackTrace();157 158 }159 finally160 {161 try162 {163 rs.close();164 ps.close();165 ps1.close();166 167 }168 catch(Exception e)169 {170 throw e;171 }172 }173 return row; 174 }175 176 //递归删除177 public int delDG(int id) throws Exception178 {179 int row = 0;180 181 Connection conn = DBHelper.getConnection();182 183 if(conn != null)184 {185 try186 {187 //设置手动提交188 conn.setAutoCommit(false);189 190 //递归级联删除191 192 row = dg(conn,id,row);193 194 conn.commit();195 }196 catch(Exception e)197 {198 //事务回滚199 conn.rollback();200 throw e;201 }202 203 }204 205 conn.close();206 return row;207 }208 209 //查询210 public ArrayList selArea(int pid) throws Exception211 {212 ArrayList arr = null;213 214 Member mem = null;215 216 Connection conn = DBHelper.getConnection();217 218 PreparedStatement ps = null;219 220 ResultSet rs = null;221 222 if(conn != null )223 {224 try225 {226 227 228 String sql = "select * from MEMBERS where parentid = ?";229 230 ps = conn.prepareStatement(sql);231 232 ps.setInt(1, pid);233 234 rs = ps.executeQuery();235 236 if(rs != null)237 {238 239 arr = new ArrayList ();240 241 while(rs.next())242 {243 244 mem = new Member();245 246 mem.setId((rs.getInt("id")));247 mem.setParentid(rs.getInt("parentid"));248 mem.setName(rs.getString("name"));249 mem.setPostcode(rs.getString("postcode"));250 251 arr.add(mem);252 } 253 }254 255 }256 catch(Exception e)257 {258 throw e;259 }260 finally261 {262 try263 {264 ps.close();265 rs.close();266 conn.close();267 }268 catch(Exception e)269 {270 conn.close();271 }272 273 }274 }275 276 return arr;277 }278 279 //单条查询280 public Member getList(int id) throws Exception281 {282 Member m = null;283 284 Connection conn = DBHelper.getConnection();285 286 PreparedStatement ps = null;287 288 ResultSet rs = null;289 290 291 if(conn != null )292 {293 try294 {295 296 297 String sql = "select * from members where id = ?";298 299 ps = conn.prepareStatement(sql);300 301 ps.setInt(1, id);302 303 rs = ps.executeQuery();304 305 if(rs != null && rs.next())306 {307 m = new Member();308 309 m.setId(rs.getInt("id"));310 m.setParentid(rs.getInt("parentid"));311 m.setName(rs.getString("name"));312 m.setPostcode(rs.getString("postcode"));313 314 }315 316 }317 catch(Exception e)318 {319 throw e;320 }321 finally322 {323 try324 {325 ps.close();326 rs.close();327 conn.close();328 }329 catch(Exception e)330 {331 conn.close();332 }333 334 }335 336 }337 338 return m;339 }340 341 //分页查询342 public ArrayList selArea(int pid,int page,int rows) throws Exception343 {344 ArrayList arr = null;345 346 Member mem = null;347 348 Connection conn = DBHelper.getConnection();349 350 PreparedStatement ps = null;351 352 ResultSet rs = null;353 354 if(conn != null )355 {356 try357 {358 int max = page * rows;359 int min = (page - 1) * rows;360 361 String sql = "select * from (select t.*,rownum rn from (select * from MEMBERS where parentid = ? order by id desc) t where rownum <= ?) where rn > ?";362 363 ps = conn.prepareStatement(sql);364 365 ps.setInt(1, pid);366 ps.setInt(2, max);367 ps.setInt(3, min);368 369 rs = ps.executeQuery();370 371 if(rs != null)372 {373 374 arr = new ArrayList ();375 376 while(rs.next())377 {378 379 mem = new Member();380 381 mem.setId((rs.getInt("id")));382 mem.setParentid(rs.getInt("parentid"));383 mem.setName(rs.getString("name"));384 mem.setPostcode(rs.getString("postcode"));385 386 arr.add(mem);387 } 388 }389 390 }391 catch(Exception e)392 {393 throw e;394 }395 finally396 {397 try398 {399 ps.close();400 rs.close();401 conn.close();402 }403 catch(Exception e)404 {405 conn.close();406 }407 408 }409 }410 411 return arr;412 }413 414 //记录条数查询415 public int getcount(int pid) throws Exception416 {417 int row = -1;418 419 Connection conn = DBHelper.getConnection();420 421 PreparedStatement ps = null;422 423 ResultSet rs = null;424 425 if(conn != null )426 {427 try428 {429 430 String sql = "select count(1) as rn from MEMBERS where parentid = ?";431 432 ps = conn.prepareStatement(sql);433 ps.setInt(1, pid);434 rs = ps.executeQuery();435 436 if(rs != null && rs.next())437 {438 row = rs.getInt("rn");439 }440 441 }442 catch(Exception e)443 {444 throw e;445 }446 finally447 {448 try449 {450 ps.close();451 rs.close();452 conn.close();453 }454 catch(Exception e)455 {456 conn.close();457 }458 459 }460 }461 462 return row;463 }464 }465 实体类
1 package com.hanqi; 2 3 //实体类 4 public class Member { 5 6 //成员变量设为私有 7 private int id; 8 private int parentid; 9 private String name;10 private String postcode;11 12 //对外开放getter和setter方法13 public int getId() {14 return id;15 }16 17 public void setId(int id) {18 this.id = id;19 }20 21 public int getParentid() {22 return parentid;23 }24 25 public void setParentid(int parentid) {26 this.parentid = parentid;27 }28 29 public String getName() {30 return name;31 }32 33 public void setName(String name) {34 this.name = name;35 }36 37 public String getPostcode() {38 return postcode;39 }40 41 public void setPostcode(String postcode) {42 this.postcode = postcode;43 }44 45 46 47 }