JDBC - Updatable ResultSet example

Posted on June 14, 2017


Technologies used:   JDK 1.8.0_121 | MySQL 5.7.12 | Eclipse Neon.3

You can access the data in a ResultSet object through a cursor, which point to a row of data in a ResultSet. By default, a ResultSet object is not scrollable / updatable and its cursor moves forward only.

To create a to create a scrollable result set object, you can use either the ResultSet.TYPE_SCROLL_INSENSITIVE or the ResultSet.TYPE_SCROLL_SENSITIVE type, which moves cursor forward and backward relative to current position.

Similarly, you can use the ResultSet.CONCUR_UPDATABLE concurrency to create an updatable result set object. An updatable ResultSet object allows us to update a column value, insert column values and delete a row.

Here the code snippet that makes a scrollable and updatable result set object.

PreparedStatement stmt = conn.prepareStatement(sql,
	  ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery();

The following examples demonstrate how to retrieve and modify values from ResultSet.

Database table

Execute the following sql script in MySQL database. We will use the EMPLOYEE table to demonstrate the scrollable/updatable ResultSet object.

-- Create a table
CREATE TABLE employee (
  id int(11) NOT NULL,
  name varchar(30) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Insert sample data in a table
insert into employee (id,name ) values (1,'Sunil Singh');
insert into employee (id,name ) values (2,'David Miller');
insert into employee (id,name ) values (3,'Dean Jones');
insert into employee (id,name ) values (4,'Ramesh Kumar');

How to move the ResultSet cursor backward and forward

The following example demonstrates how to move the ResultSet cursor to forward or backward direction using the absolute(), first() and last() methods of the ResultSet object.

package com.boraji.tutorial.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author imssbora
 */
public class UpdatableResultSetExample {
   public static void main(String[] args) {
      String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
      String username = "root";
      String password = "admin";
      String sql = "select id, name from employee";

      try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
            PreparedStatement stmt = conn.prepareStatement(sql,
                  ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = stmt.executeQuery()) {

         // Move cursor to 1st row
         rs.absolute(2);
         System.out.println("ID : " + rs.getInt("ID") + " \tNAME : " + rs.getString("NAME"));
         
         // Move cursor to 4th row
         rs.absolute(4);
         System.out.println("ID : " + rs.getInt("ID") + " \tNAME : " + rs.getString("NAME"));

         // Move cursor to last row
         rs.first();
         System.out.println("ID : " + rs.getInt("ID") + " \tNAME : " + rs.getString("NAME"));
         
         // Move cursor to last row
         rs.last();
         System.out.println("ID : " + rs.getInt("ID") + " \tNAME : " + rs.getString("NAME"));
        
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Output

ID : 2 	NAME : David Miller
ID : 4 	NAME : Ramesh Kumar
ID : 1 	NAME : Sunil Singh
ID : 4 	NAME : Ramesh Kumar

How to insert a new row in a ResultSet object

The following example demonstrates how to insert a new row in an updatable ResultSet object.

package com.boraji.tutorial.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author imssbora
 */
public class UpdatableResultSetExample {
   public static void main(String[] args) {
      String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
      String username = "root";
      String password = "admin";
      String sql = "select id, name from employee";

      try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
            PreparedStatement stmt = conn.prepareStatement(sql,
                  ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = stmt.executeQuery()) {
         
         // Save the current cursor position, and move cursor to the insert row, 
         rs.moveToInsertRow();
         //Set columns values
         rs.updateInt("ID", 5);
         rs.updateString("NAME", "Tom Hardy");
         //Insert new row
         rs.insertRow();
         
         // Move cursor back to saved position
         rs.moveToCurrentRow();
        
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Output

ID : 1 	NAME : Sunil Singh
ID : 2 	NAME : David Miller
ID : 3 	NAME : Dean Jones
ID : 4 	NAME : Ramesh Kumar
ID : 5 	NAME : Tom Hardy

 

How to update a row in a ResultSet object

The following example demonstrates how to update an existing row in an updatable ResultSet object.

package com.boraji.tutorial.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author imssbora
 */
public class UpdatableResultSetExample {
   public static void main(String[] args) {
      String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
      String username = "root";
      String password = "admin";
      String sql = "select id, name from employee";

      try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
            PreparedStatement stmt = conn.prepareStatement(sql,
                  ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = stmt.executeQuery()) {
         
         // Update 2nd row
         // Move cursor to 2nd row
         rs.absolute(2);
         rs.updateString("NAME","David");
         rs.updateRow();
         
         System.out.println("ID : " + rs.getInt("ID") + " \tNAME : " + rs.getString("NAME"));
         
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Output

ID : 2 	NAME : David

How to delete a row in a ResultSet object

The following example demonstrates how to delete a row in an updatable ResultSet object.

package com.boraji.tutorial.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author imssbora
 */
public class UpdatableResultSetExample {
   public static void main(String[] args) {
      String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
      String username = "root";
      String password = "admin";
      String sql = "select id, name from employee";

      try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
            PreparedStatement stmt = conn.prepareStatement(sql,
                  ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = stmt.executeQuery()) {

         // Move cursor to last row
         // Delete last row
         rs.last();
         rs.deleteRow();

         // Move cursor to 2nd row
         // Delete 2nd row
         rs.absolute(2);
         rs.deleteRow();

         // Move cursor to before the first row. 
         rs.absolute(0);

         while (rs.next()) {
            System.out.println(
                  "ID : " + rs.getInt("ID") + " \tNAME : " + rs.getString("NAME"));
         }

      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Output

ID : 1 	NAME : Sunil Singh
ID : 3 	NAME : Dean Jones
ID : 4 	NAME : Ramesh Kumar