JDBC – How to list databases in MySQL Server using Java

MySQL is used to manage relational databases and it can contain multiple databases to serve different applications.

In this tutorial, we shall learn to list databases in MySQL Server using Java with the help of JDBC classes.

Following is step by step process to list available databases in mysql :

1. Establish connection to MySQL Server Create a new mysql JDBC Driver instance and make a connection to the MySQL Server.
2. Execute query to list databases “SHOW DATABASES;” is the SQL query that fetches the databases’ list. Execute this query with the help of Statement class.
3. ResultSet has the list of databases Execution in the above step returns a ResultSet containing the names of databases.
4. Close ResultSet, Statement and Connection Once the desired operation with MySQL Server is completed, close the jdbc resources that we have created in the program like ResultSet, Statement and Connection in the order that we first close the resource that is created last of all, to ensure that any dependency is not violated.

Example 1 – List databases in MySQL Server using Java

The complete program to is given below. Replace username and password with credentials you have used while creating a user in MySQL. Also, if the MySQL Server is running on another machine, replace the localhost IP and port with appropriate MySQL Server IP and port.

MySQLDatabaseExample.java

import java.sql.*;
import java.util.Properties;

/**
 * Program to list databases in MySQL Server using Java
 */
public class MySQLDatabaseExample {

    static Connection conn = null;
    static String username = "username"; // replace with your MySQL client username
    static String password = "password"; // replace with your MySQL client password

    public static void main(String[] args){
        // make a connection to MySQL Server
        getConnection();
        // execute the query via connection object
        executeMySQLQuery();
    }

    public static void executeMySQLQuery(){
        Statement stmt = null;
        ResultSet resultset = null;

        try {
            stmt = conn.createStatement();
            resultset = stmt.executeQuery("SHOW DATABASES;");

            if (stmt.execute("SHOW DATABASES;")) {
                resultset = stmt.getResultSet();
            }

            while (resultset.next()) {
                System.out.println(resultset.getString("Database"));
            }
        }
        catch (SQLException ex){
            // handle any errors
            ex.printStackTrace();
        }
        finally {
            // release resources
            if (resultset != null) {
                try {
                    resultset.close();
                } catch (SQLException sqlEx) { }
                resultset = null;
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) { }
                stmt = null;
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlEx) { }
                conn = null;
            }
        }
    }

    /**
     * This method makes a connection to MySQL Server
     * In this example, MySQL Server is running in the local host (so 127.0.0.1)
     *  at the standard port 3306
     */
    public static void getConnection(){
        Properties connectionProps = new Properties();
        connectionProps.put("user", username);
        connectionProps.put("password", password);
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(
                    "jdbc:" + "mysql" + "://" +
                            "127.0.0.1" +
                            ":" + "3306" + "/"+
                            "",
                    connectionProps);
        } catch (SQLException ex) {
            // handle any errors
            ex.printStackTrace();
        } catch (Exception ex) {
            // handle any errors
            ex.printStackTrace();
        }
    }
}

Output

information_schema
db1
db2
db3
mysql
performance_schema
sys

List of Databases in MySQL Server.

Conclusion

In this Java Tutorial, we learned how to list databases in MySQL using Java.