How do I get column’s precision and scale value?

By Wayan in JDBC 0 Comment

For instance, you have a number or double typed column in a database table, and you want to get the precision and the scale of that column. Here is an example for you.

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 ScaleAndPrecisionExample {
    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)) {

            String query = "SELECT id, quantity, price FROM purchase_order_detail";
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(query);

            ResultSetMetaData metadata = resultSet.getMetaData();
            int precision = metadata.getPrecision(3);
            int scale = metadata.getScale(3);

            System.out.println("Precision: " + precision);
            System.out.println("Scale    : " + scale);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The result:

Precision: 10
Scale    : 2

Maven Dependencies

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

Maven Central

How do I know the designated column’s table name?

By Wayan in JDBC 0 Comment 
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 TableNameOfColumnExample {
    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)) {

            String query = """
                    SELECT a.id, a.quantity, a.price, c.name
                    FROM purchase_order_detail a
                    LEFT JOIN purchase_order b ON a.order_id = b.id
                    LEFT JOIN product c on a.product_id = c.id
                    """;

            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(query);

            // Here we have a query that use multiple table, we then want
            // to know to which table a column belongs to. In the
            // ResultSetMetaData there is a getTableName() method that can
            // do this functionality.
            ResultSetMetaData metadata = resultSet.getMetaData();

            String tableName = metadata.getTableName(1);
            System.out.println("Table name of column `a.id` = " + tableName);

            tableName = metadata.getTableName(4);
            System.out.println("Table name of column `c.name` = " + tableName);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The output of the code snippet above is:

Table name of column `a.id` = purchase_order_detail
Table name of column `c.name` = products

Maven Dependencies

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

Maven Central

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

By Wayan 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.1.0</version>
</dependency>

Maven Central

How do I know if a table column value is auto-increment?

By Wayan 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.1.0</version>
</dependency>

Maven Central

How do I get column names of a table using ResultSetMetaData?

By Wayan 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.1.0</version>
</dependency>

Maven Central