Metadata is like the foundation of your data
Metadata is like the foundation of your data

In this episode, I will explain what is metadata, at least, some metadata, more specifically metadata on relational databases. It’s a quick introduction.

I will also run a small Java lab to extract metadata from PostgreSQL using JDBC. The tools used are Eclipse, Java, and PostgreSQL.

I am using the JDBC DatabaseMetaData interface, implemented by driver vendors to let users know the capabilities of a database management system (DBMS) in combination with the driver based on JDBC technology (“JDBC driver”). Different RDBMSs (relational database management system) often support different features, implement features in different ways, and use different data types.

In addition, a driver may implement a feature on top of what the DBMS offers. Information returned by methods in this interface applies to the capabilities of a particular driver and a particular DBMS working together.

The code is available on GitHub.

For convenience, the Java code is added here. You will see the main steps of this small application:

  1. Get a JDBC connection.
  2. Access the metadata.
  3. Browse through tables and views.
package net.jgp.labs.jdbc.lab400_postgresql_metadata_dump;

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

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Lists all tables in a specific database
 * 
 * @author jgp
 */
public class PostgresSqlListTablesApp {
	private static final Logger log = LoggerFactory.getLogger(PostgresSqlListTablesApp.class);

	/**
	 * main() is your entry point to the application.
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		PostgresSqlListTablesApp app = new PostgresSqlListTablesApp();
		app.start();
	}

	/**
	 * The processing code.
	 */
	protected boolean start() {
		log.debug("-> start()");

		String database = "spark_labs";
		String username = "postgres";
		String password = "Spark<3Java";
		String url = "jdbc:postgresql://localhost/";

		Connection connection;
		try {
			connection = DriverManager.getConnection(url + database, username, password);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return false;
		}

		DatabaseMetaData metadata;
		try {
			metadata = connection.getMetaData();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

		String[] types = { "TABLE", "VIEW" };

		ResultSet tablesRs;
		try {
			tablesRs = metadata.getTables(null, null, "%", types);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

		try {
			while (tablesRs.next()) {
				log.info("Table/view name ... {}", tablesRs.getString("TABLE_NAME"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return true;
	}
}

This is a basic metadata extraction process, but it illustrates the basics.

More resources:

The YouTube channel for DataFriday lists all episodes. You can attend the live show every Friday morning at 8 AM EST on Zoom.