What are the differences between ResultSet types: TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE?

ResultSet is an interface in Java that provides methods to iterate over a set of results obtained from executing a SQL statement. ResultSet can be of different types based on its characteristics and the needs of the application.

One such aspect is the scrollability of the ResultSet, which allows us to navigate forward and backward in the ResultSet. There are two main types of scrollable ResultSets: TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE. Let’s discuss some key differences between these two:

1. Updatability - The TYPE_SCROLL_SENSITIVE ResultSet is updatable, which means it allows you to modify the data in the underlying table, whereas TYPE_SCROLL_INSENSITIVE ResultSet is not updatable. However, you can still insert rows into a TYPE_SCROLL_INSENSITIVE ResultSet.

2. Sensitivity - The TYPE_SCROLL_SENSITIVE ResultSet is sensitive to changes made to the data in the underlying table. If another transaction modifies the same data in the underlying table while you have a ResultSet open, you will see those changes when you scroll through the ResultSet. In contrast, if you have a TYPE_SCROLL_INSENSITIVE ResultSet open and another transaction modifies the underlying data, you will not see those changes in that ResultSet.

3. Performance - The TYPE_SCROLL_INSENSITIVE ResultSet usually performs better than the TYPE_SCROLL_SENSITIVE ResultSet. However, this is dependent on the data and database size.

4. Support - Not all databases support scrollable ResultSet types, particularly TYPE_SCROLL_SENSITIVE ResultSet. Therefore, it is important to check the database JDBC driver documentation to see which ResultSet types are supported.

Here’s a brief example of how to create a TYPE_SCROLL_INSENSITIVE ResultSet:

    String sql = "SELECT * FROM mytable";
    Connection conn = DriverManager.getConnection(url, username, password);
    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = stmt.executeQuery(sql);

And an example of how to create a TYPE_SCROLL_SENSITIVE ResultSet:

    String sql = "SELECT * FROM mytable";
    Connection conn = DriverManager.getConnection(url, username, password);
    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = stmt.executeQuery(sql);

In summary, the difference between the two types of scrollable ResultSets lies in how they handle changes made to underlying data, their updatability, and performance, and as such, you should choose the ResultSet type that best suits your needs.