How do I use DatabaseMetaData to inspect schema in JDBC?

By I Wayan Saryada in JDBC 0 Comment

You use java.sql.DatabaseMetaData by:

  1. Opening a Connection
  2. Calling conn.getMetaData()
  3. Querying catalogs/schemas/tables/columns/keys/indexes via the DatabaseMetaData “getXxx” methods (they mostly return ResultSets)
  4. Reading those result sets like normal query results

Below are the most common inspection tasks and the JDBC calls that power them.

1) Get the DatabaseMetaData

package org.kodejava.jdbc;

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

public class DatabaseMetadata1 {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {

            DatabaseMetaData md = conn.getMetaData();

            System.out.println(md.getDatabaseProductName() + " " + md.getDatabaseProductVersion());
            System.out.println(md.getDriverName() + " " + md.getDriverVersion());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

If you’re using a DataSource, it’s the same idea: try (Connection conn = dataSource.getConnection()) { ... }.

2) Understand the “filter” parameters (the #1 gotcha)

Many methods take filters like:

  • catalog (often the database name; sometimes unused)
  • schemaPattern (SQL pattern: % wildcard; sometimes case-sensitive depending on DB)
  • tableNamePattern (pattern)
  • types (e.g., {"TABLE","VIEW"})

Patterns are not regex; they’re SQL LIKE-style patterns:

  • % = any sequence
  • _ = any single character

Also: some databases store identifiers in upper-case/lower-case internally. Use:

  • md.storesUpperCaseIdentifiers()
  • md.storesLowerCaseIdentifiers()
  • md.supportsSchemasInTableDefinitions()

3) List catalogs and schemas

package org.kodejava.jdbc;

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

public class DatabaseMetadata2 {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {
            DatabaseMetaData md = conn.getMetaData();

            try (ResultSet rs = md.getCatalogs()) {
                while (rs.next()) {
                    System.out.println("Catalog: " + rs.getString("TABLE_CAT"));
                }
            }

            try (ResultSet rs = md.getSchemas()) {
                while (rs.next()) {
                    System.out.println("Schema: " + rs.getString("TABLE_SCHEM")
                                       + " (catalog=" + rs.getString("TABLE_CATALOG") + ")");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Column names like "TABLE_SCHEM" are specified by the JDBC spec for each metadata result set. (They feel a bit “old-school”, but they’re standard.)

4) List tables (and views)

package org.kodejava.jdbc;

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

public class DatabaseMetadata3 {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {
            DatabaseMetaData md = conn.getMetaData();

            String catalog = null;            // or your catalog name
            String schemaPattern = "public";  // adjust for your DB; or "%"
            String tablePattern = "%";
            String[] types = {"TABLE", "VIEW"};

            try (ResultSet rs = md.getTables(catalog, schemaPattern, tablePattern, types)) {
                while (rs.next()) {
                    String schema = rs.getString("TABLE_SCHEM");
                    String table  = rs.getString("TABLE_NAME");
                    String type   = rs.getString("TABLE_TYPE");
                    System.out.println(type + " " + schema + "." + table);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

5) Inspect columns for a table

package org.kodejava.jdbc;

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

public class DatabaseMetadata4 {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {
            DatabaseMetaData md = conn.getMetaData();

            String catalog = null;
            String schema = "public";
            String table = "users";

            try (ResultSet rs = md.getColumns(catalog, schema, table, "%")) {
                while (rs.next()) {
                    String col = rs.getString("COLUMN_NAME");
                    int jdbcType = rs.getInt("DATA_TYPE");          // java.sql.Types
                    String dbType = rs.getString("TYPE_NAME");      // vendor type name
                    int size = rs.getInt("COLUMN_SIZE");
                    int nullable = rs.getInt("NULLABLE");           // DatabaseMetaData.columnNullable/NoNulls
                    String def = rs.getString("COLUMN_DEF");

                    System.out.printf("%s %s(%d) nullable=%s default=%s%n",
                            col, dbType, size,
                            (nullable == DatabaseMetaData.columnNullable),
                            def);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

If you need precision/scale for numeric columns, also read:

  • DECIMAL_DIGITS
  • NUM_PREC_RADIX

6) Primary keys, foreign keys, and indexes

Primary key

try (ResultSet rs = md.getPrimaryKeys(null, "public", "users")) {
    while (rs.next()) {
        System.out.println("PK column: " + rs.getString("COLUMN_NAME")
                + " (name=" + rs.getString("PK_NAME") + ")");
    }
}

Foreign keys (imported keys = FKs from this table to others)

try (ResultSet rs = md.getImportedKeys(null, "public", "users")) {
    while (rs.next()) {
        System.out.println("FK " + rs.getString("FK_NAME")
                + ": " + rs.getString("FKCOLUMN_NAME")
                + " -> " + rs.getString("PKTABLE_NAME") + "." + rs.getString("PKCOLUMN_NAME"));
    }
}

Indexes

package org.kodejava.jdbc;

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

public class DatabaseMetadata5 {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {
            DatabaseMetaData md = conn.getMetaData();

            boolean unique = false; // set true to request only unique indexes
            boolean approximate = false;

            try (ResultSet rs = md.getIndexInfo(null, "public", "users", unique, approximate)) {
                while (rs.next()) {
                    String indexName = rs.getString("INDEX_NAME");
                    String column = rs.getString("COLUMN_NAME");
                    boolean nonUnique = rs.getBoolean("NON_UNIQUE");
                    short type = rs.getShort("TYPE"); // tableIndexStatistic / tableIndexClustered / etc.
                    if (indexName != null && column != null) {
                        System.out.println((nonUnique ? "IDX" : "UNIQUE") + " " + indexName + " on " + column + " type=" + type);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

7) “Dump the schema” pattern (tables → columns → keys)

For a basic schema inspection tool, you typically:

  1. getTables(...)
  2. For each table: getColumns(...)
  3. Optionally: getPrimaryKeys(...), getImportedKeys(...), getIndexInfo(...)

Keep it read-only; it’s metadata only.

8) Practical tips / pitfalls

  • Case sensitivity: If your DB stores identifiers uppercase, but you pass lowercase (or vice versa), you may get empty results. Check storesUpperCaseIdentifiers() / storesLowerCaseIdentifiers() and normalize.
  • Use % liberally while exploring: Start with schemaPattern="%" and narrow down once you see what the DB reports.
  • Some drivers are quirky: Not all metadata is perfectly implemented. When something looks missing, verify against the DB’s own system catalogs.
  • Close metadata ResultSets: They’re real JDBC resources. Use try-with-resources as shown.

How do I connect to PostgreSQL using JDBC?

By I Wayan Saryada in JDBC 0 Comment

To connect to a PostgreSQL database using JDBC, you’ll need the PostgreSQL JDBC driver and a properly formatted connection URL.

1. Add the Dependency

Add the following dependency to your pom.xml file:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.4</version>
</dependency>

2. Establish the Connection

In modern JDBC (4.0+), you no longer need to manually call Class.forName(). The DriverManager will automatically find the driver on your classpath.

Here is a standard example using a try-with-resources block to ensure the connection is closed automatically:

package org.kodejava.jdbc;

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

public class PostgresConnectionExample {
    public static void main(String[] args) {
        // URL format: jdbc:postgresql://<host>:<port>/<database>
        String url = "jdbc:postgresql://localhost:5432/your_database";
        String user = "your_username";
        String password = "your_password";

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            if (connection != null) {
                System.out.println("Connected to the PostgreSQL server successfully!");
            }
        } catch (SQLException e) {
            System.err.println("Connection failure: " + e.getMessage());
        }
    }
}

Key Details:

  • JDBC URL: The prefix is always jdbc:postgresql://. The default port for PostgreSQL is 5432.
  • Driver Class: If you are working with older code that requires it, the driver class name is org.postgresql.Driver.