JDBC Scrollable ResultSet Demo

Click here to watch in Youtube : https://www.youtube.com/watch?v=JbEJ-K1yKvI

Click the below Image to Enlarge

JDBCScrollableResultSetDemo Project Dir Structure

JDBCScrollableResultSetDemo.java

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

public class JDBCScrollableResultSetDemo
{
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/world";

    // Database credentials
    static final String USERNAME = "root";
    static final String PASSWORD = "root";

    public static void main(String[] args)
    {

        JDBCScrollableResultSetDemo jdbcScrollableResultSetDemo =
                                              new JDBCScrollableResultSetDemo();
        jdbcScrollableResultSetDemo.getCityInformation();

    }

    private void getCityInformation()
    {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try
        {
            /*
             * Register the JDBC driver in DriverManager
             */

            Class.forName(JDBC_DRIVER);

            /*
             * Establish connection to the Database using DriverManager
             */

            connection = DriverManager
                    .getConnection(DB_URL, USERNAME, PASSWORD);

            String sql = "select * from city limit ?";

            /*
             * Execute the query
             */
            preparedStatement = connection
                    .prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                            ResultSet.CONCUR_UPDATABLE);
            preparedStatement.setInt(1, 10);

            ResultSet rs = preparedStatement.executeQuery();

            /*
             * ResultSet Move Forward
             */

            System.out
                    .println("------------- ResultSet Move Forward -----------------");

            while (rs.next())
            {
                int id = rs.getInt("ID");
                String name = rs.getString("Name");
                String countryCode = rs.getString("CountryCode");
                String district = rs.getString("District");
                int population = rs.getInt("Population");

                /*
                 * Display values
                 */
                System.out.print("ID: " + id);
                System.out.print(", Name: " + name);
                System.out.print(", CountryCode: " + countryCode);
                System.out.print(", District: " + district);
                System.out.println(", Population: " + population);
            }

            System.out
                    .println("\n------------- ResultSet Move Backward ----------------");

            /*
             * Move the ResultSet cursor to the last row
             */
            rs.last();

            while (rs.previous())
            {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String countryCode = rs.getString(3);
                String district = rs.getString(4);
                int population = rs.getInt(5);

                /*
                 * Display values
                 */
                System.out.print("ID: " + id);
                System.out.print(", Name: " + name);
                System.out.print(", CountryCode: " + countryCode);
                System.out.print(", District: " + district);
                System.out.println(", Population: " + population);
            }

            System.out
                    .println("\n---- ResultSet Move to Particular position --------");

            // Getting the record of 3rd row
            rs.absolute(3);
            int id = rs.getInt(1);
            String name = rs.getString(2);
            String countryCode = rs.getString(3);
            String district = rs.getString(4);
            int population = rs.getInt(5);

            /*
             * Display values
             */
            System.out.print("ID: " + id);
            System.out.print(", Name: " + name);
            System.out.print(", CountryCode: " + countryCode);
            System.out.print(", District: " + district);
            System.out.println(", Population: " + population);

            rs.close();

        }
        catch (SQLException se)
        {
            /*
             * Handle errors for JDBC
             */
            se.printStackTrace();
        }
        catch (ClassNotFoundException e)
        {
            /*
             * Handle errors for Class.forName
             */
            e.printStackTrace();
        }
        finally
        {
            /*
             * finally block used to close resources
             */
            try
            {
                if (preparedStatement != null)
                {
                    preparedStatement.close();
                }
            }
            catch (SQLException sqlException)
            {
                sqlException.printStackTrace();
            }
            try
            {
                if (connection != null)
                {
                    connection.close();
                }
            }
            catch (SQLException sqlException)
            {
                sqlException.printStackTrace();
            }
        }

    }

}

Output

----------------- ResultSet Move Forward -----------------------
ID: 1, Name: Kabul, CountryCode: AFG, District: Kabol, Population: 1780000
ID: 2, Name: Qandahar, CountryCode: AFG, District: Qandahar, Population: 237500
ID: 3, Name: Herat, CountryCode: AFG, District: Herat, Population: 186800
ID: 4, Name: Mazar-e-Sharif, CountryCode: AFG, District: Balkh, Population: 127800
ID: 5, Name: Amsterdam, CountryCode: NLD, District: Noord-Holland, Population: 731200
ID: 6, Name: Rotterdam, CountryCode: NLD, District: Zuid-Holland, Population: 593321
ID: 7, Name: Haag, CountryCode: NLD, District: Zuid-Holland, Population: 440900
ID: 8, Name: Utrecht, CountryCode: NLD, District: Utrecht, Population: 234323
ID: 9, Name: Eindhoven, CountryCode: NLD, District: Noord-Brabant, Population: 201843
ID: 10, Name: Tilburg, CountryCode: NLD, District: Noord-Brabant, Population: 193238

----------------- ResultSet Move Backward -----------------------
ID: 9, Name: Eindhoven, CountryCode: NLD, District: Noord-Brabant, Population: 201843
ID: 8, Name: Utrecht, CountryCode: NLD, District: Utrecht, Population: 234323
ID: 7, Name: Haag, CountryCode: NLD, District: Zuid-Holland, Population: 440900
ID: 6, Name: Rotterdam, CountryCode: NLD, District: Zuid-Holland, Population: 593321
ID: 5, Name: Amsterdam, CountryCode: NLD, District: Noord-Holland, Population: 731200
ID: 4, Name: Mazar-e-Sharif, CountryCode: AFG, District: Balkh, Population: 127800
ID: 3, Name: Herat, CountryCode: AFG, District: Herat, Population: 186800
ID: 2, Name: Qandahar, CountryCode: AFG, District: Qandahar, Population: 237500
ID: 1, Name: Kabul, CountryCode: AFG, District: Kabol, Population: 1780000

----------------- ResultSet Move to Particular position -----------------------
ID: 3, Name: Herat, CountryCode: AFG, District: Herat, Population: 186800

Environment Used 

JDK version : 1.7.0_51
Mysql Server version : 5.6.19 

To Download JDBCScrollableResultSetDemo Project Click the below link

https://sites.google.com/site/javaee4321/jdbc/JDBCScrollableResultSetDemo.zip?attredirects=0&d=1

See also:

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial