No discussion of JDBC would be complete without a discussion of metadata–which is to JDBC what reflection is to Java objects.

ResultSetMetaData

A compiled PreparedStatement object, and an active ResultSet, share the getMetaData() method that returns a ResultSetMetaData instance.

Here’s a sampling of the methods you can use on ResultSetMetaData to get information about a SELECT query.

MethodFunction
int getColumnCount()Returns the number of columns returned by the query.
String getColumnClassName(int column)Returns the fully qualified name of the Java class that would be created if getObject() were used on that column.
In this call, and all others where column is passed as an argument, 1 is the first column returned by the query, 2 is the second, etc.
int getColumnDisplaySize(int column)Returns the column’s normal maximum display size in characters.
String getColumnLabel(int column)Gets the columns suggested title, for use in printouts and displays.
String getColumnName(int column)Gets the designated column’s name.
int getColumnType(int column)
String getColumnTypeName(int column)		Returns the column’s SQL type, by code number and name, respectively.
Class java.sql.Types contains named constants corresponding to each SQL type.
String getSchemaName(int column)
String getTableName(int column)		Returns the name of the schema or table from which the column comes, if applicable, and “” if not applicable.
int isNullable(int column)Returns an indicator of whether the column is nullable: ResultSetMetadata.columnNullable if so, ResultSetMetaData.columnNoNulls if not, ResultSetMetaData.columnNullableUnknown if unknown.
boolean isDefinitelyWritable(int column)Returns true if attempting to write the column will definitely succeed.
boolean isReadOnly(int column)Returns true if the column is definitely not writable.
boolean isSearchable(int column)Returns true if the column can be used in a WHERE clause.
boolean isWritable(int column)Returns true if it is possible for a write on the column to succeed.
Selected methods of ResultSetMetaData

ParameterMetaData

A PreparedStatement also has the getParameterMetaData() method, which returns information about the parameters used by the statement.

MethodFunction
int getParameterCount()Returns the number of parameters.
String getParameterClassName(int param)Returns the fully-qualified name of the Java class whose instances should be passed to setObject() for the parameter.
int getParameterType(int param)
String getParameterTypeName(int param)		Returns the SQL type code or database specific type name of the parameter.
Class java.sql.Types contains named constants corresponding to each SQL type.
int isNullable(int param)Returns an indicator of whether the parameter is nullable: ParameterMetadata.columnNullable if so, ParameterMetaData.columnNoNulls if not, ParameterMetaData.columnNullableUnknown if unknown.
Selected methods of ParameterMetaData

DatabaseMetaData

Finally, you can use a Connection’s getMetaData() method to retrieve a DatabaseMetaData instance: an object that can tell you all about the database you’ve logged into. Here are some of DatabaseMetaData’s methods.

MethodFunction
ResultSet getCatalogs()Returns a ResultSet for all the catalogs in the database:
1. TABLE_CAT String => catalog name
ResultSet getSchemas(String catalog, String schemaPattern)Returns a ResultSet for all the schemas in the named catalog (which may be null) having the specified pattern:
1. TABLE_SCHEM String => schema name
2. TABLE_CATALOG String => catalog name (may be null)
ResultSet getTables(String catalog,
String schemaPattern, String tableNamePattern,
String[] types)		Returns a ResultSet for all the tables in the named catalog, for all schemas fitting the schemaPattern, with table names fitting tableNamePattern, and having one of the specified types.
If types is null, all types are returned.

Available types are returned by the getTableTypes() method.

1. TABLE_CAT String => table catalog (may be null)
2. TABLE_SCHEM String => table schema (may be null)
3. TABLE_NAME String => table name
4. TABLE_TYPE String => table type. Typical types are “TABLE”, “VIEW”, “SYSTEM TABLE”, “GLOBAL TEMPORARY”, “LOCAL TEMPORARY”, “ALIAS”, “SYNONYM”.
5. REMARKS String => explanatory comment on the table
6. TYPE_CAT String => the types catalog (may be null)
7. TYPE_SCHEM String => the types schema (may be null)
8. TYPE_NAME String => type name (may be null)
9. SELF_REFERENCING_COL_NAME String => name of the designated “identifier” column of a typed table (may be null)
10. REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are “SYSTEM”, “USER”, “DERIVED”. (may be null)
ResultSet getColumns(String catalog,
String schemaPattern, String tableNamePattern,
String columnNamePattern)		Returns a ResultSet describing the columns in tables specified by named catalog, with schema and table names fitting their respective patterns.

1. TABLE_CAT String => table catalog (may be null)
2. TABLE_SCHEM String => table schema (may be null)
3. TABLE_NAME String => table name
4. COLUMN_NAME String => column name
5. DATA_TYPE int => SQL type from java.sql.Types
6. TYPE_NAME String => Data source dependent type name, for a UDT the type name is fully qualified
7. COLUMN_SIZE int => column size.
8. BUFFER_LENGTH is not used.
9. DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable.
10. NUM_PREC_RADIX int => Radix (typically either 10 or 2)
NULLABLE int => is NULL allowed.
columnNoNulls – might not allow NULL values
columnNullable – definitely allows NULL values
columnNullableUnknown – nullability unknown
11. REMARKS String => comment describing column (may be null)
12. COLUMN_DEF String => default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be null)
12. SQL_DATA_TYPE int => unused
13. SQL_DATETIME_SUB int => unused
14. CHAR_OCTET_LENGTH int => for char types the maximum number of bytes in the column
15. ORDINAL_POSITION int => index of column in table (starting at 1)
16. IS_NULLABLE String => ISO rules are used to determine the nullability for a column.
YES — if the column can include NULLs
NO — if the column cannot include NULLs
empty string — if the nullability for the column is unknown
17. SCOPE_CATALOG String => catalog of table that is the scope of a reference attribute (null if DATA_TYPE isn’t REF)
18. SCOPE_SCHEMA String => schema of table that is the scope of a reference attribute (null if the DATA_TYPE isn’t REF)
19. SCOPE_TABLE String => table name that this the scope of a reference attribute (null if the DATA_TYPE isn’t REF)
20. SOURCE_DATA_TYPE short => source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types (null if DATA_TYPE isn’t DISTINCT or user-generated REF)
21. IS_AUTOINCREMENT String => Indicates whether this column is auto incremented
YES — if the column is auto incremented
NO — if the column is not auto incremented
empty string — if it cannot be determined whether the column is auto incremented
22. IS_GENERATEDCOLUMN String => Indicates whether this is a generated column
YES — if this a generated column
NO — if this not a generated column
empty string — if it cannot be determined whether this is a generated column
Selected methods of DatabaseMetaData

The name patterns in the method calls above are composed of letters and digits and wildcard characters: “_” signifies any single character, and “%” signifies 0 or more characters of any value.

Exercise

Now that you’ve seen how to get metadata from the database, add a new class to the Lesson-22-Example-01 project that connects to the database you’ve been using and displays on the console the table name, column name, column type name, and column size for each column in each table where the catalog name is null and the schema name is “public”. Project Lesson-22-Exercise-01 contains a possible solution.

Remember that you’ll need to add the folder containing properties and the PostgeSQL JAR to your execution classpath.

What You Need to Know

  • JDBC provides access to relational database systems.
  • JDBC provides interfaces for establish connections, committing and rolling back database changes, static SQL queries, and prepared statements (parameterized SQL queries).

Now let’s talk about Javadoc