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



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

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

JDBC Topics