Java JDBC ResultSetMetaData Interface Example with MySQL Database

In this article, we will discuss JDBC ResultSetMetaData Interface commonly used APIs with examples.
In this example, we are going to use the MySQL database.

Technologies used

  • JDK - 1.8 or later
  • MySQL - 5.7.12
  • IDE - Eclipse Neon
  • JDBC API - 4.2

ResultSetMetaData Interface Overview

ResultSetMetaData object that can be used to get information about the types and properties of the columns in a ResultSet object.
ResultSet object rs creates the ResultSetMetaData object rsmd, and uses rsmd to find out how many columns rs has and whether the first column in rs can be used in a WHERE clause.
     ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
     ResultSetMetaData rsmd = rs.getMetaData();
     int numberOfColumns = rsmd.getColumnCount();
     boolean b = rsmd.isSearchable(1);

ResultSetMetaData InterfaceClass Diagram

ResultSetMetaData Interface Commonly used methods

  1. String getCatalogName(int column) - Gets the designated column's table's catalog name.
  2. String getColumnClassName(int column) - Returns the fully-qualified name of the Java class whose instances are manufactured if the method ResultSet.getObject is called to retrieve a value from the column.
  3. int getColumnCount() - Returns the number of columns in this ResultSet object.
  4. String getColumnName(int column) - Get the designated column's name.
  5. int getColumnType(int column) - Retrieves the designated column's SQL type.
  6. String getColumnTypeName(int column) - Retrieves the designated column's database-specific type name.
  7. int getPrecision(int column) - Get the designated column's specified column size.
  8. int getScale(int column) - Gets the designated column's number of digits to right of the decimal point.
  9. String getSchemaName(int column) - Get the designated column's table's schema.
  10. String getTableName(int column) - Gets the designated column's table name.

ResultSetMetaData Interface Examples

This example demonstrates below commonly used APIs of ResultSetMetaData interface:
package com.javaguides.jdbc.databasemetadata;

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

/**
 * 
 * @author Ramesh Fadatare
 *
 */
public class ResuleSetMetaDataExample {
    private static final String QUERY = "select id,name,email,country,password from Users";

    public static void main(String[] args) {

        // using try-with-resources to avoid closing resources (boiler plate code)

        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            Statement stmt = connection.createStatement();

            // Step 3: Execute the query or update query
            ResultSet rs = stmt.executeQuery(QUERY)) {

            ResultSetMetaData resultSetMetaData = rs.getMetaData();

            System.out.println("1. Column count in users table :: " + resultSetMetaData.getColumnCount());
            System.out.println("2. First column name in users table :: " + resultSetMetaData.getColumnName(1));
            System.out.println("3. Database name of users table' column id :: " + resultSetMetaData.getCatalogName(1));;
            System.out.println("4. Data type of column id :: " + resultSetMetaData.getColumnTypeName(1));
            System.out.println("5. Get table name of column id :: " + resultSetMetaData.getTableName(1));

        } catch (SQLException e) {
            printSQLException(e);
        }
        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
Output:
Column count in users table :: 5
First column name in users table :: id
Database name of users table' column id :: mysql_database
Data type of column id :: INT
Get table name of column id :: users

JDBC Statement Interface Examples

          Example to create a table using a Statement interface.
          Example to insert multiple records in a table using Statement interface.
          Example to update a record in a table using Statement interface.
          Example to retrieve records from a table using Statement interface.
          Example to delete a record from a table using a Statement interface.
          Example to insert records in a batch process via Statement interface.
          Example to update records in a batch process via Statement interface.

JDBC PreparedStatement Interface Examples

         Example to insert a record in a table using the PreparedStatement interface.
         Example to update a record in a table using the PreparedStatement interface.
          Example to retrieve records from a table using the PreparedStatement interface.
          Example to pass a list of values to IN clause using PreparedStatement interface.
          Example to insert records in a batch process via PreparedStatement interface.
          Example to update records in a batch process via PreparedStatement interface.

Free Spring Boot Tutorial | Full In-depth Course | Learn Spring Boot in 10 Hours


Watch this course on YouTube at Spring Boot Tutorial | Fee 10 Hours Full Course

Comments