How Do You Get a Table's Primary Key Columns? JDBC

The primary key (PK) of a relational table uniquely identifies each row or record in the table. The PK can be comprised of one or more non-null columns. For database applications (including web-based applications), you need to know the PK columns before inserting new records (to make sure that the PK columns cannot be NULL).

The DatabaseMetaData.getPrimaryKeys() method retrieves a table’s PK columns and its signature is

ResultSet getPrimaryKeys(String catalog,
String schema,
String table)
throws SQLException

This method retrieves a description of the given table’s primary key columns. The result is returned as a ResultSet object and they are ordered by COLUMN_NAME. If a database access error occurs, then this method throws SQLException. Each primary key column description has the columns shown in Table.

Each primary key column description has the columns

Parameters

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

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

GetPKColumns?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 primary key columns information as XML for a MySQL database, you may invoke it as

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

The other possibilities are

GetPKColumns?vendor=mysql&table=employees&format=html
GetPKColumns?vendor=oracle&table=employees&format=xml
GetPKColumns?vendor=oracle&table=employees&format=html

MySQL Database Setup

mysql> use octopus;
Database changed
mysql> create table mytable (
id varchar(10) not null,
name varchar(20) not null,
age integer,
primary key (id, name)
);
Query OK, 0 rows affected (0.08 sec)
mysql> desc mytable;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(10) | NO | PRI | | |
| name | varchar(20) | NO | PRI | | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

Oracle Database Setup

SQL> create table mytable(
id varchar2(10) not null,
name varchar(20) not null,
age number,
primary key (id, name)
);
Table created.
SQL> desc mytable;
Name Null? Type
----------------------------------------- -------- ------------
ID NOT NULL VARCHAR2(10)
NAME NOT NULL VARCHAR2(20)
AGE NUMBER

The Solution

import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.List;
import java.util.ArrayList;
import java.util.StringTokenizer;
import java.io.PrintWriter;
import java.io.IOException;
import jcb.util.DatabaseUtil;
import jcb.db.VeryBasicConnectionManager;
public class GetPKColumns extends HttpServlet {
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
ResultSet primaryKeys = 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);
primaryKeys = getPrimaryKeys(conn, table);
if (outputFormat.equals("xml")) {
printXML(response, primaryKeys);
}
else {
printHTML(response, primaryKeys);
}
}
catch(Exception e) {
printError(response, e.getMessage());
}
finally {
DatabaseUtil.close(primaryKeys);
DatabaseUtil.close(conn);
}
} // end doGet
private static void printHTML(HttpServletResponse response,
ResultSet primaryKeys)
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>Key Sequence</TH>");
buffer.append("<TH>PK Name</TH></TR>");
while (primaryKeys.next()) {
buffer.append("<TR><TD>");
buffer.append(primaryKeys.getString("TABLE_CAT"));
buffer.append("</TD><TD>");
buffer.append(primaryKeys.getString("TABLE_SCHEM"));
buffer.append("</TD><TD>");
buffer.append(primaryKeys.getString("TABLE_NAME"));
buffer.append("</TD><TD>");
buffer.append(primaryKeys.getString("COLUMN_NAME"));
buffer.append("</TD><TD>");
buffer.append(primaryKeys.getShort("KEY_SEQ"));
buffer.append("</TD><TD>");
buffer.append(primaryKeys.getString("PK_NAME"));
buffer.append("</TD></TR>");
}
buffer.append("</table></body></html>");
out.println(buffer.toString());
}
private static void printXML(HttpServletResponse response,
ResultSet primaryKeys)
throws Exception {
response.setContentType("text/xml");
PrintWriter out = response.getWriter();
StringBuilder buffer = new StringBuilder();
buffer.append("<?xml version="1.0" encoding="ISO-8859-1"?>");
buffer.append("<primaryKeys>");
while (primaryKeys.next()) {
buffer.append("<pkColumn><catalog>");
buffer.append(primaryKeys.getString("TABLE_CAT"));
buffer.append("</catalog><schema>");
buffer.append(primaryKeys.getString("TABLE_SCHEM"));
buffer.append("</schema><tableName>");
buffer.append(primaryKeys.getString("TABLE_NAME"));
buffer.append("</tableName><columnName>");
buffer.append(primaryKeys.getString("COLUMN_NAME"));
buffer.append("</columnName><keySEQ>");
buffer.append(primaryKeys.getShort("KEY_SEQ"));
buffer.append("</keySEQ><pkName>");
buffer.append(primaryKeys.getString("PK_NAME"));
buffer.append("</pkName></pkColumn>");
}
buffer.append("</primaryKeys>");
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) {
}
}
/**
* Retrieves a description of the given table's primary key columns.
* @param conn the Connection object
* @param tableName name of a table in the database.
* @return the list of PK columns as a ResultSet object
* @exception Failed to get the Primary Keys for a given table.
*/
public static ResultSet getPrimaryKeys(Connection conn,
String tableName)
throws Exception {
if ((tableName == null) || (tableName.length() == 0)) {
return null;
}
DatabaseMetaData meta = conn.getMetaData();
if (meta == null) {
return null;
}
//
// The Oracle database stores its table names as
// uppercase; if you pass a table name in lowercase
// characters, it will not work. MySQL database does
// not care if the table name is uppercase/lowercase.
//
return meta.getPrimaryKeys(null, null, tableName.toUpperCase());
}
}

Invoking GetPKColumns for MySQL

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

Invoking GetPKColumns for MySQL

Invoking GetPKColumns for Oracle

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

Invoking Get PK Columns for Oracle


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

JDBC Topics