How to List Names of All Databases in Java

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

In this tutorial, we will explore how to list the names of all databases using Java. We'll focus on using JDBC to connect to a MySQL server and retrieve the names of all databases. The same approach can be adopted for other database systems with minor modifications.

Prerequisites

• MySQL database installed.
• MySQL JDBC driver added to your project.
• Basic understanding of Java and JDBC.

Steps

1. Set Up Dependencies
2. Establish a Database Connection
3. Retrieve and List Database Names

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.26</version>
</dependency>

2. Establish a Database Connection

First, we need to establish a connection to the MySQL server using the DriverManager class.

3. Retrieve and List Database Names

We'll use the SHOW DATABASES SQL statement to retrieve the list of all databases and display their names.

Example Code

Below is the complete example code demonstrating how to connect to a MySQL server and list all database names.

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

public class ListDatabasesExample {

    // JDBC URL, username, and password of MySQL server
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/?useSSL=false";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD)) {
            System.out.println("Connected to the database");

            // List all database names
            listDatabaseNames(connection);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void listDatabaseNames(Connection connection) throws SQLException {
        String showDatabasesSQL = "SHOW DATABASES";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(showDatabasesSQL)) {
            System.out.println("Databases:");
            while (rs.next()) {
                String databaseName = rs.getString(1);
                System.out.println(databaseName);
            }
        }
    }
}

Explanation

1. MySQL Connection:

   • DriverManager.getConnection(JDBC_URL, USER, PASSWORD) is used to establish a connection to the MySQL server.
   • The URL jdbc:mysql://localhost:3306/?useSSL=false connects to the MySQL server without specifying a particular database.

2. Listing Database Names:

   • The listDatabaseNames() method executes the SQL query SHOW DATABASES to retrieve all database names.
   • A Statement object is created to execute the query.
   • A ResultSet object is used to iterate through the result set and print each database name.

Output

Running the code will produce output similar to the following:

Connected to the database
Databases:
information_schema
mysql
performance_schema
sys
test

Conclusion

Listing all database names in a MySQL server using Java is straightforward with JDBC. By executing the SHOW DATABASES SQL query, we can easily retrieve and display the names of all databases. This approach can be adapted for other database systems by modifying the JDBC URL, username, password, and SQL query as needed.