JDBC操作数据库之修改数据

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

使用JDBC修改数据库中的数据，起操作方法是和添加数据差不多的，只不过在修改数据的时候还要用到UPDATE语句来实现的，例如：把图书信息id为1的图书数量改为100，其sql语句是：update book set bookCount=100 where id=1。在实际开发过程中，通常会由程序传递SQL语句中的参数，所以修改数据也通常使用PreparedStatement对象进行操作。

实例代码：

（1）index.jsp

查看代码

（2）book_list.jsp代码

<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="com.book.Book" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="java.util.List" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>修改图书信息</title>
<style>
td{
font-size:12px;
}
h2{
margin:2px;
}
</style>
<script type="text/javascript">
function check(form){
with(form){
if(bookCount.value == ""){
alert("请输入更新数量！");
return false;
}
if(isNaN(bookCount.value)){
alert("格式错误！");
return false;
}
return true;
}
}
</script>
</head>
<body>
<div style="text-align:center;">
<table align="center" width="500px" border="1" height="170px" bordercolor="white" bgcolor="black" cellpadding="1">
<tr bgcolor="white">
<td align="center" colspan="6">
<h2>所有图书信息 </h2>
</td>
</tr>
<tr align="center" bgcolor="#e1ffc1">
<td><b>ID</b></td>
<td><b>图书名称</b></td>
<td><b>价格</b></td>
<td><b>作者</b></td>
<td><b>修改数量</b></td>
</tr>
<%
List<Book> list = (List<Book>)request.getAttribute("list");
if(list ==null || list.size() < 1){
out.print("数据为空");
}else{
for(Book book:list){ %>
<tr align="center" bgcolor="white">
<td><%= book.getId() %></td>
<td><%= book.getName() %></td>
<td><%= book.getPrice() %></td>
<td><%= book.getBookCount() %></td>
<td><%= book.getAuthor() %></td>
<td>
<form action="UpdateServlet" method="post" onsubmit="return check(this);">
<input type="hidden" name="id" value="<%= book.getId() %>" >
<input type="text" name="bookCount" size="3">
<input type="submit" value="修改">
</form>
</td>
</tr>
<%
}
}
%>
</table>
</div>
</body>
</html>

查看代码

（3）Book类对象

package com.book; public class Book {
private int id;
private String name;
private double price;
private int bookCount;
private String author;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public int getBookCount() {
return bookCount;
}
public void setBookCount(int bookCount) {
this.bookCount = bookCount;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
} }

查看代码

（4）DBConnection.java

package com.db; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException; public class DBConnection {
private String url = "jdbc:mysql://localhost:3306/test";
private String driver = "com.mysql.jdbc.DriverManager";
private String username = "root";
private String password = "123456";
Connection conn = null;
public Connection getConn() {
try { Class.forName(driver); conn = DriverManager.getConnection(url,username,password); }catch(ClassNotFoundException e1) {
e1.printStackTrace();
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
} }

查看代码

（5）FindServlet.java

查看代码

（6）UpdateServlet.java

package com.servlet; import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement; import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; public class UpdateServlet extends HttpServlet { private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException{
int id = Integer.valueOf(request.getParameter("id"));
int bookCount = Integer.valueOf(request.getParameter("bookCount"));
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "123456";
String sql = "update book set bookCount=? where id=?";
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, bookCount);
ps.setInt(2, id);
ps.executeUpdate();
ps.close();
conn.close();
}catch(Exception e) {
e.printStackTrace();
} //重定向到FindServlet
response.sendRedirect("FindServlet");
} }