How to get the list of all databases using JDBC?


You can get the list of databases in MySQL using the SHOW DATABASES query.

show databases;

Following JDBC program retrieves the list of databases by executing the show databases query.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ShowDatabasesExample {
   public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a Statement object
      Statement stmt = con.createStatement();
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("Show Databases");
      System.out.println("List of databases: ");
      while(rs.next()) {
         System.out.print(rs.getString(1));
         System.out.println();
      }
   }
}

Output

Connection established......
List of databases:
information_schema
base
details
errors
logging
mydatabase
mydb
mysql
performance_schema
sample_database
sampledb
students
sys
testdb
world

Or, You can use the getCatalogs() method of the DatabaseMetaData interface.

Example

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class ListOfAllDatabases {
   public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Retrieving the meta data object
      DatabaseMetaData metaData = con.getMetaData();
      //Retrieving the list of database names
      ResultSet tables = metaData.getCatalogs();
      while (tables.next()) {
         System.out.println(tables.getString("TABLE_CAT"));
      }
   }
}

Output

Connection established......
List of databases:
information_schema
base
details
errors
logging
mydatabase
mydb
mysql
performance_schema
sample_database
sampledb
students
sys
testdb
world
raja
Published on 16-May-2019 12:10:56
Advertisements