如何使用 JDBC API 更新数据库中表记录的内容?
jdbcjava 8object oriented programmingprogramming
答:您可以使用 UPDATE 查询更新/修改表中记录的现有内容。使用 UPDATE 查询，您可以更新表中的所有记录或特定记录。
语法
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
要使用 JDBC API 更新表中记录的内容，您需要:
注册驱动程序:使用 DriverManager 类的 registerDriver() 方法注册驱动程序类。将驱动程序类名作为参数传递给它。
建立连接:使用 DriverManager 类的 getConnection() 方法连接数据库。将 URL(字符串)、用户名(字符串)、密码(字符串)作为参数传递给它。
创建 Statement:使用 Connection 接口的 createStatement() 方法创建一个 Statement 对象。
执行查询:使用 Statement 接口的 executeUpdate() 方法执行查询。
示例
假设我们在 MySQL 中名为 mydatabase 的数据库中有一个名为 customer 的表，其中包含 12 条记录，如下所示:
+----+-----------+------+---------+----------------+ | ID | NAME | AGE | SALARY | ADDRESS | +----+-----------+------+---------+----------------+ | 1 | Amit | 25 | 3000.00 | Hyderabad | | 2 | Kalyan | 27 | 4000.00 | Vishakhapatnam | | 3 | Renuka | 30 | 5000.00 | Delhi | | 4 | Archana | 24 | 1500.00 | Mumbai | | 5 | Koushik | 30 | 9000.00 | Kota | | 6 | Hardik | 45 | 6400.00 | Bhopal | | 7 | Trupthi | 33 | 4360.00 | Ahmedabad | | 8 | Mithili | 26 | 4100.00 | Vijayawada | | 9 | Maneesh | 39 | 4000.00 | Hyderabad | | 10 | Rajaneesh | 30 | 6400.00 | Delhi | | 11 | Komal | 29 | 8000.00 | Ahmedabad | | 12 | Manyata | 25 | 5000.00 | Vijayawada | +----+-----------+------+---------+----------------+
以下 JDBC 程序与 MySQL 建立连接，并将 ID 为 4、5、6 和 7 的客户地址更新为 Delhi，并在更新后检索并显示表的内容。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class UpdateRecordsExample {
public static void main(String args[]) throws SQLException {
//注册驱动程序
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//获取连接
String mysqlUrl = "jdbc:mysql://localhost/MyDatabase";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("连接已建立......");
//创建语句
Statement stmt = con.createStatement();
//查询以更新记录
String query = "updatecustomerssetaddress='Delhi'whereIDin(4,5,6,7)";
//执行查询
int i = stmt.executeUpdate(query);
System.out.println("更新的行数:"+i);
System.out.println("更新记录后customers表的内容:");
//检索数据
ResultSet rs = stmt.executeQuery("Select * from customers");
while(rs.next()) {
System.out.print("ID: "+rs.getInt("ID")+", ");
System.out.print("Name: "+rs.getString("Name")+", ");
System.out.print("Age: "+rs.getInt("Age")+", ");
System.out.print("Salary: "+rs.getInt("Salary")+", ");
System.out.print("Address: "+rs.getString("Address"));
System.out.println();
}
}
}
输出
连接已建立...... Rows updated: 4 Contents of the customers table after updating the records: ID: 1, Name: Amit, Age: 25, Salary: 3000, Address: Hyderabad ID: 2, Name: Kalyan, Age: 27, Salary: 4000, Address: Vishakhapatnam ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi ID: 4, Name: Archana, Age: 24, Salary: 1500, Address: Delhi ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Delhi ID: 6, Name: Hardik, Age: 45, Salary: 6400, Address: Delhi ID: 7, Name: Trupthi, Age: 33, Salary: 4360, Address: Delhi ID: 8, Name: Mithili, Age: 26, Salary: 4100, Address: Vijayawada ID: 9, Name: Maneesh, Age: 39, Salary: 4000, Address: Hyderabad ID: 10, Name: Rajaneesh, Age: 30, Salary: 6400, Address: Delhi ID: 11, Name: Komal, Age: 8000, Salary: 29, Address: Ahmedabad ID: 12, Name: Manyata, Age: 5000, Salary: 25, Address: Vijayawada
