Resultset in java

Resultset:

A result set is an interface that is present in the package java.sql and the resultset is used to store the data that are returned from the database table after executing the SQL statements.

 The object of the resultset is used to maintain the cursor point at the result data. By default, the first row of the resultset will be pointed by the cursor.

Resultset types:

1. Forward Only (ResultSet.TYPE_FORWARD_ONLY)

This type of result set is used to move in the form of a forwarding direction that is from the first row to the last row. By using the next () method we can forward the resultset.

Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("select * from tbluser");

2. Scroll Insensitive (ResultSet.TYPE_SCROLL_INSENSITIVE)

This type of result set is used to scroll both in forwarding and backward directions and it is also can be scrolled to absolute positions by using the absolute() method. It does not change the sensitive data.

Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("select * from tbluser");

3.Scroll Sensitive (ResultSet.TYPE_SCROLL_SENSITIVE)

This type of result set is used to scroll both in forwarding and backward directions and it is also can be scrolled to absolute positions by using the absolute() method. It is used to change sensitive data.

Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("select * from tbluser");

Resultset concurrency:

Read Only (ResultSet.CONCUR_READ_ONLY)

This is the default concurrency model.  We can only perform Read-Only operations on ResultSet Instance. No update operations are allowed in this read-only.

Updatable (ResultSet.CONCUR_UPDATABLE)

we can perform update operations on the ResultSet instance.

Resultset example:

package com.journaldev.examples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
/**
* Java Resultset Example of Retrieving information.
* 
* @author Rahul
*
*/
public class ResultSetDemo {
public static void main(String[] args) {
String query = "select sid, firstname, lastname, dob from student";
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/empdb", "root", "root");
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
Integer empId = rs.getInt(1);
String firstName = rs.getString(2);
String lastName = rs.getString(3);
Date dob = rs.getDate(4);
System.out.println("SId:" + SId);
System.out.println("firstName:" + firstName);
System.out.println("lastName:" + lastName);
System.out.println("dob:" + dob);
System.out.println("");
}
rs.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
stmt.close();
conn.close();
} catch (Exception e) {}
}
}
}

Output:

empId:101
firstName:Ravi
lastName:Krishna
dob:20-04-1999
empId:102
firstName:Hari
lastName:Ram
dob:22-02-1989
empId:3
firstName:Harish
lastName:Rao
dob:25-03-1000

Resultset methods:

Example:

package com.STH.JDBC;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ResultSet_Example { 
public static void main(String[] args) throws ClassNotFoundException {
// to do the Auto-generated method stub
//Select the query as
String select_query = "select * from student";
Class.forName("oracle.jdbc.driver.OracleDriver"); 
//Connecting to Oracle DB
try(Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:system/[email protected]:1521:X
E"))
{
//Creating DatabaseMetaData object
DatabaseMetaData dbmd = conn.getMetaData();
boolean isSupportResultSetType = 
dbmd.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
if(isSupportResultSetType == true)
{
// Creating prepared Statement
PreparedStatement pstatemnt1 = 
conn.prepareStatement(select_query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.
CONCUR_UPDATABLE);
ResultSet rs = pstatemnt1.executeQuery();
//Moving the cursor to point the first row
rs.first();
System.out.println("FIRST ROW \n  STUDENT NUM = " + 
rs.getInt("SNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
//Moving the cursor to point the last row 
rs.last();
System.out.println("LAST ROW \n STUDENT NUM = " +
rs.getInt("SNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
//Moving the cursor to point the before the first row
rs.beforeFirst();
System.out.println("Cursor is pointing at before the
first row. Use next() to move in forward direction");
//Moving the cursor to point the first row using next() 
rs.next();
System.out.println("FIRST ROW \n EMP NUM = " +
rs.getInt("SNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
//Moving the cursor to point the after last row 
rs.afterLast();
System.out.println("Cursor is pointing at after the last row. Use previous() to move in backward direction");
//Moving the cursor to point the last row using previous() 
rs.previous();
System.out.println("LAST ROW \n student NUM = " + 
rs.getInt("SNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
//Moving the cursor to point the third row 
rs.absolute(3);
System.out.println("Cursor is pointing at 3rd row");
System.out.println("THIRD ROW \n student NUM = " +
rs.getInt("SNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
//Moving the cursor to point the previous row of third row 
rs.relative(-1);
System.out.println("Cursor is pointing to the 1 row previous to the 3rd row");
System.out.println("Second ROW \n student NUM = " + rs.getInt("SNum") + "\n LAST NAME = "+
rs.getString(2)+"\n FIRST NAME = " + 
rs.getString(3));
//Moving the cursor to point the 4th row after the 2nd row 
rs.relative(4);
System.out.println("Cursor is pointing to the 4th row after the 2nd row");
System.out.println("SIXTH ROW \n STUDENT NUM = " +
rs.getInt("SNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
//Moving the cursor to point the current row 
System.out.println(" Current Row = " + rs.getRow());


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

Output:

Data in the STUDENT table.