Sunday, 13 July 2014

JDBC ResultSetMetadata Demo

Enjoying Java & Database Tutorials?

Subscribe to Ram N Java on YouTube for beginner-friendly coding lessons and hands-on demos!

How to Use ResultSetMetaData in Java JDBC

When working with database queries dynamically, you might not know the exact schema or structure of the returned tabular data in advance. In Java, JDBC (Java Database Connectivity) provides the ResultSetMetaData interface to find out details about the types and properties of the columns in a ResultSet object.

What Information Does ResultSetMetaData Provide?

The ResultSetMetaData interface allows you to programmatically inspect:

Column Count: The total number of columns returned in the query using getColumnCount().
Column Names & Labels: The designated names or display aliases via getColumnName(int column) and getColumnLabel(int column).
Data Types: SQL and Java types for each column using getColumnType(int column) and getColumnTypeName(int column).
Table & Schema Info: Originating table name with getTableName(int column).

Step-by-Step Java Implementation

Follow these standard steps to inspect query results using ResultSetMetaData:

Establish Connection: Connect to your target database using DriverManager.getConnection().
Execute Query: Run your SQL SELECT query using a Statement or PreparedStatement to obtain a ResultSet.
Get Metadata: Call resultSet.getMetaData() to obtain the ResultSetMetaData object.
Iterate Through Columns: Use a loop from 1 to getColumnCount() to print column names, types, and sizes.
Clean Up: Safely close all database resources using try-with-resources.

Watch More JDBC Tutorials

Click here to watch in Youtube : https://www.youtube.com/watch?v=cdnBS9E_4Ow

Click the below Image to Enlarge

JDBCResultSetMetaDataDemo Project Dir Structure

JDBC ResultSetMetadata

JDBCResultSetMetaDataDemo.java

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 JDBCResultSetMetaDataDemo
{
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/world";

    // Database credentials
    static final String USERNAME = "root";
    static final String PASSWORD = "root";

    public static void main(String[] args)
    {

        JDBCResultSetMetaDataDemo jdbcResultSetMetaDataDemo =

                                       new JDBCResultSetMetaDataDemo();
        jdbcResultSetMetaDataDemo.getCityMetadataInformation();

    }

    private void getCityMetadataInformation()
    {
        Connection connection = null;
        Statement stmt = null;
        try
        {
            /*
             * Register the JDBC driver in DriverManager
             */

            Class.forName(JDBC_DRIVER);

            /*
             * Establish connection to the Database using DriverManager
             */

            connection = DriverManager
                    .getConnection(DB_URL, USERNAME, PASSWORD);

            stmt = connection.createStatement();
            String sql = "select ID,Name,CountryCode,District,Population from city";

            /*
             * Execute a query
             */

            ResultSet rs = stmt.executeQuery(sql);

            ResultSetMetaData rsmd = rs.getMetaData();

            int columnCount = rsmd.getColumnCount();
            System.out.println("Total number of columns : " + columnCount
                    + "\n");

            /*
             * Get all column names,column type and columnDisplaySize from the
             * ResultSetMetaData
             */
            for (int i = 1; i <= columnCount; i++)
            {
                String columnName = rsmd.getColumnName(i);
                String columnType = rsmd.getColumnTypeName(i);
                int columnDisplaySize = rsmd.getColumnDisplaySize(i);
                System.out.println("ColumnName : " + columnName + ","
                        + "ColumnType : " + columnType + ","
                        + "ColumnDisplaySize : " + columnDisplaySize);
                System.out.println();
            }

            rs.close();
            stmt.close();
            connection.close();
        }
        catch (SQLException se)
        {
            /*
             * Handle errors for JDBC
             */
            se.printStackTrace();
        }
        catch (ClassNotFoundException e)
        {
            /*
             * Handle errors for Class.forName
             */
            e.printStackTrace();
        }
        finally
        {
            /*
             * finally block used to close resources
             */
            try
            {
                if (stmt != null)
                {
                    stmt.close();
                }
            }
            catch (SQLException sqlException)
            {
                sqlException.printStackTrace();
            }
            try
            {
                if (connection != null)
                {
                    connection.close();
                }
            }
            catch (SQLException sqlException)
            {
                sqlException.printStackTrace();
            }
        }

    }

}

Output

Total number of columns : 5

ColumnName : ID,ColumnType : INT,ColumnDisplaySize : 11

ColumnName : Name,ColumnType : CHAR,ColumnDisplaySize : 35

ColumnName : CountryCode,ColumnType : CHAR,ColumnDisplaySize : 3

ColumnName : District,ColumnType : CHAR,ColumnDisplaySize : 20

ColumnName : Population,ColumnType : INT,ColumnDisplaySize : 11

Environment Used

JDK version : 1.7.0_51
Mysql Server version : 5.6.19

To Download JDBCResultSetMetaDataDemoApp Project Click the below link

https://sites.google.com/site/javaee4321/jdbc/JDBCResultSetMetaDataDemoApp.zip?attredirects=0&d=1

See also:

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial