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

By Wayan Saryada 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.example.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 = "root";
    private static final String PASSWORD = "";

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

            Statement statement = connection.createStatement();
            String query = "SELECT id, quantity, price FROM order_details";
            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

<!-- 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 know the designated column’s table name?

By Wayan Saryada in JDBC 0 Comment 
package org.kodejava.example.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 = "root";
    private static final String PASSWORD = "";

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

            Statement statement = connection.createStatement();
            String query = "SELECT a.id, a.quantity, a.price, c.name "
                + "FROM order_details a "
                + "LEFT JOIN orders b ON a.order_id = b.id "
                + "LEFT JOIN products c on a.product_id = c.id";
            ResultSet resultSet = statement.executeQuery(query);

            // Here we have a query that use multiple table, we then want
            // to know to which table a column is belong 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();
        }
    }
}

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 know if a table column can have a null value or not?

By 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 return an integer which values defined in the constants of ResultSetMetaData.columnNullable, ResultSetMetaData.columnNoNulls and ResultSetMetaData.columnNullableUnknown.

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

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

            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(
                "SELECT isbn, title, published_year, price FROM books");

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

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

By 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 return false. See the example below where we check if the first column (id) is an auto-increment column.

package org.kodejava.example.jdbc;

import java.sql.*;

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

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

<!-- 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 get column names of a table using ResultSetMetaData?

By 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 this class might help you to create an inquiry program where you don’t have all information about table columns.

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

    public static void main(String[] args) throws Exception {
        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 books");

            // 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 it self.
            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:

isbn = 9781617291999
title = Java 8 in Action
published_year = 2015

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>