Java DatabaseMetaData getIndexInfo() method with example

In this article, we will learn about the DatabaseMetaData getIndexInfo() method with an example in Java. This is useful when you need to understand the display information of a table in a result set using the indexes.

DatabaseMetaData getIndexInfo() method

The getIndexInfo() method in Java's DatabaseMetaData interface retrieves information about indexes for a specified table in a database. This method is useful for understanding the structure and performance of a database table, particularly for optimization and query tuning.

Syntax

ResultSet rs = metaData.getIndexInfo("example_database", null, "sample_table", false, false);

This method retrieves the description of the indices of a table. It accepts 5 parameters ?

• Catalog - The database name (null for the default).
  
• Schema - The schema name (null for all schemas).
  
• Table - The table name for which indexes are retrieved.
  
• Unique - true to retrieve only unique indexes; false for all indexes.
  
• Approximate - true for approximate index information; false for precise data.

This method returns a ResultSet object describing specified indices. This object holds values for the following details (as column names) ?

Retrieving the Description of the Indexes

The following program demonstrates how to use the getIndexInfo() method. It connects to a MySQL database, retrieves the metadata of a table, and displays the retrieved description of the index

Let us create a database with the name sample_database and, create a table sample_table in it using CREATE statements as shown below ?

CREATE TABLE example_database.sample_table(Name VARCHAR(255), age INT, Location VARCHAR(255));

Now, we will insert 2 records in the sample_table table using INSERT statements ?

insert INTO example_database.sample_table values('Kasyap', 29, 'Vishakhapatnam');
INSERT INTO example_database.sample_table values('Krishna', 30, 'Hyderabad');
Finally, create a n index with name sample_index on the above created table as:
mysql> CREATE INDEX sample_index ON sample_table (name) USING BTREE;
Query OK, 0 rows affected (1.42 sec)
Records: 0 Duplicates: 0 Warnings: 0

To get the description of required indices in the database ?

Register the Driver: Select the required database and register the Driver class of the particular database using the registerDriver() method of the DriverManager class or, the forName() method of the class named Class ?

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

Get connection: Create a connection object by passing the URL of the database, username, and password of a user in the database (in string format) as parameters to the getConnection() method of the DriverManager class ?

Connection mysqlCon = DriverManager.getConnection(mysqlUrl, "root", "password");

Get the DatabaseMetaData object concerning the current connection using the getMetaData() method of the Connection interface ?

DatabaseMetaData metaData = con.getMetaData();

Finally, the get ResultSet object holds the description of the required columns, by invoking the getIndexInfo() method of the DatabaseMetaData interface ?

ResultSet rs = metaData.getIndexInfo("example_database", null, "sample_table", false, false);

Example

Following the JDBC program establishes connection with the MySQL database, and retrieves the description of the index created above ?

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DatabaseMetaData_getIndexInfo {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String url = "jdbc:mysql://localhost/example_database";
      Connection con = DriverManager.getConnection(url, "root", "password");
      System.out.println("Connection established......");
      //Retrieving the meta data object
      DatabaseMetaData metaData = con.getMetaData();
      //Retrieving the columns in the database
      ResultSet rs = metaData.getIndexInfo("example_database", null, "sample_table", false, false);
      //Printing the column name and size
      while (rs.next()) {
         System.out.println("Table name: "+rs.getString("Table_NAME"));
         System.out.println("Column name: "+rs.getString("COLUMN_NAME"));
         System.out.println("Column name: "+rs.getString("NON_UNIQUE"));
         System.out.println("Index name: "+rs.getString("INDEX_NAME"));
         System.out.println(" ");
      }
   }
}

Output

Connection established......
Table name: sample_table
Column name: Name
Column name: true
Index name: sample_index

Conclusion

The getIndexInfo() method is useful for retrieving index metadata, including whether an index is unique, the column it is applied to, and its name. This information helps in database performance tuning and query optimization.