使用JDBC修改数据库中的数据,起操作方法是和添加数据差不多的,只不过在修改数据的时候还要用到UPDATE语句来实现的,例如:把图书信息id为1的图书数量改为100,其sql语句是:update book set bookCount=100 where id=1。在实际开发过程中,通常会由程序传递SQL语句中的参数,所以修改数据也通常使用PreparedStatement对象进行操作。
实例代码:
(1)index.jsp
1 <html>
2 <head>
3 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
4 <title>修改数据</title>
5 </head>
6 <body>
7 <a href="FindServlet">修改数据</a>
8 </body>
9 </html>
(2)book_list.jsp代码
1 <%@ page language="java" contentType="text/html; charset=UTF-8"
2 pageEncoding="UTF-8"%>
3 <%@ page import="com.book.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 </tr>
50 <%
51 List<Book> list = (List<Book>)request.getAttribute("list");
52 if(list ==null || list.size() < 1){
53 out.print("数据为空");
54 }else{
55 for(Book book:list){
56
57
58
59 %>
60 <tr align="center" bgcolor="white">
61 <td><%= book.getId() %></td>
62 <td><%= book.getName() %></td>
63 <td><%= book.getPrice() %></td>
64 <td><%= book.getBookCount() %></td>
65 <td><%= book.getAuthor() %></td>
66 <td>
67 <form action="UpdateServlet" method="post" onsubmit="return check(this);">
68 <input type="hidden" name="id" value="<%= book.getId() %>" >
69 <input type="text" name="bookCount" size="3">
70 <input type="submit" value="修 改">
71 </form>
72 </td>
73 </tr>
74 <%
75 }
76 }
77 %>
78 </table>
79 </div>
80 </body>
81 </html>
(3)Book类对象
1 package com.book;
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
40 }
(4)DBConnection.java
1 package com.db;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.SQLException;
6
7 public class DBConnection {
8 private String url = "jdbc:mysql://localhost:3306/test";
9 private String driver = "com.mysql.jdbc.DriverManager";
10 private String username = "root";
11 private String password = "123456";
12 Connection conn = null;
13 public Connection getConn() {
14 try {
15
16 Class.forName(driver);
17
18 conn = DriverManager.getConnection(url,username,password);
19
20 }catch(ClassNotFoundException e1) {
21 e1.printStackTrace();
22 }catch (SQLException e) {
23 // TODO Auto-generated catch block
24 e.printStackTrace();
25 }
26 return conn;
27 }
28
29 }
(5)FindServlet.java
1 package com.db;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.SQLException;
6
7 public class DBConnection {
8 private String url = "jdbc:mysql://localhost:3306/test";
9 private String driver = "com.mysql.jdbc.DriverManager";
10 private String username = "root";
11 private String password = "123456";
12 Connection conn = null;
13 public Connection getConn() {
14 try {
15
16 Class.forName(driver);
17
18 conn = DriverManager.getConnection(url,username,password);
19
20 }catch(ClassNotFoundException e1) {
21 e1.printStackTrace();
22 }catch (SQLException e) {
23 // TODO Auto-generated catch block
24 e.printStackTrace();
25 }
26 return conn;
27 }
28
29 }
(6)UpdateServlet.java
1 package com.servlet;
2
3 import java.io.IOException;
4 import java.sql.Connection;
5 import java.sql.DriverManager;
6 import java.sql.PreparedStatement;
7
8 import javax.servlet.ServletException;
9 import javax.servlet.http.HttpServlet;
10 import javax.servlet.http.HttpServletRequest;
11 import javax.servlet.http.HttpServletResponse;
12
13 public class UpdateServlet extends HttpServlet {
14
15 private static final long serialVersionUID = 1L;
16 protected void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException{
17 int id = Integer.valueOf(request.getParameter("id"));
18 int bookCount = Integer.valueOf(request.getParameter("bookCount"));
19 try {
20 Class.forName("com.mysql.jdbc.Driver");
21 String url = "jdbc:mysql://localhost:3306/test";
22 String username = "root";
23 String password = "123456";
24 String sql = "update book set bookCount=? where id=?";
25 Connection conn = DriverManager.getConnection(url,username,password);
26 PreparedStatement ps = conn.prepareStatement(sql);
27 ps.setInt(1, bookCount);
28 ps.setInt(2, id);
29 ps.executeUpdate();
30 ps.close();
31 conn.close();
32 }catch(Exception e) {
33 e.printStackTrace();
34 }
35
36 //重定向到FindServlet
37 response.sendRedirect("FindServlet");
38 }
39
40 }
程序运行结果:
index.jsp页面
book_list.jsp页面