How do I limit MySQL query result?

package org.kodejava.example.jdbc;

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

public class SqlLimitExample {
    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)) {

            // 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"));
            }

            // 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"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

An example result of our program is:

Total number of data in database: 9

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

Result fetched with specified limit 3, 3
====================================
id:5, code:P0000005, name:Ruby on Rails, price:24.00
id:6, code:P0000006, name:Championship Manager, price:0.00
id:7, code:P0000007, name:Transport Tycoon Deluxe, price:0.00

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 limit the Hibernate query result?

In the example below you’ll see how to limit the number of records returned by the Hibernate queries. Limiting the query result usually used from created a pagination result where we can navigate from page to page in our application data but only a few data are read from the database.

In the Hibernate’s Query object we need to specify the first result and max results by calling the setFirstResult() and setMaxResults() methods.

package org.kodejava.example.hibernate.app;

import org.hibernate.query.Query;
import org.hibernate.Session;
import org.kodejava.example.hibernate.model.Label;

import java.util.List;

public class LabelManager {

    public List<Label> getLabels(int pageNumber, int pageSize) {
        Session session = 
            SessionFactoryHelper.getSessionFactory().getCurrentSession();
        session.beginTransaction();

        Query<Label> query = session.createQuery("from Label", Label.class);

        // Set the first record position and the max number of record to be
        // read. The setFirstResult() tell hibernate from which row the data
        // should be read. In the example if we have pages of 10 records,
        // passing the page number 2 will read 10 records from the 20th row
        // in the selected records.
        query.setFirstResult((pageNumber - 1) * pageSize);
        query.setMaxResults(pageSize);

        List<Label> labels = query.list();
        session.getTransaction().commit();
        return labels;
    }
}
package org.kodejava.example.hibernate.app;

import org.kodejava.example.hibernate.model.Label;

import java.util.List;

public class LimitDemo {
    public static void main(String[] args) {
        LabelManager manager = new LabelManager();

        List<Label> labels = manager.getLabels(1, 10);
        for (Label label : labels) {
            System.out.println("Label = " + label);
        }
    }
}

Maven Dependencies

<dependencies>
    <!--https://search.maven.org/remotecontent?filepath=org/hibernate/hibernate-core/5.4.1.Final/hibernate-core-5.4.1.Final.jar-->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>5.4.1.Final</version>
    </dependency>
    <!--https://search.maven.org/remotecontent?filepath=org/hibernate/hibernate-ehcache/5.4.1.Final/hibernate-ehcache-5.4.1.Final.jar-->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-ehcache</artifactId>
        <version>5.4.1.Final</version>
    </dependency>
    <!--https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
</dependencies>

Maven Central
Maven Central