In this example we shall show you how to use an updatable ResultSet. An updatable result set allows modification to data in a table through the result set. To create an Updatable ResultSet and use its capabilites for data updates in a database one should perform the following steps:
- Load the JDBC driver, using the
forName(String className)API method of the Class. In this example we use the MySQL JDBC driver. - Create a Connection to the database. Invoke the
getConnection(String url, String user, String password)API method of the DriverManager to create the connection. - Create a Statement, using the
createStatement()API method of the Connection. The Statement must have the concurrency type ResultSet.CONCUR_UPDATABLE, in order to return updatable result sets. - Execute the query to the database, using the
executeQuery(String sql)API method of the Statement over a specified column. The data produced by the given query is a ResultSet. - Get the data from the current row. Use
the next()API method and thegetString(String columnLabel)API method of the ResultSet, to get the value of the designated column in the current row of this ResultSet object. - Invoke the
moveToInsertRow()API method to use the insert row. It is a buffer where a new row may be constructed, by calling the updater methods prior to inserting the row into the result set. - Set values for the new row, using the
updateString(String columnLabel, String x)API method. - Insert the new row, using the
insertRow()API method. - Move the cursor to another row, with the
absolute(int row)API method. - Update the value of a specific column on that row, with the
updateString(String columnLabel, String x)API method. - Update the row, with the
updateRow()API method. - If we want to discard the update to the row we could use
cancelRowUpdates()API method. - Move the cursor to another row in order to delete it, with the
deleteRow()API method. - Retrieve the current values of the row from the database, with the refreshRow() API method.
- Move the cursor to the front of this ResultSet object, using the
beforeFirst()API method, and then display table data again to check the updates, withnext()andgetString(String columnLabel)API methods,
as described in the code snippet below.
package com.javacodegeeks.snippets.core;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class UpdatableResultSetExample {
public static void main(String[] args) {
Connection connection = null;
try {
// Load the MySQL JDBC driver
String driverName = "com.mysql.jdbc.Driver";
Class.forName(driverName);
// Create a connection to the database
String serverName = "localhost";
String schema = "test";
String url = "jdbc:mysql://" + serverName + "/" + schema;
String username = "username";
String password = "password";
connection = DriverManager.getConnection(url, username, password);
System.out.println("Successfully Connected to the database!");
} catch (ClassNotFoundException e) {
System.out.println("Could not find the database driver " + e.getMessage());
} catch (SQLException e) {
System.out.println("Could not connect to the database " + e.getMessage());
}
try {
/*
* An updatable result set allows modification to data in a table through the result set.
* If the database does not support updatable result sets, the result sets returned from
* executeQuery() will be read-only. To get updatable results, the Statement object used
* to create the result sets must have the concurrency type ResultSet.CONCUR_UPDATABLE.
* The query of an updatable result set must specify the primary key as one of the selected
* columns and select from only one table.
*/
// Create a statement that will return updatable result sets
Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
// Primary key test_col must be specified so that the result set is updatable
ResultSet results = statement.executeQuery("SELECT test_col FROM test_table");
System.out.println("Table data prior results handling... ");
// Display table data
while (results.next()) {
// Get the data from the current row using the column name - column data are in the VARCHAR format
String data = results.getString("test_col");
System.out.println("Fetching data by column name for row " + results.getRow() + " : " + data);
}
// An updatable result supports a row called the "insert row". It is a buffer for holding the values of a new row
results.moveToInsertRow();
// Set values for the new row.
results.updateString("test_col", "inserted_test_value");
// Insert the new row
results.insertRow();
// Move cursor to the third row
results.absolute(3);
// Update the value of column test_col on that row
results.updateString("test_col", "updated_test_value");
// Update the row; if auto-commit is enabled, update is committed
results.updateRow();
// Discard the update to the row we could use
// results.cancelRowUpdates();
// Delete the fifth row
results.absolute(5);
results.deleteRow();
// Retrieve the current values of the row from the database
results.refreshRow();
System.out.println("Table data after results handling... ");
results.beforeFirst();
// Display table data
while (results.next()) {
// Get the data from the current row using the column name - column data are in the VARCHAR format
String data = results.getString("test_col");
System.out.println("Fetching data by column name for row " + results.getRow() + " : " + data);
}
} catch (SQLException e) {
System.out.println("Error while operating on updatable ResultSet " + e.getMessage());
}
}
}
Example Output:
Successfully Connected to the database!
Table data prior results handling...
Fetching data by column name for row 1 : test_value_0
Fetching data by column name for row 2 : test_value_1
Fetching data by column name for row 3 : test_value_2
Fetching data by column name for row 4 : test_value_3
Fetching data by column name for row 5 : test_value_4
Fetching data by column name for row 6 : test_value_5
Fetching data by column name for row 7 : test_value_6
Fetching data by column name for row 8 : test_value_7
Fetching data by column name for row 9 : test_value_8
Fetching data by column name for row 10 : test_value_9
Table data after results handling...
Fetching data by column name for row 1 : test_value_0
Fetching data by column name for row 2 : test_value_1
Fetching data by column name for row 3 : updated_test_value
Fetching data by column name for row 4 : test_value_3
Fetching data by column name for row 5 : test_value_5
Fetching data by column name for row 6 : test_value_6
Fetching data by column name for row 7 : test_value_7
Fetching data by column name for row 8 : test_value_8
Fetching data by column name for row 9 : test_value_9
Fetching data by column name for row 10 : inserted_test_value
This was an example of how to use an updatable ResultSet in Java.
