如何使用 JDBC 程序更新 ResultSet 的内容?

jdbcjava 8mysqlmysqli database

要更新 ResultSet 的内容,您需要通过传递可更新的 ResultSet 类型来创建语句,如下所示:

//创建 Statement 对象
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

与 getXXX() 和 setXXX() 方法一样,ResultSet 接口也提供了更新结果集中某一行内容的方法 updateXXX()。

这些方法接受表示要更新的行的索引的整数值或表示列标签的字符串值。

请注意,如果您需要更新 ResultSet 的内容,则表应该具有主键。

示例

假设我们有一个名为 Employees 的表,其中包含 5 条记录,如下所示:

+----+---------+--------+----------------+
| Id | Name    | Salary | Location       |
+----+---------+--------+----------------+
|  1 | Amit    | 3000   | Hyderabad      |
|  2 | Kalyan  | 4000   | Vishakhapatnam |
|  3 | Renuka  | 6000   | Delhi          |
|  4 | Archana | 9000   | Mumbai         |
|  5 | Sumith  | 11000  | Hyderabad      |
+----+---------+--------+----------------+

以下示例演示如何更新结果集的内容:

import java.sql.*;
public class ResultSetExample {
   public static void main(String[] args) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/TestDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a Statement object
      Statement stmt = con.createStatement(
      ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from Employees");
      //Printing the contents of the table
      System.out.println("Contents of the table: ");
      printRs(rs);
      //Moving the pointer to the starting point in the ResultSet
      rs.beforeFirst();
      //Updating the salary of each employee by 5000
      while(rs.next()){
         //Retrieve by column name
         int newSal = rs.getInt("Salary") + 5000;
         rs.updateInt( "Salary", newSal );
         rs.updateRow();
      }
      System.out.println("Contents of the ResultSet after increasing salaries");
      printRs(rs);
      // Set position to second record first
      rs.beforeFirst();
      rs.absolute(2);
      System.out.println("Record we need to delete: ");
      System.out.print("ID: " + rs.getInt("id"));
      System.out.print(", Salary: " + rs.getInt("Salary"));
      System.out.print(", Name: " + rs.getString("Name"));
      System.out.println(", Location: " + rs.getString("Location"));
      System.out.println(" ");
      //Deleting the row
      rs.deleteRow();
      System.out.println("Contents of the ResultSet after deleting one records...");
      printRs(rs);
      System.out.println("Goodbye!");
   }
   public static void printRs(ResultSet rs) throws SQLException{
      //Ensure we start with first row
      rs.beforeFirst();
      while(rs.next()){
         System.out.print("ID: " + rs.getInt("id"));
         System.out.print(", Salary: " + rs.getInt("Salary"));
         System.out.print(", Name: " + rs.getString("Name"));
         System.out.println(", Location: " + rs.getString("Location"));
      }
      System.out.println();
   }
}

输出

Connection established......
Contents of the table:
ID: 1, Salary: 3000, Name: Amit, Location: Hyderabad
ID: 2, Salary: 4000, Name: Kalyan, Location: Vishakhapatnam
ID: 3, Salary: 6000, Name: Renuka, Location: Delhi
ID: 4, Salary: 9000, Name: Archana, Location: Mumbai
ID: 5, Salary: 11000, Name: Sumith, Location: Hyderabad
Conetnets of the resultset after increaing salaries
ID: 1, Salary: 8000, Name: Amit, Location: Hyderabad
ID: 2, Salary: 9000, Name: Kalyan, Location: Vishakhapatnam
ID: 3, Salary: 11000, Name: Renuka, Location: Delhi
ID: 4, Salary: 14000, Name: Archana, Location: Mumbai
ID: 5, Salary: 16000, Name: Sumith, Location: Hyderabad
Record we need to delete:
ID: 2, Salary: 9000, Name: Kalyan, Location: Vishakhapatnam
Contents of the resultset after deleting one records...
ID: 1, Salary: 8000, Name: Amit, Location: Hyderabad
ID: 3, Salary: 11000, Name: Renuka, Location: Delhi
ID: 4, Salary: 14000, Name: Archana, Location: Mumbai
ID: 5, Salary: 16000, Name: Sumith, Location: Hyderabad
Goodbye!

相关文章