Jack Jack - 1 year ago 113
Java Question

Retrieve all Indexes for a given Table with JDBC

I want to write a SpringBatch Tasklet, that automatically activates or de-activates all indexes for a given database table. The code needs to work independantly of the DBMS (SQL Server, Oracle and HSQLDB are required).

This is what I have tried so far:

DatabaseMetaData dbMetaData = connection.getMetaData();
ResultSet rs = dbMetaData.getIndexInfo(null, null, tableName, true, false);
while (rs.next()) {
// work with ResultSet

However, I do not get the names of the Indexes or any useful information.

So could anyone give some hints on how to set all indexes of table to active or inactive with just a JDBC connection object?

Answer Source

You have to make a difference between primary keys (using DatabaseMetaData.getPrimaryKeys() to retrieve) and other indexes (via dbMetaData.getIndexInfo(null, null, tableName, true, false)).
In your loop use:

  • rs.getString("INDEX_NAME") to extract index name
  • rs.getBoolean("NON_UNIQUE") to extract unique information
  • rs.getShort("TYPE") to extract index type
  • rs.getInt("ORDINAL_POSITION") to extract ordinal position

Use ORDINAL_POSITION as key break (when current value is <= of previous one) to detect index change.
Read official DatabaseMetaData.getIndexInfo() doc

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download