JDBC ResultSet的更新程序方法意外的惊喜（？）

由 发布于 Javajdbc
 收藏

We have some data in a MySQL database that we are accessing via JDBC. Depending upon the value read from the database, we wanted to play some "tricks" in the code. I read the documentation on the updater methods of ResultSet and I was heartened to find the following text repeated for all the updateXXX methods:

updater方法不会更新基础数据库；而是调用updateRow或insertRow方法来更新数据库。

我想，“很棒”。我可以做这样的事情：

while(rs.next()) { 
  Retrieve desired values by column name
  if(condition) {
    Invoke rs.updateXXX() on the desired column name with the new value
  }
  Business Logic
  ...
}

请记住，业务逻辑是在while循环内发生的。

However, to my dismay, it appeared that rs.updateXXX() was not taking effect the way I was expecting.

Fortunately I have been able to recreate the problem with some sample code that I adapted from here:

首先，我们使用以下SQL指令创建示例数据库：

CREATE DATABASE EMP;
CREATE TABLE EMP.Employees
(
  id INT NOT NULL,
  age INT NOT NULL,
  first VARCHAR(255),
  last VARCHAR(255),
  PRIMARY KEY ( id )
);
INSERT INTO EMP.Employees VALUES (100, 18, 'Zara', 'Ali'), (101, 25, 'Mahnaz', 'Fatima'), (102, 30, 'Zaid', 'Khan'), (103, 28, 'Sumit', 'Mittal');

然后，我们有以下JDBC代码：

// Adapted from https://www.tutorialspoint.com/jdbc

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStudy {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    static final String DB_URL = "jdbc:mysql://localhost/EMP";

    //  Database credentials
    static final String USER = "username";
    static final String PASS = "password";

    public static void main(String[] args) {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");

            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);

            System.out.println("Creating statement...");

            testJDBC(conn);
        }
        catch(SQLException se){
            //Handle errors for JDBC
            se.printStackTrace();
        }
        catch(Exception e){
            //Handle errors for Class.forName
            e.printStackTrace();
        }
        finally {
            try{
                if(conn!=null)
                    conn.close();
            }
            catch(SQLException se){
                se.printStackTrace();
            }
        }
        System.out.println("Goodbye!");
    }


    private static void testJDBC(Connection conn) throws SQLException {
          System.out.println("Creating statement...");
          Statement stmt = conn.createStatement(
                               ResultSet.TYPE_SCROLL_INSENSITIVE,
                               ResultSet.CONCUR_UPDATABLE);
          String sql = "SELECT id, first, last, age FROM Employees";
          ResultSet rs = stmt.executeQuery(sql);

          //Loop through result set and add 5 in age
          //Move to BFR postion so while-loop works properly
          rs.beforeFirst();
          //Extract data from result set
          while(rs.next()){
             String firstName = rs.getString("first");
             int newAge = rs.getInt("age") + 5;
             rs.updateInt( "age", newAge );

             int retrievedAge = rs.getInt("age");
             System.out.println(firstName + "> " + "New age, Retrieved age: (" + newAge + ", " + retrievedAge + ")");
             // I was expecting the The newAge and retrievedAge to be the same
          }
          stmt.close();     
    }
}

我从该程序得到以下输出：

Zara> New age, Retrieved age: (23, 18)
Mahnaz> New age, Retrieved age: (30, 25)
Zaid> New age, Retrieved age: (35, 30)
Sumit> New age, Retrieved age: (33, 28)

我期望新年龄段和已检索年龄段具有相同的值。

Question): Why is rs.updateInt( "age", newAge ) not taking effect?

评论