CODEWITHSUNDEEP
javadatabasejdbc
user163588
user163588

Reputation: 137

Show column names of multiple-colum index via JDBC

Does anyone know how to get the column names of an index that spans multiple columns (see SQL) via JDBC ? I do know how to get all index columns (see code), but this way I cannot see which columns are linked to a single index.

CREATE TABLE IF NOT EXISTS `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test1` int(11) NOT NULL,
  `test2` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `test1` (`test1`,`test2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

DatabaseMetaData md = connection.getMetaData(); // assumed that connection exists
ResultSet rs = md.getIndexInfo(null, dbSchema, "foo", true, false);
while (rs.next()) {
    String columnName = rs.getString("COLUMN_NAME");
}

Upvotes: 3

Views: 785

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109147

A general word of advice: Look at the Java API javadoc if you want to see if some information is available before asking a question on Stackoverflow. If you look at the Javadoc of DatabaseMetaData.getIndexInfo(), you will see that the returned resultset contains a lot more columns than just COLUMN_NAME.

For your purposes, you should also look at INDEX_NAME and maybe ORDINAL_POSITION (if you want to know the order of columns inside the index). For a compound index, the ResultSet contains more than one row (ie one row per column in the index).

My answer does assume that your JDBC driver correctly implements and returns all required information.

Upvotes: 0

Evgeniy Dorofeev
Evgeniy Dorofeev

Reputation: 136092

For a multi-column index multiple entries will be returned in ResultSet with the same INDEX_NAME but different COLUMN_NAME

Upvotes: 3

Related Questions