How to Get List of Databases in MySQL using Java?

Java MySQL – Get List of Databases

In this tutorial, you will learn how to retrieve a list of databases in MySQL using Java. We’ll cover starting the MySQL server, setting up a connection in Java, writing the Java code to fetch database names, and verifying the output in MySQL Workbench.

Prerequisites

• MySQL installed and configured
• Java Development Kit (JDK) installed
• MySQL Connector/J (JDBC Driver) downloaded
• MySQL Workbench to verify the list of databases

Step 1: Start MySQL Server

Before retrieving database information, ensure the MySQL server is running.

1. Start MySQL Server on Windows

Open the Start menu, search for Services, find MySQL in the list, right-click, and select Start.

2. Start MySQL Server on macOS

Open System Preferences, click on MySQL, and select Start MySQL Server.

3. Start MySQL Server on Linux

Open a terminal and run:

sudo systemctl start mysql

Step 2: Set Up MySQL Connection in Java

To connect to MySQL from Java, you need the MySQL Connector/J library. Add this library to your Java project to establish the connection.

1. Download MySQL Connector/J

Go to the official MySQL Connector/J download page, download the connector, and add the JAR file to your Java project’s classpath.

Click on the Download button.

You may click No thanks, just start my download.
In the downloaded package, there should be a jar file.

2. Add the Connector to Your Java Project

For the sake of convenience, let us copy the jar file to libs folder in the Java Project.

Right-click on your Java project and go to Properties.

Select Java Build Path > Libraries.

Add the MySQL Connector/J JAR file you downloaded.

Click OK.

Click Apply and Close.

Step 3: Write Java Code to Get List of Databases

Now, we’ll write Java code to connect to MySQL and retrieve a list of databases.

Java Program to List Databases

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

public class ListDatabasesExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/";
        String user = "root";
        String password = "your_password";

        try {
            // Establish a connection to MySQL
            Connection connection = DriverManager.getConnection(jdbcUrl, user, password);

            // Create a statement
            Statement statement = connection.createStatement();

            // SQL command to get list of databases
            String sql = "SHOW DATABASES";

            // Execute the SQL command and get results
            ResultSet resultSet = statement.executeQuery(sql);

            System.out.println("List of databases:");

            // Print each database name
            while (resultSet.next()) {
                String databaseName = resultSet.getString(1);
                System.out.println(databaseName);
            }

            // Close resources
            resultSet.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Explanation of the Code

• jdbcUrl: The URL to connect to MySQL. Replace localhost:3306 with your MySQL server address if different.
• user and password: Your MySQL credentials.
• Statement statement: Used to execute the SQL command SHOW DATABASES, which lists all databases on the server.
• ResultSet resultSet: Holds the result of the executed query, which contains database names.

Step 4: Run the Java Program

Compile and run the Java program in your IDE or from the command line:

Command Line

javac ListDatabasesExample.java
java ListDatabasesExample

If the program executes successfully, you’ll see a list of databases in the console:

Step 5: Verify in MySQL Workbench

After running the Java program, you can verify the list of databases in MySQL Workbench:

• Open MySQL Workbench and connect to your MySQL server.
• In the “Schemas” tab, you’ll see the list of databases.

Troubleshooting Common Issues

Here are some common issues you may encounter and solutions:

• SQLException: Access denied: Ensure your MySQL username and password are correct, and the user has permissions to view databases.
• ClassNotFoundException: com.mysql.cj.jdbc.Driver: Ensure the MySQL Connector/J JAR file is in your project’s classpath.
• SQLException: No suitable driver found: Check that the JDBC URL format is jdbc:mysql://localhost:3306/.

Conclusion

Using Java and MySQL, you can easily retrieve a list of databases on the MySQL server. By setting up the MySQL server, configuring the JDBC connection, and running a simple Java program, you can dynamically access database information for your applications.