JDBC DatabaseMetaData Interface Example with MySQL Database

In this article, we will learn how to use DatabaseMetaData interface methods to get metadata of a database such as a database product name, database product version, driver name, name of a total number of tables, a name of a total number of views 

DatabaseMetaData interface Overview

DatabaseMetaData interface provides methods to get metadata of a database such as a database product name, database product version, driver name, name of a total number of tables, a name of a total number of views etc.
This interface is implemented by driver vendors to let users know the capabilities of a Database Management System (DBMS) in combination with the driver based on JDBC technology ("JDBC driver") that is used with it.
Different relational DBMSs often support different features, implement features in different ways, and use different data types. In addition, a driver may implement a feature on top of what the DBMS offers. Information returned by methods in this interface applies to the capabilities of a particular driver and a particular DBMS working together.

DatabaseMetaData interface Commonly used methods

  • public String getDriverName()throws SQLException: it returns the name of the JDBC driver.
  • public String getDriverVersion()throws SQLException: it returns the version number of the JDBC driver.
  • public String getUserName()throws SQLException: it returns the username of the database.
  • public String getDatabaseProductName()throws SQLException: it returns the product name of the database.
  • public String getDatabaseProductVersion()throws SQLException: it returns the product version of the database.
  • public ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)throws SQLException: it returns the description of the tables of the specified catalog. The table type can be TABLE, VIEW, ALIAS, SYSTEM TABLE, SYNONYM etc.

1. Simple Example of DatabaseMetaData interface

package com.javaguides.jdbc.databasemetadata;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * DatabaseMetaData interface API examples
 * @author Ramesh Fadatare
 *
 */
public class DatabaseMetaDataExample {

    public static void main(String[] args) {
        databaseInfo();
    }

    private static void databaseInfo() {
        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root")) {
            DatabaseMetaData dbmd = connection.getMetaData();
            System.out.println("Driver Name: " + dbmd.getDriverName());
            System.out.println("Driver Version: " + dbmd.getDriverVersion());
            System.out.println("UserName: " + dbmd.getUserName());
            System.out.println("Database Product Name: " + dbmd.getDatabaseProductName());
            System.out.println("Database Product Version: " + dbmd.getDatabaseProductVersion());

        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    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:
Driver Name: MySQL Connector Java
Driver Version: mysql-connector-java-5.1.44 ( Revision: b3cda4f864902ffdde495b9df93937c3e20009be )
UserName: root@localhost
Database Product Name: MySQL
Database Product Version: 8.0.11

Example of DatabaseMetaData interface that prints the total number of tables

package com.javaguides.jdbc.databasemetadata;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * DatabaseMetaData interface API examples
 * @author Ramesh Fadatare
 *
 */
public class DatabaseMetaDataExample {

    public static void main(String[] args) {
        printDatabaseTables();
    }

    private static void printDatabaseTables() {
        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root")) {
            DatabaseMetaData dbmd = connection.getMetaData();
            String table[] = {
                "TABLE"
            };
            ResultSet rs = dbmd.getTables(null, null, null, table);

            while (rs.next()) {
                System.out.println(rs.getString(3));
            }

        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    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:
users

Example of DatabaseMetaData interface that prints the total number of views

package com.javaguides.jdbc.databasemetadata;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * DatabaseMetaData interface API examples
 * @author Ramesh Fadatare
 *
 */
public class DatabaseMetaDataExample {

    public static void main(String[] args) {
        printDatabaseViews();
    }

    private static void printDatabaseViews() {
        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root")) {
            DatabaseMetaData dbmd = connection.getMetaData();
            String table[] = {
                "VIEW"
            };
            ResultSet rs = dbmd.getTables(null, null, null, table);

            while (rs.next()) {
                System.out.println(rs.getString(3));
            }

        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    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();
                }
            }
        }
    }
}

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.

Comments