如何获取JDBC中所有表的主键? [英] How to get primary keys for all tables in JDBC?

问题描述

我有一个至少有500个表的数据库.获取每个表的所有主键和外键的确切代码是什么?

I have a database with at least 500 tables. What is the exact code to get all the primary keys and foreign keys of each table?

//Primary Key
DatabaseMetaData meta=conn.getMetaData();
ResultSet rs1= meta.getTables(null, null, "TableName" , new String[]{"TABLE"});
rs1=meta.getPrimaryKeys(null, null, "TableName");
while(rs1.next())
    System.out.println("Primary Key :"+rs1.getString(4));

//Foreign Key
rs1=meta.getExportedKeys(null, null, "TableName");
while(rs1.next())
    System.out.println("Foreign Key :"+rs1.getString(4));

我已经使用了这段代码，它为我提供了准确的键，但是对于500个表，我必须将代码更改500次.有什么办法可以减少这种工作量?

I have used this code and it gives me accurate keys but for 500 tables, I have to change my code 500 times. Is there any way to minimize this effort?

推荐答案

您无需修改​​代码500次，您可以使用meta.getTables(null, null, "%", new String[]{"TABLE"})检索所有表名.

You don't need to modify your code 500 times, you can retrieve all table names using meta.getTables(null, null, "%", new String[]{"TABLE"}).

方法getTables采用tableNamePattern参数的相似模式，因此"%"匹配所有表名.

The method getTables takes a like-pattern for the tableNamePattern parameter, so "%" matches all table names.

方法getPrimaryKeys和getExportedKeys不采用模式，因此您将需要遍历getTables的结果，并对getTables结果集的每一行执行这些方法.

The method getPrimaryKeys and getExportedKeys do not take a pattern, so you will need to loop over the result of getTables and execute those methods for each row of the getTables result set.

因此，您需要执行以下操作:

So you will need to do something like:

try (ResultSet tables = meta.getTables(null, null, "%", new String[] { "TABLE" })) {
    while (tables.next()) {
        String catalog = tables.getString("TABLE_CAT");
        String schema = tables.getString("TABLE_SCHEM");
        String tableName = tables.getString("TABLE_NAME");
        System.out.println("Table: " + tableName);
        try (ResultSet primaryKeys = meta.getPrimaryKeys(catalog, schema, tableName)) {
            while (primaryKeys.next() {
                System.out.println("Primary key: " + primaryKeys.getString("COLUMN_NAME"));
            }
        }
        // similar for exportedKeys
    }
}

我包括了对目录和架构的检索，因为这可能会影响事物的工作方式.

I have included retrieval of catalog and schema, because that might influence how things work.

这篇关于如何获取JDBC中所有表的主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！