Extracting Database Metadata via JDBC Driver



In this tutorial, we will be focusing on how to extract the metadata of the data source to which you are connecting. For those who don’t know what metadata is, it characterizes your data and makes easier for anyone to understand and consume it. To put it simply, metadata is the data describing the data that is being stored in your data source.


Metadata generally includes the name, size and number of rows of each table present in a database, along with the columns in each table, their data types, precisions, etc. With this in mind, let’s start with the tutorial and learn how you can fetch metadata from any data source.


Extract Table Info

  • To get the metadata from your source, call the getMetaData() method using the Connection object that was created in the last part of this series. Here is a simple code to extract all the user defined tables from your data source.

databaseMetaData = connection.getMetaData();

//Print TABLE_TYPE "TABLE"
ResultSet resultSet = databaseMetaData.getTables(null, null, null, new String[]{"TABLE"});
System.out.println("Printing TABLE_TYPE \"TABLE\" ");
System.out.println("----------------------------------");
while(resultSet.next())
{
    //Print
    System.out.println(resultSet.getString("TABLE_NAME"));
}
  • The important method calls to notice are the connection.getMetaData() and databaseMetaData.getTables() methods. The connection.getMetaData() returns a DatabaseMetaData object that contains metadata about the database to which this Connection object is connected.

  • Using the DatabaseMetaData object, we can fetch the Tables by calling the method getTables(String catalog, String Schema, String tableNamepattern, string type). You can pass null values for catalog, schema and tableNamePattern. For type we need to send an array of Strings, with “TABLE” being stored in that array. This is a specific way of requesting all the user-defined tables. This method returns a ResultSet which you would have to iterate through to access all the table names. To learn more about this method visit this page.

  • To extract information about all the SYSTEM TABLES in your database, you just need to change the type to SYSTEM TABLE. Below is a code snippet:

resultSet = databaseMetaData.getTables(null, null, null, new String[]{"SYSTEM TABLE"});
System.out.println("Printing TABLE_TYPE \"SYSTEM TABLE\" ");
System.out.println("----------------------------------");
while(resultSet.next