jsp mysql 实现客户端简单数据的修改和删除

dao.impl


import com.mchange.v2.c3p0.ComboPooledDataSource;
import day13.dao.StudentDao;
import day13.entity.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;

public class StudentDaoImpl implements StudentDao {   //这两句一定记住 实现
    private DataSource ds =  new ComboPooledDataSource();
    private QueryRunner qr = new QueryRunner(ds);  //这两句一定记住

    public List<Student> selectAll(){
        List<Student> studentList = null;
        try {
            String sql ="select * from test1";
            studentList = qr.query(sql,new BeanListHandler<Student>(Student.class));  //小写 query  //这两句一定记住
        } catch (Exception e) {
            e.printStackTrace();
        }
        return studentList;
    }

    public Student selectByPid(int pid){
        Student student = null;
        try {
            String sql = "select * from test1 where pid = ?";
            student = qr.query(sql,new BeanHandler<Student>(Student.class),pid);   //Student.class 是什么？    Student 的 class 文件
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return student;
    }

    @Override
    public int updateStudent(Student student) {
        int num = 0;

        try {
            String sql = "update test1 set pname =?,page=? where pid = ?";
            Object[] objects = {student.getPname(),student.getPage(),student.getPid()};
            num = qr.update(sql,objects);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int deleteById(int pid) {
        int num = 0;
        String sql = "delete from test1 where pid = ?";
        try {
            num = qr.update(sql,pid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;

    }

    @Override
    public int addStudent(Student student) {
        int num = 0;
        try {
            String sql = "insert into test1(pname,page)values(?,?)";
            Object[] objects = {student.getPname(),student.getPage()};
            num = qr.update(sql,objects);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }
}

dao


import day13.entity.Student;

import java.util.List;

public interface StudentDao {
    List<Student> selectAll();  //仅定义类型（一定记住的）
    Student selectByPid(int pid);
    int updateStudent(Student student);
    int deleteById(int pid);
    int addStudent(Student student);
}

entity


import java.io.Serializable;

public class Student implements Serializable {  //这句一定记住 实现
    private int pid;        //这句一定记住 两构造 get set toString
    private int page;   // int  型 所以后面都要转型
    private String pname;

service.ServiceImpl


import day13.dao.StudentDao;
import day13.dao.impl.StudentDaoImpl;
import day13.entity.Student;
import day13.service.StudentService;

import java.util.List;

public class StudentServiceImpl implements StudentService {  //这两句一定记住  实现
    private StudentDao studentDao = new StudentDaoImpl();  //这句一定记住  实例后，才能使用

    public List<Student> selectAll(){
        return studentDao.selectAll();
    }

    @Override
    public Student selectByPid(int pid) {
        return studentDao.selectByPid(pid);  //对 Dao 的 ？？
    }

    @Override
    public int updateStudent(Student student) {
        return studentDao.updateStudent(student);
    }

    @Override
    public int deleteById(int pid) {
        return studentDao.deleteById(pid);
    }

    @Override
    public int addStudent(Student student) {
        return studentDao.addStudent(student);
    }

}

servlet


import day13.entity.Student;
import day13.service.ServiceImpl.StudentServiceImpl;
import day13.service.StudentService;

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(name = "studentServlet",urlPatterns = "/studentServlet")
public class StudentServlet extends HttpServlet {
    private StudentService studentService ;

    @Override
    public void init() throws ServletException {
        studentService = new StudentServiceImpl();  //接口
    }

    @Override  //增加修改id功能
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("text/html;charset=UTF-8");
        String flag = req.getParameter("flag");
        //查找
        if ("goUpdate".equals(flag)){
            String pidStr = req.getParameter("pid");
            Student student = studentService.selectByPid(Integer.parseInt(pidStr));
            req.setAttribute("student",student);
            req.getRequestDispatcher("update.jsp").forward(req,resp);

            //修改
        }else if("update".equals(flag)){
            //获取前端传递的参数
            String pid = req.getParameter("pid");
            String pname = req.getParameter("pname");
            String page = req.getParameter("page");
            //调用service 层 的方法
            Student student  = new Student(Integer.parseInt(pid),Integer.parseInt(page),pname);
            int num =  studentService.updateStudent(student);
            //成功
            if(num>0){
                //跳转回首页
                resp.sendRedirect("index.jsp");
            }else{
                //重定向来传递参数

                resp.sendRedirect("studentServlet?flag=goUpdate&pid="+pid);
            }
//            删除
        }else if ("delete".equals(flag)){
            String pidStr = req.getParameter("pid");  //获取前端传递的参数
            int num = studentService.deleteById(Integer.parseInt(pidStr));  //调用 service 方法
            resp.sendRedirect("index.jsp");  //删除成功，跳转到首页

//          增加页面
        }else if("goAdd".equals(flag)){
            req.getRequestDispatcher("add.jsp").forward(req,resp);
//            增加功能
        }else if("add".equals(flag)){
            String pname = req.getParameter("pname");
            int page = Integer.parseInt(req.getParameter("page"));

            Student student = new Student();
            student.setPname(pname);
            student.setPage(page);  //干啥的？
            int num = studentService.addStudent(student);
            if(num>0){
                //跳转回首页
                resp.sendRedirect("index.jsp");
            }else{
                //重定向来传递参数
                resp.sendRedirect("studentServlet?flag=goAdd");
            }
        }

        else {
                //显示
             List<Student> studentList =studentService.selectAll();
             req.setAttribute("studentList",studentList);
             req.getRequestDispatcher("index.jsp").forward(req,resp);
        }
    }

}

add.jsp


<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>添加</title>
</head>
<body>
<form method="post" action="studentServlet">
    <input type="hidden" name="flag" value="add">  //提交后 ，flag 成 add
    <p><input type="text" name="pname" value=""></p>
    <p><input type="text" name="page" value=""></p>
    <input type="submit" value="增加">

</form>
</body>
</html>

index.jsp

<%@ page import="java.util.List" %>
<%@ page import="day13.entity.Student" %>

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <title>首页</title>
  </head>
  <body>
  <%
      List<Student> studentList = null;
      if (request.getAttribute("studentList")==null){
          request.getRequestDispatcher("studentServlet").forward(request,response);
          return;
      }else {
          studentList = (List<Student>) request.getAttribute("studentList");
          System.out.println();
      }

      %>

  <%--增加页面--%>
  <h1 id="tv_h1" onclick="showInfo()">增加</h1>
  <script>
      function showInfo() {
          window.location.href="studentServlet?flag=goAdd"
      }
  </script>

<%--  主页面--%>
<table border="1px" width="700px" align="center">
  <tr>
      <td>编号</td>
      <td>姓名</td>
      <td>年龄</td>
  </tr>

<%
    for (int i = 0 ;i<studentList.size();i++){
%>
      <tr>
        <td><%=studentList.get(i).getPid()%></td>
        <td><%=studentList.get(i).getPname()%></td>
         <td><%=studentList.get(i).getPage()%></td>
          <td><a href="studentServlet?pid=<%=studentList.get(i).getPid()%>&flag=goUpdate">修改</a></td>
          <td><a href="studentServlet?pid=<%=studentList.get(i).getPid()%>&flag=delete">删除</a></td>
      </tr>

                  <%
                      }
                  %>

              </table>

  </body>
</html>

update.jsp

<%@ page import="day13.entity.Student" %>

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>更新</title>
</head>
<body>

<%
    Student student = (Student) request.getAttribute("student");
%>

<form method="post" action="studentServlet">
<%--    jsp <%%> 内不允许有符号。所以变量才用 <%%> --%>
<%--    <p><input type="hidden" name="flag" value="update"></p>--%>
    <input type="hidden" name="flag" value="update">
    <input type="hidden" name="pid" value="<%=student.getPid()%>">  <%--困恼20分钟的地方--%>
    <p><input type="text" name="pname" value="<%=student.getPname()%>"></p>
    <p><input type="text" name="page" value="<%=student.getPage()%>"></p>
    <p><input type="submit" value="提交"></p>
</form>

</body>
</html>