How to Get the Datatype of a Column of a Table using JDBC?

Java supports many databases and for each database, we need to have their respective jar files to be placed in the build path to proceed for JDBC connectivity.

• MySQL: mysql-connector-java-8.0.22 or similar mysql connectors with different versions. In this article, we are using mysql-connector-java-8.0.22
• SQL Server: sqljdbc4.jar
• Oracle: ojdbc14.jar
• MongoDB (NoSQL database): mongo-java-driver-3.12.7

First, need to decide, which database we are using and accordingly, we need to add the jars. For another database like Progress, Cassandra, etc also we have jars and need to include them in the build path. For various data processing-related matters from RDBMS(MySQL, SQL Server, etc.) we have a java.sql package. To get the ResultSetMetaData object, we need to follow the below steps.

1. Register the driver

For database wise it differs. As we are using MySQL, we can do via

Class.forName("com.mysql.cj.jdbc.Driver");
or
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver");

2. Get the connection of MySQL way

• test is a database name used in the code.
• serverTimezone=UTC, If not provided we will have java.sql.SQLException. The server time zone value xxxx is unrecognized or represents more than one time zone.
• We need to configure either the server or JDBC driver via the serverTimezone configuration property) to use a more specific time zone value if we want to utilize time zone support.
• So either in MySQL configuration we can set or like below we can provide Credentials here are root/”” i.e. username is root and password is “”.

Connection connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/test?serverTimezone=UTC”, “root”, “”);

3. Create a statement object

Statement st = connection.createStatement();

4. Execute the query

String query = "Select * from doctorsdetails";
// Executing the query
ResultSet resultSet = statement.executeQuery(query);

5. Get the ResultSetMetaData object: 

Retrieve the ResultSetMetadata object of the current ResultSet by invoking the getMetaData() method. Below methods are there in ResultSetMetadata object and they are printed in the program

• int getColumnCount(): Returns the number of columns of a table.
• String getColumnName(int columnNumber): Returns the name of the column whose index number is passed as a parameter.
• int getColumnType(int columnNumber): Returns the name of the column as an integer value whose index number is passed as parameter.

For example, 3 indicates DECIMAL, and 12 indicates VARCHAR, and 4 indicates INT.  

• 12 is represented as java.sql.Types.VARCHAR
• 4 is represented as java.sql.Types.INTEGER

Following is the list of values returned by various datatypes of java.sql.Type − i.e. getColumnType() gives these results.

• Array: 2003
• Big int: -5
• Binary: -2
• Bit: -7
• Blob: 2004
• Boolean: 16
• Char: 1
• Clob: 2005
• Date: 91
• Datalink:70
• Decimal: 3
• Distinct: 2001
• Double: 8
• Float: 6
• Integer: 4
• JavaObject: 2000
• Long var char: -16
• Nchar: -15
• NClob: 2011
• Varchar: 12
• VarBinary: -3
• Tiny int: -6
• Time stamp with time zone: 2014
• Timestamp: 93
• Time: 92
• Struct: 2002
• SqlXml: 2009
• Smallint: 5
• Rowid: -8
• Refcursor: 2012
• Ref: 2006
• Real: 7
• Nvarchar: -9
• Numeric: 2
• Null: 0
• Smallint: 5

Let us check that by creating a table in MySQL and find the datatype by using ResultSetMetaData

Let a table named doctorsdetails available in MySQL under test database,

CREATE TABLE `doctorsdetails` (
 `id` int(6) unsigned NOT NULL,
 `Name` varchar(50) DEFAULT NULL,
 `AvailableDays` varchar(200) DEFAULT NULL,
 `consultation_fees` int(11) DEFAULT NULL,
 `qualification` varchar(20) DEFAULT NULL,
 `experience` int(11) DEFAULT NULL,
 `gender` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

As per the above table, getColumnType() for the columns of the above table are as follows

ColumnName	ColumnType	Value
id	int	4
Name	varchar	12
AvailableDays	varchar	12
consultation_fees	int	4
qualification	varchar	12
experience	int	4
gender	varchar	12

In the code, let us use the steps, and they are given as comments

Java

// Java program to get the column type in JDBC

import java.sql.*;

public class GetColumnTypeInJdbc {

    public static void main(String[] args)
    {
        System.out.println(
            "Methods of column to get column type in JDBC");
        Connection con = null;
        try {
            // We need to have mysql-connector-java-8.0.22
            // or relevant jars in build path of project
            // Class.forName("com.mysql.jdbc.Driver");
            // //Earlier these were supported. If we use ,
            // we will be getting warning messages This
            // driver is the latest one
            // 1. Register the driver

            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. Get the connection
            con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/test?serverTimezone=UTC",
                "root", "");
            try {

                // Create statement so that we can execute
                // all of our queries
                // 3. Create a statement object
                Statement statement = con.createStatement();

                // Query to retrieve records
                String query
                    = "Select * from doctorsdetails";

                // 4. Executing the query
                ResultSet resultSet
                    = statement.executeQuery(query);

                // 5. Get the ResultSetMetaData object
                ResultSetMetaData resultSetMetaData
                    = resultSet.getMetaData();

                for (int i = 1;
                     i
                     <= resultSetMetaData.getColumnCount();
                     i++) {
                    System.out.println(
                        "ColumnName = "
                        + resultSetMetaData.getColumnName(
                              i));
                    System.out.println(
                        "ColumnType = "
                        + resultSetMetaData.getColumnType(i)
                        + "   ");
                    System.out.println(
                        "ColumnLabel = "
                        + resultSetMetaData.getColumnLabel(
                              i)
                        + "   ");
                    System.out.println(
                        "ColumnDisplaySize = "
                        + resultSetMetaData
                              .getColumnDisplaySize(i)
                        + "   ");
                    System.out.println(
                        "ColumnTypeName = "
                        + resultSetMetaData
                              .getColumnTypeName(i)
                        + "   ");
                    System.out.println(
                        "------------------");
                }
            }

            // in case of any SQL exceptions
            catch (SQLException s) {
                System.out.println(
                    "SQL statement is not executed!");
            }
        }

        // in case of general exceptions
        // other than SQLException
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            // After completing the operations, we
            // need to null resultSet and connection
            resultSet = null;
            con = null;
        }
    }
}

On execution of the program, we will get the output as below. i.e. similar to the table value that is shown above