How do I execute a simple SQL query with Statement?

By I Wayan Saryada in JDBC 0 Comment

Executing a simple SQL query using a Statement object in JDBC follows a straightforward pattern: establish a connection, create the statement, execute the query, and process the results.

Here is a clean example of how to perform a SELECT query:

Simple SQL Query Example

package org.kodejava.jdbc;

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

public class SimpleQueryExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "username";
        String password = "password";

        String sql = "SELECT id, username, email FROM users";

        // Use try-with-resources to ensure resources are closed automatically
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            // Iterate through the result set
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("username");
                String email = rs.getString("email");

                System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
            }

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

Key Methods to Know

The Statement interface provides different methods depending on the type of SQL you are running:

  1. executeQuery(String sql): Used for SELECT statements. It returns a ResultSet containing the data.
  2. executeUpdate(String sql): Used for INSERT, UPDATE, or DELETE statements. It returns an int representing the number of rows affected.
  3. execute(String sql): A general-purpose method that can execute any SQL statement. It returns true if the result is a ResultSet (query) and false if it is an update count or there are no results.

Important Tips

  • Try-with-Resources: Always use the try-with-resources block (shown above) for Connection, Statement, and ResultSet. This prevents memory leaks by ensuring the database handles are closed even if an exception occurs.
  • Security: While Statement is great for simple or static queries, use PreparedStatement if your query includes variables provided by a user. This prevents SQL Injection attacks.
  • Indices vs. Names: When reading from a ResultSet, you can use column names (e.g., rs.getString("username")) or 1-based indices (e.g., rs.getString(1)). Names are generally more readable and maintainable.

How do I set the query timeout limit?

By I Wayan Saryada in JDBC 1 Comment

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

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

    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 product");

            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

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

Maven Central

How do I set the fetch size of a statement?

By I Wayan Saryada in JDBC 0 Comment

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 better by reducing the network communication generated between the program and the database server.

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

    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 product";
            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

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

Maven Central

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

By I Wayan Saryada in JDBC 0 Comment

If you want to limit the result of your query, you can call the Statement.setMaxRows(int max) method. This call will allow the ResultSet object 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.jdbc;

import java.sql.*;

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

            // Executes an SQL query to get the total number of data
            // in product table.
            String query = "select count(*) from product";
            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 product
            // table.
            query = "select id, code, name, price from product";
            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 were read from the product 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

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

Maven Central

How do I use enum in switch statement?

By I Wayan Saryada in Basic, Core API 0 Comment

This example show you how to use enumeration or enum type in a switch statement.

package org.kodejava.basic;

public enum RainbowColor {
    RED, ORANGE, YELLOW, GREEN, BLUE, INDIGO, VIOLET
}

package org.kodejava.basic;

public class EnumSwitch {
    public static void main(String[] args) {
        RainbowColor color = RainbowColor.INDIGO;

        EnumSwitch es = new EnumSwitch();
        String colorCode = es.getColorCode(color);
        System.out.println("ColorCode = #" + colorCode);
    }

    public String getColorCode(RainbowColor color) {
        String colorCode = "";

        // We use the switch-case statement to get the hex color code of our
        // enum type rainbow colors. We can pass the enum type as expression
        // in the switch. In the case statement we only use the enum named
        // constant excluding its type name.
        switch (color) {
            // We use RED instead of RainbowColor.RED
            case RED -> colorCode = "FF0000";
            case ORANGE -> colorCode = "FFA500";
            case YELLOW -> colorCode = "FFFF00";
            case GREEN -> colorCode = "008000";
            case BLUE -> colorCode = "0000FF";
            case INDIGO -> colorCode = "4B0082";
            case VIOLET -> colorCode = "EE82EE";
        }
        return colorCode;
    }
}