devbox
小蓝xlanll
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

击穿JDBC 例子详解 JDBC+Servlet +jsp增删改查案例（三）全_jsp servlet jdbc mysql查询示列

作者：小蓝xlanll | 2024-05-15 23:14:15

赞

踩

jsp servlet jdbc mysql查询示列

用Servlset + JSP + JDBC 实现增删改查

 

先看看结构

entity是一个学生类，repository是一个与数据库交互的类，servlet与用户交互

关于idea里面连接数据库遇到的时区问题

好了进入正题

先看一下效果

 准备一个学生类（展示少量代码）

  1. public class Student {
  2.     private Integer id;
  3.     private String username;
  4.  
  5.     public Student(Integer id, String username) {
  6.         this.id = id;
  7.         this.username = username;
  8.     }

 准备一个工具类DBUtil用来快捷处理数据库连接

  1. public class DBUtil {
  2.     //静态方法更方便
  3.     static Connection connection;
  4.     static String url = "jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
  5.     static String user = "root";
  6.     static String password = "123456";
  7.  
  8.     static{
  9.         try {
  10.             Class.forName("com.mysql.cj.jdbc.Driver");
  11.         } catch (ClassNotFoundException e) {
  12.             e.printStackTrace();
  13.         }
  14.     }
  15.  
  16.     public static Connection getConnection(){
  17.         try {
  18.             connection = DriverManager.getConnection(url, user, password);
  19.         } catch (SQLException e) {
  20.             e.printStackTrace();
  21.         }
  22.         return connection;
  23.     }
  24.  
  25.     public static void close(ResultSet resultSet, Statement statement,Connection connection){
  26.         if (resultSet != null) {
  27.             try {
  28.                 resultSet.close();
  29.             } catch (SQLException e) {
  30.                 e.printStackTrace();
  31.             }
  32.         }
  33.         if (statement != null) {
  34.             try {
  35.                 statement.close();
  36.             } catch (SQLException e) {
  37.                 e.printStackTrace();
  38.             }
  39.         }
  40.         if (connection != null){
  41.             try {
  42.                 connection.close();
  43.             } catch (SQLException e) {
  44.                 e.printStackTrace();
  45.             }
  46.         }
  47.     }
  48. }

 这是主页面，用到了forech标签

  1. <%@ page contentType="text/html;charset=UTF-8" language="java" %>
  2. <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
  3. <html>
  4.   <head>
  5.     <title>$Title$</title>
  6.   </head>
  7.   <body>
  8.     <h1>学生管理系统</h1>
  9.     <table>
  10.       <tr>
  11.         <th>编号</th>
  12.         <th>姓名</th>
  13.       </tr>
  14.       <c:forEach items="${list}" var="student">
  15.         <tr>
  16.           <td>${student.id}</td>
  17.           <td>${student.username}</td>
  18.           <td><a href="/student?method=deleteBId&id=${student.id}">删除</a></td>
  19.           <td><a href="/student?method=findBId&id=${student.id}">修改</a></td>
  20.         </tr>
  21.       </c:forEach>
  22.     </table>
  23.   <h3><td><a href="add.jsp">添加</a></td></h3>
  24.   </body>
  25. </html>

 这是添加页面add.jsp

  1. <%@ page contentType="text/html;charset=UTF-8" language="java" %>
  2. <html>
  3. <head>
  4.     <title>Title</title>
  5. </head>
  6. <body>
  7.     <form action="/student" method="post">
  8.         编号<input type="text" name="id"><br/>
  9.         姓名<input type="text" name="username">
  10.         <input type="hidden" name="method" value="add">
  11.         <input type="submit" value="提交">
  12.     </form>
  13. </body>
  14. </html>

这是修改操作

  1. <%@ page contentType="text/html;charset=UTF-8" language="java" %>
  2. <html>
  3. <head>
  4.     <title>Title</title>
  5. </head>
  6. <body>
  7.     <form action="/student" method="post">
  8.         编号<input type="text" name="id" value="${student.id}"><br>
  9.         姓名<input type="text" name="username">
  10.         <input type="hidden" name="method" value="update">
  11.         <input type="submit" value="修改">
  12.     </form>
  13. </body>
  14. </html>

 注意隐藏标签，form表单method都设置是post请求，那么怎么区分呢add和update呢，设置隐藏标签，这样就能区分了

主要要看StudentServlet类，根据这个类去回看jsp，因为他接收jsp传过来的method再去调用数据库操作

  1. @WebServlet("/student")
  2. public class StudentServlet extends HttpServlet {
  3.     StudentRepository studentRepository = new StudentRepository();
  4.     @Override
  5.     protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
  6.         String method = req.getParameter("method");
  7.         switch (method){
  8.             case "add":
  9.                 req.setCharacterEncoding("UTF-8");
  10.                 String idStr = req.getParameter("id");
  11.                 String username = req.getParameter("username");
  12.                 Integer id = Integer.parseInt(idStr);
  13.                 studentRepository.add(id,username);
  14.                 break;
  15.             case "update":
  16.                 String idStr1 = req.getParameter("id");
  17.                 String username1 = req.getParameter("username");
  18.                 Integer id1 = Integer.parseInt(idStr1);
  19.                 studentRepository.update(id1,username1);
  20.                 resp.sendRedirect("/student");
  21.         }
  22.  
  23.     }
  24.  
  25.     @Override
  26.     protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
  27.         String method = req.getParameter("method");
  28.         if (method == null) {
  29.             method = "findAll";
  30.         }
  31.         switch (method){
  32.             case "findAll":
  33.                 studentRepository = new StudentRepository();
  34.                 List<Student> list = studentRepository.findAll();
  35.                 req.setAttribute("list",list);
  36.                 req.getRequestDispatcher("index.jsp").forward(req,resp);
  37.                 break;
  38.             case "deleteBId":
  39.                 String idStr = req.getParameter("id");
  40.                 Integer id = Integer.parseInt(idStr);
  41.                 studentRepository.delete(id);
  42.                 resp.sendRedirect("/student");
  43.                 break;
  44.             case "findBId":
  45.                 String idStr1 = req.getParameter("id");
  46.                 Integer id1 = Integer.parseInt(idStr1);
  47.                 Student student = studentRepository.find(id1);
  48.                 req.setAttribute("student",student);
  49.                 req.getRequestDispatcher("update.jsp").forward(req,resp);
  50.                 break;
  51.         }
  52.     }
  53. }

 findBId 是在index.jsp里面点击修改，把id参数传了过来，调用find方法把查询结果放在student里，再转到update.jsp，这样编号对应的才是我们要修改的

repository类

  1. public class StudentRepository {
  2.     PreparedStatement preparedStatement = null;
  3.     ResultSet resultSet = null;
  4.     Connection connection = null;
  5.     Student student = null;
  6.     List<Student> list = new ArrayList<>();
  7.     /**
  8.      * 查询
  9.      * @return
  10.      */
  11.     public List<Student> findAll() {
  12.         try {
  13.             Connection connection = DBUtil.getConnection();
  14.             String sql = "select * from t_user";
  15.             //取得数据库操作对象
  16.             preparedStatement = connection.prepareStatement(sql);
  17.  
  18.             resultSet = preparedStatement.executeQuery(sql);
  19.             //依次取出
  20.             while (resultSet.next()) {
  21.                 Integer id = resultSet.getInt(1);
  22.                 String username = resultSet.getString(2);
  23.                 student = new Student(id,username);
  24.                 list.add(student);
  25.             }
  26.         } catch (SQLException e) {
  27.             e.printStackTrace();
  28.         } finally {
  29.             //关闭流
  30.             DBUtil.close(resultSet,preparedStatement,connection);
  31.         }
  32.         return list;
  33.     }
  34.  
  35.     /**
  36.      * 添加
  37.      * @param id
  38.      * @param username
  39.      */
  40.     public void add(Integer id, String username){
  41.         try {
  42.             connection = DBUtil.getConnection();
  43.             String sql = "insert into t_user(id,username) values (?,?)";
  44.             //取得数据库操作对象
  45.             preparedStatement = connection.prepareStatement(sql);
  46.             preparedStatement.setInt(1,id);
  47.             preparedStatement.setString(2,username);
  48.             preparedStatement.executeUpdate();
  49.  
  50.         } catch (SQLException e) {
  51.             e.printStackTrace();
  52.         } finally {
  53.             //关闭流
  54.              DBUtil.close(resultSet,preparedStatement,connection);
  55.         }
  56.     }
  57.  
  58.     /**
  59.      * 删除
  60.      * @param id
  61.      */
  62.     public void delete(Integer id){
  63.         try{
  64.             connection = DBUtil.getConnection();
  65.             String sql = "delete from t_user where id = ?";
  66.             //取得数据库操作对象
  67.             preparedStatement = connection.prepareStatement(sql);
  68.             preparedStatement.setInt(1,id);
  69.             preparedStatement.executeUpdate();
  70.         } catch (Exception e) {
  71.             e.printStackTrace();
  72.         }finally {
  73.             DBUtil.close(resultSet,preparedStatement,connection);
  74.         }
  75.     }
  76.  
  77.     /**
  78.      * 将需要修改的信息传给student对象
  79.      * @param id
  80.      */
  81.     public Student find(Integer id){
  82.         Student student = null;
  83.         try{
  84.             connection = DBUtil.getConnection();
  85.             String sql = "select * from t_user where id = ?";
  86.             //取得数据库操作对象
  87.             preparedStatement = connection.prepareStatement(sql);
  88.             preparedStatement.setInt(1,id);
  89.             resultSet = preparedStatement.executeQuery();
  90.             //一个结果不用遍历
  91.             Integer id1 = resultSet.getInt(1);
  92.             String username1 = resultSet.getString(2);
  93.             student = new Student(id1,username1);
  94.         } catch (Exception e) {
  95.             e.printStackTrace();
  96.         }finally {
  97.             DBUtil.close(resultSet,preparedStatement,connection);
  98.         }
  99.         return student;
  100.     }
  101.  
  102.     /**
  103.      * 修改
  104.      */
  105.     public void update(Integer id, String username){
  106.         try{
  107.             connection = DBUtil.getConnection();
  108.             String sql = "update t_user set username=? where id=?";
  109.             //取得数据库操作对象
  110.             preparedStatement = connection.prepareStatement(sql);
  111.             preparedStatement.setString(1,username);
  112.             preparedStatement.setInt(2,id);
  113.             preparedStatement.executeUpdate();
  114.         } catch (Exception e) {
  115.             e.printStackTrace();
  116.         }finally {
  117.             DBUtil.close(resultSet,preparedStatement,connection);
  118.         }
  119.     }
  120. }

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小蓝xlanll/article/detail/575436
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号