JavaWeb增删改查的基本操作

更新时间：2022-03-17 10:42:11 来源：动力节点浏览911次

大致流程是：

首先访问到servlet层，在servlet层里调用StudentRepository的各个方法，然后展示到jsp页面中。所以浏览器访问路径是servlet层里StudentServlet中@WebServlet("/student")的路径(http://localhost:8080/student)

工具：idea，mysql数据库

1.首先看一下我的基本目录：

数据库：

2.各个层的代码：

Student.java

public class Student {
    private Integer id;
    private String name;
    private Integer numsex;
    private Integer age;
    private String password;    
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getNumsex() {
        return numsex;
    }
    public void setNumsex(Integer numsex) {
        this.numsex = numsex;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
    public Student(Integer id, String name, Integer numsex, Integer age, String password) {
        this.id = id;
        this.name = name;
        this.numsex = numsex;
        this.age = age;
        this.password = password;
    }
    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", numsex=" + numsex +
                ", age=" + age +
                ", password='" + password + '\'' +
                '}';
    }

StudentRepository：

import com.javaweb.entity.Student;
import com.javaweb.util.JDBCTools;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class StudentRepository {
    public List<Student> findAll(){
        List<Student> list=new ArrayList<>();
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            //调用JDBCTools连接mysql数据库
            connection= JDBCTools.getConnection();
            String sql="select * from student";//查询语句
            preparedStatement=connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                //从resultSet拿出每个属性数据
                Integer id=resultSet.getInt(1);
                String name=resultSet.getString(2);
                Integer numsex=resultSet.getInt(3);
                Integer age=resultSet.getInt(4);
                String password=resultSet.getString(5);
                //这里可以理解为，resultSet拿出每个属性数据赋予student对象，形成一个有数据的student对象
                Student student = new Student(id, name, numsex, age, password);
                list.add(student);//可能多条数据，放到集合中
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //调用JDBCTools，关闭connection，preparedStatement，resultSet
            JDBCTools.release(connection,preparedStatement,resultSet);
        }
        return list;
    }
    //添加操作
    public void add(Integer id,String name,Integer numsex,
                     Integer age,String password){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            connection= JDBCTools.getConnection();
            String sql="insert into student(id,name,numsex,age,password) values (?,?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            //这里注意第一个参数对应sql语句问号的序号，
            preparedStatement.setInt(1,id);//就是把id替代sql的第一个问号，id由前端传过来
            preparedStatement.setString(2,name);
            preparedStatement.setInt(3,numsex);
            preparedStatement.setInt(4,age);
            preparedStatement.setString(5,password);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection,preparedStatement,null);
        }
    }
    //删除操作
    public void deleteById(Integer id){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
    try {
        connection= JDBCTools.getConnection();
        String sql="delete from student where id=?";
        preparedStatement=connection.prepareStatement(sql);
        preparedStatement.setInt(1,id);
        preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection,preparedStatement,null);
        }
    }
    //根据id查询
    public Student findById(Integer id){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        Student student=null;
        try {
            connection= JDBCTools.getConnection();
            String sql="select * from student where id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Integer id2=resultSet.getInt(1);
                String name=resultSet.getString(2);
                Integer numsex=resultSet.getInt(3);
                Integer age=resultSet.getInt(4);
                String password=resultSet.getString(5);
                student = new Student(id2, name, numsex, age, password);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection,preparedStatement,resultSet);
        }
        return student;
    }
    //更新操作
    public void update(Integer id,String name,Integer numsex,
                       Integer age,String password){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            connection= JDBCTools.getConnection();
            String sql="update student set name=?,numsex=?,age=?,password=? where id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setInt(2,numsex);
            preparedStatement.setInt(3,age);
            preparedStatement.setString(4,password);
            preparedStatement.setInt(5,id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection,preparedStatement,null);
        }
    }
}

StudentServlet：

import com.javaweb.entity.Student;
import com.javaweb.repository.StudentRepository;
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 java.io.IOException;
import java.util.List;
@WebServlet("/student")
public class StudentServlet extends HttpServlet {
    //调用StudentRepository中的增删改查方法
    private StudentRepository studentRepository=new StudentRepository();
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //判断前端传来的标记，以此执行相对应的增删改查操作
        String method=req.getParameter("method");
        if (method==null){
            method="findAll";
        }
        switch (method){
            case "findAll"://查询所有数据
                List<Student> list = studentRepository.findAll();//调用StudentRepository中的findAll()方法
                req.setAttribute("list",list);//存入request中
                req.getRequestDispatcher("index.jsp").forward(req,resp);//转发到index.jsp中
            case "delete"://删除操作
                String idStr=req.getParameter("id");
                Integer id=Integer.parseInt(idStr);
                studentRepository.deleteById(id);//根据id删除
                resp.sendRedirect("/student");
                break;
            case "findById":
                idStr=req.getParameter("id");
                id=Integer.parseInt(idStr);
                req.setAttribute("student",studentRepository.findById(id));
                req.getRequestDispatcher("update.jsp").forward(req,resp);
                break;
            case "add":
                req.getRequestDispatcher("add.jsp").forward(req,resp);
        }
    }
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");//防止中文乱码
        String method=req.getParameter("method");
        switch (method){
            case "add"://添加操作
                //获取前端传来的数据
                String idStr=req.getParameter("id");
                String name=req.getParameter("name");
                String numsexStr=req.getParameter("numsex");
                String ageStr=req.getParameter("age");
                String password=req.getParameter("password");
                Integer id=Integer.parseInt(idStr);//转化为整型
                Integer numsex=Integer.parseInt(numsexStr);
                Integer age=Integer.parseInt(ageStr);
                studentRepository.add(id,name,numsex,age,password);//调用add方法
                break;
            case "update"://更新操作
                idStr=req.getParameter("id");
                name=req.getParameter("name");
                numsexStr=req.getParameter("numsex");
                ageStr=req.getParameter("age");
                password=req.getParameter("password");
                id=Integer.parseInt(idStr);
                numsex=Integer.parseInt(numsexStr);
                age=Integer.parseInt(ageStr);
                studentRepository.update(id, name, numsex, age, password);
                break;
        }
        resp.sendRedirect("/student");//重定向到index.jsp页面
    }
}

JDBCTools：

import java.sql.*;
public class JDBCTools {
    private static Connection connection;
    private static String url="jdbc:mysql://localhost:3306/he?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT";
    private static String user="root";//用户名
    private static String pass="123z";//密码
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        try {
            connection= DriverManager.getConnection(url,user,pass);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if (connection!=null) {
                connection.close();
            }
            if (statement!=null){
                statement.close();
            }
            if (resultSet!=null){
                resultSet.close();
            }
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
}

其中，web.xml创建之后不曾配置过，所以不贴代码了

add.jsp：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<form action="/student" method="post">
    编号: <input type="text" name="id"/><br/>
    姓名：<input type="text" name="name"/><br/>
    性别：<input type="text" name="numsex"/><br/>
    年龄：<input type="text" name="age"/><br/>
    密码：<input type="password" name="password"/><br/>
    <input type="hidden" name="method" value="add"/>
    <input type="submit" value="提交"/>
</form>
</body>
</html>

index.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>
      <h1>学生管理系统</h1>
        <div ><a href="/student?method=add">添加</a></div>
  <table>
    <tr>
      <th>编号</th>
      <th>姓名</th>
      <th>性别</th>
      <th>年龄</th>
      <th>密码</th>
      <th>操作</th>
    </tr>
    <c:forEach items="${list}" var="student">
      <tr>
        <td>${student.id}</td>
        <td>${student.name}</td>
        <td>${student.numsex}</td>
        <td>${student.age}</td>
        <td>${student.password}</td>
        <td>
          <a href="/student?method=delete&id=${student.id}">删除</a>
          <a href="/student?method=findById&id=${student.id}">修改</a>
        </td>
      </tr>
    </c:forEach>
  </table>
  </body>
</html>

update.jsp：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<form action="/student" method="post">
    编号: <input type="text" name="id" value="${student.id}" readonly/><br/>
    姓名：<input type="text" name="name" value="${student.name}"/><br/>
    性别：<input type="text" name="numsex" value="${student.numsex}"/><br/>
    年龄：<input type="text" name="age" value="${student.age}"/><br/>
    密码：<input type="password" name="password" value="${student.password}"/><br/>
    <input type="hidden" name="method" value="update"/>
    <input type="submit" value="修改"/>
</form>
</body>
</html>