- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
The ResultSet updateRow() method with example
When we execute certain SQL queries (SELECT query in general) they return tabular data.
The java.sql.ResultSet interface represents such tabular data returned by the SQL statements.
i.e. the ResultSet object holds the tabular data returned by the methods that execute the statements which quires the database (executeQuery() method of the Statement interface in general).
The ResultSet object has a cursor/pointer which points to the current row. Initially this cursor is positioned before first row.
The updateRow() method of the ResultSet interface updates the contents of the current row to the database. For example if we have updated the values of a particular record using the updateXXX() methods of the ResultSet interface (updateNClob(), updateNCharacterStream(), updateString(), updateInt(), updateNString(), updateBinaryStream())
You need to invoke this method to reflect the changes you have made to the row in the database.
Note: To update the contents of a ResultSet using the updateRow() method. The ResultSet should be of type CONCUR_UPDATABLE and, the table should contain a primary key constraint.
Let us create a table with name customers in MySQL database using CREATE statement as shown below:
CREATE TABLE customers ( ID INT, Name VARCHAR(20), AGE INT, SALARY INT, ADDERSS VARCHAR(255), PRIMARY KEY (ID) );
Now, we will insert 12 records in customers table using INSERT statements -
insert into customers values(1, 'Amit', 25, 3000, 'Hyderabad'); insert into customers values(2, 'Kalyan', 27, 4000, 'Vishakhapatnam'); insert into customers values(3, 'Renuka', 30, 5000, 'Delhi'); insert into customers values(4, 'Archana', 24, 1500, 'Mumbai'); insert into customers values(5, 'Kaushik', 30, 9000, 'Kota'); insert into customers values(6, 'Hardik', 45, 6400, 'Bhopal'); insert into customers values(7, 'Trupthi', 33, 4360, 'Ahmedabad'); insert into customers values(8, 'Mithili', 26, 4100, 'Vijayawada'); insert into customers values(9, 'Maneesh', 39, 4000, 'Hyderabad'); insert into customers values(10, 'Rajaneesh', 30, 6400, 'Delhi'); insert into customers values(11, 'Komal', 29, 8000, 'Ahmedabad'); insert into customers values(12, 'Manyata', 25, 5000, 'Vijayawada');
Following JDBC program establishes connection with the database, retrieves the contents of the customers table into a ResultSet object, updates the contents of the row with id value 5 using the updateXXX() methods and updates the entire row to the database using the updateRow() method.
Example
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class ResultSet_updateRow { public static void main(String args[]) throws SQLException { //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/mydatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established......"); //Creating the Statement Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); //Query to retrieve records String query = "Select * from Customers"; //Executing the query ResultSet rs = stmt.executeQuery(query); //Updating the contents of the record with id value 5 while(rs.next()) { if(rs.getInt("ID")==5) { //Updating the salary rs.updateInt("Salary", 11000); //Updating the address rs.updateString("ADDRESS","Narsipatnam"); //Updating the row rs.updateRow(); } } rs.beforeFirst(); System.out.println("Contents of the Customers table after the update: "); //Printing the contents of the table while(rs.next()) { System.out.print("ID: "+rs.getInt("ID")+", "); System.out.print("Name: "+rs.getString("Name")+", "); System.out.print("Age: "+rs.getInt("Age")+", "); System.out.print("Salary: "+rs.getInt("Salary")+", "); System.out.print("Address: "+rs.getString("Address")); System.out.println(); } } }
Output
Connection established...... Contents of the table Customers: ID: 1, Name: Amit, Age: 25, Salary: 3000, Address: Hyderabad ID: 2, Name: Kalyan, Age: 27, Salary: 4000, Address: Vishakhapatnam ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi ID: 4, Name: Archana, Age: 24, Salary: 1500, Address: Mumbai ID: 5, Name: Koushik, Age: 30, Salary: 11000, Address: Narsipatnam ID: 6, Name: Hardik, Age: 45, Salary: 6400, Address: Bhopal ID: 7, Name: Trupthi, Age: 33, Salary: 4360, Address: Ahmedabad ID: 8, Name: Mithili, Age: 26, Salary: 4100, Address: Vijayawada ID: 9, Name: Maneesh, Age: 39, Salary: 4000, Address: Hyderabad ID: 10, Name: Rajaneesh, Age: 30, Salary: 6400, Address: Delhi ID: 11, Name: Komal, Age: 29, Salary: 8000, Address: Ahmedabad ID: 12, Name: Manyata, Age: 25, Salary: 5000, Address: Vijayawada