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 = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Create PreparedStatement to get all data from a database.
            String query = "select count(*) from product";
            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 product 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 product 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

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>

Maven Central

How do I insert a record into database table?

In this example you’ll learn how to create a program to insert data into a database table. To insert a data we need to get connected to a database. After a connection is obtained you can create a java.sql.Statement object from it, and using this object we can execute some query strings.

package org.kodejava.jdbc;

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

public class InsertStatementExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Create a statement object.
            Statement stmt = connection.createStatement();
            String sql = "INSERT INTO book (isbn, title, published_year) " +
                    "VALUES ('978-1617293566', 'Modern Java in Action', 2019)";

            // Call execute() method of the statement object and pass the
            // query.
            stmt.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Below is the script from creating the book table.

CREATE TABLE `book`
(
    `id`             bigint(20) unsigned                  NOT NULL AUTO_INCREMENT,
    `isbn`           varchar(50) COLLATE utf8_unicode_ci  NOT NULL,
    `title`          varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `published_year` int(11)                                       DEFAULT NULL,
    `price`          decimal(10, 2)                       NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_unicode_ci;

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>

Maven Central