JavaWeb JDBC + MySql 通讯录实现简单的增删改查功能案例详解

package pers.contact.dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class BaseDao {    private static final String DRIVER = "com.mysql.jdbc.Driver";    public static final String URL = "jdbc:mysql://localhost:3306/demo?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=GMT%2B8";    public static final String USER = "root";    public static final String PASSWORD = "sasa";    Connection conn = null;    PreparedStatement pstmt = null;    ResultSet rs = null;    public void getConnection() {        try {            // 加载数据库驱动            Class.forName(DRIVER);            // 获得数据库连接            conn = DriverManager.getConnection(URL, USER, PASSWORD);        }        catch (ClassNotFoundException e) {            e.printStackTrace();        }        catch (SQLException e) {            e.printStackTrace();        }    }    public int executeUpdate(String sql, Object... obj) {        int num = 0;        getConnection();        try {            PreparedStatement pstmt = conn.prepareStatement(sql);            for (int i = 0; i < obj.length; i++) {                pstmt.setObject(i + 1, obj[i]);            }            num = pstmt.executeUpdate();        } catch (SQLException e) {            e.printStackTrace();        } finally {            closeAll();        }        return num;    }    public ResultSet executeQuery(String sql, Object... obj) {        getConnection();        try {            PreparedStatement pstmt = conn.prepareStatement(sql);            for (int i = 0; i < obj.length; i++) {                pstmt.setObject(i + 1, obj[i]);            }            rs = pstmt.executeQuery();        } catch (SQLException e) {            e.printStackTrace();        }        return rs;    }    public void closeAll() {        try {            rs.close();        } catch (SQLException e) {            e.printStackTrace();        }        try {            pstmt.close();        } catch (SQLException e) {            e.printStackTrace();        }        try {            conn.close();        } catch (SQLException e) {            e.printStackTrace();        }    }}

package pers.contact.entity;import java.util.Date;public class Contact {    public Contact(int id, String name, int age, String phone, Date date, String favorite) {        super();        this.id = id;        this.name = name;        this.age = age;        this.phone = phone;        this.date = date;        this.favorite = favorite;    }    private int id;    private String name;    private int age;    private String phone;    private Date date;    private String favorite;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public int getAge() {        return age;    }    public void setAge(int age) {        this.age = age;    }    public String getPhone() {        return phone;    }    public void setPhone(String phone) {        this.phone = phone;    }    public Date getDate() {        return date;    }    public void setDate(Date date) {        this.date = date;    }    public String getFavorite() {        return favorite;    }    public void setFavorite(String favorite) {        this.favorite = favorite;    }}

package pers.contact.service;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import pers.contact.entity.Contact;import pers.contact.dao.BaseDao;;public class ContactService extends BaseDao {    ResultSet rs = null;    public List<Contact> GetAllContact(){        List<Contact> list = new ArrayList();        String sql = "select * from contact";        rs = executeQuery(sql);        try {            while (rs.next()) {                Contact f = new Contact(rs.getInt(1), rs.getString(2),                        rs.getInt(3), rs.getString(4), rs.getDate(5),                        rs.getString(6));                list.add(f);            }        } catch (SQLException e) {            e.printStackTrace();        }        return list;    }    public int AddContact(Contact contact)    {        int num = 0;        String sql = "insert into contact(name,age,phone,date,favorite) values(?,?,?,?,?)";        try {            num = executeUpdate(sql, contact.getName(), contact.getAge(), contact.getPhone(),                    contact.getDate(), contact.getFavorite());        } catch (Exception e) {            e.printStackTrace();        }        return num;    }    public int DeleteContact(int id)    {        int num = 0;        String sql = "delete from contact where id = ?";        try {            num = executeUpdate(sql, id);        }        catch(Exception ex) {            ex.printStackTrace();        }        return num;    }    public Contact GetContact(int id) {        String sql = "select * from contact where id = ?";        Contact contact = null;        rs = executeQuery(sql, id);        try {            while(rs.next()) {                contact = new Contact(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getDate(5),rs.getString(6));            }        }        catch(SQLException ex){            ex.printStackTrace();        }        return contact;    }    public int UpdateContact(Contact contact) {        int num = 0;        String sql = "update contact set name = ?,age = ?,phone = ?,date = ?,favorite = ? where id = ?";        try {            num = executeUpdate(sql, contact.getName(),contact.getAge(),contact.getPhone(),contact.getDate(),contact.getFavorite(),contact.getId());        }        catch(Exception ex) {            ex.printStackTrace();        }        return num;    }}

package pers.contact.servlet;import java.io.IOException;import java.io.PrintWriter;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.servlet.http.HttpSession;import pers.contact.entity.Contact;import pers.contact.service.ContactService;/** * Servlet implementation class ContactServlet */@WebServlet("/ContactServlet")public class ContactServlet extends HttpServlet {    private static final long serialVersionUID = 1L;  /**   * @see HttpServlet#HttpServlet()   */  public ContactServlet() {    super();    // TODO Auto-generated constructor stub  }    /**     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)     */    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {        doPost(request,response);    }    /**     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)     */    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {        response.setContentType("text/html;charset=utf-8");        request.setCharacterEncoding("utf-8");        HttpSession session = request.getSession();        PrintWriter out = response.getWriter();        ContactService ud = new ContactService();        // 获得do属性        String dos = request.getParameter("do");        if (dos == null || dos.equals("")) {            dos = "index";        }        // 主页        if (dos.equals("index")) {            List<Contact> ulist = ud.GetAllContact();            request.setAttribute("ulist", ulist);            request.getRequestDispatcher("/index.jsp").forward(request, response);            return;        }        if(dos.equals("add")) {            String name = request.getParameter("name");            int age = Integer.parseInt(request.getParameter("age"));            String phone = request.getParameter("phone");            String dates = request.getParameter("date");            SimpleDateFormat sdf = new SimpleDateFormat("yy-MM-dd");            Date date = null;            try {                date = (Date)sdf.parse(dates);            } catch (ParseException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }            //爱好获取            String favorite = request.getParameter("favorite");            Contact contact = new Contact(0,name,age,phone,date,favorite);            ud.AddContact(contact);            out.print("<script>alert('新增成功！');window.location='ContactServlet?do=index';</script>");        }        if(dos.equals("del")) {            String ids = request.getParameter("id");            int id = Integer.parseInt(ids);            ud.DeleteContact(id);            out.print("<script>alert('删除成功！');window.location='ContactServlet?do=index';</script>");        }        if(dos.equals("editbefore")) {            int id = Integer.parseInt(request.getParameter("id"));            Contact f = ud.GetContact(id);            session.setAttribute("edituser", f);            response.sendRedirect("edit.jsp");            return;        }        if(dos.equals("edit")) {            try {                int id = Integer.parseInt(request.getParameter("id"));                String name = request.getParameter("name");                int age = Integer.parseInt(request.getParameter("age"));                String phone = request.getParameter("phone");                String dates = request.getParameter("date");                SimpleDateFormat sdf = new SimpleDateFormat("yy-MM-dd");                Date date = null;                date = (Date)sdf.parse(dates);                String favorite = request.getParameter("favorite");                Contact contact = new Contact(id,name,age,phone,date,favorite);                ud.UpdateContact(contact);                out.print("<script>alert('修改成功！');window.location='ContactServlet?do=index';</script>");            }            catch(ParseException ex) {                ex.printStackTrace();            }        }    }}

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@ page import="pers.contact.entity.Contact"%><%@ page import="pers.contact.service.ContactService"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%><%    String path = request.getContextPath();    String basePath = request.getScheme() + "://"            + request.getServerName() + ":" + request.getServerPort()            + path + "/";    //下面的语句初始为初始化页面，如果不加下面语句访问主页不会显示数据库中保存的数据    ContactService ud = new ContactService();    List<Contact> ulist = ud.GetAllContact();    request.setAttribute("ulist", ulist);%><!DOCTYPE html><html><head><base href="<%=basePath%>" rel="external nofollow" rel="external nofollow" rel="external nofollow" ><meta charset="ISO-8859-1"><link rel="stylesheet"    href="https://cdn.bootcss.com/foundation/5.5.3/css/foundation.min.css" rel="external nofollow" rel="external nofollow" rel="external nofollow" ><script src="https://cdn.bootcss.com/jquery/2.1.1/jquery.min.js"></script><script    src="https://cdn.bootcss.com/foundation/5.5.3/js/foundation.min.js"></script><script    src="https://cdn.bootcss.com/foundation/5.5.3/js/vendor/modernizr.js"></script><style type="text/css">table {    margin: auto;}td {    text-align: center;}h1 {    margin-left: 40%;}a#add {    margin-left: 45%;}</style><title>Insert title here</title></head><body style="padding:20px;">    <h1>通讯录主页</h1>    <a id="add" href="add.jsp" rel="external nofollow" >新增小伙伴</a>    <table>        <thead>            <tr>                <th>序号</th>                <th>姓名</th>                <th>年龄</th>                <th>电话</th>                <th>生日</th>                <th>爱好</th>                <th>操作</th>            </tr>            <c:forEach var="U" items="${ulist}">            <tr>                <th>${U.id}</th>                <th>${U.name}</th>                <th>${U.age}</th>                <th>${U.phone}</th>                <th>${U.date}</th>                <th>${U.favorite}</th>                <th><a href="ContactServlet?do=editbefore&id=${U.id}" rel="external nofollow" >修改</a> <a href="ContactServlet?do=del&id=${U.id}" rel="external nofollow" >删除</a> </th>            </tr>            </c:forEach>        </thead>        <%--<c:forEach/>标签遍历List--%>    </table></body></html>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%    String path = request.getContextPath();    String basePath = request.getScheme() + "://"            + request.getServerName() + ":" + request.getServerPort()            + path + "/";%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><base href="<%=basePath%>" rel="external nofollow" rel="external nofollow" rel="external nofollow" ><title>My JSP 'add.jsp' starting page</title><meta name="viewport" content="width=device-width, initial-scale=1"><link rel="stylesheet"    href="https://cdn.bootcss.com/foundation/5.5.3/css/foundation.min.css" rel="external nofollow" rel="external nofollow" rel="external nofollow" ><script src="https://cdn.bootcss.com/jquery/2.1.1/jquery.min.js"></script><script    src="https://cdn.bootcss.com/foundation/5.5.3/js/foundation.min.js"></script><script    src="https://cdn.bootcss.com/foundation/5.5.3/js/vendor/modernizr.js"></script></head><body>    <h1>新增页面</h1><form action="ContactServlet?do=add" method="post" style="width:50%;">  <fieldset>  <label>姓名 <input type="text" placeholder="name" required="required" name="name" /> </label>  <label>年龄 <input type="number" placeholder="age" required="required" min="1" max="133" name="age" /> </label>  <label>电话<input type="text" placeholder="phonenum" required="required" name="phone" /> </label>  <label>生日<input type="date" placeholder="date" required="required" name="date" /> </label>  <label>爱好<input type="text" placeholder="favorite" required="required" name="favorite" /> </label>  <input type="submit" value="新增" class="button" />  <input type="reset" class="button" />  </fieldset> </form></body></html>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%    String path = request.getContextPath();    String basePath = request.getScheme() + "://"            + request.getServerName() + ":" + request.getServerPort()            + path + "/";%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><base href="<%=basePath%>" rel="external nofollow" rel="external nofollow" rel="external nofollow" ><title>My JSP 'add.jsp' starting page</title><meta name="viewport" content="width=device-width, initial-scale=1"><link rel="stylesheet"    href="https://cdn.bootcss.com/foundation/5.5.3/css/foundation.min.css" rel="external nofollow" rel="external nofollow" rel="external nofollow" ><script src="https://cdn.bootcss.com/jquery/2.1.1/jquery.min.js"></script><script    src="https://cdn.bootcss.com/foundation/5.5.3/js/foundation.min.js"></script><script    src="https://cdn.bootcss.com/foundation/5.5.3/js/vendor/modernizr.js"></script></head><body>    <h1>修改页面</h1>    <form action="ContactServlet?do=edit&id=${edituser.id}" method="post" style="width:50%">        <fieldset>            <label>姓名 <input type="text" placeholder="name" name="name" value = "${edituser.name}">            </label>            <label>年龄 <input type="text" placeholder="age" name="age" value = "${edituser.age}">            </label>            <label>电话<input type="text" placeholder="phone" name="phone" value = "${edituser.phone}">            </label>            <label>生日<input type="date" placeholder="date" name="date" value = "${edituser.date}">            </label>            <label>爱好<input type="text" placeholder="favorite" name="favorite" value = "${edituser.favorite}">            </label>            <input type="submit" value="修改" class="button"> <input type="reset" class="button">        </fieldset>    </form></body></html>