How do I limit MySQL query result?

package org.kodejava.example.sql;

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

public class SqlLimitExample {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = getConnection();

            //
            // Create PreparedStatement to get all data from database.
            //
            String query = "select count(*) from products";
            PreparedStatement ps = connection.prepareStatement(query);
            ResultSet result = ps.executeQuery();

            int total = 0;
            while (result.next()) {
                total = result.getInt(1);
            }

            System.out.println("Total number of data in database: " +
                    total + "n");

            //
            // Create PreparedStatement to the first 5 records only.
            //
            query = "select * from products limit 5";
            ps = connection.prepareStatement(query);
            result = ps.executeQuery();

            System.out.println("Result fetched with specified limit 5");
            System.out.println("====================================");
            while (result.next()) {
                System.out.println("id:" + result.getInt("id") +
                        ", code:" + result.getString("code") +
                        ", name:" + result.getString("name") +
                        ", price:" + result.getString("price") +
                        ", qty:" + result.getString("qty"));
            }

            //
            // Create PreparedStatement to get data from the 4th
            // record (remember the first record is 0) and limited
            // to 3 records only.
            //
            query = "select * from products limit 3, 3";
            ps = connection.prepareStatement(query);
            result = ps.executeQuery();

            System.out.println("nResult fetched with specified limit 3, 3");
            System.out.println("====================================");
            while (result.next()) {
                System.out.println("id:" + result.getInt("id") +
                        ", code:" + result.getString("code") +
                        ", name:" + result.getString("name") +
                        ", price:" + result.getString("price") +
                        ", qty:" + result.getString("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");
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "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();
        }
    }
}

An example result of our program is:

Total number of data in database: 10

Result fetched with specified limit 5
====================================
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

Result fetched with specified limit 3, 3
====================================
id:4, code:P0004, name:Product 04, price:49.99, qty:40
id:5, code:P0005, name:Product 05, price:59.99, qty:50
id:6, code:P0006, name:Product 06, price:69.99, qty:60
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