Get Primary Key Column from ResultSet Java

Question

I am trying to get Primary Key Column(s) of table from ResultSet. Following are the Steps

I followed:

1. SQL QUERY: Select id,subid,email,empname from Employee
2. Executed this from java.sql.Statement and got the Results in ResultSet.

Here is the Interesting Part.

3. ResultSetMetadata rsmd = rs.getMetadata();

Now, if i watch this variable "rsmd" , it is showing primary key flags for relevant column names but I am not able to access it or get it into any variable.

I need help regarding the same.

NOTE: I do not want to use DatabaseMetadata and its getPrimaryKeys() function as it will take an additonal hit into External Database. Also, the ResultSetMetadata object is already having the primary key Information which i just need to fetch.

Answer 1

Some thoughts about your question and also a solution (hopefully helpful):

It didn't occur to me in my life time experience working with result sets having primary key information in the results set meta data.

It seems to me even strange because in principal a result set is not limited to show rows organized in columns of only one table and it is not forced to show all columns of one table and even the columns might be no table columns.

For example we might issue a query like

select X.a, X.b, Y.n, Y.m, 'bla'||X.c||'blub'||Y.l from X, Y;

In this case we may have or may not have primary columns from one or from both tables or from none of them.

As you already know the standard ResultSetMetaData-Interface doesn't provide primary key information access. What you see in the debugger is an instance of a class which implements that interface.

There are several ways to deal with your task:

1. (Not my preferred way)
   Cast to the specific implementing ResultSetMetaData-class and access primary key information if its available. But be aware that not every
   ResultSetMetaData implementation provides this information.

2. (A bit more architectural approach, also not proposed from my side, but needed
   if we deal with an incomplete JDBC-driver)
   Take advantage of the system tables of the different databases you use but hiding it of course in an abstraction, for example a bridge pattern. Depending on the grants you have its normally not a big deal (including testing up to 4 person days work for the base architecture part and ca. 1 person day for each database system you want to access) Then you get any desired meta data information from there including about foreign key relations.

3. (What I do)
   Just use java.sql.DatabaseMetaData-class It provides among others your desired primary key information for every accessible table.

Here a code-snippet (in Java 7):

  public static Set<String> getPrimaryKeyColumnsForTable(Connection connection, String tableName) throws SQLException {
    try(ResultSet pkColumns= connection.getMetaData().getPrimaryKeys(null,null,tableName);) {
      SortedSet<String> pkColumnSet = new TreeSet<>();
      while(pkColumns.next()) {
        String pkColumnName = pkColumns.getString("COLUMN_NAME");
        Integer pkPosition = pkColumns.getInt("KEY_SEQ");
        out.println(""+pkColumnName+" is the "+pkPosition+". column of the primary key of the table "+tableName);
        pkColumnSet.add(pkColumnName);
      }
      return pkColumnSet;
    }