How do I set the maximum rows to read in a query?

If you want to limit the result of your query, you can call the Statement.setMaxRows(int max) method. This call will allow the ResultSet object contains a maximum number of records specified in the parameter of the setMaxRows method.

Another way to limit the number of data returned in a query is to use the database-specific command such as the MySQL limit command.

package org.kodejava.jdbc;

import java.sql.*;

public class SetMaxRowExample {
    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)) {
            Statement statement = connection.createStatement();

            // Executes an SQL query to get the total number of data
            // in product table.
            String query = "select count(*) from product";
            ResultSet rs = statement.executeQuery(query);

            while (rs.next()) {
                System.out.println("Total Products: " + rs.getInt(1));
            }

            // Set the maximum row of data that can be stored in the
            // ResultSet.
            statement.setMaxRows(5);

            // Executes an SQL query to retrieve data from product
            // table.
            query = "select id, code, name, price from product";
            rs = statement.executeQuery(query);

            System.out.println("Data read after the MaxRows is set.");
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id")
                                   + ", CODE: " + rs.getString("code")
                                   + ", NAME: " + rs.getString("name")
                                   + ", PRICE: " + rs.getBigDecimal("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

When running the code, we’ll see that only 5 records were read from the product table instead of 10 records. This is the result of setting the maximum rows in the Statement object.

Below is the output of our code.

Total Products: 9
Data read after the MaxRows is set.
ID: 1, CODE: P0000001, NAME: UML Distilled 3rd Edition, PRICE: 25.00
ID: 3, CODE: P0000003, NAME: PHP Programming, PRICE: 20.00
ID: 4, CODE: P0000004, NAME: Longman Active Study Dictionary, PRICE: 40.00
ID: 5, CODE: P0000005, NAME: Ruby on Rails, PRICE: 24.00
ID: 6, CODE: P0000006, NAME: Championship Manager, PRICE: 0.00

Maven Dependencies

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

Author
Recent Posts

Wayan

Founder at Kode Java

A programmer, runner and recreational diver, lives on the island of Bali, Indonesia. Programming in Java, Spring Framework, Hibernate / JPA, MySQL. To support me working on this project, buy me a cup of coffee, every little bit helps. Thank you.

Leave a ReplyCancel reply