对于任何项目开发，创建，读取，更新和删除(CRUD)记录操作是应用程序的一个最重要部分。 在这个Servlet应用示例中，我们将演示如何使用Servlet连接MySQL数据库，并执行增删改查操作。
1. Servlet CRUD示例
在MySQL数据库中创建xn_users表。其中有5个字段：id, name, password, email 以及 country，id字段使用序列使用自动增量。
启动Eclipse，创建一个动态Web项目：ServletCurd，完整的目录结构如下所示：
连接到MySQL，创建一个数据库：testdb，然后在这个数据库中创建一张表：xn_employee，xn_employee的字段如下：
| 编号 | 字段 | 类型 | 其他说明 |
|---|---|---|---|
| 1 | id |
int(10) | 主键，自动增加 |
| 2 | name |
varchar(32) | 用户名 |
| 3 | password |
varchar(32) | 密码 |
| 4 | email |
varchar(254) | 邮件地址 |
| 5 | country |
varchar(32) | 国家 |
表：xn_employee 的创建语句如下：
CREATE TABLE `xn_employee` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT '', `password` varchar(32) DEFAULT '', `email` varchar(255) DEFAULT '', `country` varchar(255) DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=10026 DEFAULT CHARSET=utf8; 文件：index.html
<!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Servlet+MySQL增删改查示例</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="password" name="password" /></td> </tr> <tr> <td>Email地址:</td> <td><input type="email" name="email" /></td> </tr> <tr> <td>国家:</td> <td><select name="country" style="width: 150px"> <option value="中国">中国</option> <option value="美国">美国</option> <option value="日本">日本</option> <option value="英国">英国</option> <option value="其他">其他</option> </select></td> </tr> <tr> <td colspan="2"><input type="submit" value="保存员工信息" /></td> </tr> </table> </form> <br /> <a href="ViewServlet">员工列表</a> </body> </html> 文件：Employee.java
package com.xntutor.dao; public class Employee { private int id; private String name, password, email, country; 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 String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getCountry() { return country; } public void setCountry(String country) { this.country = country; } } 文件：EmployeeDao.java
package com.xntutor.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import com.xntutor.db.ConnectDb; public class EmployeeDao { public static Connection getConnection() { return ConnectDb.getConnection(); } public static int save(Employee e) { int status = 0; try { Connection con = EmployeeDao.getConnection(); PreparedStatement ps = con .prepareStatement("insert into xn_employee(name,password,email,country) values (?,?,?,?)"); ps.setString(1, e.getName()); ps.setString(2, e.getPassword()); ps.setString(3, e.getEmail()); ps.setString(4, e.getCountry()); status = ps.executeUpdate(); con.close(); } catch (Exception ex) { ex.printStackTrace(); } return status; } public static int update(Employee e) { int status = 0; try { Connection con = EmployeeDao.getConnection(); PreparedStatement ps = con .prepareStatement("update xn_employee set name=?,password=?,email=?,country=? where id=?"); ps.setString(1, e.getName()); ps.setString(2, e.getPassword()); ps.setString(3, e.getEmail()); ps.setString(4, e.getCountry()); ps.setInt(5, e.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 = EmployeeDao.getConnection(); PreparedStatement ps = con.prepareStatement("delete from xn_employee where id=?"); ps.setInt(1, id); status = ps.executeUpdate(); con.close(); } catch (Exception e) { e.printStackTrace(); } return status; } public static Employee getEmployeeById(int id) { Employee e = new Employee(); try { Connection con = EmployeeDao.getConnection(); PreparedStatement ps = con.prepareStatement("select * from xn_employee where id=?"); ps.setInt(1, id); ResultSet rs = ps.executeQuery(); if (rs.next()) { e.setId(rs.getInt(1)); e.setName(rs.getString(2)); e.setPassword(rs.getString(3)); e.setEmail(rs.getString(4)); e.setCountry(rs.getString(5)); } con.close(); } catch (Exception ex) { ex.printStackTrace(); } return e; } public static List<Employee> getAllEmployees() { List<Employee> list = new ArrayList<Employee>(); try { Connection con = EmployeeDao.getConnection(); PreparedStatement ps = con.prepareStatement("select * from xn_employee"); ResultSet rs = ps.executeQuery(); while (rs.next()) { Employee e = new Employee(); e.setId(rs.getInt(1)); e.setName(rs.getString(2)); e.setPassword(rs.getString(3)); e.setEmail(rs.getString(4)); e.setCountry(rs.getString(5)); list.add(e); } con.close(); } catch (Exception e) { e.printStackTrace(); } return list; } } 文件：ConnectDb.java
package com.xntutor.db; import java.sql.Connection; import java.sql.DriverManager; public class ConnectDb { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/testdb?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8"; // Database credentials static final String USER = "root"; static final String PASS = "123456"; public static Connection getConnection() { Connection con = null; try { // STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // STEP 3: Open a connection System.out.println("Connecting to testdb database..."); con = DriverManager.getConnection(DB_URL, USER, PASS); } catch (Exception e) { e.printStackTrace(); } return con; } } 文件：SaveServlet.java
package com.xntutor.servlet; 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; import com.xntutor.dao.Employee; import com.xntutor.dao.EmployeeDao; @WebServlet("/SaveServlet") public class SaveServlet extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=utf-8"); response.setCharacterEncoding("utf-8"); // 请求解决乱码 request.setCharacterEncoding("utf-8"); PrintWriter out = response.getWriter(); String name = request.getParameter("name"); String password = request.getParameter("password"); String email = request.getParameter("email"); String country = request.getParameter("country"); // System.out.println("name=>"+name); Employee e = new Employee(); e.setName(name); e.setPassword(password); e.setEmail(email); e.setCountry(country); int status = EmployeeDao.save(e); if (status > 0) { out.print("<p>新增记录成功!</p>"); request.getRequestDispatcher("index.html").include(request, response); } else { out.println("抱歉，新增记录失败!"); } out.close(); } } 文件：DeleteServlet.java
package com.xntutor.servlet; 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; import com.xntutor.dao.EmployeeDao; @WebServlet("/DeleteServlet") public class DeleteServlet extends HttpServlet { protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=utf-8"); response.setCharacterEncoding("utf-8"); // 请求解决乱码 request.setCharacterEncoding("utf-8"); PrintWriter out = response.getWriter(); String sid = request.getParameter("id"); int id = Integer.parseInt(sid); EmployeeDao.delete(id); out.println("删除成功！"); response.sendRedirect("ViewServlet"); } } 文件：DoEditServlet.java
package com.xntutor.servlet; 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; import com.xntutor.dao.Employee; import com.xntutor.dao.EmployeeDao; @WebServlet("/DoEditServlet") public class DoEditServlet extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=utf-8"); response.setCharacterEncoding("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 password = request.getParameter("password"); String email = request.getParameter("email"); String country = request.getParameter("country"); Employee e = new Employee(); e.setId(id); e.setName(name); e.setPassword(password); e.setEmail(email); e.setCountry(country); int status = EmployeeDao.update(e); if (status > 0) { response.sendRedirect("ViewServlet"); } else { out.println("抱歉，更新记录失败！"); } out.close(); } } 文件：EditServlet.java
package com.xntutor.servlet; 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; import com.xntutor.dao.Employee; import com.xntutor.dao.EmployeeDao; @WebServlet("/EditServlet") public class EditServlet extends HttpServlet { protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=utf-8"); response.setCharacterEncoding("utf-8"); PrintWriter out=response.getWriter(); out.println("<h1>更新员工信息</h1>"); String sid=request.getParameter("id"); int id=Integer.parseInt(sid); Employee e=EmployeeDao.getEmployeeById(id); out.print("<form action='DoEditServlet' 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='password' name='password' value='"+e.getPassword()+"'/></td></tr>"); out.print("<tr><td>Email:</td><td><input type='email' name='email' value='"+e.getEmail()+"'/></td></tr>"); out.print("<tr><td>国家:</td><td>"); out.print("<select name='country' style='width:150px'>"); out.print("<option value='中国'>中国</option>"); out.print("<option value='美国'>美国</option>"); out.print("<option value='日本'>日本</option>"); out.print("<option value='英国'>英国</option>"); out.print("<option value='其他'>其他</option>"); out.print("</select>"); out.print("</td></tr>"); out.print("<tr><td colspan='2'><input type='submit' value=/"修改保存/"/></td></tr>"); out.print("</table>"); out.print("</form>"); out.close(); } } 文件：ViewServlet.java
package com.xntutor.servlet; 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; import com.xntutor.dao.Employee; import com.xntutor.dao.EmployeeDao; @WebServlet("/ViewServlet") public class ViewServlet extends HttpServlet { protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=utf-8"); response.setCharacterEncoding("utf-8"); PrintWriter out=response.getWriter(); out.println("<a href='index.html'>添加新员工</a>"); out.println("<h2>员工管理列表</h2>"); List<Employee> list=EmployeeDao.getAllEmployees(); out.print("<table border='1' width='100%'"); out.print("<tr><th>编号</th><th>姓名</th><th>密码</th><th>Email</th><th>国家</th><th>操作</th></tr>"); if(list.size()>0) { for(Employee e:list){ out.print("<tr><td>"+e.getId()+"</td><td>"+e.getName()+"</td><td>"+e.getPassword()+"</td>" + "<td>"+e.getEmail()+"</td><td>"+e.getCountry()+"</td><td><a href='EditServlet?id="+e.getId()+"'>编辑</a> | <a href='DeleteServlet?id="+e.getId()+"'>删除</a></td></tr>"); } }else { out.print("<tr><td colspan=6>暂无数据</td></tr>"); } out.print("</table>"); out.close(); } } 2. 运行结果
右键单击项目：ServletCurd，选择“Run As/Run on Server”，等待Apache Tomcat启动完成后，打开浏览器访问以下网址：
如果程序没有错误，应该会看到(index.html)显示的结果。即增加新员工信息：
填写一些新员工信息，然后提交保存。点击员工列表，应该可以看到所有员工信息列表：
删除和修改操作如下图所示：
3. 注意事项
- 需要将Java连接MySQL驱动器(
mysql-connector-java-8.0.18.jar)放到项目的目录Servlet/WebContent/WEB-INF/lib中。
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/tech/pnotes/264032.html