Retrieve all Indexes for a given Table with JDBC

Question

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 1

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

Answer 2

The other way of getting all indexes is using the information_schema statistics table:

public void GetIndexesOfThisTable(final String tableName) {
    try {
        PreparedStatement ps = conn.prepareStatement("SELECT DISTINCT INDEX_NAME FROM information_schema.statistics WHERE table_name = ?");
        ps.setString(1, tableName);
        ResultSet rs = ps.executeQuery();

        while(rs.next()){
            System.out.println(rs.getString(1));
        }
        rs.close();
        ps.close();//important to close to prevent resource leaks
    }
    catch (Exception ex) {System.out.println(ex.getMessage());}        
}

Answer 3

Below code is used to retrieve the INDEX_NAME From the DATABASE.

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class Main {
    public static void main(String[] args) throws Exception {
        Connection conn = getOracleConnection();  //Based on the database corresponding method should be called
        List<String> schemaList=new ArrayList<String>();
        List<String> catalogList=new ArrayList<String>();
        List<String> indexs=new ArrayList<String>();
        String dbIndexName=null;
        ResultSet rs=null;
        System.out.println("Got Connection.");
        try {
            DatabaseMetaData metaData = conn.getMetaData();

            ResultSet schemas = metaData.getSchemas();
            ResultSet catalog = metaData.getCatalogs();
            while (schemas.next()) {
                String tableSchema = schemas.getString(1);  
                schemaList.add(tableSchema);
            }
            while (catalog.next()) {
                String allCatalog = catalog.getString(1);  
                catalogList.add(allCatalog);
            }


            for(int i=0;i<schemaList.size();i++){
                try{
                    if(schemaList.get(i)!=null){
                        ResultSet indexValues = metaData.getIndexInfo(null, schemaList.get(i),tablename, true, false);

                        while (indexValues.next()) {

                            dbIndexName = indexValues.getString("INDEX_NAME");
                            if(dbIndexName!=null){
                                indexs.add(dbIndexName);
                            }
                        }
                        System.out.println("CORRESPONDING TABLE SCHEMA IS : "+schemaList.get(i));
                        System.out.println("INDEX_NAMES IS ::: "+indexs);
                    }

                }catch(Exception e){
            }

        }
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
}

public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:oracledb";
    String username = "oop";
    String password = "oop";

    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
}

private static Connection getDB2Connection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
}

public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";

    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
}

}

Answer 4

I had a similar problem and the cause in my case was, the case-sensitivity. I had to write the whole tablename in lower case letters (like it appeared in the result set of the SQL "SELECT tablename,indexname,indexdef FROM pg_indexes".

I also changed the second last parameter ("unique" parameter) to false to get all indexes (otherwise I just got the primary key).

Hope this helps.