How do I retrieve values from ResultSet?

Here is another example on how to read data from a ResultSet returned by executing an SQL query in a 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 (e.g. 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.jdbc;

import java.math.BigDecimal;
import java.sql.*;

public class ResultSetExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

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

            String query = """
                    SELECT id,
                        isbn,
                        title,
                        published_year,
                        price
                    FROM book
                    """;

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

                // Read values using column index
                BigDecimal price = rs.getBigDecimal(5);

                System.out.printf("%s, %s, %s, %d, %.2f\n", id, isbn, title,
                        publishedYear, price);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

As an example, running the code above will give us the following output:

1, 978-1491910771, Head First Java: A Brain-Friendly Guide, 2022, 45.49
2, 978-1617293566, Modern Java in Action, 2019, 54.99

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central

Wayan

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.