JDBC: Unable to get composite index for my table

Question

I am trying to list all the indexes for all the tables in my DB (using a generic solution which is not specific to an RDBMS)

To test out my solution, I have 2 tables in an MS SQL Server DB tempdb

• Stores
• Staffs

Each table has a PK and the staffs table also have a Unique key column called email. There is a composite index on the stores table on store_id, store_name.

Following are the indexes (screenshots from DBeaver)

The code snippet is as follows

.
.
.
System.out.println("**********************************************************");
System.out.println("schema >>> "+schema);
System.out.println("table >>> "+table);
ResultSet indexValues = tableMeta.getIndexInfo(null, null, table, true, false);
while (indexValues.next()) {
    ResultSetMetaData rsmd = indexValues.getMetaData();  
    String idxName = indexValues.getString("INDEX_NAME");
    System.out.println("=====================================================");
    System.out.println("rs1 >>> "+indexValues.getString(1));
    System.out.println("rs2 >>> "+indexValues.getString(2));
    System.out.println("rs3 >>> "+indexValues.getString(3));
    System.out.println("rs4 >>> "+indexValues.getString(4));
    System.out.println("rs5 >>> "+indexValues.getString(5));
    System.out.println("rs6 >>> "+indexValues.getString(6));
    System.out.println("rsmd >>> "+rsmd.getColumnCount());
    System.out.println("INDEX_NAME >>> "+indexValues.getString("INDEX_NAME"));
    System.out.println("INDEX_QUALIFIER >>> "+indexValues.getString("INDEX_QUALIFIER"));
    System.out.println("NON_UNIQUE >>> "+indexValues.getBoolean("NON_UNIQUE"));
    System.out.println("TYPE >>> "+indexValues.getShort("TYPE"));
    System.out.println("ORDINAL_POSITION >>> "+indexValues.getString("ORDINAL_POSITION"));
    System.out.println("COLUMN_NAME >>> "+indexValues.getString("COLUMN_NAME"));
    System.out.println("ASC_OR_DESC >>> "+indexValues.getString("ASC_OR_DESC"));
    System.out.println("FILTER_CONDITION >>> "+indexValues.getString("FILTER_CONDITION"));
    System.out.println("TABLE_SCHEM >>> "+indexValues.getString("TABLE_SCHEM"));
    System.out.println("TABLE_CAT >>> "+indexValues.getString("TABLE_CAT"));
    System.out.println("=====================================================");

}

The output is as follows (the compound/composite index is not returned)

**********************************************************
schema >>> dbo
table >>> stores
=====================================================
rs1 >>> tempdb
rs2 >>> dbo
rs3 >>> stores
rs4 >>> null
rs5 >>> null
rs6 >>> null
rsmd >>> 13
INDEX_NAME >>> null
INDEX_QUALIFIER >>> null
NON_UNIQUE >>> false
TYPE >>> 0
ORDINAL_POSITION >>> null
COLUMN_NAME >>> null
ASC_OR_DESC >>> null
FILTER_CONDITION >>> null
TABLE_SCHEM >>> dbo
TABLE_CAT >>> tempdb
=====================================================
=====================================================
rs1 >>> tempdb
rs2 >>> dbo
rs3 >>> stores
rs4 >>> 0
rs5 >>> stores
rs6 >>> PK__stores__A2F2A30C0F20F60B
rsmd >>> 13
INDEX_NAME >>> PK__stores__A2F2A30C0F20F60B
INDEX_QUALIFIER >>> stores
NON_UNIQUE >>> false
TYPE >>> 1
ORDINAL_POSITION >>> 1
COLUMN_NAME >>> store_id
ASC_OR_DESC >>> A
FILTER_CONDITION >>> null
TABLE_SCHEM >>> dbo
TABLE_CAT >>> tempdb
=====================================================
**********************************************************
schema >>> dbo
table >>> staffs
=====================================================
rs1 >>> tempdb
rs2 >>> dbo
rs3 >>> staffs
rs4 >>> null
rs5 >>> null
rs6 >>> null
rsmd >>> 13
INDEX_NAME >>> null
INDEX_QUALIFIER >>> null
NON_UNIQUE >>> false
TYPE >>> 0
ORDINAL_POSITION >>> null
COLUMN_NAME >>> null
ASC_OR_DESC >>> null
FILTER_CONDITION >>> null
TABLE_SCHEM >>> dbo
TABLE_CAT >>> tempdb
=====================================================
=====================================================
rs1 >>> tempdb
rs2 >>> dbo
rs3 >>> staffs
rs4 >>> 0
rs5 >>> staffs
rs6 >>> PK__staffs__1963DD9CCB589A48
rsmd >>> 13
INDEX_NAME >>> PK__staffs__1963DD9CCB589A48
INDEX_QUALIFIER >>> staffs
NON_UNIQUE >>> false
TYPE >>> 1
ORDINAL_POSITION >>> 1
COLUMN_NAME >>> staff_id
ASC_OR_DESC >>> A
FILTER_CONDITION >>> null
TABLE_SCHEM >>> dbo
TABLE_CAT >>> tempdb
=====================================================
=====================================================
rs1 >>> tempdb
rs2 >>> dbo
rs3 >>> staffs
rs4 >>> 0
rs5 >>> staffs
rs6 >>> UQ__staffs__AB6E6164E6F8EDB6
rsmd >>> 13
INDEX_NAME >>> UQ__staffs__AB6E6164E6F8EDB6
INDEX_QUALIFIER >>> staffs
NON_UNIQUE >>> false
TYPE >>> 3
ORDINAL_POSITION >>> 1
COLUMN_NAME >>> email
ASC_OR_DESC >>> A
FILTER_CONDITION >>> null
TABLE_SCHEM >>> dbo
TABLE_CAT >>> tempdb
=====================================================

I've read through the following documentation link [JDBC doc]

Also, read through these stackoverflow questions on the same topic

[Question 1] [Question 2] [Question 3]

What should I do to get this done via JDBC? One option I have is to have DB specific queries to get the list of indices and run them through JDBC (but I don't want to go that route)

Answer 1

The fourth parameter for getIndexInfo() is

unique - when true, return only indices for unique values; when false, return indices regardless of whether unique or not

As you are passing true for that parameter, only unique indexes are returned, but myCompound1 is not defined as unique, so getIndexInfo() does not return it. You need to pase false for that parameter:

tableMeta.getIndexInfo(null, null, table, false, false);