10

how to get list of Databases "Schema" names of MySql using java JDBC ?

Ismail Marmoush
  • 13,140
  • 25
  • 80
  • 114

4 Answers4

20

The getSchemas() method of the DatabaseMetaData is the obvious but with MySQL you have to use getCatalogs()

http://download.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getSchemas() http://download.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getCatalogs()

Example:

Class.forName("com.mysql.jdbc.Driver");

// change user and password as you need it
Connection con = DriverManager.getConnection (connectionURL, "user", "password");

ResultSet rs = con.getMetaData().getCatalogs();

while (rs.next()) {
    System.out.println("TABLE_CAT = " + rs.getString("TABLE_CAT") );
}
bw_üezi
  • 4,483
  • 4
  • 23
  • 41
  • 1
    JDBC allows to access Schemas and Catalogs but it's meaning is a bit DBMS specific and some provide both while others just one and not always with the method you'd first suspect... – bw_üezi Apr 15 '11 at 16:26
  • If the MySQL server is running on Windows, this code prints the database names in lowercase. This may or may not be what you want... – slowhand Dec 14 '13 at 23:59
  • on the side, if someone is looking for fetching the schema name from Spring JdbcTemplate (instead of plain JDBC), it would be jdbcTemplate.getDataSource().getConnection().getCatalog() for mySql – JavaTec Nov 21 '17 at 00:46
4
  • Either use SHOW DATABASES to see if it is inside,
  • Check the INFORMATION_SCHEMA,
  • or just do USE DATABASE; and catch the error.
Konerak
  • 39,272
  • 12
  • 98
  • 118
3
DatabaseMetaData meta = conn.getMetaData();
ResultSet schemas = meta.getSchemas();
while (schemas.next()) {
  String tableSchema = schemas.getString(1);    // "TABLE_SCHEM"
  String tableCatalog = schemas.getString(2); //"TABLE_CATALOG"
  System.out.println("tableSchema "+tableSchema);
}
Matthieu
  • 2,736
  • 4
  • 57
  • 87
Adi Sembiring
  • 5,798
  • 12
  • 58
  • 70
0
DatabaseMetaData dbmd = con.getMetaData();
ResultSet ctlgs = dbmd.getCatalogs();
while(ctlgs.next())
{
System.out.println("ctlgs  =  "+ctlgs.getString(1));
}
Tanmaya Sahu
  • 1
  • 1