JDBC操作数据库之删除数据

删除数据使用的SQL语句为delete语句，如果删除图书id为1的图书信息，其SQL语句为：

delete from book where id=1

在实际开发中删除数据通常使用PreparedStatement对象进行操作。

实例代码：

（1）index.jsp

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 4 <html>
 5 <head>
 6 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 7 <title>查看所有图书</title>
 8 </head>
 9 <body>
10     <a href="FindServlet">查看所有图书</a>
11 </body>
12 </html>

（2）book_list.jsp

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <%@ page import="com.java.Book" %>
 4 <%@ page import="java.util.ArrayList" %>
 5 <%@ page import="java.util.List" %>
 6 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 7 <html>
 8 <head>
 9 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
10 <title>删除数据</title>
11 <style>
12     td{
13         font-size:12px;
14     }
15     h2{
16         margin:2px;
17     }
18 </style>
19 <script type="text/javascript">
20     function check(form){
21         with(form){
22             if(bookCount.value == ""){
23                 alert("请输入更新数量！");
24                 return false;
25             }
26             if(isNaN(bookCount.value)){
27                 alert("格式错误！");
28                 return false;
29             }
30             return true;
31         }
32     }
33 </script>
34 </head>
35 <body>
36     <div style="text-align:center;">
37         <table align="center" width="500px" border="1" height="170px" bordercolor="white" bgcolor="black" cellpadding="1">
38             <tr bgcolor="white">
39                 <td align="center" colspan="6">
40                     <h2>所有图书信息 </h2>
41                 </td>
42             </tr>
43             <tr align="center" bgcolor="#e1ffc1">
44                 <td><b>ID</b></td>
45                 <td><b>图书名称</b></td>
46                 <td><b>价格</b></td>
47                 <td><b>作者</b></td>
48                 <td><b>修改数量</b></td>
49                 <td><b>删除</b></td>
50             </tr>
51             <%
52                 List<Book> list = (List<Book>)request.getAttribute("list");
53                 if(list ==null || list.size() < 1){
54                     out.print("数据为空");
55                 }else{
56                     for(Book book:list){
57                         
58                     
59                 
60             %>
61             <tr align="center" bgcolor="white">
62                 <td><%= book.getId() %></td>
63                 <td><%= book.getName() %></td>
64                 <td><%= book.getPrice() %></td>
65                 <td><%= book.getBookCount() %></td>
66                 <td><%= book.getAuthor() %></td>
67                 <td>
68                     <a href="DeleteServlet?id=<%=book.getId()%>">删除</a>
69                 </td>
70             </tr>
71             <%
72                     }
73                 }
74             %>
75          </table>
76     </div>
77 </body>
78 </html>

查看代码

（3）Book类

 1 package com.java;
 2 
 3 public class Book {
 4      private int id;
 5         private String name;
 6         private double price;
 7         private int bookCount;
 8         private String author;
 9         public int getId() {
10             return id;
11         }
12         public void setId(int id) {
13             this.id = id;
14         }
15         public String getName() {
16             return name;
17         }
18         public void setName(String name) {
19             this.name = name;
20         }
21         public double getPrice() {
22             return price;
23         }
24         public void setPrice(double price) {
25             this.price = price;
26         }
27         public int getBookCount() {
28             return bookCount;
29         }
30         public void setBookCount(int bookCount) {
31             this.bookCount = bookCount;
32         }
33         public String getAuthor() {
34             return author;
35         }
36         public void setAuthor(String author) {
37             this.author = author;
38         }
39 }

查看代码

（4）FindServlet类

 1 package com.java;
 2 
 3 import java.io.IOException;
 4 import java.sql.Connection;
 5 import java.sql.DriverManager;
 6 import java.sql.PreparedStatement;
 7 import java.sql.ResultSet;
 8 import java.sql.SQLException;
 9 import java.util.ArrayList;
10 import java.util.List;
11 
12 import javax.servlet.ServletException;
13 import javax.servlet.http.HttpServlet;
14 import javax.servlet.http.HttpServletRequest;
15 import javax.servlet.http.HttpServletResponse;
16 
17 import com.java.Book;
18 
19 public class FindServlet extends HttpServlet {
20     private static final long serialVersionUID = 1L;
21     private String sql = "select * from book";  
22     private String url = "jdbc:mysql://localhost:3306/test";
23     private String username = "root";
24     private String password = "123456";
25    
26     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
27         try {
28             Class.forName("com.mysql.jdbc.Driver");
29         } catch (ClassNotFoundException e1) {
30             // TODO Auto-generated catch block
31             e1.printStackTrace();
32         }
33         Connection conn=null;
34         try {
35             conn = DriverManager.getConnection(url,username,password);
36         } catch (SQLException e1) {
37             // TODO Auto-generated catch block
38             e1.printStackTrace();
39         }
40         
41         try {
42             PreparedStatement ps = conn.prepareStatement(sql);
43             ResultSet rs = ps.executeQuery(sql);
44             List<Book> list = (List<Book>)new ArrayList();
45             while(rs.next()) {
46                 Book book = new Book();
47                 book.setId(rs.getInt("id"));
48                 book.setName(rs.getString("name"));
49                 book.setPrice(rs.getDouble("price"));
50                 book.setBookCount(rs.getInt("bookCount"));
51                 book.setAuthor(rs.getString("author"));
52                 list.add(book);
53             }
54             request.setAttribute("list",list);
55             rs.close();
56             ps.close();
57             conn.close();
58         } catch (SQLException e) {
59             // TODO Auto-generated catch block
60             e.printStackTrace();
61         }
62         
63         request.getRequestDispatcher("book_list.jsp").forward(request, response);
64     }
65 }

查看代码

（5）DeleteServlet类

 1 package com.java;
 2 
 3 import java.io.IOException;
 4 import java.sql.Connection;
 5 import java.sql.DriverManager;
 6 import java.sql.PreparedStatement;
 7 import java.sql.SQLException;
 8 
 9 import javax.servlet.ServletException;
10 import javax.servlet.http.HttpServlet;
11 import javax.servlet.http.HttpServletRequest;
12 import javax.servlet.http.HttpServletResponse;
13 
14 public class DeleteServlet extends HttpServlet {
15 
16     private static final long serialVersionUID = 1L;
17     protected void doGet(HttpServletRequest request,HttpServletResponse response) throws IOException,ServletException{
18         int id = Integer.valueOf(request.getParameter("id"));
19         
20             try {
21                 Class.forName("com.mysql.jdbc.Driver");
22                 String url = "jdbc:mysql://localhost:3306/test";
23                 String username = "root";
24                 String password = "123456";
25                 Connection conn=null;
26                 try {
27                     conn = DriverManager.getConnection(url,username,password);
28                 } catch (SQLException e1) {
29                     // TODO Auto-generated catch block
30                     e1.printStackTrace();
31                 }
32                 String sql = "delete from book where id=?";
33                 PreparedStatement ps;
34                 try {
35                     ps = conn.prepareStatement(sql);
36                     ps.setInt(1, id);
37                     ps.executeUpdate();
38                     ps.close();
39                     conn.close();
40                 } catch (SQLException e) {
41                     // TODO Auto-generated catch block
42                     e.printStackTrace();
43                 }
44                 
45             } catch (ClassNotFoundException e) {
46                 // TODO Auto-generated catch block
47                 e.printStackTrace();
48             }
49             
50             response.sendRedirect("FindServlet");
51         
52     }
53     
54     
55 }

查看代码

最有项目运行结果