Servlet增刪改查

CRUD(創建，讀取，更新和刪除)應用程序是任何項目開發的最重要的應用程序。在Servlet中，我們可以輕鬆創建CRUD應用程序。

Servlet CRUD示例

在MySQL數據庫中使用序列自動遞增ID創建「employees」表。這個表中有以下幾個字段：id，name，age，address和salary。

創建表的SQL語句如下 -

CREATE TABLE `employees` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '',
  `age` int(3) unsigned NOT NULL DEFAULT '0',
  `education` varchar(32) DEFAULT '' COMMENT '學歷',
  `address` varchar(254) DEFAULT NULL,
  `salary` float(8,2) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

打開Eclipse，創建一個動態Web項目：ServletCURD，其完整的目錄結構如下所示 -

Servlet增刪改查

以下是這個項目中的幾個主要的代碼文件。

文件：index.html -

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加新員工</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="text" name="age" /></td>
            </tr>

            <tr>
                <td>學歷:</td>
                <td><select name="education" style="width: 150px">
                        <option value="專科">專科</option>
                        <option value="本科">本科</option>
                        <option value="研究生">研究生</option>
                        <option value="博士">博士</option>
                        <option value="其它">其它</option>
                </select></td>
            </tr>
            <tr>
                <td>地址:</td>
                <td><input type="text" name="address" /></td>
            </tr>
            <tr>
                <td>薪水:</td>
                <td><input type="text" name="salary" /></td>
            </tr>
            <tr>
                <td colspan="2"><input type="submit" value="保存員工信息" /></td>
            </tr>
        </table>
    </form>

    <br />
    <a href="ViewServlet">查看員工信息列表</a>
</body>
</html>

文件：Emp.java -

package com.yiibai;

public class Emp {

    private int id;
    private String name;
    private int age;
    private String address;
    private String education;
    private float salary;

    public int getId() {
        return id;
    }

    public String getEducation() {
        return education;
    }

    public void setEducation(String education) {
        this.education = education;
    }

    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 getAddress() {
        return address;
    }

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

    public float getSalary() {
        return salary;
    }

    public void setSalary(float salary) {
        this.salary = salary;
    }

}

文件：EmpDao.java -

package com.yiibai;

import java.util.*;
import java.sql.*;

public class EmpDao {
    // 表名稱
    private static String tbName = "employees";

    public static Connection getConnection() {
        Connection con = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/testdb?useSSL=false&characterEncoding=utf8", "root", "123456");
        } catch (Exception e) {
            System.out.println(e);
        }
        return con;
    }

    public static int save(Emp e) {
        int status = 0;
        try {
            Connection con = EmpDao.getConnection();
            String sql = "INSERT INTO " + tbName + "(name,age,education,address,salary) values (?,?,?,?,?)";
            PreparedStatement ps = con.prepareStatement(sql);

            ps.setString(1, e.getName());
            ps.setInt(2, e.getAge());
            ps.setString(3, e.getEducation());
            ps.setString(4, e.getAddress());
            ps.setFloat(5, e.getSalary());

            status = ps.executeUpdate();

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

        return status;
    }

    public static int update(Emp e) {
        int status = 0;
        try {
            String sql = "UPDATE " + tbName + " SET name=?,age=?,education=?,address=?,salary=? where id=?";
            Connection con = EmpDao.getConnection();
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, e.getName());
            ps.setInt(2, e.getAge());
            ps.setString(3, e.getEducation());
            ps.setString(4, e.getAddress());
            ps.setFloat(5, e.getSalary());
            ps.setInt(6, e.getId());

            status = ps.executeUpdate();

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

        return status;
    }

    public static int delete(int id) {
        int status = 0;
        try {
            String sql = "DELETE FROM " + tbName + " WHERE id=?";
            Connection con = EmpDao.getConnection();
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            status = ps.executeUpdate();

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

        return status;
    }

    public static Emp getEmployeeById(int id) {
        Emp e = new Emp();

        try {
            String sql = "SELECT * FROM " + tbName + " WHERE id=?";
            Connection con = EmpDao.getConnection();
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                e.setId(rs.getInt("id"));
                e.setName(rs.getString("name"));
                e.setAge(rs.getInt("age"));
                e.setAddress(rs.getString("address"));
                e.setEducation(rs.getString("education"));
                e.setSalary(rs.getFloat("salary"));
            }
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return e;
    }

    public static List<Emp> getAllEmployees() {
        List<Emp> list = new ArrayList<Emp>();

        try {
            String sql = "SELECT * FROM " + tbName+" ORDER BY id DESC";
            Connection con = EmpDao.getConnection();
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Emp e = new Emp();
                e.setId(rs.getInt("id"));
                e.setName(rs.getString("name"));
                e.setAddress(rs.getString("address"));
                e.setAge(rs.getInt("age"));
                e.setEducation(rs.getString("education"));
                e.setSalary(rs.getFloat("salary"));
                list.add(e);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }
}

文件：SaveServlet.java -

package com.yiibai;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/SaveServlet")
public class SaveServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        request.setCharacterEncoding("UTF-8");
        PrintWriter out = response.getWriter();

        String name = request.getParameter("name");
        String age = request.getParameter("age");
        //System.out.println("age is :"+age);
        String education = request.getParameter("education");
        String address = request.getParameter("address");
        String salary = request.getParameter("salary");

        Emp e = new Emp();
        e.setName(name);
        e.setAge(Integer.parseInt(age));
        e.setAddress(address);
        e.setEducation(education);
        e.setSalary(Float.parseFloat(salary));

        int status = EmpDao.save(e);
        if (status > 0) {
            out.print("<p>保存員工信息記錄成功！</p>");
            request.getRequestDispatcher("index.html").include(request, response);
        } else {
            out.println("對不起，保存失敗！");
        }

        out.close();
    }

}

文件：EditServlet.java -

package com.yiibai;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/EditServlet")
public class EditServlet extends HttpServlet {

    // 顯示要修改的用戶信息
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        request.setCharacterEncoding("UTF-8");
        PrintWriter out = response.getWriter();
        out.println("<h1>更新員工信息</h1>");
        String sid = request.getParameter("id");
        int id = Integer.parseInt(sid);

        Emp e = EmpDao.getEmployeeById(id);

        String education = e.getEducation();

        out.print("<form action='EditServlet' 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><input type='text' name='age' value='" + e.getAge()+ "'/></td></tr>");
        out.print("<tr><td>地址:</td><td><input type='text' name='address' value='" + e.getAddress() + "'/></td></tr>");
        out.print("<tr><td>學歷:</td><td>");
        out.print("<select name='education' style='width:150px'>");
        if(education == "專科") {
            out.print("<option value='專科' selected='selected'>專科</option>");
        }else {
            out.print("<option value='專科'>專科</option>");
        }

        if(education == "本科") {
            out.print("<option value='本科' selected='selected'>本科</option>");
        }else {
            out.print("<option value='本科'>本科</option>");
        }

        if(education == "研究生") {
            out.print("<option value='研究生' selected='selected'>研究生</option>");
        }else {
            out.print("<option value='研究生'>研究生</option>");
        }
        if(education == "博士") {
            out.print("<option value='博士' selected='selected'>博士</option>");
        }else {
            out.print("<option value='博士'>博士</option>");
        }

        if(education == "其它") {
            out.print("<option value='其它' selected='selected'>其它</option>");
        }else {
            out.print("<option value='其它'>其它</option>");
        }
        out.print("</select>");
        out.print("</td></tr>");
        out.print("<tr><td>薪水:</td><td><input type='text' name='salary' value='" + e.getSalary() + "'/></td></tr>");
        out.print("<tr><td colspan='2'><input type='submit' value='編輯&保存'/></td></tr>");
        out.print("</table>");
        out.print("</form>");

        out.close();
    }

    // 收集表單信息並將修改更新到數據庫
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        request.setCharacterEncoding("UTF-8");
        PrintWriter out = response.getWriter();

        String sid = request.getParameter("id");
        int id = Integer.parseInt(sid);
        String name = request.getParameter("name");
        String age = request.getParameter("age");
        String address = request.getParameter("address");
        String education = request.getParameter("education");
        String salary = request.getParameter("salary");

        Emp e = new Emp();
        e.setId(id);
        e.setName(name);
        e.setAddress(address);
        e.setAge(Integer.parseInt(age));
        e.setSalary(Float.parseFloat(salary));
        e.setEducation(education);

        int status = EmpDao.update(e);
        if (status > 0) {
            response.sendRedirect("ViewServlet");
        } else {
            out.println("對不起更新信息失敗！");
        }
        out.close();
    }
}

文件：DeleteServlet.java -

package com.yiibai;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        request.setCharacterEncoding("UTF-8");
        String sid = request.getParameter("id");
        int id = Integer.parseInt(sid);
        EmpDao.delete(id);
        response.sendRedirect("ViewServlet");
    }
}

文件：ViewServlet.java -

package com.yiibai;

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

@WebServlet("/ViewServlet")
public class ViewServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response)   
               throws ServletException, IOException {  
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        request.setCharacterEncoding("UTF-8");
        PrintWriter out=response.getWriter();  
        out.println("<a href='index.html'>添加新員工</a>");  
        out.println("<h1>員工列表</h1>");  

        List<Emp> list=EmpDao.getAllEmployees();  

        out.print("<table border='1' width='100%'");  
        out.print("<tr><th>編號</th><th>姓名</th><th>年齡</th><th>學歷</th><th>薪水</th><th>地址</th><th>操作</th></tr>");  
        for(Emp e:list){  
         out.print("<tr><td>"+e.getId()+"</td><td>"+e.getName()+"</td><td>"+e.getAge()+"</td><td>"+e.getEducation()+"</td><td>"+e.getSalary()+"</td><td>"+e.getAddress()+"</td><td><a href='EditServlet?id="+e.getId()+"'>編輯</a> | <a href='DeleteServlet?id="+e.getId()+"' onClick=\"return confirm('確定要刪除嗎？')\">刪除</a></td></tr>");  
        }  
        out.print("</table>");  
        out.close();  
    }
}