Sunday, March 25, 2018

DatabaseMetaData Interface in Java-JDBC

DatabaseMetaData in Java, which resides in java.sql package, provides information about the database (DB meta data) you are connected to.

Using the methods provided by Java DatabaseMetaData interface you can get information about-

  • Database like DB name and version
  • JDBC driver like the driver’s name and version,
  • names of DB schemas,
  • name of tables in any DB schema,
  • names of views,
  • information about the procedures.

In this post we’ll see examples of some of the commonly used methods. You can get the list of full methods here- https://docs.oracle.com/javase/9/docs/api/java/sql/DatabaseMetaData.html


How to get DatabaseMetaData object in JDBC

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

DatabaseMetaData dbMetaData = connection.getMetaData();

DatabaseMetaData example-Getting DB product and version information

This example code shows how you can get DB name and version information using DatabaseMetaData in JDBC.

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

public class DBMetaData {

 public static void main(String[] args) {
  Connection connection = null;
   try {
    // Loading driver
    Class.forName("com.mysql.jdbc.Driver");
    
    // Creating connection
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                        "root", "admin");
    
    DatabaseMetaData dbMetaData = connection.getMetaData();
    
    System.out.println("Database Name - " + dbMetaData.getDatabaseProductName());
    System.out.println("Database Version - " + dbMetaData.getDatabaseProductVersion());
    System.out.println("Database Major Version - " + dbMetaData.getDatabaseMajorVersion());
    System.out.println("Database Minor Version - " + dbMetaData.getDatabaseMinorVersion());
    System.out.println("Database 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){
         //closing connection 
         try {
           connection.close();
         } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         }
       } // if condition
   }// finally
 }
}

DatabaseMetaData example - Getting driver information

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

public class DBMetaData {
 public static void main(String[] args) {
  Connection connection = null;
   try {
    // Loading driver
    Class.forName("com.mysql.jdbc.Driver");
    
    // Creating connection
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                     "root", "admin");
    
    DatabaseMetaData dbMetaData = connection.getMetaData();
    
    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){
          //closing connection 
         try {
           connection.close();
         } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         }
       } // if condition
   }// finally
 }
}

Example to get tables using DatabaseMetaData in JDBC

For getting tables you can use getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) method. You can provide null as value for all the parameters, that way you don’t narrow the search and all the tables are returned. If you want to narrow your search to get specific tables then you can provide values for these parameters.

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

public class DBMetaData {

 public static void main(String[] args) {
  Connection connection = null;
   try {
    // Loading driver
    Class.forName("com.mysql.jdbc.Driver");
    
    // Creating connection
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/world", 
                        "root", "admin");
    
    DatabaseMetaData dbMetaData = connection.getMetaData();
    
    ResultSet rs = dbMetaData.getTables(null, null, null, null);
    while (rs.next()){
     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){
          //closing connection 
          try {
            connection.close();
          } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
          }
       } // if condition
   }// finally
 }
}

Output

Table name city
Table name country
Table name countrylanguage

Here I am connecting to “world” schema in MySQL and getting all the tables.

Each table description in the returned ResultSet has the following columns:

Table Description Columns
Column Name Type Description
TABLE_CAT String table catalog (may be null)
TABLE_SCHEM String table schema (may be null)
TABLE_NAME String table name
TABLE_TYPE String table type. Typical types are "TABLE", "VIEW" etc.
REMARKS String explanatory comment on the table (may be null)
TYPE_CAT String the types catalog (may be null)
TYPE_SCHEM String the types schema (may be null)
TYPE_NAME String type name (may be null)
SELF_REFERENCING_COL_NAME String name of the designated "identifier" column of a typed table (may be null)
REF_GENERATION String specifies how values in SELF_REFERENCING_COL_NAME are created.

That’s why column index is 3 while getting result from ResultSet as TABLE_NAME is at number 3.

Example to get Procedures using DatabaseMetaData in JDBC

For getting procedures you can use getProcedures(String catalog, String schemaPattern, String procedureNamePattern) method. Again you can pass null as value for all the parameters if you don’t want to narrow the search.

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

public class DBMetaData {

 public static void main(String[] args) {
  Connection connection = null;
   try {
    // Loading driver
    Class.forName("com.mysql.jdbc.Driver");
    
    // Creating connection
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                        "root", "admin");
    
    DatabaseMetaData dbMetaData = connection.getMetaData();
    
    ResultSet rs = dbMetaData.getProcedures(null, null, null);
    
    while (rs.next()){
     System.out.println("Procedure 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){
          //closing connection 
          try {
            connection.close();
          } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
          }
       } // if condition
   }// finally
 }
}

Each procedure description in the returned ResultSet has the following columns:

Procedure Description Columns
Column Name Type Description
PROCEDURE_CAT String procedure catalog (may be null)
PROCEDURE_SCHEM String procedure schema (may be null)
PROCEDURE_NAME String procedure name
reserved for future use
reserved for future use
reserved for future use
REMARKS String explanatory comment on the procedure
PROCEDURE_TYPE short type name (may be null)
SPECIFIC_NAME String The name which uniquely identifies this procedure within its schema.

That’s why column index is 3 while getting result from ResultSet as PROCEDURE_NAME is at number 3.

That's all for this topic DatabaseMetaData Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page


Related Topics

  1. DataSource in Java-JDBC
  2. Java JDBC Steps to Connect to DB
  3. Statement Interface in Java-JDBC
  4. ResultSet Interface in Java-JDBC
  5. Data access in Spring framework

You may also like-

  1. How LinkedList class works internally in Java
  2. Difference between HashMap and ConcurrentHashMap in Java
  3. ReentrantReadWriteLock in Java
  4. Livelock in Java Multi-Threading
  5. Enum Type in Java
  6. interface static methods in Java 8
  7. Arrange Non-Negative Integers to Form Largest Number - Java Program
  8. Print odd-even numbers using threads and wait-notify