servlet+mysql+jsp经典开发案例

package com.wgh.servlet; import java.io.IOException; import java.io.PrintWriter; import java.sql.ResultSet; import java.sql.SQLException; import java.util.*; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import com.wgh.model.LinkmanForm; import com.wgh.tools.ConnDB; public class LinkmanServlet extends HttpServlet { /** * The doGet method of the servlet. <br> * * This method is called when a form has its tag value method equals to get. * * @param request * the request send by the client to the server * @param response * the response send by the server to the client * @throws ServletException * if an error occurred * @throws IOException * if an error occurred */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { HttpSession session = request.getSession(); if (session.getAttribute("user") == null || session.getAttribute("id") == null) { PrintWriter out = response.getWriter(); out .println("<script>alert('您的帐号已经过期，请重新登录！');window.location.href='login.jsp'"); } else { String action = request.getParameter("action"); // 获取action参数的值 if ("query".equals(action)) { this.query(request, response); // 查询联系人数量 } else if ("queryLinkman".equals(action)) { this.queryLinkman(request, response); // 查询联系人列表 } else if ("add".equals(action)) { this.add(request, response); // 保存联系人信息 } else if ("del".equals(action)) { this.del(request, response); } else if ("modify".equals(action)) { //查询要修改的联系人信息 this.modify(request, response); } else if ("saveModify".equals(action)) { //保存修改后的联系人信息 this.saveModify(request, response); } } } /** * 统计联系人个数 * * @param request * @param response * @throws ServletException * @throws IOException */ public void query(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { ConnDB conn = new ConnDB(); //创建数据库连接对象 int id = Integer.parseInt(request.getSession().getAttribute("id") .toString()); // 获取用户ID String sql = "SELECT COUNT(*) FROM tb_linkman WHERE byUser=" + id + ""; ResultSet rs = conn.executeQuery(sql); // 统计当前用户的联系人个数 int count = 0; try { if (rs.next()) { count = rs.getInt(1); //获取联系人个数 } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { conn.close(); //关闭数据库连接 } request.setAttribute("linkmanCount", count); // 保存联系人个数 request.getRequestDispatcher("main.jsp").forward(request, response); //重定向页面 } /** * 查询联系人信息 * * @param request * @param response * @throws ServletException * @throws IOException */ public void queryLinkman(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { ConnDB conn = new ConnDB(); //创建数据库连接对象 int id = Integer.parseInt(request.getParameter("id")); // 获取通讯组ID String sql_g = "SELECT * FROM tb_group WHERE id=" + id + ""; ResultSet rs_g = conn.executeQuery(sql_g); try { if (rs_g.next()) { request.setAttribute("groupName", rs_g.getString("name")); // 保存通讯组名称 } } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } int uid = Integer.parseInt(request.getSession().getAttribute("id") .toString()); // 获取用户ID String sql = "SELECT * FROM tb_linkman WHERE byUser=" + uid + " AND byGroup=" + id + ""; ResultSet rs = conn.executeQuery(sql); // 查询联系人列表 List list = null; try { if (rs.next()) { list = new ArrayList(); do { LinkmanForm f = new LinkmanForm(); f.setId(rs.getInt(1)); f.setName(rs.getString(2)); f.setTel(rs.getString(3)); f.setMobileTel(rs.getString(4)); f.setEmail(rs.getString(5)); f.setAddress(rs.getString(6)); f.setPostcode(rs.getString(7)); list.add(f); // 将联系人信息添加到List集合中 } while (rs.next()); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { conn.close(); } request.setAttribute("linkmanList", list); // 保存联系人列表 request.getRequestDispatcher("linkmanList.jsp").forward(request, response); } /** * 保存添加的联系人信息 * * @param request * @param response * @throws ServletException * @throws IOException */ public void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String name = request.getParameter("name"); // 获取姓名 String tel = request.getParameter("tel"); // 获取电话号码 String mobileTel = request.getParameter("mobileTel"); // 获取手机号码 String email = request.getParameter("email"); // 获取E-mail地址 String address = request.getParameter("address"); // 获取地址 String postcode = request.getParameter("postcode"); // 获取邮政编码 String byUser = request.getSession().getAttribute("id").toString(); // 获取所属用户 String byGroup = request.getParameter("byGroup"); // 获取通讯组 String message = ""; // 保存提示信息的变量 ConnDB conn = new ConnDB(); //创建数据库连接对象 String sql_ins = "INSERT INTO tb_linkman (name,tel,mobileTel,email,address,postcode,byUser,byGroup) VALUES('" + name + "','" + tel + "','" + mobileTel + "','" + email + "','" + address + "','" + postcode + "','" + byUser + "','" + byGroup + "')"; int rtn = conn.executeUpdate(sql_ins); // 将联系人信息保存到数据表中 System.out.println(sql_ins); if (rtn > 0) { message = "联系人添加成功！"; } else { message = "联系人添加失败"; } conn.close(); request.setAttribute("message", message); // 将提示信息保存到HttpServletRequest中 request.getRequestDispatcher("deal.jsp").forward(request, response); } /** * 删除联系人 * * @param request * @param response * @throws ServletException * @throws IOException */ public void del(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String linkID = request.getParameter("linkID"); // 获取联系人ID String message = ""; // 保存提示信息的变量 ConnDB conn = new ConnDB(); //创建数据库连接对象 String sql = "DELETE FROM tb_linkman WHERE id=" + linkID + ""; int rtn = conn.executeUpdate(sql); // 删除指定的联系人信息 if (rtn > 0) { message = "联系人删除成功！"; } else { message = "联系人删除失败"; } conn.close(); request.setAttribute("message", message); // 将提示信息保存到HttpServletRequest中 request.getRequestDispatcher("deal.jsp").forward(request, response); } /** * 查询要修改的联系人信息 * * @param request * @param response * @throws ServletException * @throws IOException */ public void modify(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { ConnDB conn = new ConnDB(); //创建数据库连接对象 String linkID = request.getParameter("linkID"); // 获取联系人ID String sql = "SELECT * FROM tb_linkman WHERE id=" + linkID + ""; ResultSet rs = conn.executeQuery(sql); // 查询联系人信息 LinkmanForm f = new LinkmanForm(); //实例化LikmanForm的对象 try { if (rs.next()) { //如果找到要修改的联系人信息 f.setId(rs.getInt(1)); //设置ID属性 f.setName(rs.getString(2)); //设置姓名属性 f.setTel(rs.getString(3)); //设置电话属性 f.setMobileTel(rs.getString(4)); //设置手机属性