อัปเดตข้อมูลใน ResultSet ด้วยโปรแกรม JDBC

เพื่ออัปเดตข้อมูลใน ResultSet ต้องสร้าง Statement ด้วย result set type updatable:

// Creating a Statement object
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet interface มีเมธอด updateXXX() คล้าย getXXX()/setXXX() รับ index หรือ column label ของแถวที่จะอัปเดต

หมายเหตุ: ตารางต้องมี primary key ถึงจะอัปเดต 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      |
+----+---------+--------+----------------+

โปรแกรมอัปเดตเงินเดือนเพิ่ม 5000 และลบแถวที่ 2:

import java.sql.*;
public class ResultSetExample {
    public static void main(String[] args) throws Exception {
        // ลงทะเบียน Driver
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        // เชื่อมต่อ
        String mysqlUrl = "jdbc:mysql://localhost/TestDB";
        Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
        System.out.println("Connection established......");
        // สร้าง Statement updatable
        Statement stmt = con.createStatement(
            ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        // ดึงข้อมูล
        ResultSet rs = stmt.executeQuery("select * from Employees");
        // พิมพ์ข้อมูลเดิม
        System.out.println("Contents of the table: ");
        printRs(rs);
        // กลับไปจุดเริ่มต้น
        rs.beforeFirst();
        // อัปเดตเงินเดือนแต่ละคน +5000
        while(rs.next()){
            int newSal = rs.getInt("Salary") + 5000;
            rs.updateInt("Salary", newSal);
            rs.updateRow();
        }
        System.out.println("Contents of the ResultSet after increasing salaries");
        printRs(rs);
        // ลบแถวที่ 2
        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(" ");
        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{
        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!