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

实验五~JDBC数据库访问与DAO设计模式

作者：AllinToyou | 2024-05-15 23:00:58

赞

踩

实验五~JDBC数据库访问与DAO设计模式

1. 使用传统JDBC方法与Model 1模型通过JSP页面访问数据库。

【步骤1】在MySQL数据库test中创建books表，其结构如下：

创建数据库MySQL代码与插入数据代码

  1. drop database if exists web_test;
  2. create database web_test character set utf8mb4;
  3.  
  4. use web_test;
  5.  
  6. create table books(
  7.     book_id varchar(10) not null,
  8.     title varchar(50),
  9.     author varchar(20),
  10.     publisher varchar(40),
  11.     price float
  12. );
  13. insert into books values ('204', 'Head First Servlets & JSP', 'Bryan Basham', '中国电力出版社', 98);
  14. insert into books values ('201', 'Servlets与JSP核心教程', 'Hall Marty', '清华大学出版社', 45);
  15. insert into books values ('202', 'Tomcat与Java Web 开发技术详解', '孙卫琴', '机械工业出版社', 45);
  16. insert into books values ('203', 'JSP 应用开发技术', '柳永坡', '人民邮电出版社', 52);
  17. insert into books values ('205', 'J2EE 1.4 编程指南', 'Spielman Sue', '电子工业出版社', 68);

 【步骤2】创建模型：编写名称为BookBean.java的JavaBean用于封装图书的信息；编写BookDAO.java，采用传统JDBC方法创建数据库连接，对图书信息进行查询。

DBUtil的代码 

  1. package org.example.utils;
  2.  
  3. import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
  4.  
  5. import javax.sql.DataSource;
  6. import java.sql.Connection;
  7. import java.sql.ResultSet;
  8. import java.sql.SQLException;
  9. import java.sql.Statement;
  10. public class DBUtil {
  11.     private static volatile DataSource DS;
  12.     private static DataSource getDataSource() {
  13.         if (DS == null) {
  14.             synchronized (DBUtil.class) {
  15.                 if (DS == null) {
  16.                     MysqlDataSource dataSource = new MysqlDataSource();
  17.                     dataSource.setURL("jdbc:mysql://localhost:3306/web_test");
  18.                     dataSource.setUser("root");//账号
  19.                     dataSource.setPassword("142516");
  20.                     dataSource.setUseSSL(false);//不设置不会出现问题，但会出现红色警告
  21.                     //数据库的编码格式，可以设置为UTF-8，或是utf8
  22.                     dataSource.setCharacterEncoding("utf8");
  23.                     DS = dataSource;
  24.                 }
  25.             }
  26.         }
  27.         return DS;
  28.     }
  29.     //获取数据库连接
  30.     public static Connection getConnection() {
  31.         try {
  32.             return getDataSource().getConnection();
  33.         } catch (SQLException e) {
  34.             throw new RuntimeException("获取数据库连接异常", e);
  35.         }
  36.     }
  37.     //释放连接
  38.     public static void close(java.sql.Connection c, Statement s, ResultSet rs) {
  39.         try {
  40.             if(rs != null) rs.close();
  41.             if(s != null) s.close();
  42.             if(c != null) c.close();
  43.         } catch (SQLException e) {
  44.             throw new RuntimeException("jdbc释放资源出错", e);
  45.         }
  46.     }
  47. }

上面这部分需要改一下

BookBean代码 

  1. package org.example.beans;
  2.  
  3. public class BookBean {
  4.     private String bookId;
  5.     private String title;
  6.     private String author;
  7.     private String publisher;
  8.     private double price;
  9.  
  10.     public String getBookId() {
  11.         return bookId;
  12.     }
  13.  
  14.     public void setBookId(String bookId) {
  15.         this.bookId = bookId;
  16.     }
  17.  
  18.     public String getTitle() {
  19.         return title;
  20.     }
  21.  
  22.     public void setTitle(String title) {
  23.         this.title = title;
  24.     }
  25.  
  26.     public String getAuthor() {
  27.         return author;
  28.     }
  29.  
  30.     public void setAuthor(String author) {
  31.         this.author = author;
  32.     }
  33.  
  34.     public String getPublisher() {
  35.         return publisher;
  36.     }
  37.  
  38.     public void setPublisher(String publisher) {
  39.         this.publisher = publisher;
  40.     }
  41.  
  42.     public double getPrice() {
  43.         return price;
  44.     }
  45.  
  46.     public void setPrice(double price) {
  47.         this.price = price;
  48.     }
  49. }

BookDao代码 

  1. package org.example.dao;
  2.  
  3. import org.example.beans.BookBean;
  4. import org.example.utils.DBUtil;
  5.  
  6. import java.sql.Connection;
  7. import java.sql.PreparedStatement;
  8. import java.sql.ResultSet;
  9. import java.util.ArrayList;
  10. import java.util.List;
  11.  
  12. public class BookDao {
  13.     public static List<BookBean> queryBooks() {
  14.         Connection c = null;
  15.         PreparedStatement ps = null;
  16.         ResultSet rs = null;
  17.         try {
  18.             c = DBUtil.getConnection();
  19.             String sql = "select * from books";
  20.             ps = c.prepareStatement(sql);
  21.             rs = ps.executeQuery();
  22.             List<BookBean> books = new ArrayList<>();
  23.             while (rs.next()) {
  24.                 BookBean b = new BookBean();
  25.                 b.setBookId(rs.getString("book_id"));
  26.                 b.setAuthor(rs.getString("author"));
  27.                 b.setTitle(rs.getString("title"));
  28.                 b.setPublisher(rs.getString("publisher"));
  29.                 b.setPrice(rs.getDouble("price"));
  30.                 books.add(b);
  31.             }
  32.             return books;
  33.         } catch (Exception e) {
  34.             throw new RuntimeException("查询books出错", e);
  35.         } finally {
  36.             DBUtil.close(c, ps, rs);
  37.         }
  38.     }
  39. }

【步骤3】创建兼具视图与控制器功能的JSP页面：编写displayBooks.jsp页面，使用<jsp:useBean>动作调用BookDAO查询所有图书的信息，以表格的形式显示所有图书的书号、书名、作者、价格和出版社名称。

displayBooks.jsp 

  1. <%@ page import="java.util.*" %>
  2. <%@ page import="org.example.beans.*" %>
  3. <%@ page contentType="text/html;charset=utf-8" %>
  4. <jsp:useBean id="book" class="org.example.dao.BookDao" scope="session"/>
  5. <html>
  6. <head>
  7.     <title>displayBooks</title>
  8. <style>
  9.     td{
  10.         text-align: center;
  11.     }
  12. </style>
  13. </head>
  14. <body>
  15.     <table width="500" height="256" border="1" align="center">
  16.         <tr>
  17.         <th scope="col">bookid</th>
  18.         <th scope="col">title</th>
  19.         <th scope="col">author</th>
  20.         <th scope="col">publisher</th>
  21.         <th scope="col">price</th>
  22.     </tr>
  23.     <%
  24.         List<BookBean> books = book.queryBooks();
  25.         int len = books.size();
  26.         for (int i = 0; i < len; i++) {
  27.             String book_id = books.get(i).getBookId();
  28.             String title = books.get(i).getTitle();
  29.             String author = books.get(i).getAuthor();
  30.             String publisher = books.get(i).getPublisher();
  31.             double price = books.get(i).getPrice();
  32.             %>
  33.             <tr>
  34.                 <td><%=book_id%> </td>
  35.                 <td><%=title%></td>
  36.                 <td><%=author%></td>
  37.                 <td><%=publisher%></td>
  38.                 <td><%=price%></td>
  39.             </tr>
  40.         <% } %>
  41.     </table>
  42. </body>
  43. </html>

 2. 基于实验内容1创建的数据库表和模型，采用MVC设计模式，通过数据源和DAO对象访问数据库。其中JavaBeans实现模型与数据库访问与操作，Servlet实现控制器，JSP页面实现视图。

  • 模型包括2个JavaBean：BookBean用于存放图书信息，BookDAO用于访问数据库。

 BookBean.java(BookBean与上面的BookBean一样)

  1. package org.example.beans;
  2.  
  3. import java.io.Serializable;
  4.  
  5. /**
  6.  * Created with IntelliJ IDEA.
  7.  * Description:
  8.  * User: Li_yizYa
  9.  * Date: 2023—04—24
  10.  * Time: 17:57
  11.  */
  12. //@SuppressWarnings("serial")
  13. public class BookBean {
  14.     private String bookId;
  15.     private String title;
  16.     private String author;
  17.     private String publisher;
  18.     private double price;
  19.  
  20.     public String getBookId() {
  21.         return bookId;
  22.     }
  23.  
  24.     public void setBookId(String bookId) {
  25.         this.bookId = bookId;
  26.     }
  27.  
  28.     public String getTitle() {
  29.         return title;
  30.     }
  31.  
  32.     public void setTitle(String title) {
  33.         this.title = title;
  34.     }
  35.  
  36.     public String getAuthor() {
  37.         return author;
  38.     }
  39.  
  40.     public void setAuthor(String author) {
  41.         this.author = author;
  42.     }
  43.  
  44.     public String getPublisher() {
  45.         return publisher;
  46.     }
  47.  
  48.     public void setPublisher(String publisher) {
  49.         this.publisher = publisher;
  50.     }
  51.  
  52.     public double getPrice() {
  53.         return price;
  54.     }
  55.  
  56.     public void setPrice(double price) {
  57.         this.price = price;
  58.     }
  59. }

 BookDao.java（BookDao在上面的java文件中额外加了几个方法）

  1. package org.example.dao;
  2.  
  3. import org.example.beans.BookBean;
  4. import org.example.utils.DBUtil;
  5.  
  6. import java.sql.Connection;
  7. import java.sql.PreparedStatement;
  8. import java.sql.ResultSet;
  9. import java.util.ArrayList;
  10. import java.util.List;
  11.  
  12. /**
  13.  * Created with IntelliJ IDEA.
  14.  * Description:
  15.  * User: Li_yizYa
  16.  * Date: 2023—04—24
  17.  * Time: 18:13
  18.  */
  19. public class BookDao {
  20.     public static List<BookBean> queryBooks() {
  21.         Connection c = null;
  22.         PreparedStatement ps = null;
  23.         ResultSet rs = null;
  24.         try {
  25.             c = DBUtil.getConnection();
  26.             String sql = "select * from books";
  27.             ps = c.prepareStatement(sql);
  28.             rs = ps.executeQuery();
  29.             List<BookBean> books = new ArrayList<>();
  30.             while (rs.next()) {
  31.                 BookBean b = new BookBean();
  32.                 b.setBookId(rs.getString("book_id"));
  33.                 b.setAuthor(rs.getString("author"));
  34.                 b.setTitle(rs.getString("title"));
  35.                 b.setPublisher(rs.getString("publisher"));
  36.                 b.setPrice(rs.getDouble("price"));
  37.                 books.add(b);
  38.             }
  39.             return books;
  40.         } catch (Exception e) {
  41.             throw new RuntimeException("查询books出错", e);
  42.         } finally {
  43.             DBUtil.close(c, ps, rs);
  44.         }
  45.     }
  46.     //根据序号查询
  47.     public static BookBean queryById(String id) {
  48.         Connection c = null;
  49.         PreparedStatement ps = null;
  50.         ResultSet rs = null;
  51.         BookBean b = null;
  52.         try {
  53.             c = DBUtil.getConnection();
  54.             String sql = "select * from books where book_id = ?";
  55.             ps = c.prepareStatement(sql);
  56.             ps.setString(1, id);
  57.             rs = ps.executeQuery();
  58.             while (rs.next()) {
  59.                 b = new BookBean();
  60.                 b.setBookId(rs.getString("book_id"));
  61.                 b.setAuthor(rs.getString("author"));
  62.                 b.setTitle(rs.getString("title"));
  63.                 b.setPublisher(rs.getString("publisher"));
  64.                 b.setPrice(rs.getDouble("price"));
  65.             }
  66.             return b;
  67.         } catch (Exception e) {
  68.             throw new RuntimeException("查询books出错", e);
  69.         } finally {
  70.             DBUtil.close(c, ps, rs);
  71.         }
  72.     }
  73.  
  74.     //根据书名查询
  75.     public static BookBean queryByTitle(String title) {
  76.         Connection c = null;
  77.         PreparedStatement ps = null;
  78.         ResultSet rs = null;
  79.         BookBean b = null;
  80.         try {
  81.             c = DBUtil.getConnection();
  82.             String sql = "select * from books where title = ?";
  83.             ps = c.prepareStatement(sql);
  84.             ps.setString(1, title);
  85.             rs = ps.executeQuery();
  86.             while (rs.next()) {
  87.                 b = new BookBean();
  88.                 b.setBookId(rs.getString("book_id"));
  89.                 b.setAuthor(rs.getString("author"));
  90.                 b.setTitle(rs.getString("title"));
  91.                 b.setPublisher(rs.getString("publisher"));
  92.                 b.setPrice(rs.getDouble("price"));
  93.             }
  94.             return b;
  95.         } catch (Exception e) {
  96.             throw new RuntimeException("查询books出错", e);
  97.         } finally {
  98.             DBUtil.close(c, ps, rs);
  99.         }
  100.     }
  101.     //根据作者查询
  102.     public static BookBean queryByAuthor(String author) {
  103.         Connection c = null;
  104.         PreparedStatement ps = null;
  105.         ResultSet rs = null;
  106.         BookBean b = null;
  107.         try {
  108.             c = DBUtil.getConnection();
  109.             String sql = "select * from books where author = ?";
  110.             ps = c.prepareStatement(sql);
  111.             ps.setString(1, author);
  112.             rs = ps.executeQuery();
  113.             while (rs.next()) {
  114.                 b = new BookBean();
  115.                 b.setBookId(rs.getString("book_id"));
  116.                 b.setAuthor(rs.getString("author"));
  117.                 b.setTitle(rs.getString("title"));
  118.                 b.setPublisher(rs.getString("publisher"));
  119.                 b.setPrice(rs.getDouble("price"));
  120.             }
  121.             return b;
  122.         } catch (Exception e) {
  123.             throw new RuntimeException("查询books出错", e);
  124.         } finally {
  125.             DBUtil.close(c, ps, rs);
  126.         }
  127.     }
  128.     //插入数据
  129.     public static int insert(BookBean book) {
  130.         Connection c = null;
  131.         PreparedStatement ps = null;
  132.         try {
  133.             c = DBUtil.getConnection();
  134.             String sql = "insert into books values (?, ?, ?, ?, ?);";
  135.             ps = c.prepareStatement(sql);
  136.             ps.setString(1, book.getBookId());
  137.             ps.setString(2, book.getTitle());
  138.             ps.setString(3, book.getAuthor());
  139.             ps.setString(4, book.getPublisher());
  140.             ps.setDouble(5, book.getPrice());
  141.             return ps.executeUpdate();
  142.         } catch (Exception e) {
  143.             throw new RuntimeException("插入books出错", e);
  144.         } finally {
  145.             DBUtil.close(c, ps, null);
  146.         }
  147.     }
  148. }

  • 控制器包括2个Servlet：BookQueryServlet根据请求参数查询图书信息、BookInsertServlet用来向数据库中插入一条图书信息。

BookQueryServlet.java

  1. package org.example.servlet;
  2.  
  3. import org.example.beans.BookBean;
  4. import org.example.dao.BookDao;
  5.  
  6. import javax.servlet.RequestDispatcher;
  7. import javax.servlet.ServletException;
  8. import javax.servlet.annotation.WebServlet;
  9. import javax.servlet.http.HttpServlet;
  10. import javax.servlet.http.HttpServletRequest;
  11. import javax.servlet.http.HttpServletResponse;
  12. import java.io.IOException;
  13.  
  14. /**
  15.  * Created with IntelliJ IDEA.
  16.  * Description:
  17.  * User: Li_yizYa
  18.  * Date: 2023—04—24
  19.  * Time: 20:12
  20.  */
  21. @WebServlet("/query")
  22. public class BookQueryServlet extends HttpServlet {
  23.     @Override
  24.     protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
  25.         resp.setCharacterEncoding("utf-8");
  26.         String selection = req.getParameter("selection");
  27.         String content = req.getParameter("content");
  28.         BookBean book = null;
  29.         if (selection.equals("book_id")) {
  30.             book = BookDao.queryById(content);
  31.         } else if (selection.equals("title")) {
  32.             book = BookDao.queryByTitle(content);
  33.         } else {
  34.             book = BookDao.queryByAuthor(content);
  35.         }
  36.         if(book!=null){
  37.             req.getSession().setAttribute("book", book);
  38.             RequestDispatcher view = req.getRequestDispatcher("display.jsp");
  39.             view.forward(req, resp);
  40.         }else{
  41.             RequestDispatcher view = req.getRequestDispatcher("errorPage.jsp");
  42.             view.forward(req, resp);
  43.         }
  44.     }
  45. }

BookInsertServlet.java

  1. package org.example.servlet;
  2.  
  3. import org.example.beans.BookBean;
  4. import org.example.dao.BookDao;
  5.  
  6. import javax.servlet.ServletException;
  7. import javax.servlet.annotation.WebServlet;
  8. import javax.servlet.http.HttpServlet;
  9. import javax.servlet.http.HttpServletRequest;
  10. import javax.servlet.http.HttpServletResponse;
  11. import java.io.IOException;
  12. import java.io.PrintWriter;
  13. import java.util.Date;
  14.  
  15. /**
  16.  * Created with IntelliJ IDEA.
  17.  * Description:
  18.  * User: Li_yizYa
  19.  * Date: 2023—04—24
  20.  * Time: 20:12
  21.  */
  22. @WebServlet("/insert")
  23. public class BookInsertServlet extends HttpServlet {
  24.     @Override
  25.     protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
  26.         req.setCharacterEncoding("utf-8");
  27.         resp.setContentType("text/html;charset=utf-8");
  28.         BookBean book = new BookBean();
  29.         book.setBookId(req.getParameter("bookId"));
  30.         book.setTitle(req.getParameter("title"));
  31.         book.setAuthor(req.getParameter("author"));
  32.         book.setPublisher(req.getParameter("publisher"));
  33.         book.setPrice(Double.parseDouble(req.getParameter("price")));
  34.         System.out.println(book.getTitle());
  35.         int ret = BookDao.insert(book);
  36.         String respContent = "插入失败";
  37.         if (ret == 1) {
  38.             respContent = "插入成功";
  39.         }
  40.         PrintWriter out = resp.getWriter();
  41.         out.println("<HTML>");
  42.         out.println("  <BODY><center>");
  43.         out.println("<h2>"+respContent + "</h2>");
  44.         out.println(" </center> </BODY>");
  45.         out.println("</HTML>");
  46.     }
  47. }

  • 视图包括4个JSP页面：bookQuery.jsp显示图书查询表单的页面、bookInsert.jsp显示收集图书信息表单的页面、display.jsp显示查询结果页面、errorPage.jsp显示查询错误页面。

bookQuery.jsp

  1. <%@ page contentType="text/html;charset=UTF-8" language="java" %>
  2. <head>
  3.     <title>bookQuery</title>
  4. </head>
  5. <body>
  6.     <form action="query" method="post">
  7.         请做选择：<select name="selection">
  8.             <option value="book_id">序号</option>
  9.             <option value="title">书名</option>
  10.             <option value="author">作者</option>
  11.         </select>
  12.         <br>
  13.         查询内容：<input type="text" name="content">
  14.         <input type="submit" value="查询">
  15.     </form>
  16. </body>
  17. </html>

bookInsert.jsp

  1. <%@ page contentType="text/html;charset=UTF-8" language="java" %>
  2. <jsp:useBean id="book" class="org.example.beans.BookBean" scope="session"/>
  3. <html>
  4. <head>
  5.     <title>bookInsert</title>
  6. </head>
  7. <body>
  8. <h3>请输入图书信息：</h3>
  9. <form action="insert" method="post">
  10.     <table>
  11.         <tr><td>书号</td> <td><input type="text" name="bookId" ></td></tr>
  12.         <tr><td>书名</td><td><input type="text" name="title"></td></tr>
  13.         <tr><td>作者</td><td><input type="text" name="author" ></td></tr>
  14.         <tr><td>出版社</td><td><input type="text" name="publisher" ></td></tr>
  15.         <tr><td>单价</td><td><input type="text" name="price" ></td></tr>
  16.         <tr><td><input type="submit" value="确定" ></td>
  17.             <td><input type="reset" value="重置" ></td>
  18.         </tr>
  19.     </table>
  20. </form>
  21. </body>
  22. </html>

display.jsp

  1. <%@ page contentType="text/html;charset=UTF-8" language="java" %>
  2. <jsp:useBean id="book" class="org.example.beans.BookBean" scope="session"/>
  3. <jsp:setProperty name="book" property="*"/>
  4. <html>
  5. <head>
  6.     <title>display</title>
  7. </head>
  8. <body>
  9. 书号：<jsp:getProperty name="book" property="bookId"/><br><br>
  10. 书名：<jsp:getProperty name="book" property="title"/><br><br>
  11. 作者：<jsp:getProperty name="book" property="author"/><br><br>
  12. 出版社：<jsp:getProperty name="book" property="publisher"/><br><br>
  13. 价格：<jsp:getProperty name="book" property="price"/><br><br>
  14. </body>
  15. </html>

errorPage.jsp

  1. <%@ page contentType="text/html;charset=UTF-8" language="java" %>
  2. <html>
  3. <head>
  4.     <title>errorPage</title>
  5. </head>
  6. <body>
  7. Sorry，未查询到您需要的图书！
  8. </body>
  9. </html>

3. DAO设计模式练习。 

【步骤1】：使用root用户登录MySQL的test数据库，创建customer表，包括custName、email、phone等字段，设计CustomerBean传输对象，使其实现java.io.Serializable接口。

创建数据库代码

  1. create table customer(
  2.     custName varchar(20),
  3.     email varchar(40),
  4.     phone varchar(20)
  5. );

 CustomerBean.java

  1. package org.example.beans;
  2.  
  3. import java.io.Serializable;
  4.  
  5. /**
  6.  * Created with IntelliJ IDEA.
  7.  * Description:
  8.  * User: Li_yizYa
  9.  * Date: 2023—04—24
  10.  * Time: 21:49
  11.  */
  12. @SuppressWarnings("serial")
  13. public class CustomerBean implements Serializable {
  14.     private String custName;
  15.     private String email;
  16.     private String phone;
  17.  
  18.     public String getCustName() {
  19.         return custName;
  20.     }
  21.  
  22.     public void setCustName(String custName) {
  23.         this.custName = custName;
  24.     }
  25.  
  26.     public String getEmail() {
  27.         return email;
  28.     }
  29.  
  30.     public void setEmail(String email) {
  31.         this.email = email;
  32.     }
  33.  
  34.     public String getPhone() {
  35.         return phone;
  36.     }
  37.  
  38.     public void setPhone(String phone) {
  39.         this.phone = phone;
  40.     }
  41. }

【步骤2】：创建CustomerDAO类实现数据库访问逻辑，通过该类可以获得一个连接对象，对数据库进行查询、插入、修改和删除操作，最后关闭指定的对象。

  1. package org.example.dao;
  2.  
  3. import org.example.beans.BookBean;
  4. import org.example.beans.CustomerBean;
  5. import org.example.utils.DBUtil;
  6.  
  7. import java.sql.Connection;
  8. import java.sql.PreparedStatement;
  9. import java.sql.ResultSet;
  10. import java.sql.SQLException;
  11. import java.util.ArrayList;
  12. import java.util.List;
  13.  
  14. /**
  15.  * Created with IntelliJ IDEA.
  16.  * Description:
  17.  * User: Li_yizYa
  18.  * Date: 2023—04—24
  19.  * Time: 21:57
  20.  */
  21. public class CustomerDAO {
  22.     public static CustomerBean queryByName(String name) {
  23.         Connection c = null;
  24.         PreparedStatement ps = null;
  25.         ResultSet rs = null;
  26.         try {
  27.             System.out.println(name);
  28.             c = DBUtil.getConnection();
  29.             String sql = "select * from customer where custName = ?";
  30.             ps = c.prepareStatement(sql);
  31.             ps.setString(1, name);
  32.             rs = ps.executeQuery();
  33.             CustomerBean customer = null;
  34.             while (rs.next()) {
  35.                 customer = new CustomerBean();
  36.                 customer.setCustName(rs.getString("custName"));
  37.                 customer.setEmail(rs.getString("email"));
  38.                 customer.setPhone(rs.getString("phone"));
  39.             }
  40.             return customer;
  41.         } catch (Exception e) {
  42.             throw new RuntimeException("查询customer出错", e);
  43.         } finally {
  44.             DBUtil.close(c, ps, rs);
  45.         }
  46.     }
  47.  
  48.     //查询数据
  49.     public static List<CustomerBean> queryCustomer() {
  50.         Connection c = null;
  51.         PreparedStatement ps = null;
  52.         ResultSet rs = null;
  53.         try {
  54.             c = DBUtil.getConnection();
  55.             String sql = "select * from customer";
  56.             ps = c.prepareStatement(sql);
  57.             rs = ps.executeQuery();
  58.             List<CustomerBean> customers = new ArrayList<>();
  59.             while (rs.next()) {
  60.                 CustomerBean customer = new CustomerBean();
  61.                 customer.setCustName(rs.getString("custName"));
  62.                 customer.setEmail(rs.getString("email"));
  63.                 customer.setPhone(rs.getString("phone"));
  64.                 customers.add(customer);
  65.             }
  66.             return customers;
  67.         } catch (Exception e) {
  68.             throw new RuntimeException("查询customer出错", e);
  69.         } finally {
  70.             DBUtil.close(c, ps, rs);
  71.         }
  72.     }
  73.     //插入数据
  74.     public static int insert(CustomerBean customer) {
  75.         Connection c = null;
  76.         PreparedStatement ps = null;
  77.         try {
  78.             c = DBUtil.getConnection();
  79.             String sql = "insert into customer values (?, ?, ?);";
  80.             ps = c.prepareStatement(sql);
  81.             ps.setString(1, customer.getCustName());
  82.             ps.setString(2, customer.getEmail());
  83.             ps.setString(3, customer.getPhone());
  84.             return ps.executeUpdate();
  85.         } catch (Exception e) {
  86.             throw new RuntimeException("插入books出错", e);
  87.         } finally {
  88.             DBUtil.close(c, ps, null);
  89.         }
  90.     }
  91.     //根据name删除数据
  92.     public static int delete(String name) {
  93.         Connection c = null;
  94.         PreparedStatement ps = null;
  95.         try {
  96.             c = DBUtil.getConnection();
  97.             String sql = "delete from customer where custName=?";
  98.             ps = c.prepareStatement(sql);
  99.             ps.setString(1, name);
  100.             return ps.executeUpdate();
  101.         } catch (SQLException e) {
  102.             throw new RuntimeException("删除数据库图片出错", e);
  103.         } finally {
  104.             DBUtil.close(c, ps, null);
  105.         }
  106.     }
  107.     //修改数据
  108.     public static int update(CustomerBean customer) {
  109.         Connection c = null;
  110.         PreparedStatement ps = null;
  111.         try {
  112.             c = DBUtil.getConnection();
  113.             String sql = "update customer set custName=?,email=?,phone=? where custName = ?";
  114.             ps = c.prepareStatement(sql);
  115.             ps.setString(1, customer.getCustName());
  116.             ps.setString(2, customer.getEmail());
  117.             ps.setString(3, customer.getPhone());
  118.             ps.setString(4, customer.getCustName());
  119.             return ps.executeUpdate();
  120.         } catch (SQLException e) {
  121.             throw new RuntimeException("修改数据异常", e);
  122.         } finally {
  123.             DBUtil.close(c, ps, null);
  124.         }
  125.     }
  126. }

【步骤3】：创建insertCustomer.jsp页面，通过一个表单录入向数据库中插入的数据。

  1. <%@ page contentType="text/html;charset=UTF-8" language="java" %>
  2. <html>
  3. <head>
  4.     <title>insertCustomer</title>
  5. </head>
  6. <body>
  7. <h3>请输入顾客信息：</h3>
  8. <form action = "insertCustomer" method = "post">
  9.     <table>
  10.         <tr><td>姓名</td> <td><input type="text" name="custName" ></td></tr>
  11.         <tr><td>邮箱</td><td><input type="text" name="email"></td></tr>
  12.         <tr><td>电话</td><td><input type="text" name="phone" ></td></tr>
  13.         <tr><td><input type="submit" value="确定" ></td>
  14.             <td><input type="reset" value="重置" ></td>
  15.         </tr>
  16.     </table>
  17. </form>
  18. </body>
  19. </html>

【步骤4】：创建InsertCustomerServlet.java控制器，调用CustomerDAO对象和传输对象，将数据插入到数据库中。

  1. package org.example.servlet;
  2.  
  3. import org.example.beans.BookBean;
  4. import org.example.beans.CustomerBean;
  5. import org.example.dao.BookDao;
  6. import org.example.dao.CustomerDAO;
  7.  
  8. import javax.servlet.ServletException;
  9. import javax.servlet.annotation.WebServlet;
  10. import javax.servlet.http.HttpServlet;
  11. import javax.servlet.http.HttpServletRequest;
  12. import javax.servlet.http.HttpServletResponse;
  13. import java.io.IOException;
  14. import java.io.PrintWriter;
  15.  
  16. /**
  17.  * Created with IntelliJ IDEA.
  18.  * Description:
  19.  * User: Li_yizYa
  20.  * Date: 2023—04—24
  21.  * Time: 21:56
  22.  */
  23. @WebServlet("/insertCustomer")
  24. public class InsertCustomerServlet extends HttpServlet {
  25.     @Override
  26.     protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
  27.         req.setCharacterEncoding("utf-8");
  28.         resp.setContentType("text/html;charset=utf-8");
  29.         CustomerBean customer = new CustomerBean();
  30.         customer.setCustName(req.getParameter("custName"));
  31.         customer.setEmail(req.getParameter("email"));
  32.         customer.setPhone(req.getParameter("phone"));
  33.         int ret = CustomerDAO.insert(customer);
  34.         String respContent = "插入失败";
  35.         if (ret == 1) {
  36.             respContent = "插入成功";
  37.         }
  38.         PrintWriter out = resp.getWriter();
  39.         out.println("<HTML>");
  40.         out.println("  <BODY><center>");
  41.         out.println("<h2>"+respContent + "</h2>");
  42.         out.println("<a href=\"insertCustomer.jsp\">返回添加页面</a>");
  43.         out.println("<a href=\"showCustmer.jsp\">返回显示页面</a>");
  44.         out.println(" </center> </BODY>");
  45.         out.println("</HTML>");
  46.     }
  47. }

【步骤5】：创建showCustmer.jsp，显示所有客户的信息，每一条客户信息后面增加修改和删除超链接，编写ActionServlet.java控制器，调用DAO和传输对象实现对客户信息的删除和修改功能。

showCustmer.jsp

  1. <%@ page import="java.util.*" %>
  2. <%@ page import="org.example.beans.*" %>
  3. <%@ page contentType="text/html;charset=utf-8" %>
  4. <jsp:useBean id="customer" class="org.example.dao.CustomerDAO" scope="session"/>
  5. <html>
  6. <head>
  7.     <title>showCustmer</title>
  8. <style>
  9.     td{
  10.         text-align: center;
  11.     }
  12. </style>
  13. </head>
  14. <body>
  15.     <table width="500" height="256" border="1">
  16.         <tr>
  17.         <th scope="col">客户姓名</th>
  18.         <th scope="col">邮箱</th>
  19.         <th scope="col">电话号</th>
  20.         <th scope="col">操作</th>
  21.     </tr>
  22.     <%
  23.         List<CustomerBean> customers = customer.queryCustomer();
  24.         int len = customers.size();
  25.         for (int i = 0; i < len; i++) {
  26.             String custName = customers.get(i).getCustName();
  27.             String email = customers.get(i).getEmail();
  28.             String phone = customers.get(i).getPhone();
  29.             %>
  30.             <tr>
  31.                 <td><%=custName%> </td>
  32.                 <td><%=email%></td>
  33.                 <td><%=phone%></td> 
  34.                 <td>
  35.                     <a href="update.jsp?name=<%= custName %>">修改</a>
  36.                     <a href="delete1?name=<%= custName %>">删除</a>
  37.                 </td> 
  38.             </tr>
  39.         <% } %>
  40.     </table>
  41. </body>
  42. </html>

ActionServlet.java

  1. package org.example.servlet;
  2.  
  3. import org.example.beans.CustomerBean;
  4. import org.example.dao.CustomerDAO;
  5.  
  6. import javax.servlet.ServletContext;
  7. import javax.servlet.ServletException;
  8. import javax.servlet.annotation.WebServlet;
  9. import javax.servlet.http.HttpServlet;
  10. import javax.servlet.http.HttpServletRequest;
  11. import javax.servlet.http.HttpServletResponse;
  12. import java.io.IOException;
  13. import java.io.PrintWriter;
  14.  
  15. /**
  16.  * Created with IntelliJ IDEA.
  17.  * Description:
  18.  * User: Li_yizYa
  19.  * Date: 2023—04—24
  20.  * Time: 22:21
  21.  */
  22. @WebServlet({"/delete1", "/update1"})
  23. public class ActionServlet extends HttpServlet {
  24.     @Override
  25.     protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
  26.         String uri = req.getRequestURI();
  27.         if (uri.endsWith("/delete1")) {
  28.             delete1(req, resp);
  29.         }
  30.         if (uri.endsWith("/update1")) {
  31.             update1(req, resp);
  32.         }
  33.     }
  34.  
  35.     @Override
  36.     protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
  37.         doGet(req, resp);
  38.     }
  39.  
  40.     private void delete1(HttpServletRequest req, HttpServletResponse resp) throws IOException {
  41.         String name = req.getParameter("name");
  42.         int ret = CustomerDAO.delete(name);
  43.         resp.sendRedirect("showCustmer.jsp");
  44.     }
  45.     private void update1(HttpServletRequest req, HttpServletResponse resp) throws IOException {
  46.         req.setCharacterEncoding("utf-8");
  47.         resp.setContentType("text/html;charset=utf-8");
  48.         CustomerBean customer = new CustomerBean();
  49.         customer.setCustName(req.getParameter("custName"));
  50.         customer.setEmail(req.getParameter("email"));
  51.         customer.setPhone(req.getParameter("phone"));
  52.         System.out.println(customer.getCustName());
  53.         int ret = CustomerDAO.update(customer);
  54.         String respContent = "修改失败";
  55.         if (ret == 1) {
  56.             respContent = "修改成功";
  57.         }
  58.         PrintWriter out = resp.getWriter();
  59.         out.println("<HTML>");
  60.         out.println("  <BODY><center>");
  61.         out.println("<h2>"+respContent + "</h2>");
  62.         out.println("<a href=\"showCustmer.jsp\">返回显示页面</a>");
  63.         out.println(" </center> </BODY>");
  64.         out.println("</HTML>");
  65.     }
  66.  
  67. }

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

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

  

闽ICP备14008679号