How do I limit MySQL query result?

By Wayan in JDBC API 0 Comment 
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

Programmer, runner, recreational diver, live in the island of Bali, Indonesia. Mostly programming in Java, creating web based application with Spring Framework, Hibernate / JPA.

Latest posts by Wayan (see all)

Views: 258

Leave a Reply