使用Servlet完成CRUD

任何项目开发，CRUD（增加，查询，更新和删除）应用程序都是最基础最核心的功能。在Servlet中，我们可以轻松创建CRUD应用程序。

1 创建表和准备数据

1）在MySQL的test数据库，使用以下SQL创建customer表：

CREATE TABLE `customer` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(50) DEFAULT NULL,
   `gender` varchar(20) DEFAULT NULL,
   `telephone` varchar(100) DEFAULT NULL,
   `address` varchar(100) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

注意：id采用MySQL的自增长策略，所以我们程序不用维护id值。

2）插入一些测试数据，如下：

2 编写index.jsp

编写index.jsp页面，该页面是应用的主页面，可以添加客户，也可以点击查询所有客户。

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>一点教程网-使用Session完成增删改成（CRUD）</title>
</head>
<body>
<h1>添加客户</h1>
<form action="SaveServlet" method="post">
    <table>
        <tr><td>客户名称:</td><td><input type="text" name="name"/></td></tr>
        <tr><td>性别:</td><td><input type="radio" name="gender" value="男"/>男<input type="radio" name="gender" value="女"/>女</td></tr>
        <tr><td>联系电话:</td><td><input type="text" name="telephone"/></td></tr>
        <tr><td>地址:</td><td><input type="text" name="address"/></td></tr>
        <tr><td colspan="2"><input type="submit" value="保存"/></td></tr>
    </table>
</form>

<br/>
<a href="ViewServlet">查询客户</a>
</body>
</html>

3 编写实体类

创建Customer实体类，用于封装表数据：

package com.yiidian.domain;

/**
 *一点教程网 - http://www.yiidian.com
 */
public class Customer {
    private Integer id;
    private String name;
    private String gender;
    private String telephone;
    private String address;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

4 编写CustomerDao

CustomerDao提供了增删改查的方法：

package com.yiidian.dao;

import com.yiidian.domain.Customer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 一点教程网 - http://www.yiidian.com
 */
public class CustomerDao {
    public static Connection getConnection(){
        Connection con=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            con= DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
        }catch(Exception e){System.out.println(e);}
        return con;
    }

  
    public static int save(Customer customer){
        int status=0;
        try{
            Connection con=CustomerDao.getConnection();
            PreparedStatement ps=con.prepareStatement(
                    "insert into customer(name,gender,telephone,address) values (?,?,?,?)");
            ps.setString(1,customer.getName());
            ps.setString(2,customer.getGender());
            ps.setString(3,customer.getTelephone());
            ps.setString(4,customer.getAddress());

            status=ps.executeUpdate();

            con.close();
        }catch(Exception ex){ex.printStackTrace();}

        return status;
    }
    public static int update(Customer customer){
        int status=0;
        try{
            Connection con=CustomerDao.getConnection();
            PreparedStatement ps=con.prepareStatement(
                    "update customer set name=?,gender=?,telephone=?,address=? where id=?");
            ps.setString(1,customer.getName());
            ps.setString(2,customer.getGender());
            ps.setString(3,customer.getTelephone());
            ps.setString(4,customer.getAddress());
            ps.setInt(5,customer.getId());

            status=ps.executeUpdate();

            con.close();
        }catch(Exception ex){ex.printStackTrace();}

        return status;
    }
    public static int delete(int id){
        int status=0;
        try{
            Connection con=CustomerDao.getConnection();
            PreparedStatement ps=con.prepareStatement("delete from customer where id=?");
            ps.setInt(1,id);
            status=ps.executeUpdate();

            con.close();
        }catch(Exception e){e.printStackTrace();}

        return status;
    }
    public static Customer getEmployeeById(int id){
        Customer e=new Customer();

        try{
            Connection con=CustomerDao.getConnection();
            PreparedStatement ps=con.prepareStatement("select * from customer where id=?");
            ps.setInt(1,id);
            ResultSet rs=ps.executeQuery();
            if(rs.next()){
                e.setId(rs.getInt(1));
                e.setName(rs.getString(2));
                e.setGender(rs.getString(3));
                e.setTelephone(rs.getString(4));
                e.setAddress(rs.getString(5));
            }
            con.close();
        }catch(Exception ex){ex.printStackTrace();}

        return e;
    }
    public static List<Customer> getAllEmployees(){
        List<Customer> list=new ArrayList<Customer>();

        try{
            Connection con=CustomerDao.getConnection();
            PreparedStatement ps=con.prepareStatement("select * from customer");
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                Customer e=new Customer();
                e.setId(rs.getInt(1));
                e.setName(rs.getString(2));
                e.setGender(rs.getString(3));
                e.setTelephone(rs.getString(4));
                e.setAddress(rs.getString(5));
                list.add(e);
            }
            con.close();
        }catch(Exception e){e.printStackTrace();}

        return list;
    }
}

5 编写SaveServlet

SaveServlet用于保存客户：

package com.yiidian.servlet;

import com.yiidian.dao.CustomerDao;
import com.yiidian.domain.Customer;

import java.io.*;
import javax.servlet.*;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;

/**
 * 一点教程网 - http://www.yiidian.com
 */
@WebServlet("/SaveServlet")
public class SaveServlet extends HttpServlet {

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        //设置请求参数的编码
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out=response.getWriter();

        String name=request.getParameter("name");
        String gender=request.getParameter("gender");
        String telephone=request.getParameter("telephone");
        String address=request.getParameter("address");

        Customer e=new Customer();
        e.setName(name);
        e.setGender(gender);
        e.setTelephone(telephone);
        e.setAddress(address);

        int status= CustomerDao.save(e);
        if(status>0){
            out.print("<p>客户保存成功!</p>");
            request.getRequestDispatcher("index.jsp").include(request, response);
        }else{
            out.println("抱歉!保存失败");
        }

        out.close();
    }
}

6 编写EditServlet

EditServlet用于回显客户信息：

package com.yiidian.servlet;

import com.yiidian.dao.CustomerDao;
import com.yiidian.domain.Customer;

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 java.io.IOException;
import java.io.PrintWriter;

/**
 * 一点教程网 - http://www.yiidian.com
 */
@WebServlet("/EditServlet")
public class EditServlet extends HttpServlet {

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out=response.getWriter();
        out.println("<h1>编辑客户</h1>");
        String sid=request.getParameter("id");
        int id=Integer.parseInt(sid);

        Customer e= CustomerDao.getEmployeeById(id);

        out.print("<form action='EditServlet2' method='post'>");
        out.print("<table>");
        out.print("<tr><td></td><td><input type='hidden' name='id' value='"+e.getId()+"'/></td></tr>");
        out.print("<tr><td>客户名称:</td><td><input type='text' name='name' value='"+e.getName()+"'/></td></tr>");
        out.print("<tr><td>性别:</td><td>");
        if(e.getGender().equals("男")){
            out.print("<input type='radio' name='gender' value='男' checked/>男");
        }else{
            out.print("<input type='radio' name='gender' value='男'/>男");
        }
        if(e.getGender().equals("女")){
            out.print("<input type='radio' name='gender' value='女' checked/>女");
        }else{
            out.print("<input type='radio' name='gender' value='女'/>女");
        }
        out.print("</td></tr>");
        out.print("<tr><td>联系电话:</td><td><input type='text' name='telephone' value='"+e.getTelephone()+"'/></td></tr>");
        out.print("<tr><td>地址:</td><td><input type='text' name='address' value='"+e.getAddress()+"'/></td></tr>");
        out.print("<tr><td colspan='2'><input type='submit' value='编辑保存'/></td></tr>");
        out.print("</table>");
        out.print("</form>");

        out.close();
    }
}

7 编写EditServlet2

EditServlet2用于保存编辑后的客户数据：

package com.yiidian.servlet;

import com.yiidian.dao.CustomerDao;
import com.yiidian.domain.Customer;

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 java.io.IOException;
import java.io.PrintWriter;

/**
 * 一点教程网 - http://www.yiidian.com
 */
@WebServlet("/EditServlet2")
public class EditServlet2 extends HttpServlet {

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out=response.getWriter();

        String sid=request.getParameter("id");
        int id=Integer.parseInt(sid);
        String name=request.getParameter("name");
        String gender=request.getParameter("gender");
        String telephone=request.getParameter("telephone");
        String address=request.getParameter("address");

        Customer e=new Customer();
        e.setId(id);
        e.setName(name);
        e.setGender(gender);
        e.setTelephone(telephone);
        e.setAddress(address);

        int status=CustomerDao.update(e);
        if(status>0){
            response.sendRedirect("ViewServlet");
        }else{
            out.println("抱歉!更新失败");
        }

        out.close();
    }
}

8 编写DeleteServlet

DeleteServlet用于删除客户：

package com.yiidian.servlet;

import com.yiidian.dao.CustomerDao;
import com.yiidian.domain.Customer;

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 java.io.IOException;
import java.io.PrintWriter;

/**
 * 一点教程网 - http://www.yiidian.com
 */
@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String sid=request.getParameter("id");
        int id=Integer.parseInt(sid);
        CustomerDao.delete(id);
        response.sendRedirect("ViewServlet");
    }
}

9 编写ViewServlet

ViewServlet用于查询显示所有客户数据：

package com.yiidian.servlet;

import com.yiidian.dao.CustomerDao;
import com.yiidian.domain.Customer;

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 java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

/**
 * 一点教程网 - http://www.yiidian.com
 */
@WebServlet("/ViewServlet")
public class ViewServlet extends HttpServlet {

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out=response.getWriter();
        out.println("<a href='index.jsp'>添加客户</a>");
        out.println("<h1>客户列表</h1>");

        List<Customer> list=CustomerDao.getAllEmployees();

        out.print("<table border='1' width='100%'");
        out.print("<tr><th>Id</th><th>客户名称</th><th>性别</th><th>联系电话</th><th>地址</th><th>编辑</th><th>删除</th></tr>");
        for(Customer e:list){
            out.print("<tr><td>"+e.getId()+"</td><td>"+e.getName()+"</td><td>"+e.getGender()+"</td>  "
                  +" <td>"+e.getTelephone()+"</td><td>"+e.getAddress()+"</td><td><a href='EditServlet?id="+e.getId()+"'>编辑</a></td>  "
                 +"<td><a href='DeleteServlet?id="+e.getId()+"'>删除</a></td></tr>");
        }
        out.print("</table>");

        out.close();
    }
}