以下是使用ResultSet教學中描述的ResultSet.CONCUR_UPDATABLE和ResultSet.TYPE_SCROLL_INSENSITIVE的範例。此範例將演示如何在表上執行INSERT，UPDATE和DELETE操作。
應該注意的是，要處理的表應該正確設定主鍵。
此範例程式碼是基於前面章節中完成的環境和資料庫設定編寫的。
複製並通過以下範例程式碼放在檔案：UpdatingResultSet.java 中，然後編譯並執行如下 -
//STEP 1. Import required packages
import java.sql.*;
public class JDBCExample {
// 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{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
//STEP 4: Execute a query to create statment with
// required arguments for RS example.
System.out.println("Creating statement...");
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
//STEP 5: Execute a query
String sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
System.out.println("List result set for reference....");
printRs(rs);
//STEP 6: Loop through result set and add 5 in age
//Move to BFR postion so while-loop works properly
rs.beforeFirst();
//STEP 7: Extract data from result set
while(rs.next()){
//Retrieve by column name
int newAge = rs.getInt("age") + 5;
rs.updateDouble( "age", newAge );
rs.updateRow();
}
System.out.println("List result set showing new ages...");
printRs(rs);
// Insert a record into the table.
//Move to insert row and add column data with updateXXX()
System.out.println("Inserting a new record...");
rs.moveToInsertRow();
rs.updateInt("id",104);
rs.updateString("first","John");
rs.updateString("last","Paul");
rs.updateInt("age",40);
//Commit row
rs.insertRow();
System.out.println("List result set showing new set...");
printRs(rs);
// Delete second record from the table.
// Set position to second record first
rs.absolute( 2 );
System.out.println("List the record before deleting...");
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
//Delete row
rs.deleteRow();
System.out.println("List result set after \
deleting one records...");
printRs(rs);
//STEP 8: Clean-up environment
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
public static void printRs(ResultSet rs) throws SQLException{
//Ensure we start with first row
rs.beforeFirst();
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
System.out.println();
}//end printRs()
}//end JDBCExample
編譯並執行如下 -
F:\worksp\jdbc>javac -Djava.ext.dirs=F:\worksp\jdbc\libs UpdatingResultSet.java
F:\worksp\jdbc>java -Djava.ext.dirs=F:\worksp\jdbc\libs UpdatingResultSet
執行上面程式碼，得到以下結果 -
F:\worksp\jdbc>javac -Djava.ext.dirs=F:\worksp\jdbc\libs UpdatingResultSet.java
F:\worksp\jdbc>
F:\worksp\jdbc>java -Djava.ext.dirs=F:\worksp\jdbc\libs UpdatingResultSet
Connecting to database...
Wed May 31 23:36:34 CST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Creating statement...
List result set for reference....
ID: 100, Age: 28, First: Max, Last: Su
ID: 101, Age: 25, First: Wei, Last: Wang
ID: 102, Age: 35, First: Xueyou, Last: Zhang
ID: 103, Age: 30, First: Jack, Last: Ma
com.mysql.jdbc.NotUpdatable: Result Set not updatable (referenced table has no primary keys).This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.
at com.mysql.jdbc.UpdatableResultSet.generateStatements(UpdatableResultSet.java:590)
at com.mysql.jdbc.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1468)
at com.mysql.jdbc.UpdatableResultSet.updateDouble(UpdatableResultSet.java:1986)
at com.mysql.jdbc.UpdatableResultSet.updateDouble(UpdatableResultSet.java:2013)
at UpdatingResultSet.main(UpdatingResultSet.java:45)
Goodbye!
F:\worksp\jdbc>
上面執行結果有一個異常提示，這是由表Employees沒有設定主鍵導致的。現在，在表 Employees 的ID欄位上建立主鍵，重新編譯執行應該就沒有問題了。
F:\worksp\jdbc>javac -Djava.ext.dirs=F:\worksp\jdbc\libs UpdatingResultSet.java
F:\worksp\jdbc>java -Djava.ext.dirs=F:\worksp\jdbc\libs ViewingResultSet
Connecting to database...
Wed May 31 23:39:32 CST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Creating statement...
Moving cursor to the last...
Displaying record...
ID: 103, Age: 30, First: Jack, Last: Ma
Moving cursor to the first row...
Displaying record...
ID: 100, Age: 28, First: Max, Last: Su
Moving cursor to the next row...
Displaying record...
ID: 101, Age: 25, First: Wei, Last: Wang
Goodbye!
F:\worksp\jdbc>