How do I know if a table column can have a null value or not?

By I Wayan Saryada in JDBC 0 Comment

In this example we’ll show how to use ResultSetMetaData.isNullable() method to know if a column can be null or not. This method returns an integer which values defined in the constants of ResultSetMetaData.columnNullable, ResultSetMetaData.columnNoNulls and ResultSetMetaData.columnNullableUnknown.

package org.kodejava.jdbc;

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

public class IsNullableExample {
    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();
            ResultSet resultSet = statement.executeQuery(
                    "SELECT id, isbn, title, published_year, price FROM book");

            int columnIndex = 2;
            // The ResultSetMetaData is where all metadata related
            // information for a result set is stored.
            ResultSetMetaData metadata = resultSet.getMetaData();
            String columnName = metadata.getColumnName(columnIndex);
            int nullable = metadata.isNullable(columnIndex);

            // Check the nullable status of a column (`isbn`)
            if (nullable == ResultSetMetaData.columnNullable) {
                System.out.printf("`%s` can have a null value.%n", columnName);
            } else if (nullable == ResultSetMetaData.columnNoNulls) {
                System.out.printf("`%s` does not allowed to have a " +
                                  "null value.%n", columnName);
            } else if (nullable == ResultSetMetaData.columnNullableUnknown) {
                System.out.println("Nullability unknown.");
            }
        } 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 know if a table column value is auto-increment?

By I Wayan Saryada in JDBC 0 Comment

The ResultSetMetaData.isAutoIncrement() method can tell us if a column value is automatically numbered or not. This method return true when the column is an auto-increment column, otherwise it returns false. See the example below where we check if the first column (id) is an auto-increment column.

package org.kodejava.jdbc;

import java.sql.*;

public class IsAutoIncrementExample {
    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();
            ResultSet resultSet = statement.executeQuery(
                    "SELECT id, username, order_date FROM purchase_order");

            // The ResultSetMetaData is where all metadata related
            // information for a result set is stored.
            ResultSetMetaData metadata = resultSet.getMetaData();
            if (metadata.isAutoIncrement(1)) {
                System.out.println("Column `id` is an auto-increment column.");
            }
        } 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 get column names of a table using ResultSetMetaData?

By I Wayan Saryada in JDBC 0 Comment

This example show how we can use the ResultSetMetadata class to get the number of columns and column names of the selected table. The ResultSetMetaData class can also be used to get the column type and its properties.

Using the ResultSetMetaData class might help you to create an inquiry program where you don’t have all information about a table columns.

package org.kodejava.jdbc;

import java.sql.*;
import java.util.ArrayList;

public class MetadataColumnExample {
    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)) {

            // In the statement below we'll select all records from users
            // table and then try to find all the columns it has.
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(
                    "SELECT * FROM book");

            // The ResultSetMetaData is where all metadata related
            // information for a result set is stored.
            ResultSetMetaData metadata = resultSet.getMetaData();
            int columnCount = metadata.getColumnCount();

            // To get the column names we do a loop for a number of column
            // count returned above. And please remember a JDBC operation
            // is 1-indexed so every index begin from 1 not 0 as in array.
            ArrayList<String> columns = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metadata.getColumnName(i);
                columns.add(columnName);
            }

            // Later we use the collected column names to get the value of
            // the column itself.
            while (resultSet.next()) {
                for (String columnName : columns) {
                    String value = resultSet.getString(columnName);
                    System.out.println(columnName + " = " + value);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Some result generated by our above examples are:

id = 1
isbn = 978-1491910771
title = Head First Java: A Brain-Friendly Guide
published_year = 2022
price = 45.49

Maven Dependencies

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

Maven Central

How do I retrieve auto-generated keys?

By I Wayan Saryada in JDBC 0 Comment 
package org.kodejava.jdbc;

import java.sql.*;

public class GetGeneratedKeyExample {
    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)) {

            // The orders table have an ID column which value will be auto
            // generated by database. When inserting a new record a new id
            // for the primary key will be generated, and we will get the
            // generated key, so we can use it in another process. For
            // instance if we have a master detail tables where the details
            // table required an id from the master table.
            String sql = """
                    INSERT INTO purchase_order (username, order_date)
                    VALUES ('foobar', '2021-09-25')
                    """;

            Statement stmt = connection.createStatement();

            // When executing the statement we can pass the
            // Statement.RETURN_GENERATED_KEYS so that we can later extract
            // the generated key from the statement object after executing
            // the query.
            stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);

            ResultSet keys = stmt.getGeneratedKeys();
            long lastKey = 1L;
            while (keys.next()) {
                lastKey = keys.getLong(1);
            }

            System.out.println("Last Key: " + lastKey);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The structure of purchase_order table.

CREATE TABLE `purchase_order`
(
    `id`         bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `username`   varchar(50)         NOT NULL,
    `order_date` date                NOT NULL,
    PRIMARY KEY (`id`)
);

Maven Dependencies

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

Maven Central

How do to commit or rollback transaction in JDBC?

By I Wayan Saryada in JDBC 0 Comment

Executing a database manipulation command such as insert, update or delete can sometime throw exception due to invalid data. To protect the integrity of our application data we must make sure that when a transaction fails we must rollback all the executed queries.

In this example we are using MySQL database. To enable transaction capability in MySQL make sure that you are using InnoDB storage engine to create your databases and tables.

package org.kodejava.jdbc;

import java.math.BigDecimal;
import java.sql.*;

public class TransactionRollbackExample {
    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 conn =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            conn.setAutoCommit(false);

            String orderQuery = """
                    INSERT INTO purchase_order (username, order_date)
                    VALUES (?, ?)
                    """;

            try (PreparedStatement stmt = conn.prepareStatement(orderQuery,
                    PreparedStatement.RETURN_GENERATED_KEYS)) {
                stmt.setString(1, "jduke");
                stmt.setDate(2, new Date(System.currentTimeMillis()));
                stmt.execute();

                ResultSet keys = stmt.getGeneratedKeys();
                long orderId = 1L;
                if (keys.next()) {
                    orderId = keys.getLong(1);
                }

                // This is an invalid statement that will cause exception to
                // demonstrate a rollback.
                String orderDetailQuery = """
                        INSERT INTO purchase_order_detail (order_id, product_id, quantity, price)
                        VALUES (?, ?, ?, ?)
                        """;

                PreparedStatement detailStmt = conn.prepareStatement(orderDetailQuery);
                detailStmt.setLong(1, orderId);
                detailStmt.setInt(2, 1);
                detailStmt.setInt(3, 10);
                detailStmt.setBigDecimal(4, new BigDecimal("29.99"));
                detailStmt.execute();

                // Commit transaction to mark it as a success database operation
                conn.commit();
                System.out.println("Transaction commit...");
            } catch (SQLException e) {
                // Rollback any database transaction due to exception occurred
                conn.rollback();
                System.out.println("Transaction rollback...");
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

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

Maven Central