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:

GetColumns?vendor=<vendor-name>&table=<table-name>&format=<HTML-or-XML>

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

GetColumns?vendor=mysql&table=employees&format=xml

The other possibilities are

MySQL Database Setup

Oracle Database Setup

The Solution

import java.sql.*;
 import javax.servlet.*;
 import javax.servlet.http.*;
 import java.io.PrintWriter;
 import java.io.IOException;
 import jcb.util.DatabaseUtil;
 import jcb.db.VeryBasicConnectionManager;
 public class GetColumns extends HttpServlet {
 public void doGet(HttpServletRequest request,
 HttpServletResponse response)
 throws ServletException, IOException {
 ResultSet columns = null;
 Connection conn = null;
  try {
 String dbVendor = request.getParameter("vendor").trim();
 String table = request.getParameter("table").trim();
 String outputFormat = request.getParameter("format").trim();
 conn = VeryBasicConnectionManager.getConnection(dbVendor);
 columns = getColumns(conn, table);
 if (outputFormat.equals("xml")) {
 printXML(response, columns);
 }
 else {
 printHTML(response, columns);
 }
 }
 catch(Exception e) {
 printError(response, e.getMessage());
 }
 finally {
 DatabaseUtil.close(columns);
 DatabaseUtil.close(conn);
 }
 } // end doGet
 private static void printHTML(HttpServletResponse response,
 ResultSet columns)
 throws Exception {
 response.setContentType("text/html");
 PrintWriter out = response.getWriter();
 StringBuilder buffer = new StringBuilder();
 buffer.append
 ("<html><body><table border=1 cellspacing=0 cellpadding=0>");
 buffer.append("<TR><TH>Catalog</TH>");
 buffer.append("<TH>Schema</TH>");
 buffer.append("<TH>Table Name</TH>");
 buffer.append("<TH>Column Name</TH>");
 buffer.append("<TH>Data Type</TH>");
 buffer.append("<TH>Type Name</TH>");
 buffer.append("<TH>Column Size</TH>");
 buffer.append("<TH>Is Nullable?</TH>");
 buffer.append("<TH>Is Nullable?</TH>");
 buffer.append("<TH>Ordinal Position</TH></TR>");
 while (columns.next()) {
 buffer.append("<TR><TD>");
 buffer.append(columns.getString("TABLE_CAT"));
 buffer.append("</TD><TD>");
 buffer.append(columns.getString("TABLE_SCHEM"));
 buffer.append("</TD><TD>");
 buffer.append(columns.getString("TABLE_NAME"));
  buffer.append("</TD><TD>");
 buffer.append(columns.getString("COLUMN_NAME"));
 buffer.append("</TD><TD>");
 buffer.append(columns.getShort("DATA_TYPE"));
 buffer.append("</TD><TD>");
 buffer.append(columns.getString("TYPE_NAME"));
 buffer.append("</TD><TD>");
 buffer.append(columns.getString("COLUMN_SIZE"));
 buffer.append("</TD><TD>");
 int nullable = columns.getInt("NULLABLE");
 if (nullable == DatabaseMetaData.columnNullable) {
 buffer.append("true");
 }
 else if (nullable == DatabaseMetaData.columnNoNulls) {
 buffer.append("false");
 }
 else {
 buffer.append("unknown");
 }
 buffer.append("</TD><TD>");
 buffer.append(columns.getString("IS_NULLABLE"));
 buffer.append("</TD><TD>");
 buffer.append(columns.getString("ORDINAL_POSITION"));
 buffer.append("</TD></TR>");
 }
 buffer.append("</table></body></html>");
 out.println(buffer.toString());
 }
 private static void printXML(HttpServletResponse response,
 ResultSet columns)
 throws Exception {
 response.setContentType("text/xml");
 PrintWriter out = response.getWriter();
 StringBuilder buffer = new StringBuilder();
 buffer.append("<?xml version="1.0"?>");
 buffer.append("<columns>");
 while (columns.next()) {
 buffer.append("<column><catalog>");
 buffer.append(columns.getString("TABLE_CAT"));
 buffer.append("</catalog><schema>");
 buffer.append(columns.getString("TABLE_SCHEM"));
 buffer.append("</schema><tableName>");
 buffer.append(columns.getString("TABLE_NAME"));
 buffer.append("</tableName><columnName>");
 buffer.append(columns.getString("COLUMN_NAME"));
  buffer.append("</columnName><dataType>");
 buffer.append(columns.getShort("DATA_TYPE"));
 buffer.append("</dataType><typeName>");
 buffer.append(columns.getString("TYPE_NAME"));
 buffer.append("</typeName><columnSize>");
 buffer.append(columns.getString("COLUMN_SIZE"));
 buffer.append("</columnSize><nullable>");
 int nullable = columns.getInt("NULLABLE");
 if (nullable == DatabaseMetaData.columnNullable) {
 buffer.append("true");
 }
 else if (nullable == DatabaseMetaData.columnNoNulls) {
 buffer.append("false");
 }
 else {
 buffer.append("unknown");
 }
 buffer.append("</nullable><isNullable>");
 buffer.append(columns.getString("IS_NULLABLE"));
 buffer.append("</isNullable><ordinalPosition>");
 buffer.append(columns.getString("ORDINAL_POSITION"));
 buffer.append("</ordinalPosition></column>");
 }
 buffer.append("</columns>");
 out.println(buffer.toString());
 }
 private static void printError(HttpServletResponse response,
 String message) {
 try {
 PrintWriter out = response.getWriter();
 StringBuffer buffer = new StringBuffer();
 buffer.append("<html><body>");
 buffer.append(message);
 buffer.append("</body></html>");
 out.println(buffer);
 }
 catch(Exception ignore) {
 }
 }

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.

« Previous Topics
How Do You Get A Table's Primary Key Columns?
How Do You Get A List Of Sql's Keywords?
How Do You Get A List Of Tables And Views For A Database?

Next Topics »
How Do You Get A View's Columns?
How Do You Get Stored Procedure Names?
How Do You Get The Signature Of A Stored Procedure?

JDBC Related Tutorials
Core Java Tutorial	JSP Tutorial
Java Servlets Tutorial	EJB(Enterprise JavaBeans) Tutorial
Java Bean Utils Tutorial	AWT (Abstract Window Toolkit) Tutorial

JDBC Related Interview Questions
Core Java Interview Questions	JSP Interview Questions
Java Servlets Interview Questions	EJB(Enterprise JavaBeans) Interview Questions
JSTL(JSP Standard Tag Library) Interview Questions	JNDI (Java Naming and Directory Interface) Interview Questions
Java Bean Utils Interview Questions	AWT (Abstract Window Toolkit) Interview Questions

JDBC Related Practice Tests
Core Java Practice Tests	JSP Practice Tests
Java Servlets Practice Tests	EJB(Enterprise JavaBeans) Practice Tests
JSTL(JSP Standard Tag Library) Practice Tests

How Do You Get a Table's Columns? JDBC

JDBC Related Tutorials

JDBC Related Interview Questions

JDBC Related Practice Tests

Skills by Location

Jobs By Companies

State Govt Jobs

Bank Jobs

Defence Jobs

PSC

SSC Jobs

Railway Jobs

Latest walkins

Walkins by Skill

Walkins by location

Walkins by Company

POPULAR COURSES

Resume Writing

Resume Samples

Sample Cover Letters

Job Letter Samples

Recruiter Reach