How do I move to absolute or relative row?

By Wayan Saryada in JDBC 0 Comment

In this example we are showing you how to use result set absolute() and relative() method to jump from one result set row to the other. When you pass a positive number as the parameter you’ll move from the first record to the last. But if you pass a negative number as parameter you’ll move from the last record backward.

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 MoveAbsoluteOrRelativeExample {
    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 rs = statement.executeQuery(sql);

            // Move to the second row
            rs.absolute(2);
            System.out.println("You are now in: " + rs.getRow());

            // Move 2 records forward from the current position 
            // (fourth row)
            rs.relative(2);
            System.out.println("You are now in: " + rs.getRow());

            // Move to the last row in the result set
            rs.absolute(-1);
            System.out.println("You are now in: " + rs.getRow());

            // Move 3 records backward from the current position 
            // (second row)
            rs.relative(-3);
            System.out.println("You are now in: " + rs.getRow());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The code output the following result:

You are now in: 2
You are now in: 4
You are now in: 5
You are now in: 2

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?

By Wayan Saryada in JDBC 0 Comment

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?

By Wayan Saryada in JDBC 0 Comment

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?

By Wayan Saryada in JDBC 0 Comment

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?

By Wayan Saryada in JDBC 0 Comment

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?

By Wayan Saryada in JDBC 0 Comment 
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

How do I retrieve values from ResultSet?

By Wayan Saryada in JDBC 0 Comment

Here is another example on how to read data from a ResultSet returned by executing an SQL query in database.

We start by creating a connection to the database. Creates a PreparedStatement to execute a query to get some data from the books table.

After executing the PreparedStatement we will have a ResultSet object. To iterate all the data in the ResultSet we call the next() method in a while-loop. When no more record to read the method return false. The ResultSet object also provides some methods to read value of the fields, the name of the method is corresponded to the type of data stored on each field of the table.

To read data using the ResultSet‘s methods (eg. getString(), getInt(), getFloat(), etc) we can either use the column name or the column index of the field read in the SQL statement.

Let’s see the complete code snippet below:

package org.kodejava.example.jdbc;

import java.sql.*;

public class ResultSetExample {
    public static void main(String[] args) throws Exception {

        String url = "jdbc:mysql://localhost/kodejava";
        String username = "root";
        String password = "";

        try (Connection connection =
                 DriverManager.getConnection(url, username, password)) {

            String query = "SELECT isbn, title, published_year, price " +
                "FROM books";

            PreparedStatement ps = connection.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                // Read values using column name
                String isbn = rs.getString("isbn");
                String title = rs.getString("title");
                int publishedYear = rs.getInt("published_year");

                // Read values using column index
                double price = rs.getDouble(4);

                System.out.printf("%s, %s, %d, %.2f\n", isbn, title,
                    publishedYear, price);
            }
        }
    }
}

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 query records from table?

By Wayan Saryada in JDBC 0 Comment 
package org.kodejava.example.jdbc;

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

public class JdbcQueryExample {
    // Database connection information
    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) throws Exception {
        // Get a connection to database.
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            // Create a statement object.
            Statement statement = connection.createStatement();

            // Executes a query command to select isbn and the book title
            // from books table. The execute query returns a ResultSet
            // object which is the result of our query execution.
            String query = "SELECT isbn, title, published_year FROM books";
            ResultSet books = statement.executeQuery(query);

            // To get the value returned by the statement.executeQuery we
            // need to iterate the books object until the last items.
            while (books.next()) {
                // To get the value from the ResultSet object we can call
                // a method that correspond to the data type of the column
                // in database table. In the example below we call
                // books.getString("isbn") to get the book's ISBN 
                // information.
                System.out.println(books.getString("isbn") + ", " +
                    books.getString("title") + ", " +
                    books.getInt("published_year"));
            }
        }
    }
}

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