Java ResultSet insertRow() method with example

JDBCJava 8Object Oriented ProgrammingProgramming

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 insertRow() method of the ResultSet interface inserts the contents of the row into the ResultSet object (and into the table as well).

rs.moveToInsertRow();
rs.updateInt("ID", id);
rs.updateString("First_Name", "Ishant");
rs.updateString("Last_Name", "Sharma");
rs.updateDate("Date_Of_Birth", new Date(904694400000L));
rs.updateString("Place_Of_Birth", "Delhi");
rs.updateString("Country", "India");
rs.insertRow();

To insert a record into a ResultSet using the insertRow() method −

  • Create the ResultSet object as −

Statement stmt = con.createStatement(
   ResultSet.TYPE_SCROLL_SENSITIVE,
   ResultSet.CONCUR_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 in to the ResultSet object add values to the new row using these methods for example if you need to insert an integer value at 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");
  • Finally, insert the row into the table and the ResultSet using the insertRow() method.

rs.insertRow();

Example

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
   );

Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following example retrieves the contents of the MyPlayers table as a ResultSet object and, inserts a new record into it, as well as the table, using the insertRow() method.

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ResultSet_insertRow {
   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_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      //Query to retrieve records
      String query = "Select * from MyPlayers";
      //Executing the query
      ResultSet rs = stmt.executeQuery(query);
      rs.last();
      int id = rs.getInt("ID")+1;
      rs.moveToInsertRow();
      rs.updateInt("ID", id);
      rs.updateString("First_Name", "Ishant");
      rs.updateString("Last_Name", "Sharma");
      rs.updateDate("Date_Of_Birth", new Date(904694400000L));
      rs.updateString("Place_Of_Birth", "Delhi");
      rs.updateString("Country", "India");
      rs.insertRow();
      rs.beforeFirst();
      System.out.println("Contents of the table after inserting a new row: ");
      while(rs.next()) {
         System.out.print("ID: "+rs.getInt("ID")+", ");
         System.out.print("Name: "+rs.getString("First_Name")+", ");
         System.out.print("Age: "+rs.getString("Last_Name")+", ");
         System.out.print("Salary: "+rs.getDate("Date_Of_Birth")+", ");
         System.out.print("Country: "+rs.getString("Place_Of_Birth")+", ");
         System.out.print("Address: "+rs.getString("Country"));
         System.out.println();
      }
   }
}

Output

Connection established......
Contents of the table after inserting a new row:
ID: 1, Name: Shikhar, Age: Dhawan, Salary: 1981-12-05, Country: Delhi, Address: India
ID: 2, Name: Jonathan, Age: Trott, Salary: 1981-04-22, Country: CapeTown, Address: SouthAfrica
ID: 3, Name: Kumara, Age: Sangakkara, Salary: 1977-10-27, Country: Matale, Address: Srilanka
ID: 4, Name: Virat, Age: Kohli, Salary: 1988-11-05, Country: Mumbai, Address: India
ID: 5, Name: Rohit, Age: Sharma, Salary: 1987-04-30, Country: Nagpur, Address: India
ID: 6, Name: Ravindra, Age: Jadeja, Salary: 1988-12-06, Country: Nagpur, Address: India
ID: 7, Name: James, Age: Anderson, Salary: 1982-06-30, Country: Burnley , Address: England
ID: 8, Name: Ishant, Age: Sharma, Salary: 1998-09-02, Country: Delhi, Address: India
raja
Published on 09-May-2019 16:02:44
Advertisements