how to get list of Databases "Schema" names of MySql using java JDBC ?
0
Add a comment
|
4 Answers
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") );
}
3 Comments
bw_üezi
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...
slowhand
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...
JavaTec
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
- Either use
SHOW DATABASESto see if it is inside, - Check the INFORMATION_SCHEMA,
- or just do
USE DATABASE;and catch the error.
Comments
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);
}
Comments
DatabaseMetaData dbmd = con.getMetaData();
ResultSet ctlgs = dbmd.getCatalogs();
while(ctlgs.next())
{
System.out.println("ctlgs = "+ctlgs.getString(1));
}
