从 ResultSet Java 获取主键列

Get Primary Key Column from ResultSet Java

提问by SID

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

我正在尝试从 ResultSet 中获取表的主键列。以下是步骤

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.

现在，如果我观察这个变量“ rsmd”，它会显示相关列名的主键标志，但我无法访问它或将它放入任何变量中。

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.

注意：我不想使用 DatabaseMetadata 和它的 getPrimaryKeys() 函数，因为它会对外部数据库产生额外的影响。此外， ResultSetMetadata 对象已经具有我只需要获取的主键信息。

采纳答案by Keerthivasan

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

我有一个想法来检查表中的列是否是主键或不使用ResultSet.

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

在MySql JDBC驱动中，仔细看，真正的实现类java.sql.ResultSetMetaData应该是com.mysql.jdbc.ResultSetMetaDataclass。此类提供了protected获取有关每个字段的信息的方法

protected Field getField(int columnIndex) throws SQLException {

This method can give you the Fieldinstance for every columnindex. 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提供每个column索引的实例。使用 Field 实例，您可以获取 Field 的属性。要检查它是否是主键，您可以调用

Field.isPrimaryKey() 

Use FQN of com.mysql.jdbc.ResultSetMetaDatain 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

com.mysql.jdbc.ResultSetMetaData在您的类型转换中使用 FQN of ，如((com.mysql.jdbc.ResultSetMetaData) rsmd).getField(i).isPrimaryKey(). 这是因为您不能导入两个同名的类文件并在整个文件中使用它们

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

请阅读MySql JDBC API的Field文档以了解更多信息。希望这可以帮助！

回答by Peter Brummer

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.

如您所知，标准ResultSetMetaData-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
   ResultSetMetaDataimplementation 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.

1. （不是我的首选方式）
   转换到特定的实现ResultSetMetaData类并访问主键信息（如果可用）。但请注意，并非每个
   ResultSetMetaData实现都提供此信息。

2. （更多的架构方法，我也没有提出，但
   如果我们处理不完整的 JDBC 驱动程序，则需要）
   利用您使用的不同数据库的系统表，但当然将其隐藏在抽象中，例如桥梁图案。根据资助，您通常没什么大不了的（包括测试基础架构部分最多 4 人天的工作，以及您要访问的每个数据库系统大约 1 人天的工作）然后您从那里包括关于外键关系。

3. （我所做的）
   只需使用java.sql.DatabaseMetaData-class 它为每个可访问的表提供所需的主键信息。

Here a code-snippet (in Java 7):

这是一个代码片段（在 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;
    }

回答by Krishantha Dinesh

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

使用@Keerthivasan Approach 这里是完整的工作代码。唯一的问题是答案不能使用这样的方法，因为它是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();
    }


}

回答by Harish M A

    // 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

这将适用于所有 RDBMS，而不仅仅是 MSSQL