Servlet+MySQL增删改查

对于任何项目开发，创建，读取，更新和删除(CRUD)记录操作是应用程序的一个最重要部分。在这个Servlet应用示例中，我们将演示如何使用Servlet连接MySQL数据库，并执行增删改查操作。

1. Servlet CRUD示例

在MySQL数据库中创建xn_users表。其中有5个字段：id, name, password, email 以及 country，id字段使用序列使用自动增量。

启动Eclipse，创建一个动态Web项目：ServletCurd，完整的目录结构如下所示：
Servlet CRUD示例

连接到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启动完成后，打开浏览器访问以下网址：

http://localhost:8080/ServletCurd/

如果程序没有错误，应该会看到(index.html)显示的结果。即增加新员工信息：

增加新员工信息

填写一些新员工信息，然后提交保存。点击员工列表，应该可以看到所有员工信息列表：

员工列表

删除和修改操作如下图所示：

删除和修改

3. 注意事项

需要将Java连接MySQL驱动器(mysql-connector-java-8.0.18.jar)放到项目的目录Servlet/WebContent/WEB-INF/lib中。

易百教程