删除数据使用的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 }
最有项目运行结果