”;
The ResultSet interface contains a collection of update methods for updating the data of a result set.
As with the get methods, there are two update methods for each data type −
-
One that takes in a column name.
-
One that takes in a column index.
For example, to update a String column of the current row of a result set, you would use one of the following updateString() methods −
S.N. | Methods & Description |
---|---|
1 | public void updateString(int columnIndex, String s) throws SQLException
Changes the String in the specified column to the value of s.
|
2 | public void updateString(String columnName, String s) throws SQLException
Similar to the previous method, except that the column is
|
There are update methods for the eight primitive data types, as well as String, Object, URL, and the SQL data types in the java.sql package.
Updating a row in the result set changes the columns of the current row in the ResultSet object, but not in the underlying database. To update your changes to the row in the database, you need to invoke one of the following methods.
S.N. | Methods & Description |
---|---|
1 | public void updateRow()
Updates the current row by updating the corresponding row in the database.
|
2 | public void deleteRow()
Deletes the current row from the database
|
3 | public void refreshRow()
Refreshes the data in the result set to reflect any recent changes in the database.
|
4 | public void cancelRowUpdates()
Cancels any updates made on the current row.
|
5 | public void insertRow()
Inserts a row into the database. This method can only be invoked when the cursor is pointing to the insert row.
|
Following is the example, which makes use of the ResultSet.CONCUR_UPDATABLE and ResultSet.TYPE_SCROLL_INSENSITIVE described in the Result Set tutorial. This example would explain INSERT, UPDATE and DELETE operation on a table.
It should be noted that tables you are working on should have Primary Key set properly. Let”s update our referece table first.
Drop the table and Create the table Employees again as follows −
mysql> use TUTORIALSPOINT; mysql> drop table Employees; Query OK, 0 rows affected (0.08 sec) mysql> create table Employees -> ( -> id int primary key auto_increment, -> age int not null, -> first varchar (255), -> last varchar (255) -> ); Query OK, 0 rows affected (0.08 sec) mysql>
Create Data Records
Finally you create few records in Employee table as follows −
mysql> INSERT INTO Employees(AGE, FIRST, LAST) VALUES (18, ''Zara'', ''Ali''); Query OK, 1 row affected (0.05 sec) mysql> INSERT INTO Employees(AGE, FIRST, LAST) VALUES (25, ''Mahnaz'', ''Fatma''); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Employees(AGE, FIRST, LAST) VALUES (30, ''Zaid'', ''Khan''); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Employees(AGE, FIRST, LAST) VALUES (28, ''Sumit'', ''Mittal''); Query OK, 1 row affected (0.00 sec) mysql>
Copy and paste the following example in TestApplication.java, compile and run as follows −
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestApplication { static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER = "guest"; static final String PASS = "guest123"; static final String QUERY = "SELECT id, first, last, age FROM Employees"; public static void printResultSet(ResultSet rs) throws SQLException{ // Ensure we start with first row rs.beforeFirst(); while(rs.next()){ // Display values System.out.print("ID: " + rs.getInt("id")); System.out.print(", Age: " + rs.getInt("age")); System.out.print(", First: " + rs.getString("first")); System.out.println(", Last: " + rs.getString("last")); } System.out.println(); } public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery(QUERY); ) { System.out.println("List result set for reference...."); printResultSet(rs); // Loop through result set and add 5 in age // Move to Before first position 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..."); printResultSet(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.updateString("first","John"); rs.updateString("last","Paul"); rs.updateInt("age",40); // Commit row rs.insertRow(); System.out.println("List result set showing new set..."); printResultSet(rs); } catch (SQLException e) { e.printStackTrace(); } } }
Now let us compile the above example as follows −
C:>javac TestApplication.java C:>
When you run TestApplication, it produces the following result −
C:>java TestApplication List result set for reference.... ID: 1, Age: 18, First: Zara, Last: Ali ID: 2, Age: 25, First: Mahnaz, Last: Fatma ID: 3, Age: 30, First: Zaid, Last: Khan ID: 4, Age: 28, First: Sumit, Last: Mittal List result set showing new ages... ID: 1, Age: 23, First: Zara, Last: Ali ID: 2, Age: 30, First: Mahnaz, Last: Fatma ID: 3, Age: 35, First: Zaid, Last: Khan ID: 4, Age: 33, First: Sumit, Last: Mittal Inserting a new record... List result set showing new set... ID: 1, Age: 23, First: Zara, Last: Ali ID: 2, Age: 30, First: Mahnaz, Last: Fatma ID: 3, Age: 35, First: Zaid, Last: Khan ID: 4, Age: 33, First: Sumit, Last: Mittal ID: 5, Age: 40, First: John, Last: Paul C:>
”;