以下是使用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>
