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

If you want to limit the result of your query you can use the Statement.setMaxRows(int max) method call. This call will allow the ResultSet object to 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.example.sql;

import java.sql.*;

public class SetMaxRowExample {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = getConnection();
            Statement statement = connection.createStatement();

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

            while (rs.next()) {
                System.out.println("Total number of 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 Products
            // table.
            //
            query = "select id, code, name, price, qty from products";
            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")
                         + ", QTY: " + rs.getInt("qty"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }


    /**
     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getConnection("jdbc:mysql://localhost/kodejavadb", "root", "");
    }

    /**
     * Close a connection to database.
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
     */
    private static void closeConnection(Connection connection) throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

When running the code we’ll see that only 5 records was read from the Products 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 number of Products: 10

Data read after the MaxRows is set.
ID: 1, CODE: P0001, NAME: Product 01, PRICE: 19.99, QTY: 10
ID: 2, CODE: P0002, NAME: Product 02, PRICE: 29.99, QTY: 20
ID: 3, CODE: P0003, NAME: Product 03, PRICE: 39.99, QTY: 30
ID: 4, CODE: P0004, NAME: Product 04, PRICE: 49.99, QTY: 40
ID: 5, CODE: P0005, NAME: Product 05, PRICE: 59.99, QTY: 50
Wayan Saryada

Wayan Saryada

A programmer, runner, recreational diver, currently living in the island of Bali, Indonesia. Mostly programming in Java, creating web based application with Spring Framework, JPA, etc. If you need help on Java programming you can hire me on Fiverr.
Wayan Saryada

Leave a Reply