Types of ResultSet

Types of ResultSet

• The SQL statements that read data from a database query, return the data in a result set. 
• The SELECT statement is the standard way to select rows from a database and view them in a result set. 
• The java.sql.ResultSet interface represents the result set of a database query.

What is the Resultset interface?

• The object of ResultSet maintains a cursor pointing to a row of a table. 
• The term "result set" refers to the row and column data contained in a ResultSet object.
• Initially, cursor points to before the first row.

• Three types of methods
• Navigational methods: Used to move the cursor.
• public boolean next(): is used to move the cursor to the one row next from the current position.
• public boolean previous(): is used to move the cursor to the one row previous from the current position.
• public boolean first(): is used to move the cursor to the first row in result set object.
• public boolean last(): is used to move the cursor to the last row in result set object.
• public void beforeFirst() throws SQLException: is used to moves the cursor just before the first row.
• public void afterLast() throws SQLException: is used to moves the cursor just after the last row.
• public boolean absolute(int row) throws SQLException: is used to moves the cursor to the specified row.
• public boolean relative(int row) throws SQLException: is used to moves the cursor the given number of rows forward or backward, from where it is currently pointing
• Get methods: Used to view the data in the columns of the current row being pointed by the cursor.
• There is a get method for each of the possible data types, and each get method has two versions −
• One that takes in a column name.
• One that takes in a column index.

• public int getInt(int columnIndex): is used to return the data of the specified column index of the current row as int.
• public int getInt(String columnName): is used to return the data of the specified column name of the current row as int.
• public String getString(int columnIndex): is used to return the data of the specified column index of the current row as String.
• public String getString(String columnName): is used to return the data of the specified column name of the current row as String.

• Similarly, there are get methods in the ResultSet interface for each of the eight Java primitive types, as well as common types.
• java.lang.String
• java.lang.Object
• java.net.URL
• java.sql.Date
• java.sql.Time
• java.sql.TimeStamp
• java.sql.Clob
• java.sql.Blob
• Update methods: Used to update the data in the columns of the current row. The updates can then be updated in the underlying database as well.
• The ResultSet interface contains a collection of update methods for updating the data of a result set.
• As with the get methods, there are two update methods for each data type −
• One that takes in a column name.
• One that takes in a column index.
• public void updateString(int columnIndex, String s) throws SQLException: Changes the String in the specified column to the value of s.
• public void updateString(String columnName, String s) throws SQLException: Similar to the previous method, except that the column is specified by its name instead of its index.
• The cursor is movable based on the properties of the ResultSet. 
• There are three connection methods to create statements...
• createStatement(int RSType, int RSConcurrency);
• prepareStatement(String SQL, int RSType, int RSConcurrency);
• prepareCall(String SQL, int RSType, int RSConcurrency);

• RSType means ResultSet Type.
• The possible RSType are given below. 
• If you do not specify any ResultSet type, you will automatically get one that is TYPE_FORWARD_ONLY.
• ResultSet.TYPE_FORWARD_ONLY: The cursor can only move forward in the result set.
• The result set cannot be scrolled; 
• Its cursor moves forward only, from before the first row to after the last row. 
• ResultSet.TYPE_SCROLL_INSENSITIVE: The cursor can scroll forward and backward, and the result set is not sensitive to changes made by others to the database that occur after the result set was created.
• The result can be scrolled; 
• Its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.
• ResultSet.TYPE_SCROLL_SENSITIVE: The cursor can scroll forward and backward, and the result set is sensitive to changes made by others to the database that occur after the result set was created.
• The result can be scrolled; 
• Its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. 
• RSConcurrency means ResultSet Concurrency.
• The possible RSConcurrency are given below. 
• If you do not specify any Concurrency type, you will automatically get one that is CONCUR_READ_ONLY.
• ResultSet.CONCUR_READ_ONLY: Creates a read-only result set. This is the default
• ResultSet.CONCUR_UPDATABLE : Creates an updatable result set.
• Example:

Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);