Does an Index Exist for a Specific Table? JDBC

Given a table, such as the ACCOUNT table created in the previous section, you can find out whether a particular index exists. There is no such explicit method in the JDBC API, but you can use the DatabaseMetaData.getIndexInfo() method in the solution to solve the problem.

The Solution: indexExists()

public static boolean indexExists(java.sql.Connection conn,
String catalog,
String schema,
String tableName,
String indexName)
throws Exception {
if ((tableName == null) || (tableName.length() == 0) ||
(indexName == null) || (indexName.length() == 0)) {
return false;
}
DatabaseMetaData dbMetaData = conn.getMetaData();
if (dbMetaData == null) {
return false;
}
ResultSet rs = dbMetaData.getIndexInfo(catalog,
schema, tableName, false, true);
while (rs.next()) {
String dbIndexName = rs.getString(COLUMN_NAME_INDEX_NAME);
if (indexName.equalsIgnoreCase(dbIndexName)) {
return true;
}
}
return false;
}

A Client:MySQL

System.out.println("-------- Does index exist? -------------");
System.out.println("conn="+conn);
boolean indexExist = DatabaseMetaDataTool.indexExists
(conn,
conn.getCatalog(), // catalog
null, // schema
"ACCOUNT", // table name
"ID_STATUS_INDEX"); // index name
System.out.println("Index name: ID_STATUS_INDEX");
System.out.println("Table name: ACCOUNT");
System.out.println("Index Exist?: " + indexExist);
System.out.println("-------- Does index exist? -------------");
boolean indexExist22 = DatabaseMetaDataTool.indexExists
(conn,
conn.getCatalog(), // catalog
null, // schema
"ACCOUNT", // table name
"ID_STATUS_INDEX22"); // index name
System.out.println("Index name: ID_STATUS_INDEX22");
System.out.println("Table name: ACCOUNT");
System.out.println("Index Exist?: " + indexExist22);

Output:MySQL

-------- Does index exist? -------------
[email protected]
Index name: ID_STATUS_INDEX
Table name: ACCOUNT
Index Exist?: true
-------- Does index exist? -------------
Index name: ID_STATUS_INDEX22
Table name: ACCOUNT
Index Exist?: false

A Client:Oracle

System.out.println("-------- Does index exist? -------------");
System.out.println("conn="+conn);
boolean indexExist = DatabaseMetaDataTool.indexExists
(conn,
conn.getCatalog(), // catalog
null, // schema
"ACCOUNT", // table name
"ID_STATUS_INDEX"); // index name
System.out.println("Index name: ID_STATUS_INDEX");
System.out.println("Table name: ACCOUNT");
System.out.println("Index Exist?: " + indexExist);
System.out.println("-------- Does index exist? -------------");
boolean indexExist22 = DatabaseMetaDataTool.indexExists
(conn,
conn.getCatalog(), // catalog
null, // schema
"ACCOUNT", // table name
"ID_STATUS_INDEX22"); // index name
System.out.println("Index name: ID_STATUS_INDEX22");
System.out.println("Table name: ACCOUNT");
System.out.println("Index Exist?: " + indexExist22);

Output:Oracle

-------- Does index exist? -------------
[email protected]
Index name: ID_STATUS_INDEX
Table name: ACCOUNT
Index Exist?: true
-------- Does index exist? -------------
Index name: ID_STATUS_INDEX22
Table name: ACCOUNT
Index Exist?: false


Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics