How to Use Updatable ResultSet in JDBC?
A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. An updatable result set allows modification to data in a table through the result set. The following code makes a result set that is scrollable and insensitive to updates by others:
try {
// Create a statement that will return updatable result sets
Statement stmt = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
//Primary key EmployeeID must be specified
//so that the result set is updatable
ResultSet resultSet = stmt.executeQuery(
"SELECT EmployeeID, Name, Office FROM employees");
} catch (SQLException e) {
}
The updatable result set may be used few ways:
- to update a column value in the current row. In a scrollable
ResultSetobject, the cursor can be moved backwards and forwards, to an absolute position, or to a position relative to the current row. The following code fragment updates the Office column in the fifth row of the ResultSet object rs and then uses the method updateRow to update the data source table from which rs was derived.rs.absolute(5); // moves the cursor to the fifth row of rs
rs.updateString("Office", "HQ222"); // updates the
// Office column of row 5 to be HQ222
rs.updateRow(); // updates the row in the data source - to insert column values into the insert row. An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a three-column row, and inserts it into rs and into the data source table using the method insertRow.
rs.moveToInsertRow(); // moves cursor to the insert row
rs.updateInt("EmployeeID", 1001);
rs.updateString("Name", "Divad Walker");
rs.updateString("Office", "HQ101");
rs.insertRow();
rs.moveToCurrentRow(); - to delete a row. The following code fragment moves to the first row of the ResultSet object rs and then uses the method deleteRow to delete the data source table from which rs was derived.
rs.first(); // moves cursor to the deleting row
rs.deleteRow();
A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.
cancelRowUpdates cancels the updates made to the current row in this ResultSet object. This method may be called after calling an updater method(s) and before calling the method updateRow to roll back the updates made to a row. If no updates have been made or updateRow has already been called, this method has no effect.
The following is an example for this topic:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUpdatableRS {
private static final String DBURL =
"jdbc:mysql://localhost:3306/mydb?user=usr&password=sql" +
"&useUnicode=true&characterEncoding=UTF-8";
private static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
static {
try {
Class.forName(DBDRIVER).newInstance();
} catch (Exception e){
e.printStackTrace();
}
}
private static Connection getConnection()
{
Connection connection = null;
try {
connection = DriverManager.getConnection(DBURL);
}
catch (Exception e) {
e.printStackTrace();
}
return connection;
}
public static void createEmployees()
{
Connection con = getConnection();
Statement stmt =null;
String createString;
createString = "CREATE TABLE `mydb`.`employees` ("+
"`EmployeeID` int(10) unsigned NOT NULL default '0',"+
"`Name` varchar(45) collate utf8_unicode_ci NOT NULL default '',"+
"`Office` varchar(10) collate utf8_unicode_ci NOT NULL default '',"+
"`CreateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,"+
"PRIMARY KEY (`EmployeeID`)"+
") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
try {
stmt = con.createStatement();
stmt.executeUpdate(createString);
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
private static void dropEmployees()
{
Connection con = getConnection();
Statement stmt =null;
String createString;
createString = "DROP TABLE IF EXISTS `mydb`.`employees`;";
try {
stmt = con.createStatement();
stmt.executeUpdate(createString);
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
public static void showEmployee() {
Connection con = getConnection();
Statement stmt =null;
try {
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("Select * from employees "
+ where EmployeeID=1001");
if (rs.next()) {
System.out.println("EmployeeID : " +
rs.getInt("EmployeeID"));
System.out.println("Name : " + rs.getString("Name"));
System.out.println("Office : " + rs.getString("Office"));
}
else {
System.out.println("No Specified Record.");
}
rs.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
public static void insertEmployee() {
Connection con = getConnection();
Statement stmt =null;
String sqlString = "SELECT EmployeeID, Name, " +
" Office FROM employees;";
try {
stmt = con.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sqlString);
//Check the result set is an updatable result set
int concurrency = rs.getConcurrency();
if (concurrency == ResultSet.CONCUR_UPDATABLE) {
rs.moveToInsertRow();
rs.updateInt(1, 1001);
rs.updateString(2, "Divad Walker");
rs.updateString(3, "HQ101");
rs.insertRow();
rs.moveToCurrentRow();
} else {
System.out.println("ResultSet is not an updatable result set.");
}
rs.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
public static void updateEmployee(){
Connection con = getConnection();
Statement stmt =null;"
String sqlString = "SELECT EmployeeID, Name, Office " +
" FROM employees WHERE EmployeeID=1001";
try {
stmt = con.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sqlString);
//Check the result set is an updatable result set
int concurrency = rs.getConcurrency();
if (concurrency == ResultSet.CONCUR_UPDATABLE) {
rs.first();
rs.updateString("Office", "HQ222");
rs.updateRow();
} else {
System.out.println("ResultSet is not an updatable result set.");
}
rs.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
public static void deleteEmployee(){
Connection con = getConnection();
Statement stmt =null;
String sqlString = "SELECT EmployeeID, Name, Office " +
" FROM employees WHERE EmployeeID=1001";
try {
stmt = con.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sqlString);
//Check the result set is an updatable result set
int concurrency = rs.getConcurrency();
if (concurrency == ResultSet.CONCUR_UPDATABLE) {
rs.first();
rs.deleteRow();
} else {
System.out.println("ResultSet is not an updatable result set.");
}
rs.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
public static void main(String[] args) {
dropEmployees();
createEmployees();
insertEmployee();
System.out.println("\nAfter inserting a Record ...");
showEmployee();
updateEmployee();
System.out.println("\nAfter updating a Record ...");
showEmployee();
deleteEmployee();
System.out.println("\nAfter deleting a Record ...");
showEmployee();
}
}
 |
 |
 |
 |
Most Recent java Faqs
- How to uncompress a file in the gzip format?
- How to make a gzip file in Java?
- How to use Java String.split method to split a string by dot?
- How to validate URL in Java?
- How to schedule a job in Java?
- How to return the content in the correct encoding from a servlet?
- What is the difference between JDK and JRE?
Most Viewed java Faqs
- How to read input from console (keyboard) in Java?
- What are class variables in Java?
- What are local variables in Java?
- How to Retrieve Multiple Result Sets from a Stored Procedure in JDBC?
- How to Use Updatable ResultSet in JDBC?
- How to Use JDBC Java to Create Table?
- Why final variable in Enhanced for Loop does not act final?