使用Servlet对Mysql数据库表中数据进行增删改查

一： 要对数据库内的数据进行增删改查，首先我们需要连接到数据库

1）为了方便我们可以把jdbc代码写到一个Utils工具类内

package westos;

import java.sql.*;

public class Utils {
														//这里的b是指你要查询的表名
    static final String URL = "jdbc:mysql://localhost:3306/b?serverTimezone=GMT%2B8&useSSL=false" +
            "&useServerPrepStmts=true&cachePrepStmts=true&rewriteBatchedStatements=true" +
            "&useCursorFetch=true&defaultFetchSize=100&allowPublicKeyRetrieval=true";
            				//数据库用户名
    static final String USERNAME = "root";
    							//数据库密码
    static final String PASSWORD = "itcast";

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        return conn;
    }

    public static void close(ResultSet rs, PreparedStatement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(PreparedStatement stmt, Connection conn) {
        close(null, stmt, conn);
    }
}

二：写好工具类后就可以写增删改查的代码了

增

@WebServlet(urlPatterns = "/insert")
public class Insert extends HttpServlet{

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        //req.getRequestDispatcher("login.jsp").forward(req,resp);

        // 获取部门编号
        String deptno = req.getParameter("deptno");
        int no = Integer.parseInt(deptno);

        // 获取部门名称
        String dname = req.getParameter("dname");

        // 获取地区
        String loc = req.getParameter("loc");

        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = Utils.getConnection();
            stmt = conn.prepareStatement(
                    "insert into dept(deptno,dname,loc) values(?,?,?)");
            stmt.setInt(1, no);
            stmt.setString(2, dname);
            stmt.setString(3, loc);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Utils.close(stmt, conn);
        }
        resp.sendRedirect("/select");
    }
}

删

@WebServlet(urlPatterns = "/delete")
public class Delete extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        String[] deptnos = req.getParameterValues("deptno");
        Connection conn = null;
        PreparedStatement stmt = null;
        try{
            conn = Utils.getConnection();
            stmt = conn.prepareStatement("delete from dept where deptno=?");

            for (String deptno : deptnos) {
                int no = Integer.parseInt(deptno);
                stmt.setInt(1, no);
                stmt.executeUpdate();
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.close(stmt, conn);
        }

        resp.sendRedirect("/select");
    }
}

改

修改之前需要查询到原数据，才能便于修改（同时需要一个Dept实体类，将查询到的数据存放至作用域中）

@WebServlet(urlPatterns = "/update1")
public class Update1 extends HttpServlet{

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        String deptno = req.getParameter("deptno");
        int no = Integer.parseInt(deptno);

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = Utils.getConnection();
            stmt = conn.prepareStatement("select deptno,dname,loc from dept where deptno=?");
            stmt.setInt(1, no);
            rs = stmt.executeQuery();
            if(rs.next()) {
                System.out.println(rs.getInt("deptno"));
                System.out.println(rs.getString("dname"));
                Dept dept = new Dept(
                        rs.getInt("deptno"),
                        rs.getString("dname"),
                        rs.getString("loc")
                );

                req.setAttribute("d", dept);
            } else {

                req.setAttribute("d", null);
            }
            req.getRequestDispatcher("update.jsp").forward(req,resp);
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.close(rs, stmt, conn);
        }
    }
}

Dept实体类

public class Dept {
    private int deptno;
    private String dname;
    private String loc;
    private boolean close;

    public boolean isClose() {
        return close;
    }

    public void setClose(boolean close) {
        this.close = close;
    }

    public Dept(int deptno, String dname, String loc) {
        this.deptno = deptno;
        this.dname = dname;
        this.loc = loc;
    }

    public int getDeptno() {
        return deptno;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }

    public String getName() { // name
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getLoc() {
        return loc;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }
}

查询到数据后，进行修改

@WebServlet(urlPatterns = "/update2")
public class Update2 extends HttpServlet {

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");


        int deptno = Integer.parseInt(req.getParameter("deptno"));
        String dname = req.getParameter("dname");
        String loc = req.getParameter("loc");

        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = Utils.getConnection();
            stmt = conn.prepareStatement("update dept set dname=?, loc=? where deptno=?");
            stmt.setString(1, dname);
            stmt.setString(2, loc);
            stmt.setInt(3, deptno);
            stmt.executeUpdate();
        } catch (Exception e ) {
            e.printStackTrace();
        } finally {
            Utils.close(stmt, conn);
        }

        resp.sendRedirect("/select");
    }
}

查询并自定义分页

@WebServlet(urlPatterns = "/select")
public class Select extends HttpServlet{

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        String page = req.getParameter("page");
        String size = req.getParameter("size");
//页数
        int p = 1;
        if(page != null) {
            p = Integer.parseInt(page);
        }
//每页四条记录
        int s = 4;
        if(size != null) {
            s = Integer.parseInt(size);
        }

//m,n是限制查询的参数
        int m = (p-1)*s;
        int n = s;

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = Utils.getConnection();
            stmt = conn.prepareStatement("select deptno,dname,loc from dept limit ?,?");
            stmt.setInt(1, m);
            stmt.setInt(2, n);
            rs = stmt.executeQuery();
            List<Dept> list = new ArrayList<>();
            while(rs.next()){
                list.add(new Dept(
                        rs.getInt("deptno"),
                        rs.getString("dname"),
                        rs.getString("loc")
                ));
            }
//计算总页数
            stmt = conn.prepareStatement("select count(*) from dept");
            rs = stmt.executeQuery();
            rs.next();
            int count = rs.getInt(1);
            int total = 0;
            if(count % s == 0) {
                total = count / s;
            } else {
                total = count / s + 1;
            }
            req.setAttribute("p",p);
            req.setAttribute("aaa", list);
            req.setAttribute("total", total);


            req.getRequestDispatcher("select.jsp").forward(req, resp);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Utils.close(rs, stmt, conn);
        }

    }
}

三 ：以下是上面代码所需要的jsp代码

insert.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>新增部门</title>
    <script src="js/jquery-3.2.1.min.js"></script>
</head>
<body>
<form action="/insert" method="post" id="addForm">
    <p>部门编号 <input type="text" name="deptno" id="deptno"> <span id="deptnoMsg"></span> </p>
    <p>部门名称 <input type="text" name="dname" id="dname"> <span id="dnameMsg"></span></p>
    <p>部门地区 <input type="text" name="loc" id="loc"> <span id="locMsg"></span></p>
    <p><input type="submit" value="新增部门"></p>
</form>
<script>
    function checkDeptno() {
        // 部门编号必须为一个以上的数字
        if(/^[0-9]+$/.test($("#deptno").val())){
            $("#deptnoMsg").text("");
            return true;
        } else {
            $("#deptnoMsg").text("部门编号必须为一个以上的数字");
            return false;
        }
    }
    function checkDname() {
        if(/^.+$/.test($("#dname").val())){
            $("#dnameMsg").text("");
            return true;
        } else {
            $("#dnameMsg").text("部门名称是必须的");
            return false;
        }
    }
    function checkLoc() {
        if(/^.+$/.test($("#loc").val())) {
            $("#locMsg").text("");
            return true;
        } else {
            $("#locMsg").text("部门地区是必须的");
            return false;
        }
    }
    $("#deptno").blur(checkDeptno);
    $("#dname").blur(checkDname);
    $("#loc").blur(checkLoc);
    // 给表单添加提交事件
    $("#addForm").submit(function(){
        // 返回结果为true表示可以提交, 返回结果是false,表示表单不能提交
        return checkDeptno() && checkDname() && checkLoc();
    });
</script>
</body>
</html>

select.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" import="java.util.*,westos.Dept" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
    <table border="1" width="100%">
        <tbody>
        <c:forEach items="${aaa}" var="d">
            <tr>
                <td>${d.deptno}</td>
                <td>${d.name}</td>
                <td>${d.loc}</td>
                <td><a href="/delete?deptno=${d.deptno}">删除</a></td>
                <td><a href="/update1?deptno=${d.deptno}">修改</a></td>
            </tr>
        </c:forEach>
        </tbody>
    </table>
    <div>
        <a href="/select?page=1">首页</a>
        <c:if test="${p!= 1}">
        <a href="/select?page=${p-1}">上一页</a>
        </c:if>
        <c:forEach begin="1" end="${total}" var="i">
            <c:if test="${p==i}"><span>第${i}页</span></c:if>
            <c:if test="${p!=i}">
            <a href="/select?page=${i}">第${i}页</a>
            </c:if>

        </c:forEach>
        <c:if test="${p!=total}">
        <a href="/select?page=${p+1}" name="shang">下一页</a>
        </c:if>
        <a href="/select?page=${total}" name="xia">尾页</a>
    </div>

    <a href="insert.jsp">新增</a>

</body>
</html>

update.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<c:if test="${d==null}">
    要修改的用户不存在!
</c:if>
<c:if test="${d!=null}">
    ${d.deptno}, ${d.name}, ${d.loc}
    <form action="/update2" method="post">
        <p>部门编号<input type="text" name="deptno" id="deptno" value="${d.deptno}" readonly></p>
        <p>部门名称<input type="text" name="dname" id="dname" value="${d.name}"></p>
        <p>部门地址<input type="text" name="loc" id="loc" value="${d.loc}"></p>
        <p><input type="submit" value="修改"></p>
    </form>
</c:if>
</body>
</html>