JDBC Tutorial: Extracting Database Metadata via JDBC Driver

How do you extract the metadata from a database you're connecting to? This answer and more in part two of our JDBC series.

This tutorial is part two of the JDBC-Revisited tutorial series. In the last part, we demonstrated how easy it is to connect to a data source regardless of whether it's a Relational or Big Data or SaaS source, and how to execute simple queries using Progress DataDirect JDBC drivers.

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

1. 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"));

   }

2. 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.
3. 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.
4. 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())

   {

       //Print

       System.out.println(resultSet.getString("TABLE_NAME"));

    

5. In a similar manner you can easily extract information about views, by adding ‘VIEW’ to the type parameter of getTables() method.

Extract Column Info

1. To extract columns belonging to a table, we are going to use the same DatabaseMetaData object to call a method called getColumns(String Schema, String catalog, String tableNamePattern, String ColumnNamePattern).
2. For this method, just pass the table name you are looking for to get its column information (all the remaining parameters can be null) to fetch information about all the columns in that table. This method returns a ResultSet object which you can iterate on to get the column name, datatype, precision, etc. from it. The following is a code snippet that prints all the column info of a table to console:

     

   ResultSet columns = databaseMetaData.getColumns(null,null, tableName, null);

   while(columns.next())

   {

       String columnName = columns.getString("COLUMN_NAME");

       String datatype = columns.getString("DATA_TYPE");

       String columnsize = columns.getString("COLUMN_SIZE");

       String decimaldigits = columns.getString("DECIMAL_DIGITS");

       String isNullable = columns.getString("IS_NULLABLE");

       String is_autoIncrment = columns.getString("IS_AUTOINCREMENT");

       //Printing results

       System.out.println(columnName + "---" + datatype + "---" + columnsize + "---" + decimaldigits + "---" + isNullable + "---" + is_autoIncrment);

   }

    

3. To know about the Primary Key Columns in a table, call the method getPrimaryKeys(String Catalog, String Schema, String tableName). This returns a ResultSet object which has info about all the Primary Keys of the table, which you can access by iterating through each result in ResultSet. Here is a code snippet that shows how to do it:
   //GetPrimarykeys

   ResultSet PK = databaseMetaData.getPrimaryKeys(null,null, tableName);

   System.out.println("------------PRIMARY KEYS-------------");

   while(PK.next())

   {

       System.out.println(PK.getString("COLUMN_NAME") + "===" + PK.getString("PK_NAME"));

   }

    

4. Similarly, if you want to fetch all the Foreign Key Columns along with the Primary Table and column they refer to, you can call the method getImportedKeys(String catalog, String Schema, String tableName). Here is a code snippet to demonstrate this:
   //Get Foreign Keys

   ResultSet FK = databaseMetaData.getImportedKeys(null, null, tableName);

   System.out.println("------------FOREIGN KEYS-------------");

   while(FK.next())

   {

       System.out.println(FK.getString("PKTABLE_NAME") + "---" + FK.getString("PKCOLUMN_NAME") + "===" + FK.getString("FKTABLE_NAME") + "---" + FK.getString("FKCOLUMN_NAME"));

   }

    

5. Apart from the Table Info, you can also get info about Stored Procedures by calling the method getStoredProcedures(String Catalog, String schemaPattern, String TableNamePattern) and functions by calling the method getFunctions(String catalog, String schemaPattern, String functionNamePattern).
6. The following is a screenshot showing the list of extracted table names when connected to Salesforce for your reference:
   
   

Metadata Extraction Made Simple

We hope this tutorial gave you a basic idea on how you can access the metadata of your data source using Progress DataDirect JDBC drivers. If you want to learn more about the DatabaseMetaData class and its methods, you can always visit this page. Also, I have pushed the code that’s used in this tutorial to GitHub for your reference. If you still have any issues connecting to your database using Progress DataDirect JDBC drivers, leave your comments below or contact support.

In the next part of this JDBC-Revisited tutorial series, we will show you how Progress DataDirect JDBC drivers offer database interoperability features as well as how they can make the life of developer easier. Subscribe to our blog via Email or RSS feed  for updates to this tutorial series.

Try Any JDBC Driver Free for 15 Days