JDBC操作数据库之修改数据

使用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页面

相关阅读:
Mysql：用户操作命令
 Cookie和Set-Cookie
Matcher中appendReplacement（）方法与replaceAll()方法的联系
 记录一次mysql执行异常（磁盘空间）
ehcache简单配置
 md5
mysql后台运行简单的备份脚本
 shell脚本研习
 SpringBoot根据条件，去注入需要的Bean
springboot后台跨域设置
原文地址：https://www.cnblogs.com/lihuibin/p/7489381.html