How do I create a scrollable result sets?

package org.kodejava.example.jdbc;

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

public class ScrollableResultSetExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Since JDBC 2.0 (JDK 1.2) a scrollable ResultSet was
            // introduced to the java.sql API family. Using this
            // ResultSet enables us to navigate the result set in
            // forward or backward way.
            //
            // To enable the scrollable ResultSet we need to create
            // a statement object by defining the ResultSet type
            // (ResultSet.TYPE_SCROLL_SENSITIVE,
            // ResultSet.TYPE_SCROLL_INSENSITIVE). If you define the
            // ResultSet type to ResultSet.TYPE_FORWARD_ONLY then you
            // get a regular ResultSet where you can move forward
            // only as in JDBC 1.0
            Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

            // This result set is a scrollable result set
            String query = "SELECT * FROM products";
            ResultSet resultSet = statement.executeQuery(query);

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

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How do I know the current position of cursor?

While moving through the result set you might want to know in what row is the current cursor positioned. To get this information you can call the ResultSet’s getRow() method.

package org.kodejava.example.jdbc;

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

public class ScrollableGetRowExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

            String sql = "SELECT code, name, price FROM products";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                String code = resultSet.getString("code");

                // By calling the getRow() method of the result set we
                // know what is the current row in the result set that
                // we are reading the data from.
                int row = resultSet.getRow();

                System.out.println(row + ". " + code);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The result of the code snippet above:

1. P0000001
2. P0000002
3. P0000003
4. P0000004
5. P0000005

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How do I check if cursor is in the last row?

On the previous example we check for the beginning of the result set. Now using the isAfterLast() method we can check the opposite whether the pointer is at the end of the result set.

package org.kodejava.example.jdbc;

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

public class ScrollableIsAfterLastExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

            String sql = "SELECT code, name, price FROM products";
            ResultSet resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                // Read all ResultSet data
            }

            // Using the isAfterLast() method we can check if we are
            // at the end of the result set.
            if (resultSet.isAfterLast()) {
                System.out.println("You are at the end of the " +
                    "result set.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How do I check if cursor is in the first row?

When iterating the scrollable result sets you can check whether you are in the beginning of the result set or not. The isBeforFirst() method check if the position is at the beginning, if yes this method will return true, otherwise it will return false.

package org.kodejava.example.jdbc;

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

public class ScrollableIsBeforeFirstExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

            String sql = "SELECT code, name, price FROM products";
            ResultSet resultSet = statement.executeQuery(sql);

            // Using the isBeforeFirst() method we can check if we are at
            // the beginning of the result set.
            if (resultSet.isBeforeFirst()) {
                System.out.println("You are at the beginning of the " +
                    "result set.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How do I move cursor to the last record?

To move the cursor to the end of result set (after the last record) you can call the afterLast() method of the result set.

package org.kodejava.example.jdbc;

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

public class ScrollableAfterLastExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

            String sql = "SELECT code, name, price FROM products";
            ResultSet resultSet = statement.executeQuery(sql);

            // Let's jump to the last records. The afterLast() method will
            // position the cursor after the last record.
            resultSet.afterLast();

            while (resultSet.previous()) {
                String code = resultSet.getString("code");
                String name = resultSet.getString("name");
                double price = resultSet.getDouble("price");

                System.out.println(code + "\t" + name + "\t" + price);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How do I move cursor in scrollable result sets?

package org.kodejava.example.jdbc;

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

public class ScrollableMoveExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // In this example we first create a statement that allows us
            // to go back and forth in the result set object. First we'll
            // iterate the result from beginning to the end.
            Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

            String sql = "SELECT id, code, name, price FROM products";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                print(resultSet);
            }

            System.out.println();

            // Now the result set pointer is placed after the last record.
            // With the previous method of the result set we can now move
            // the pointer backward to the beginning of the result set.
            while (resultSet.previous()) {
                print(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void print(ResultSet resultSet) throws SQLException {
        long id = resultSet.getLong("id");
        String code = resultSet.getString("code");
        String name = resultSet.getString("name");
        double price = resultSet.getDouble("price");

        System.out.println(id + "\t" + code + "\t" + name + "\t" + price);
    }
}

Here are some results of our program.

1   P0000001    Java 2 Notebook 25.0
2   P0000002    Java Servlet Programming    30.0
3   P0000003    PHP Programming 20.0
4   P0000004    Longman Active Study Dictionary 40.0
5   P0000005    Ruby on Rails   24.0

5   P0000005    Ruby on Rails   24.0
4   P0000004    Longman Active Study Dictionary 40.0
3   P0000003    PHP Programming 20.0
2   P0000002    Java Servlet Programming    30.0
1   P0000001    Java 2 Notebook 25.0

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central