Kaushal Daga Kaushal Daga - 10 days ago 6
MySQL Question

How to get primary keys for all tables in JDBC?

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


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?

Answer

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

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

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.