What Are a Table's Optimal Set of Columns That Uniquely Identify a Row or Record? JDBC

To return a table’s optimal set of columns that uniquely identify a row, you can use the DatabaseMetaData.getBestRowIdentifier() method. This method returns the result as a ResultSet object, which is not very useful to the web clients. I provide a Java servlet, GetBestRowIdentifier, which returns a table’s optimal set of columns that uniquely identify a row:

This retrieves a description of a table’s optimal set of columns that uniquely identifies a row. They are ordered by SCOPE. Each column description has the columns shown in Table.

Table : ResultSet Columns for getBestRowIdentifier()

ResultSet Columns for getBestRowIdentifier()

ResultSet Columns for getBestRowIdentifier()

The parameters are

  • catalog: A catalog name; must match the catalog name as it is stored in the database. "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search.
  • schema: A schema name; must match the schema name as it is stored in the database. "" retrieves those without a schema; null means that the schema name should not be used to narrow the search.
  • table: A table name; must match the table name as it is stored in the database.
  • scope: The scope of interest; use same values as SCOPE.
  • nullable: Include columns that are nullable.

To be as complete as possible, I’ve included the table name in XML syntax. This can aid clients in distinguishing the best row identifiers as easily as possible.

The servlet signature is

Some things to keep in mind about the GetBestRowIdentifier servlet are

  • The MySQL database does not understand schema; you have to use catalog.
  • The Oracle database does not understand catalog; you have to use schema.
  • For other databases, you should check their JDBC documentation.

Oracle Database Setup

The Solution

Invoking GetBestRowIdentifier for MySQL

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

Invoking GetBestRowIdentifier for MySQL

Invoking GetBestRowIdentifier for Oracle

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

Invoking GetBestRowIdentifier for Oracle


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

JDBC Topics