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.

Solution 1

I have an idea to check whether a Column in table is Primary key or not using ResultSet.

In MySql JDBC driver, if you take a closer look, the real implementation class of java.sql.ResultSetMetaData would be com.mysql.jdbc.ResultSetMetaData class. This class provides a protected method to get information about each field

protected Field getField(int columnIndex) throws SQLException {

This method can give you the Field instance for every column index. Using the Field instance, you can get to the properties of the Field. To check whether it is a primary key, you can invoke

Field.isPrimaryKey() 

Use FQN of com.mysql.jdbc.ResultSetMetaData in your type cast like ((com.mysql.jdbc.ResultSetMetaData) rsmd).getField(i).isPrimaryKey(). This is because you cannot import two class files with the same name and use them across the file

Please read the documentation of Field from MySql JDBC API to learn more about it. Hope this helps!

Solution 2

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;
    }

Solution 3

with @Keerthivasan Approach here is the complete working code. only problem was that answer cannot use the method like that as it is protected method. here is the working code.

ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
int count = resultSetMetaData.getColumnCount();
for (int x = 1; x <= count; x++) {

    Method method = null;
    try {
        method = com.mysql.jdbc.ResultSetMetaData.class.getDeclaredMethod("getField", int.class);
        method.setAccessible(true);
        com.mysql.jdbc.Field field = (com.mysql.jdbc.Field) method.invoke(resultSetMetaData, x);

        if (field.isPrimaryKey()) {
            System.out.println("-----------PK---------------------");
        } else {
            System.out.println("+++++++++++++++NPK++++++++++++++++++");
        }
    } catch (NoSuchMethodException e) {
        e.printStackTrace();
    } catch (IllegalAccessException e) {
        e.printStackTrace();
    } catch (InvocationTargetException e) {
        e.printStackTrace();
    }


}

Solution 4

    // find primary keys
    try {
        ResultSet rs = conn.getMetaData().getPrimaryKeys(null, conn.getSchema(), table);
        while (rs.next()) {
            System.out.println(rs.getString("COLUMN_NAME") + ":" + rs.getString("KEY_SEQ"));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

This will work for all RDBMS, not just MSSQL

View more solutions