How to Read Database Meta Data in JDBC

Besides retrieving the actual data stored in tables from a database, you can also read information about that data such as table names, column names, column types, column sizes, and other capabilities and features supported by the DBMS. This information about information is called meta data.

In this JDBC tutorial, you will learn how to read database meta data in JDBC with two interfaces DatabaseMetaData  and ResultSetMetaData.

Reading database meta data is useful when you want to create database tools that allow the users to explore the structure of databases of different DBMSs, or when you have to check whether the underlying database supports some features or not, to process further accordingly.

Once a connection is established with the database, you can retrieve meta data about that database by invoking the getMetaData() method on the Connection object:

Connection connection = DriverManager.getConnection(url, username, password);
DatabaseMetaData meta = connection.getMetaData();

The getMetaData() method returns an object that implements the DatabaseMetaData  interface that provides a variety of methods to read comprehensive information about the database as a whole.

1. The DatabaseMetaData Interface

This interface is implemented by JDBC driver to let users (programmers) know the capabilities of the DBMS as well as information about the JDBC driver itself.

You know, different DBMSs often support different features, implement features in different ways, and use different data types. Therefore, having access to such information is helpful for developing database tools that can work with different databases.

The DatabaseMetaData interface is huge, containing hundreds of methods for reading capabilities of a DBMS. So in this tutorial, I show you only a small portion of its API, and you should refer to its Javadoc for the complete list of methods.

For example, the following code reads information about database product name and version:

DatabaseMetaData meta = connection.getMetaData();
String productName = meta.getDatabaseProductName();
String productVersion = meta.getDatabaseProductVersion();

You will see more examples below.

2. The ResultSetMetaData Interface

A ResultSet object can be used to get information about the types and properties of columns in the result set returned by a query.

The ResultSet interface provides the getMetaData() method that returns a ResultSetMetaData object which you can use to obtain information about columns in the result set. For example, the following code gets the number of columns in the result set:

String sql = "SELECT * FROM student";

Statement statement = conn.createStatement();
ResultSet result = statement.executeQuery(sql);

ResultSetMetaData rsMeta = result.getMetaData();

int numberOfColumns = rsMeta.getColumnCount();

Let’s see more detailed code examples about DatabaseMetaData and ResultSetMetaData below.

3. Reading Structural Information of Tables in a Database

To retrieve names of all tables, call getTables() method of the DatabaseMetaData interface like the following code:

String catalog = null, schemaPattern = null, tableNamePattern = null;
String[] types = {"TABLE"};

ResultSet result = meta.getTables(catalog, schemaPattern, tableNamePattern, types);

Here, the first 3 parameters are set to null indicates that we don’t want to narrow the search based on those parameters. The getTables() method returns a ResultSet object in which the 3^rd column stores the name of the table. The following code reads name of the first table:

result.next();
String tableName = result.getString(3);

To read meta data about columns of a particular table, call the getColumns() method DatabaseMetaData interface like this:

String catalog = null, schemaPattern = null, columnNamePattern = null;
String tableName = "student";

ResultSet rsColumns = meta.getColumns(catalog, schemaPattern, tableName, columnNamePattern);

Again, the null arguments meaning that they are not used to narrow the search. The getColumns() method returns a ResultSet object which you can use its getXXX() methods to read properties of the columns such as name, type, size, etc.

To get information about primary keys of a table, invoke the getPrimaryKeys() method of the DatabaseMetaData interface like this:

String catalog = null, schemaPattern = null;
String tableName = "student";

ResultSet rsPK = meta.getPrimaryKeys(catalog, schemaPattern, tableName);

The returned ResultSet object stores the name of the primary columns.

The following program demonstrates how to read structural information of a database, including table names, column names, column types, column sizes, and primary keys. Here’s the code:

import java.sql.*;

/**
 * This program demonstrates how to get structural information of
 * a database.
 *
 * @author www.codejava.net
 */
public class ReadDatabaseStructureExample {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/college";
		String username = "root";
		String password = "password";

		try (Connection conn = DriverManager.getConnection(url, username, password)) {

			DatabaseMetaData meta = conn.getMetaData();

			String catalog = null, schemaPattern = null, tableNamePattern = null;
			String[] types = {"TABLE"};

			ResultSet rsTables = meta.getTables(catalog, schemaPattern, tableNamePattern, types);


			while (rsTables.next()) {
				String tableName = rsTables.getString(3);
				System.out.println("\n=== TABLE: " + tableName);

				String columnNamePattern = null;
				ResultSet rsColumns = meta.getColumns(catalog, schemaPattern, tableName, columnNamePattern);

				ResultSet rsPK = meta.getPrimaryKeys(catalog, schemaPattern, tableName);

				while (rsColumns.next()) {
					String columnName = rsColumns.getString("COLUMN_NAME");
					String columnType = rsColumns.getString("TYPE_NAME");
					int columnSize = rsColumns.getInt("COLUMN_SIZE");
					System.out.println("\t" + columnName + " - " + columnType + "(" + columnSize + ")");
				}

				while (rsPK.next()) {
					String primaryKeyColumn = rsPK.getString("COLUMN_NAME");
					System.out.println("\tPrimary Key Column: " + primaryKeyColumn);
				}

			}

		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
			ex.printStackTrace();
		}

	}
}

Run this program and you may see the output something like this:

NOTE: You can also use ResultSetMetaData to read meta data about columns in a result set returned from a SQL SELECT query like this:

String sql = "SELECT * FROM student";

Statement statement = conn.createStatement();
ResultSet result = statement.executeQuery(sql);

ResultSetMetaData rsMeta = result.getMetaData();

However it is not recommended because it requires getting all rows from the table first.

4. Reading Information about Database Product and JDBC Driver

The following program demonstrates how to use the DatabaseMetaData interface to get information about the database product and JDBC driver software such as their names and versions. Here’s the code:

import java.sql.*;

/**
 * This program shows an example of reading database metadata
 * such as product name and version.
 *
 * @author www.codejava.net
 */
public class ReadDatabaseInfoExample {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/college";
		String username = "root";
		String password = "P@ssw0rd";

		try (Connection conn = DriverManager.getConnection(url, username, password)) {

			DatabaseMetaData meta = conn.getMetaData();

			String productName = meta.getDatabaseProductName();
			String productVersion = meta.getDatabaseProductVersion();
			System.out.println(productName +  " " + productVersion);

			int majorVersion = meta.getDatabaseMajorVersion();
			int minorVersion = meta.getDatabaseMinorVersion();
			System.out.printf("Database version: %d.%d\n", majorVersion, minorVersion);

			String driverName = meta.getDriverName();
			String driverVersion = meta.getDriverVersion();

			System.out.println("Driver Info: " + driverName + " - " + driverVersion);

			int jdbcMajorVersion = meta.getJDBCMajorVersion();
			int jdbcMinorVersion = meta.getJDBCMinorVersion();

			System.out.println("JDBC Version: " + jdbcMajorVersion + "." + jdbcMinorVersion);

		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
			ex.printStackTrace();
		}

	}
}

This program would produce the following output:

5. Checking Supported Features

Another common usage of DatabaseMetaData is to check whether the database supports some certain features or not, as in some cases, you have to perform the check before proceeding further.

For example, the following code checks if the database supports scrollable and updatable result sets:

boolean scrollableUpdatable = meta.supportsResultSetConcurrency(
		ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

System.out.println("Support Scrollable & Updatable Result Set: " + scrollableUpdatable);

And the following program illustrates how to check various features supported by the DBMS:

import java.sql.*;


/**
 * This program checks if the database support some features or not.
 *
 * @author www.codejava.net
 */
public class CheckFeaturesExample {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/college";
		String username = "root";
		String password = "P@ssw0rd";

		try (Connection conn = DriverManager.getConnection(url, username, password)) {

			DatabaseMetaData meta = conn.getMetaData();

			System.out.println("Support Batch Updates: " + meta.supportsBatchUpdates());
			System.out.println("Support Column Aliasing: " + meta.supportsColumnAliasing());
			System.out.println("Support Core SQL Grammar: " + meta.supportsCoreSQLGrammar());
			System.out.println("Support Full Outer Joins: " + meta.supportsFullOuterJoins());
			System.out.println("Support Group By: " + meta.supportsGroupBy());

			System.out.println("Support Savepoints: " + meta.supportsSavepoints());
			System.out.println("Support Stored Procedures: " + meta.supportsStoredProcedures());
			System.out.println("Support Subqueries in EXISTS: " + meta.supportsSubqueriesInExists());
			System.out.println("Support Transactions: " + meta.supportsTransactions());
			System.out.println("Support Union: " + meta.supportsUnion());
			System.out.println("Support Union All: " + meta.supportsUnionAll());



		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
			ex.printStackTrace();
		}

	}
}

Run this program, you may see the following output:

So far you have learned how to read database meta data with DatabaseMetaData and ResultSetMetaData interfaces. Reading database meta data is helpful when you want to check the capabilities or some features supported by the DBMS, or when you want to develop database tools that needs to work with different databases.

References:

DatabaseMetaData Javadoc

ResultSetMetaData Javadoc

Other JDBC Tutorials:

• JDBC Driver Downloads
• JDBC Database Connection URLs
• How to connect to a database with JDBC
• JDBC CRUD Tutorial
• JDBC Transaction Tutorial
• How to call stored procedure with JDBC
• How to read database metadata in JDBC
• How to insert binary data into database with JDBC
• How to read binary data from database with JDBC
• How to use Scrollable ResultSet
• How to use Updatable ResultSet
• How to use CachedRowSet

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.