How to insert rows into a ResultSet in JDBC?

You can retrieve the contents of a table as a ResultSet and, insert a new row to it directly. To do so, first of all, you need to make sure your ResultSet is updatable.

• The moveToInsertRow() method of the ResultSet interface navigates the cursor to the position where you need to insert the next record. Therefore, move the cursor to the appropriate position to insert a row using this method.

• The updateXXX() methods of the ResultSet interface allows you to insert/update values into the ResultSet object.

  Add values to the new row using these methods for example if you need to insert an integer value at the 1st column and, String value at 2nd column you can do so using the updateInt() and updateString() methods as:

rs.updateInt(1, integerValue);
rs.updateString(2, "stringValue");

• The insertRow() method inserts the row to the ResultSet and to the table as well.

  Therefore, insert the above-created row into result set object and the table using this method.

Example

Assume we have a table in the database named Employees with the following contents:

+----+---------+--------+----------------+
| Id | Name    | Salary | Location       |
+----+---------+--------+----------------+
| 1  | Amit    | 3000   | Hyderabad      |
| 2  | Kalyan  | 4000   | Vishakhapatnam |
| 3  | Renuka  | 6000   | Delhi          |
| 4  | Archana | 96000  | Mumbai         |
| 5  | Sumith  | 11000  | Hyderabad      |
| 6  | Rama    | 11000  | Goa            |
+----+---------+--------+----------------+

Following example retrieves the contents of this table as a ResultSet object and, inserts a new record to the ResultSet as well as the table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class InsertRowToResultSet {
   public static void main(String[] args) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String url = "jdbc:mysql://localhost/testdb";
      Connection con = DriverManager.getConnection(url, "root", "password");
      //Creating a Statement object
      Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from Employees");
      //Printing the contents of the Result Set
      System.out.println("Contents of the Result Set");
      while(rs.next()) {
         System.out.print("ID: " + rs.getInt("id"));
         System.out.print(", Salary: " + rs.getInt("Salary"));
         System.out.print(", Name: " + rs.getString("Name"));
         System.out.println(", Location: " + rs.getString("Location"));
      }
      System.out.println();
      rs.moveToInsertRow();
      rs.updateInt(1, 7);
      rs.updateString(2, "Santosh");
      rs.updateInt(3, 96000);
      rs.updateString(4, "Mumbai");
      rs.insertRow();

      //Retrieving the contents of result set again
      System.out.println("Contents of the ResultSet after inserting another row in to it");
      rs.beforeFirst();
      while(rs.next()) {
         System.out.print("ID: " + rs.getInt("id"));
         System.out.print(", Salary: " + rs.getInt("Salary"));
         System.out.print(", Name: " + rs.getString("Name"));
         System.out.println(", Location: " + rs.getString("Location"));
      }
   }
}

Output

Contents of the Result Set
ID: 1, Salary: 3000, Name: Amit, Location: Hyderabad
ID: 2, Salary: 4000, Name: Kalyan, Location: Vishakhapatnam
ID: 3, Salary: 6000, Name: Renuka, Location: Delhi
ID: 4, Salary: 96000, Name: Archana, Location: Mumbai
ID: 5, Salary: 11000, Name: Sumith, Location: Hyderabad
ID: 6, Salary: 11000, Name: Rama, Location: Goa

Contents of the ResultSet after inserting another row in to it
ID: 1, Salary: 3000, Name: Amit, Location: Hyderabad
ID: 2, Salary: 4000, Name: Kalyan, Location: Vishakhapatnam
ID: 3, Salary: 6000, Name: Renuka, Location: Delhi
ID: 4, Salary: 96000, Name: Archana, Location: Mumbai
ID: 5, Salary: 11000, Name: Sumith, Location: Hyderabad
ID: 6, Salary: 11000, Name: Rama, Location: Goa
ID: 7, Salary: 96000, Name: Santosh, Location: Mumbai