15.1 Overview of JDBC ResultSet Meta Data

Meta Data is data about data which means information about data. Now we know that fetching data from a database using Statement or PreparedStatement returns a ResultSet Object. ResultSet Object can give us the useful information about data like table name, column name, column properties, label etc.

In this chapter we will discuss how to get the meta data from ResultSet object. 

Following are the useful methods of ResultSetMetaData object.

1. int getColumnCount() – This method returns the number of columns that the ResultSet contains.
2. boolean isAutoIncrement(int column) – This method is used to determine if the column is defined as auto increment or not.
3. int getColumnDisplaySize(int column)- This method is used to determine the size of column.
4.  String getColumnLabel(int column) – This method is used to determine the alias name of column.
5. String getColumnName(int column)- This method is used to determine the name of the column.
6. String getColumnTypeName(int column)- This method is used to determine the data type of column.
7. int isNullable(int column)- This method is used to determine if the columns is nullable or not. This method returns 0 if the column is not null and 1 if column is nullable.

 There are other methods available but these are the most commonly used.    

15.3 Examples

We will use the MySQL database .

a.  Use the below sql statement to create a schema 

        Create schema JDBCTutorial;

b.  Create table User with 4 columns using below SQL

     CREATE  TABLE `User` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NULL ,  `age` VARCHAR(45) NULL ,  `email` VARCHAR(45) NULL ,  PRIMARY KEY (`id`) );

15.3.1 – Write an Example to determine the meta data of the User table

Solution –

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

public class ResultSetMetaDataExample {

    public static void main(String args[])
    {
        try
        {
            Connection conn= getConnection();
            String sql = "select * from  User";
          
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            ResultSetMetaData metaData= rs.getMetaData();
            int numberOfColumns= metaData.getColumnCount();

            System.out.println("Total Number of Columns are -" + numberOfColumns);

            System.out.println();
            System.out.println();
            for(int i=1;i<=numberOfColumns;i++){

                String columnName= metaData.getColumnName(i);                
                int colSize= metaData.getColumnDisplaySize(i);                
                String dataType=metaData.getColumnTypeName(i);                
                String tableName=metaData.getTableName(i);
                boolean isAutoInc=metaData.isAutoIncrement(i);
                int isNull=metaData.isNullable(i);

                System.out.println("Name of Column is = " + columnName);
                System.out.println("Size of Column is =" +colSize);
                System.out.println("Data Type of Column is =" +dataType);
                System.out.println("Table Name is="+tableName);
                System.out.println("Is the Column is Auto Incremented ?" + isAutoInc);
                System.out.println("Is column is Defined as Nullable ?"+isNull);                
                System.out.println("- - - - - - - - - - - - - - - - - - - - - - - - ");                
            }
                rs.close();
                stmt.close();
                conn.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
    private static Connection getConnection() {

        Connection con = null;
        String dbDriver = "com.mysql.jdbc.Driver";
        String dbUsername = "root";
        String dbPassword="password";
        String dbHostname="localhost";
        String dbPort="3306";
        String schema="JdbcTutorial";
        try {
               String     url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema;
            Class.forName(dbDriver);
            con = DriverManager.getConnection(url, dbUsername, dbPassword);
        }
        catch (ClassNotFoundException ex1)
        {
            System.out.println("Failed to find driver class " + ex1.getMessage());
            System.exit(1);
        }
        catch (SQLException ex2) {
            System.out.println("Connection failed " + ex2.getMessage());
            System.exit(2);
        }
        return con;
    }
}

On Running above program, we can see below output

Total Number of Columns are -4

 

Name of Column is = id

Size of Column is = 11

Data Type of Column is = INT

Table Name is=user

Is the Column is Auto Incremented ?true

Is column is Defined as Nullable ?0

- - - - - - - - - - - - - - - - - - - - - - - -

Name of Column is = name

Size of Column is =45

Data Type of Column is =VARCHAR

Table Name is=user

Is the Column is Auto Incremented ?false

Is column is Defined as Nullable ?1

- - - - - - - - - - - - - - - - - - - - - - - -

Name of Column is = age

Size of Column is =45

Data Type of Column is =VARCHAR

Table Name is=user

Is the Column is Auto Incremented ?false

Is column is Defined as Nullable ?1

- - - - - - - - - - - - - - - - - - - - - - - -

Name of Column is = email

Size of Column is =45

Data Type of Column is =VARCHAR

Table Name is=user

Is the Column is Auto Incremented ?false

Is column is Defined as Nullable ?1

- - - - - - - - - - - - - - - - - - - - - - - -