JDBC: How to Print All Table Names from a Database

In this tutorial, we will learn how to retrieve and print all table names from a database using JDBC. This can be useful for database schema exploration or debugging purposes. We will use the DatabaseMetaData interface to achieve this. The example will demonstrate connecting to a MySQL database and listing all tables.

Prerequisites

• MySQL database installed and running.
• MySQL JDBC driver (mysql-connector-java) added to the classpath.

Step-by-Step Guide

1. Set Up Dependencies

Ensure you have the MySQL JDBC driver in your classpath. If you are using Maven, add the following dependency to your pom.xml:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.27</version>
</dependency>

2. Establish a Database Connection

First, we need to establish a connection to the MySQL database. We will use the DriverManager class for this purpose.

3. Retrieve Table Names Using DatabaseMetaData

The DatabaseMetaData interface provides methods to get metadata about the database. We will use the getTables method to retrieve the table names.

4. Print the Table Names

Iterate through the result set returned by the getTables method and print each table name.

Example Code

Below is the complete example code demonstrating how to print all table names from a MySQL database.

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

public class ListTablesExample {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String DB_USER = "your_username";
    private static final String DB_PASSWORD = "your_password";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            if (connection != null) {
                System.out.println("Successfully connected to the database.");
                printTableNames(connection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void printTableNames(Connection connection) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        try (ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"})) {
            System.out.println("List of tables:");
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                System.out.println(tableName);
            }
        }
    }
}

Explanation

1. Database Connection:

   • DriverManager.getConnection is used to establish a connection to the database.
   • The connection string (DB_URL), username (DB_USER), and password (DB_PASSWORD) should be replaced with your actual database credentials.

2. Retrieve and Print Table Names:

   • DatabaseMetaData object is obtained from the connection using connection.getMetaData().
   • metaData.getTables is called to get the list of tables. The parameters are:
     • null for the catalog (not used).
     • null for the schema pattern (not used).
     • "%" for the table name pattern (matches all tables).
     • new String[]{"TABLE"} for the types of objects to retrieve (only tables).
   • Iterate through the result set to get the table names and print them.

Output

If you have a database named your_database with tables, running this code will print:

Successfully connected to the database.
List of tables:
table1
table2
...

Conclusion

Using JDBC, you can easily retrieve and print all table names from a database by utilizing the DatabaseMetaData interface. This tutorial covered the necessary steps to establish a connection, retrieve table names, and print them to the console. This approach can be adapted for other types of metadata retrieval as well.