ResultSet Interface

  • Represents table of data, usually generated by executing a statement that queries database
  • A ResultSet object maintains a cursor to its current row of data
  • Initially cursor points before the first row
  • ResultSet.next method moves the cursor to next row of data
  • ResultSet.next method returns false if there is no more rows in ResultSet object
  • ResultSet cursor moves forward only
  • To iterate through ResultSet object while loop can be used
  • A default ResultSet object is not upgradable – what does it mean?
  • It is possible to produce ResultSet objects that are scrollable and/or updatable(insensitive to updates by others)
  • Statement stmt = con.createStatement(
                                          ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_UPDATABLE);
           ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");
           // rs will be scrollable, will not show changes made by others,
           // and will be updatable
    
  • ResultSet interface provides getter methods(like getString, getInt, and so on) to retrieve column value from the current row
  • Values can be retrieved using either the index number of the column or the name of the column
  • In general using column number will be more efficient, it starts from 1 not 0
  • For maximum portability
    • ResultSet columns within each row should be read in left-to-right order
    • Each column should be read only once
  • For the getter methods,
    • A JDBC driver attempts to convert the underlying data to the Java type specified in the getter method and returns a suitable Java value.
    • The JDBC specification has a table showing the allowable mappings from SQL types to Java types that can be used by the ResultSet getter methods
  • Column names used as input to getter methods are case insensitive
  • When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned
  • The column name option is designed to be used when column names are used in the SQL query that generated the result set
  • For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause
  • ResultSet update methods may be used in 2 ways
    • To update a column value in the current row. In a scrollable ResultSet object, the cursor can be moved backwards and forwards, to an absolute position, or to a position relative to the current row. The following code fragment updates the NAME column in the fifth row of the ResultSet object rs and then uses the method updateRow to update the data source table from which rs was derived.
    • rs.absolute(5); // moves the cursor to the fifth row of rs
             rs.updateString("NAME", "AINSWORTH"); // updates the
                // NAME column of row 5 to be AINSWORTH
             rs.updateRow(); // updates the row in the data source
      
    • To insert column values into the insert row. An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a three-column row, and inserts it into rs and into the data source table using the method insertRow.
    • rs.moveToInsertRow(); // moves cursor to the insert row
             rs.updateString(1, "AINSWORTH"); // updates the
                // first column of the insert row to be AINSWORTH
             rs.updateInt(2,35); // updates the second column to be 35
             rs.updateBoolean(3, true); // updates the third column to true
             rs.insertRow();
             rs.moveToCurrentRow();
      
  • A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results
  • The number, types and properties of a ResultSet object's columns are provided by the ResultSetMetaData object returned by the ResultSet.getMetaData method

Commonly used methods of ResultSet interface

// Retrieves the value of the designated column in the current row of this // ResultSet object as an int in the Java programming language. int getInt(int columnIndex) // Retrieves the value of the designated column in the current row of this // ResultSet object as an int in the Java programming language. int getInt(String columnLabel) // Retrieves the value of the designated column in the current row of this // ResultSet object as a String in the Java programming language. String getString(int columnIndex) // Retrieves the value of the designated column in the current row of this // ResultSet object as a String in the Java programming language. String getString(String columnLabel) // Moves the cursor forward one row from its current position. boolean next() // Moves the cursor to the previous row in this ResultSet object. boolean previous() // Moves the cursor to the last row in this ResultSet object. boolean last()
package com.atuldwivedi.jee.jdbc.basic;

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

public class LearnResultSet {

	public static void main(String[] args) {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// Loading the driver class
			Class.forName("org.h2.Driver");
			// Establishing the connection with data source
			con = DriverManager.getConnection("jdbc:h2:~/test", "sa", "");
			// Creating statement
			stmt = con.createStatement();

			// Performing operation
			rs = stmt.executeQuery("SELECT * FROM LEARN_JDBC");

			// Getting first record
			rs.first();
			System.out.print(rs.getInt(1));
			System.out.print(" ");
			System.out.println(rs.getString(2));

			// Getting the record of particular row
			rs.absolute(2);
			System.out.print(rs.getInt(1));
			System.out.print(" ");
			System.out.println(rs.getString(2));

			// Getting last record
			rs.last();
			System.out.print(rs.getInt(1));
			System.out.print(" ");
			System.out.println(rs.getString(2));

			// Iterating over result set
			while (rs.next()) {
				System.out.print(">>");
				System.out.print(rs.getInt(1));
				System.out.print(" ");
				System.out.println(rs.getString(2));
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
}
 
Footer with Map

Top