Iterating Through JDBC ResultSet


 

Iterating Through JDBC ResultSet

In this tutorial you will learn how to iterate/traverse in result set object

In this tutorial you will learn how to iterate/traverse in result set object

Iterating Through ResultSet

The ResultSet object are basically three types. The iteration depends on the types of the result set object

1. Forward Only.- In this type of result set object we move only from start to end i.e in forward direction.

2.Scroll sensitive- This type of object have the capability to move the cursor in both the direction. i.e it us capable for scrolling.

3. Scroll insensitive. This type of result set object is not capable for scrolling. i.e the cursor moves in only one direction.

Example-

IteratingThroughResultSet.java

package roseindia.net;

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

public class ScrollSensitiveExample {
	public static void main(String[] args) throws SQLException {
		Connection connection = null; // connection reference variable for
										// getting
		// connection
		Statement statement = null; // Statement reference variable for query
		// Execution
		ResultSet resultSet = null; // ResultSet reference variable for saving
									// query
		// result
		String conUrl = "jdbc:mysql://localhost:3306/";
		String driverName = "com.mysql.jdbc.Driver";
		String databaseName = "student";
		String usrName = "root";
		String usrPass = "root";
		try {
			// Loading Driver
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			System.out.println(e.toString());
		}
		try {
			// Getting Connection
			connection = DriverManager.getConnection(conUrl + databaseName,
					usrName, usrPass);

			// Getting reference to connection object and Setting ResultSet
			// object types
			statement = connection
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);
			// creating Query String
			String query = "SELECT * FROM student";
			// excecuting query
			resultSet = statement.executeQuery(query);
			// Getting Results ifrom ForWard only direction
			System.out.println("From First to lat ");
			while (resultSet.next()) {
				System.out.println("Roll No " + resultSet.getInt("RollNo")
						+ ", Name " + resultSet.getString("Name") + ", Course "
						+ resultSet.getString("Course") + ", Address "
						+ resultSet.getString("Address"));
			}
			System.out.println("Printing lastValue ");
			resultSet.last();
			// Didplaying data of tables
			System.out.println("Roll No " + resultSet.getInt("RollNo")
					+ ", Name " + resultSet.getString("Name") + ", Course "
					+ resultSet.getString("Course") + ", Address "
					+ resultSet.getString("Address"));
			// resultSet.previous();
			resultSet.afterLast();
			System.out.println("From Last to first\n");
			while (resultSet.previous()) {
				System.out.println("Roll No " + resultSet.getInt("RollNo")
						+ ", Name " + resultSet.getString("Name") + ", Course "
						+ resultSet.getString("Course") + ", Address "
						+ resultSet.getString("Address"));
			}
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			// Closing connection
			resultSet.close();
			statement.close();
			connection.close();
		}
	}
}

When you run this application it will display message as shown below:


From First to lat
Roll No 1, Name Rajan, Course MCA, Address Motihari
Roll No 3, Name Rohan, Course MCA, Address Mumbai
Printing lastValue
Roll No 3, Name Rohan, Course MCA, Address Mumbai
From Last to first

Roll No 3, Name Rohan, Course MCA, Address Mumbai
Roll No 1, Name Rajan, Course MCA, Address Motihari

Download this example code

Ads