How do I limit MySQL query result?

package org.kodejava.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.28/mysql-connector-java-8.0.28.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>

Maven Central

How do I limit Hibernate query result?

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

In hibernate Query object we need to specify the first result and max results by calling the setFirstResult() and setMaxResults() methods to limit the query results.

package org.kodejava.hibernate.service;

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

import java.util.List;

public class LabelService {
    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.hibernate;

import org.kodejava.hibernate.model.Label;
import org.kodejava.hibernate.service.LabelService;

import java.util.List;

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

        List<Label> labels = service.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.6.9.Final/hibernate-core-5.6.9.Final.jar -->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>5.6.9.Final</version>
    </dependency>
    <!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.29/mysql-connector-java-8.0.29.jar -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.29</version>
    </dependency>
</dependencies>

Maven Central Maven Central

How do I retrieve a list of Hibernate’s persistent objects?

In this example we add the function to read a list of records in our LabelService class. This function will read all Label persistent object from database. You can see the other functions such as saveLabel, getLabel and deleteLabel in the related example section of this example.

package org.kodejava.hibernate.service;

import org.hibernate.Session;
import org.kodejava.hibernate.SessionFactoryHelper;
import org.kodejava.hibernate.model.Label;

import java.util.List;

public class LabelService {
    public List<Label> getLabels() {
        Session session =
                SessionFactoryHelper.getSessionFactory().getCurrentSession();
        session.beginTransaction();

        // We read labels record from database using a simple Hibernate
        // query, Hibernate Query Language (HQL).
        List<Label> labels = session.createQuery("from Label", Label.class)
                .list();
        session.getTransaction().commit();

        return labels;
    }

    public void saveLabel(Label label) {
        // To save an object we first get a session by calling 
        // getCurrentSession() method from the SessionFactoryHelper class. 
        // Next we create a new transaction, save the Label object and 
        // commit it to database,
        Session session = SessionFactoryHelper.getSessionFactory()
                .getCurrentSession();

        session.beginTransaction();
        session.save(label);
        session.getTransaction().commit();
    }
}
package org.kodejava.hibernate;

import org.kodejava.hibernate.model.Label;
import org.kodejava.hibernate.service.LabelService;

import java.util.Date;
import java.util.List;

public class ListDemo {
    public static void main(String[] args) {
        LabelService service = new LabelService();

        // Creates a Label object we are going to store in the database.
        // We set the name, modified by and modified date information.
        Label newLabel = new Label();
        newLabel.setName("PolyGram");
        newLabel.setCreated(new Date());

        // Call the LabelManager saveLabel method.
        service.saveLabel(newLabel);

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

Maven Dependencies

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

Maven Central Maven Central

How do I query records from a table?

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcQueryExample {
    // Database connection information
    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) throws Exception {
        // Get a connection to database.
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            // Create a statement object.
            Statement statement = connection.createStatement();

            // Executes a query command to select isbn and the book title
            // from books table. The execute query returns a ResultSet
            // object which is the result of our query execution.
            String query = "SELECT isbn, title, published_year FROM books";
            ResultSet books = statement.executeQuery(query);

            // To get the value returned by the statement.executeQuery we
            // need to iterate the books object until the last items.
            while (books.next()) {
                // To get the value from the ResultSet object we can call
                // a method that correspond to the data type of the column
                // in database table. In the example below we call
                // books.getString("isbn") to get the book's ISBN 
                // information.
                System.out.println(books.getString("isbn") + ", " +
                    books.getString("title") + ", " +
                    books.getInt("published_year"));
            }
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>

Maven Central