Java DatabaseMetaData getIndexInfo() method with example


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

  • catalog − A string parameter representing the name of the catalog (database in general) in which the table (that contains the indices of which you need the description of) exists, pass "" to get the description of the indices in tables with no catalog and, pass null if you don't want to use catalog and thus narrow the search.

  • schema − A String parameter representing the name of the schema of the table, pass "" to get the description of the columns in tables with no schema and, pass null if you don't want to use schema.

  • table − A String parameter representing the name of the table.

  • unique − A boolean parameter, if true this method returns indices only for unique values, If false it returns indices irrespective of unique values.

  • approximate − A boolean parameter, if true this method returns approximate values, if false this method returns accurate values.

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

Column name
Data type
Description
TABLE_CAT
String
Catalog of the table.
TABLE_SCHEM
String
Catalog of the schema.
TABLE_NAME
String
Name of the table.
INDEX_QUALIFIER
String
This represents the index catalog, and it is null when type is tableIndexStatistic.
INDEX_NAME
String
Name of the index.
TYPE
Short
Type of the index.
ORDINAL_POSITION
short
Sequence number of the index.
COLUMN_NAME
String
Name of the column.
CARDINALITY
Int
When TYPE is tableIndexStatistic, then this is the number of rows in the table; otherwise, it is the number of unique values in the index.
FILTER_CONDITION
String
Filter condition.


To get the description of required indices in the database −

  • Make sure your database is up and running.

  • Register the driver using the registerDriver() method of the DriverManager class. Pass an object of the driver class corresponding to the underlying database.

  • Get the connection object using the getConnection() method of the DriverManager class. Pass the URL the database and, user name, password of a user in the database, as String variables.

  • Get the DatabaseMetaData object with respect to the current connection using the getMetaData() method of the Connection interface.

  • Finally, get ResultSet object holding the description of the required columns, by invoking the getIndexInfo() method of the DatabaseMetaData interface.

Example

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

CREATE DATABASE example_database;
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

Following JDBC program establishes connection with MySQL database, 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

Vikyath Ram
Vikyath Ram

A born rival

Updated on: 30-Jul-2019

484 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements