How Do You Get a Table's Columns? JDBC

A relational table has a finite set of columns (column names must be unique and each might have a different data type). In GUI database applications, in order to manipulate a relational table, you need to understand column names and types. Also, for a web-based application, you need to know the primary key (PK) columns before inserting new records (to make sure that the PK columns cannot be NULL).

The DatabaseMetaData.getColumns() method retrieves a description of table columns available in the specified catalog. The method’s signature is

Only column descriptions matching the catalog, schema, table, and column name criteria are returned. They are ordered by TABLE_SCHEM, TABLE_NAME, and ORDINAL_POSITION. Each column description has the columns shown in Table.

Table : Column Descriptions

The parameters are

• catalog: A catalog name; it 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.
• schemaPattern: A schema name pattern; it 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.
• tableNamePattern: A table name pattern; it must match the table name as it is stored in the database.
• columnNamePattern: A column name pattern; it must match the column name as it is stored in the database.

As you can see, this method returns its result as a ResultSet object (each row is a detailed column description), which is not very useful for web-based applications. Next I present a Java servlet (GetColumns), which will be able to get a table’s columns (as well as other related metadata information) as HTML or XML. Here is the signature of the servlet:

You may add other parameters to suit your database application. Here, I use vendor to act as a database selector. So, to get the employee table’s columns information as XML for a MySQL database, you may invoke it as

The other possibilities are

MySQL Database Setup

Oracle Database Setup

The Solution

Invoking GetColumns for MySQL

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

Invoking GetColumns for Oracle

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