Syntax error How to get the list of all databases using JDBC?

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
Updated on: 2019-07-30T22:30:26+05:30

3K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements