JDBC ResultSetMetaData for ResultSet Column Examination

ResultSetMetaData is an interface in Java under the package java.sql.ResultSetMetaData which can be used in determining or retrieving the structural characteristics of a table’s ResultSet. It is not always necessary for the programmer to know the structural information of a ResultSet column (such as name, datatype, count, or other information associated with a specific column) being returned by the ResultSet object’s get( ) method.

In this article, we are going to learn about retrieving the Metadata of a table in the database through JDBC’s ResultSetMetaData interface.

Steps to use ResultSetMetaData in JDBC

Below are the steps to use the RSMD (ResultSetMetaData) in JDBC to retrieve information about ResultSet Columns.

Step 1: ResultSetMetaData can be used by importing the ResultSetMetaData interface from java.sql package in the JDBC program.

import java.sql.ResultSetMetaData;

Step 2: After importing the package create a connection with your database through your jdbc connector and Driver. In this article, I’ll be using MySQL as my jdbc database

Connection con = DriverManager.getConnection("jdbc:mysql://localhost/dbname", "yourusername", 
                                                                                          "your password");
Statement stmt = con.createStatement()) ;

Step 3: Create a SQL query through the Statement object which returns an object of ResultSet type. e.g.: select query to view all information.

String query = "SELECT * FROM testtable";
            ResultSet rs = stmt.executeQuery(query);

Step 4: Now that we have the ResultSet object we can create the ResultSetMetaData object using the .getMetaData( ) method of the ResultSet interface as:

ResultSetMetaData rstm=rs.getMetaData();

The getMetaData( ) method of ResultSet returns an instance of ResultSetMetaData for the same resultset which triggered the method call. This ResultSetMetaData object can be further used with its various methods such as getColumnName( ) , getColumnCount( ) which we are going to discuss in later section of the article.

Implementation of RSMD Methods

Before we go on with the Java program, we should have done our database set up with the required JDBC connectors up and working.

• First, we must create the ResultSetMetaData object.
• After creation of the object, we are ready to retrieve column set information.

Let’s discuss some of the important methods. Here is the sample database we are going to extract metadata of.

Step 1: getColumnName(int columnNumber)

In this step, it returns a string of name of the column number which is specified in the parameter.

Note: Column number starts from 1. If passed column number which does not exists column index out of range runtime exception occurs.

Java

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

class GFG {
    public static void main (String[] args) {
         try {Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "username", "password");
             Statement stmt = con.createStatement();            //creating object of statement interface

            String query = "SELECT * FROM testtable";
            ResultSet rs = stmt.executeQuery(query);
            ResultSetMetaData rstmd=rs.getMetaData();           //creating an object of Resultsetmetadata Interface

            System.out.println("1st Column name :"+rstmd.getColumnName(1)); //name of 1st column
            System.out.println("2nd Column name :"+rstmd.getColumnName(2)); //name of 2nd column
            System.out.println("4rd Column name :"+rstmd.getColumnName(4)); //Runtime exception as column 4 does not exists

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

Output:

Below we can see the name of the Columns.

Step 2: getColumnCount()

This method, returns an integer of total column count in the ResultSet.

Java

import java.sql.*;
class GFG
{
    public static void main (String[] args)
    {
        try {
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "username", "password");
            Statement stmt = con.createStatement(); // creating object of statement interface

            String query = "SELECT * FROM testtable";
            ResultSet rs = stmt.executeQuery(query);
            ResultSetMetaData rstmd = rs.getMetaData(); // creating an object of Resultsetmetadata Interface

            int columnsNumber = rstmd.getColumnCount(); // getting the number of column in table
            System.out.println("Column Number: " + columnsNumber);

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

Output:

Below we can see the total column count.

Step 3: getColumnTypeName(int ColumnNumber)

This method returns a String for datatype of the designated column. This method is usually used for data validation. Tt throws column index out of range exception if the specified column does not exists.

Java

import java.sql.*;

// Driver Class
class GFG {
      // Main Function
    public static void main(String[] args)
    {
        try {
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test",
                                                         "username","password");

              // Creating object of statement interface
            Statement stmt = con.createStatement();

            String query = "SELECT * FROM testtable";
            ResultSet rs = stmt.executeQuery(query);

              // Creating an object of Resultsetmetadata
            // Interface
            ResultSetMetaData rstmd = rs.getMetaData();

            System.out.println("Type of column 1 :" + rstmd.getColumnTypeName(1));
            System.out.println("Type of column 2 :" + rstmd.getColumnTypeName(2));
            System.out.println("Type of column 3 :" + rstmd.getColumnTypeName(3));
              // Gives type of first column, can
               // Be used for data validation
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Output:

Below we can see the datatype of the designated column.

Step 4: getPrecesion(int columnNumber)

It returns an integer of the specified column size. this method is useful when you are unaware of the pre-specified size of a column in the database. can be used for handling unexpected input length errors. for number the method returns the maximum precision possible, for char it represents the length in character and for date and time it is the length in characters of the String representation. and 0 is returned where column size cannot be calculated.

Java

import java.sql.*;

// Driver Class
class GFG
{
      // Main Function
    public static void main (String[] args)
    {
         try {
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test",
                                                         "username", "password");

               // Creating Object of Statement Interface
            Statement stmt = con.createStatement();

            String query = "SELECT * FROM testtable";
            ResultSet rs = stmt.executeQuery(query);

               // Creating an Object of Resultsetmetadata Interface
            ResultSetMetaData rstmd = rs.getMetaData();

               // Returns Precision of Int
            System.out.println("Precesion : " + rstmd.getPrecision(1));

               // Returns Precision of String
            System.out.println("Precesion : " + rstmd.getPrecision(2));

               // Returns Precision of Int
            System.out.println("Precesion : " + rstmd.getPrecision(3));

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

Output:

Below we can see the integer of the specified column size.

Methods in RSMD

Below is the list of some frequently used method in RSMD:

Method Name	Return Type	Description
getColumnCount()	int	returns the number of columns in the ResultSet object.
isNullable(int column)	int	returns 0(no null values allowed) ,1(null allowed),2(unknown nullability).
getColumnDisplaySize(int column)	int	returns column’s maximum width in characters.
getScale(int column)	int	returns the number of digits after decimal point in the column ,0 if not applicable.
getPrecision(int column)	int	returns the designated column length.
getColumnLabel(int column)	String	returns the alias specified for the column, if no alias specified returns the column name.
getColumnName(int column)	String	returns the specified column name.
getSchemaName(int column)	String	returns the schema for the designated column’s table.
getTableName(int column)	String	returns the name of the table.
getColumnTypeName(int column)	String	returns the column’s datatype name.
isAutoIncrement(int column)	boolean	returns if the column automatically increments.
isWritable(int column)	boolean	returns true if the column is writable, false otherwise.
isReadOnly(int column)	boolean	returns true if column is not writable, false otherwise.