Get primary key column from ResultSet Java

Question

I am trying to get table primary key columns from a 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 look at this rsmd variable , it displays primary key flags for the corresponding column names, but I cannot access it or get it in any variable.

I need help regarding the same.

NOTE. I do not want to use DatabaseMetadata and its getPrimaryKeys () function, since it adds an add to the external database. In addition, the ResultSetMetadata object already has a primary key. Information I just need to extract.

Answer 1

, ResultSet.

JDBC MySql, , java.sql.ResultSetMetaData com.mysql.jdbc.ResultSetMetaData. protected

protected Field getField(int columnIndex) throws SQLException {

Field column. Field, . , ,

Field.isPrimaryKey() 

FQN com.mysql.jdbc.ResultSetMetaData , ((com.mysql.jdbc.ResultSetMetaData) rsmd).getField(i).isPrimaryKey(). ,

Field MySql JDBC API, . , !

Answer 2

, (, ):

, .

, , , , .

,

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

.

, ResultSetMetaData -Interface . , , , .

:

• ( )
  ResultSetMetaData , . , ResultSetMetaData .

• ( , , JDBC-)
    , , , , , . ( 4 - 1 , ) , .

• ( )
  java.sql.DatabaseMetaData - , , .

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

Answer 3

@Keerthivasan - . , , protected. .

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


}

Answer 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 DBMSs, not necessarily MSSQL