JDBC : Mengambil Daftar Index dari Suatu Table

posted Sep 19, 2011, 5:17 PM by Editor KursusInternet   [ updated Feb 25, 2012, 8:34 PM ]

Pendahuluan

Index merupakan fitur pada berbagai teknologi database, yang berfungsi untuk mempercepat pencarian data. Selain itu index juga dapat digunakan sebagai constraint untuk memperjelas relasi antar table melalui primary dan foreign key.

Jika kita ingin mengambil informasi index ini dengan Java, kita kembali menggunakan interface DatabaseMetaData - seperti halnya pada saat kita mengambil daftar nama table dari artikel berikut

Method yang digunakan adalah getIndexInfo() dengan syntax berdasarkan dokumentasinya adalah sebagai berikut :
ResultSet getIndexInfo(String catalog,
                       String schema,
                       String table,
                       boolean unique,
                       boolean approximate)
                       throws SQLException
Keterangan :
Retrieves a description of the given table's indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.

Each index column description has the following columns:

  1. TABLE_CAT String => table catalog (may be null)
  2. TABLE_SCHEM String => table schema (may be null)
  3. TABLE_NAME String => table name
  4. NON_UNIQUE boolean => Can index values be non-unique. false when TYPE is tableIndexStatistic
  5. INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic
  6. INDEX_NAME String => index name; null when TYPE is tableIndexStatistic
  7. TYPE short => index type:
    • tableIndexStatistic - this identifies table statistics that are returned in conjuction with a table's index descriptions
    • tableIndexClustered - this is a clustered index
    • tableIndexHashed - this is a hashed index
    • tableIndexOther - this is some other style of index
  8. ORDINAL_POSITION short => column sequence number within index; zero when TYPE is tableIndexStatistic
  9. COLUMN_NAME String => column name; null when TYPE is tableIndexStatistic
  10. ASC_OR_DESC String => column sort sequence, "A" => ascending, "D" => descending, may be null if sort sequence is not supported; null when TYPE is tableIndexStatistic
  11. 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.
  12. PAGES int => When TYPE is tableIndexStatisic then this is the number of pages used for the table, otherwise it is the number of pages used for the current index.
  13. FILTER_CONDITION String => Filter condition, if any. (may be null)
Parameters:
catalog - a catalog name; must match the catalog name as it is stored in this database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
schema - a schema name; must match the schema name as it is stored in this database; "" retrieves those without a schema; null means that the schema name should not be used to narrow the search
table - a table name; must match the table name as it is stored in this database
unique - when true, return only indices for unique values; when false, return indices regardless of whether unique or not
approximate - when true, result is allowed to reflect approximate or out of data values; when false, results are requested to be accurate

Contoh Penggunaan

Berikut adalah contoh kode Java lengkap untuk mengambil nama index dan kolom-kolom pada index tersebut pada table ms_harga_harian dari database PHI-Minimart (disimpan pada SQL Server 2008 R2).

package com.kursus.internet.database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class AmbilDaftarIndex {
  public static void main(String[] args) {
    Connection koneksi = null;
    DatabaseMetaData objmd = null;
    ResultSet rs = null;

    String namaClassDriver = "net.sourceforge.jtds.jdbc.Driver";
    String urlKoneksi = "jdbc:jtds:sqlserver://localhost/phi_minimart";

    Properties connectionProps = new Properties();
    connectionProps.put("user""kursusinternet");
    connectionProps.put("password""kursusinternet");

    try {
      Class.forName(namaClassDriver);
      koneksi = DriverManager.getConnection(urlKoneksi, connectionProps);
      objmd = koneksi.getMetaData();
      rs = objmd.getIndexInfo(null, null, "ms_harga_harian", false, false);      
      while(rs.next())
      {
        System.out.print("Nama Index = " + rs.getString("INDEX_NAME"));
        System.out.println(", kolom = " + rs.getString("COLUMN_NAME"));
      }

    catch (SQLException e) {
      e.printStackTrace();
    catch (ClassNotFoundException e) {
      e.printStackTrace();
    }
    finally {
      try {
        if(rs != nullrs.close();
        if(koneksi != nullkoneksi.close();
      catch (SQLException e) {
        e.printStackTrace();
      }      
    }
  }
}

Hasil Eksekusi

Nama Index = null, kolom = null
Nama Index = PK_ms_harga_harian, kolom = kode_produk
Nama Index = PK_ms_harga_harian, kolom = tgl_berlaku
Nama Index = PK_ms_harga_harian, kolom = kode_cabang


Gambar : Tampilan struktur index pada table ms_harga_harian menggunakan SQL Server Management Studio

Sumber Referensi

Comments