How do I set the query timeout limit?

The Statement.setQueryTimeout() method set the limit in seconds for query execution time. When the value is set to zero it means that the execution has no timeout limit.

package org.kodejava.example.jdbc;

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

public class QueryTimeout {
    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)) {
            Statement stmt = connection.createStatement();

            // Sets the number of seconds the driver will wait for
            // a statement object to execute to the given number of
            // seconds. If the limit is exceeded, an SQLException
            // is thrown.
            stmt.setQueryTimeout(60);

            // Execute sql query
            ResultSet rs = stmt.executeQuery("select * from products");

            while (rs.next()) {
                System.out.println("code: " + rs.getString("code")
                    + " ,product: " + rs.getString("name")
                    + " ,price: " + rs.getDouble("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven dependencies

<!-- 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>

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/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>

How do I set the fetch size of a statement?

Fetch size is the number of rows that should be fetched from the database on a single database network trip, when more rows are needed another request is sent by the application to the database server.

Setting the correct fetch size will help our program to perform well regarding to the number of network communication generated between the program and the database server.

package org.kodejava.example.jdbc;

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

public class SetFetchSizeExample {
    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)) {
            Statement statement = connection.createStatement();

            // Set the fetch size to 100.
            statement.setFetchSize(100);

            // Execute the given sql query
            String q = "select id, code, name, price from products";
            ResultSet rs = statement.executeQuery(q);

            while (rs.next()) {
                System.out.println("id:" + rs.getLong("id") +
                    ", code:" + rs.getString("code") +
                    ", name:" + rs.getString("name") +
                    ", price:" + rs.getString("price"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Example result of the code snippet above is:

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
id:7, code:P0000007, name:Transport Tycoon Deluxe, price:0.00
id:8, code:P0000008, name:Roller Coaster Tycoon 3, price:0.00
id:9, code:P0000009, name:Pro Evolution Soccer, price:0.00
id:10, code:P0000010, name:Data Structures, Algorithms, price:50.99

Maven dependencies

<!-- 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>

How do I set the maximum rows to read in a query?

If you want to limit the result of your query you can use the Statement.setMaxRows(int max) method call. This call will allow the ResultSet object to contains a maximum number of records specified in the parameter of the setMaxRows method.

Another way to limit the number of data returned in a query is to use the database specific command such as the MySQL limit command.

package org.kodejava.example.jdbc;

import java.sql.*;

public class SetMaxRowExample {
    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)) {
            Statement statement = connection.createStatement();

            // Executes an SQL query to get the total number of data
            // in products table.
            String query = "select count(*) from products";
            ResultSet rs = statement.executeQuery(query);

            while (rs.next()) {
                System.out.println("Total Products: " + rs.getInt(1));
            }

            // Set the maximum row of data that can be stored in the
            // ResultSet.
            statement.setMaxRows(5);

            // Executes an SQL query to retrieve data from Products
            // table.
            query = "select id, code, name, price from products";
            rs = statement.executeQuery(query);

            System.out.println("Data read after the MaxRows is set.");
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id")
                    + ", CODE: " + rs.getString("code")
                    + ", NAME: " + rs.getString("name")
                    + ", PRICE: " + rs.getBigDecimal("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

When running the code we’ll see that only 5 records was read from the Products table instead of 10 records. This is the result of setting the maximum rows in the Statement object.

Below is the output of our code.

Total Products: 9
Data read after the MaxRows is set.
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

Maven dependencies

<!-- 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>

How do I convert java.util.Date to java.sql.Date?

package org.kodejava.example.jdbc;

import java.util.Date;

public class UtilDateToSqlDate {
    public static void main(String[] args) {
        // Create a new instance of java.util.Date
        Date date = new Date();

        // To covert java.util.Date to java.sql.Date we need to
        // create an instance of java.sql.Date and pass the long
        // value of java.util.Date as the parameter.
        java.sql.Date sqlDate = new java.sql.Date(date.getTime());

        System.out.println("Date    = " + date);
        System.out.println("SqlDate = " + sqlDate);
    }
}

The result of the code snippet above:

Date    = Thu Dec 06 11:03:35 WITA 2018
SqlDate = 2018-12-06