使用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>
  • 7
    点赞
  • 52
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
首先,需要下载并安装MySQL数据库,然后创建一个数据库,并创建一个表格存储数据。 接下来,打开IntelliJ IDEA,创建一个Web应用程序工程。 在工程的src目录下,创建一个Java类,用于连接和操作数据库。 ``` import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DBHelper { private static String driver = "com.mysql.jdbc.Driver"; private static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8"; private static String user = "root"; private static String password = "123456"; public static Connection getConn() { Connection conn = null; try { Class.forName(driver); conn = DriverManager.getConnection(url, user, password); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } return conn; } public static void closeConn(Connection conn) { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static int executeUpdate(String sql) { Connection conn = null; Statement stmt = null; int result = 0; try { conn = getConn(); stmt = conn.createStatement(); result = stmt.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { closeConn(conn); } return result; } public static ResultSet executeQuery(String sql) { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = getConn(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); } catch (SQLException e) { e.printStackTrace(); } return rs; } } ``` 这个类中包含了获取数据库连接、关闭数据库连接、执行增删改查语句的方法。 接下来,创建一个Servlet的Java类,用于处理Http请求,并调用DBHelper中的方法操作数据库。 ``` import java.io.IOException; import java.io.PrintWriter; import java.sql.ResultSet; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class StudentServlet extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public StudentServlet() { super(); } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=utf-8"); PrintWriter out = response.getWriter(); String action = request.getParameter("action"); if ("add".equals(action)) { String name = request.getParameter("name"); int age = Integer.parseInt(request.getParameter("age")); String gender = request.getParameter("gender"); String sql = "insert into student(name,age,gender) values('" + name + "'," + age + ",'" + gender + "')"; int result = DBHelper.executeUpdate(sql); if (result > 0) { out.print("<script>alert('添加成功');window.location.href='index.jsp';</script>"); } else { out.print("<script>alert('添加失败');history.go(-1);</script>"); } } else if ("delete".equals(action)) { int id = Integer.parseInt(request.getParameter("id")); String sql = "delete from student where id=" + id; int result = DBHelper.executeUpdate(sql); if (result > 0) { out.print("<script>alert('删除成功');window.location.href='index.jsp';</script>"); } else { out.print("<script>alert('删除失败');history.go(-1);</script>"); } } else if ("update".equals(action)) { int id = Integer.parseInt(request.getParameter("id")); String name = request.getParameter("name"); int age = Integer.parseInt(request.getParameter("age")); String gender = request.getParameter("gender"); String sql = "update student set name='" + name + "',age=" + age + ",gender='" + gender + "' where id=" + id; int result = DBHelper.executeUpdate(sql); if (result > 0) { out.print("<script>alert('修改成功');window.location.href='index.jsp';</script>"); } else { out.print("<script>alert('修改失败');history.go(-1);</script>"); } } else if ("query".equals(action)) { String name = request.getParameter("name"); String sql = "select id,name,age,gender from student where name like '%" + name + "%'"; ResultSet rs = DBHelper.executeQuery(sql); StringBuilder sb = new StringBuilder("<table border='1'>"); sb.append("<tr><th>编号</th><th>姓名</th><th>年龄</th><th>性别</th><th>操作</th></tr>"); try { while (rs.next()) { sb.append("<tr>"); sb.append("<td>" + rs.getInt("id") + "</td>"); sb.append("<td>" + rs.getString("name") + "</td>"); sb.append("<td>" + rs.getInt("age") + "</td>"); sb.append("<td>" + rs.getString("gender") + "</td>"); sb.append("<td><a href='update.jsp?id=" + rs.getInt("id") + "'>修改</a> <a href='StudentServlet?action=delete&id=" + rs.getInt("id") + "'>删除</a></td>"); sb.append("</tr>"); } } catch (Exception e) { e.printStackTrace(); } finally { DBHelper.closeConn(DBHelper.getConn()); } sb.append("</table>"); out.print(sb.toString()); } else { response.sendRedirect("index.jsp"); } out.flush(); out.close(); } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } } ``` 这个Servlet中包含了添加学生记录、删除学生记录、修改学生记录、查询学生记录的方法。 最后,在Web应用程序中创建JSP页面,用于添加、修改、查询和展示学生信息的操作。 以查询学生信息页面query.jsp为例: ``` <%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>学生信息查询</title> </head> <body> <h2>学生信息查询</h2> <form action="StudentServlet" method="get"> 姓名:<input type="text" name="name"> <input type="hidden" name="action" value="query"> <input type="submit" value="查询"> </form> <hr> <h3>查询结果</h3> <%= request.getAttribute("resultStr") %> </body> </html> ``` 在这个页面中,用户可以输入学生姓名,然后点击查询按钮,调用Servlet中的查询学生记录的方法,返回查询结果并显示在页面上。 这样就完成了使用Idea连接MySQL数据库Servlet进行增删改查操作的示例。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值