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，其完整的目錄結構如下所示 -
以下是這個專案中的幾個主要的程式碼檔案。
檔案：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();
}
}
注意： 這裡不需要組態web.xml，所有Servlet的模式對映都是使用
@WebServlet("/mapurl")註解來宣告了。
在編寫上面程式碼後，部署此Web應用程式(在專案名稱上點選右鍵->」Run On Server…」)，開啟瀏覽器存取URL： http://localhost:8080/ServletCURD/ ，如果沒有錯誤，應該會看到以下結果 -
新增一個員工資訊，如下 -
點選儲存員工資訊提交後，得到以下結果 -
檢視員工列表： http://localhost:8080/ServletCURD/
其它的操作：修改，刪除等請自行執行本範例，然後點選對應連結來進行測試，這裡不再截圖演示了。