JDBC DatabaseMetaData Interface

Using java.sql.DatabaseMetaData interface you can get metadata about the DB you are connected to.

DatabaseMetaData interface in Java has methods to get information about-

  • Database like DB Product name and version, scehemas available in DB.
  • JDBC Driver like the driver’s name and version
  • Tables in any DB schema,
  • Views in the Schema
  • Stored procedures and functions

How to get DatabaseMetaData Object

You obtain the DatabaseMetaData instance by calling the getMetaData() method of the Connection class.

DatabaseMetaData databaseMetaData = connection.getMetaData();

DB Product, version and user information using DatabaseMetaData

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

public class MetaDataInfo {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode", 
                        "root", "admin");
      // DBMetaData instance
      DatabaseMetaData dbMetaData = connection.getMetaData();
      // Database information
      System.out.println("DB Name - " + dbMetaData.getDatabaseProductName());
      System.out.println("DB Version - " + dbMetaData.getDatabaseProductVersion());
      System.out.println("DB Major Version - " + dbMetaData.getDatabaseMajorVersion());
      System.out.println("DB Minor Version - " + dbMetaData.getDatabaseMinorVersion());
      System.out.println("DB User - " + dbMetaData.getUserName());
      
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null) {
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } 
    }
  }
}

JDBC Driver Name, version information using DatabaseMetaData

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

public class MetaDataInfo {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode", 
                        "root", "admin");
      // DBMetaData instance
      DatabaseMetaData dbMetaData = connection.getMetaData();
      // Driver information
      System.out.println("Driver Name - " + dbMetaData.getDriverName());
      System.out.println("Driver Version - " + dbMetaData.getDriverVersion());
      System.out.println("Driver Major Version - " + dbMetaData.getDriverMajorVersion());
      System.out.println("Driver Minor Version - " + dbMetaData.getDriverMinorVersion());
      System.out.println("JDBC Major Version - " + dbMetaData.getJDBCMajorVersion());
      System.out.println("JDBC Minor Version - " + dbMetaData.getJDBCMinorVersion());
      
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null) {
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } 
    }
  }
}

Listing tables in DB using DatabaseMetaData

You can get a list of tables in the DB using getTables() method.

getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)

Parameters passed to this method are-

  • catalog- A catalog name
  • schemaPattern- A schema name pattern
  • tableNamePattern- A table name pattern
  • types- A list of table types. Typical types are “TABLE”, “VIEW”, “SYSTEM TABLE”, “GLOBAL TEMPORARY”, “LOCAL TEMPORARY”, “ALIAS”, “SYNONYM”.

If you pass values for any of these parameters that helps in narrowing down the search and limit the number of tables returned. You can pass all of these parameters as null to return all the table types.

The getTables() method returns a ResultSet where each row contains a table description. This table description is made up of 10 columns. For getting the table names you will have to get the third column value from each row in the ResultSet.

TABLE_CAT– Table catalog
TABLE_SCHEM– Table schema
TABLE_NAME– Table name
TABLE_TYPE– Table type
REMARKS– Explanatory comment on the table
TYPE_CAT– The types catalog
TYPE_SCHEM– The types schema
TYPE_NAME– Type name
SELF_REFERENCING_COL_NAME– Name of the designated “identifier” column of a typed table
REF_GENERATION– Specifies how values in SELF_REFERENCING_COL_NAME are created. Values are “SYSTEM”, “USER”, “DERIVED”.

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

public class MetaDataInfo {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode", 
                        "root", "admin");
      // DBMetaData instance
      DatabaseMetaData dbMetaData = connection.getMetaData();
      ResultSet rs = dbMetaData.getTables(null, null, null, null);
      while (rs.next()){
       // Third column for table name
       System.out.println("Table name " + rs.getString(3));
      }
      
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null) {
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } 
    }
  }
}

If you want to list only tables then you can limit the search by passing table types as table.

String table[] = {"table"};
ResultSet rs = dbMetaData.getTables(null, null, null, table);

If you want to list only views then you can limit the search by passing table types as view.

String table[] = {"view"};
ResultSet rs = dbMetaData.getTables(null, null, null, table);

Columns of a DB table using DatabaseMetaData

You can get the column names of a specific table by using the getColumns() method.

getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)

Parameters passed to this method are-

  • catalog- A catalog name
  • schemaPattern- A schema name pattern
  • tableNamePattern- A table name pattern
  • columnNamePattern- A column name pattern

The getColumns() method returns a ResultSet where each row contains a column description. This column description is made up of 24 columns. For getting the column names you will have to get the fourth column value from each row in the ResultSet.

public class MetaDataInfo {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode", 
                        "root", "admin");
      // DBMetaData instance
      DatabaseMetaData dbMetaData = connection.getMetaData();
      ResultSet rs = dbMetaData.getColumns(null, null, "Employee", null);
     
      while (rs.next()){
       System.out.println("Column name-" + rs.getString(4));
      }
      
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null) {
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } 
    }
  }
}

Related Posts

That’s all for the topic JDBC DatabaseMetaData Interface. If something is missing or you have something to share about the topic please write a comment.


You may also like

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.