24.7 Discovering Database and ResultSet Metadata
The JDBC API allows Java programs to interact with many different types of databases. Obviously, different database providers have different capabilities and default behaviors, and may or may not support certain SQL features. It is possible to obtain such information using the java.sql.DatabaseMetaData object. This object is obtained from the JDBC Connection and can be used to investigate various capabilities and many other properties, such as support for type, concurrency, and holdability of a result set.
Following selected methods from the DatabaseMetaData API can be used to examine the capabilities of a database:
String getDatabaseProductName()
Retrieves the name of this database product.
String getDatabaseProductVersion()
Retrieves the version number of this database product.
String getSQLKeywords()
Retrieves a comma-separated list of all SQL keywords supported by this database.
boolean supportsResultSetType(int resultSettype)
Retrieves whether this database supports the given result set type.
boolean supportsResultSetHoldability(int holdability)
Retrieves whether this database supports the given result set holdability.
boolean supportsResultSetConcurrency(int resultSettype, int concurrency)
Retrieves whether this database supports the given combination result set type/concurrency type.
In addition to the database metadata, a ResultSetMetaData object can be used to discover information about the structure of a given result set.
Following selected methods from the ResultSetMetaData API can be used to examine the structure of the result set:
int getColumnCount()
Returns the number of columns in this result set.
String getColumnName(int column)
Returns the name of the designated columns in this result set.
int getColumnType(int column)
Returns the type of the designated columns in this result set. The int value returned corresponds to the SQL type designated by constants in java.sql.Types. The values 12 and 4 represent the SQL types VARCHAR and INTEGER, respectively.
Example 24.7 prints various metadata about the database and the result set.
Example 24.7 Discovering Metadata for the Database and ResultSet
package dbDemo;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
public class DBMetadata {
public static void main(String[] args) {
final String jdbcURL = “jdbc:derby:musicDB”;
try (var connection = DriverManager.getConnection(jdbcURL)) {
// Print various information about the database:
DatabaseMetaData dbMetaData
= connection.getMetaData(); // Obtain DatabaseMetaData.
String dbName = dbMetaData.getDatabaseProductName();
String dbVersion = dbMetaData.getDatabaseProductVersion();
String sqlKeywords = dbMetaData.getSQLKeywords();
boolean forwardOnly = dbMetaData.supportsResultSetType(
ResultSet.TYPE_FORWARD_ONLY);
boolean cursorOpen = dbMetaData.supportsResultSetHoldability(
ResultSet.HOLD_CURSORS_OVER_COMMIT);
boolean forwardUpdate = dbMetaData.supportsResultSetConcurrency(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
System.out.println(“Various info about the database:”);
System.out.println(“Database name: ” + dbName);
System.out.println(“Version: ” + dbVersion);
System.out.println(“SQL keywords: ” + sqlKeywords);
System.out.println(“TYPE_FORWARD_ONLY: ” + forwardOnly);
System.out.println(“HOLD_CURSORS_OVER_COMMIT: ” + cursorOpen);
System.out.println(“TYPE_FORWARD_ONLY/CONCUR_UPDATABLE: “
+ forwardUpdate);
// Create a ResultSet and print its structure:
String sql = “select * from compositions where duration > ?”;
try (var pStatement = connection.prepareStatement(sql);) {
pStatement.setInt(1, 100);
var resultSet = pStatement.executeQuery();
try (resultSet){
System.out.println(“Structure of ResultSet:”);
ResultSetMetaData rsMetaData
= resultSet.getMetaData(); // Obtain ResultSetMetadata.
int columnCount = rsMetaData.getColumnCount();
System.out.println(“Number of columns:” + columnCount);
for (int i = 1; i <= columnCount; i++){
String name = rsMetaData.getColumnName(i);
int type = rsMetaData.getColumnType(i); // Value of Types constant.
System.out.println(name + “: ” + type);
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Probable output from the program:
Various info about the database:
Database name: Apache Derby
Version: 10.15.2.0 – (1873585)
SQL keywords: ALIAS,BIGINT,BOOLEAN,CALL,CLASS,COPY,DB2J_DEBUG,EXECUTE,… (EDITED)
TYPE_FORWARD_ONLY: true
HOLD_CURSORS_OVER_COMMIT: true
TYPE_FORWARD_ONLY/CONCUR_UPDATABLE: true
Structure of ResultSet:
Number of columns:3
ISRC: 12
TITLE: 12
DURATION: 4
