How Do You Get Database Schemas? JDBC

Oracle treats “schema” as a database name, while MySQL treats “catalog” as a database name. So, in order to get the names of databases from Oracle, you must use getSchemas(); to get the names of databases from MySQL, you must use getCatalogs(). If you use getCatalogs() for an Oracle database, or getSchemas() for MySQL, it returns nothing (as null objects). In the JDBC API, getSchemas() claims that it returns a set of two columns (table schema and table catalog), but in reality it just returns table schema, as a first column of the result set. Once again, this proves at least two points:

  • You have to test your code against different databases; that is, databases can have different semantics by using the same JDBC API.
  • When you define connections, make sure that the vendor parameter is defined. By knowing who the database vendor is, you can invoke the correct methods.

MySQL Database Setup

$ mysql -u root -proot Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 36 to server version: 5.0.12-beta-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

Oracle Database Setup

In Oracle, the DBA_USERS table describes all users (in Oracle, “user” is semantically equivalent to a “schema”) of the database. Partial output is shown for some SQL queries.

The Solution

Invoking GetSchemas for MySQL

Figure : shows how to run the solution for the MySQL database.

Invoking GetSchemas for MySQL

Invoking GetSchemas for Oracle

Figure :shows how to run the solution for the Oracle database.

Invoking GetSchemas for Oracle

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

JDBC Topics