Example usage for java.sql DatabaseMetaData getIndexInfo

Introduction

In this page you can find the example usage for java.sql DatabaseMetaData getIndexInfo.

Prototype

ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate)
        throws SQLException;

Source Link

Document

Retrieves a description of the given table's indices and statistics.

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    ResultSet indexInformation = null;
    DatabaseMetaData meta = conn.getMetaData();

    // The '_' character represents any single character.
    // The '%' character represents any sequence of zero
    // or more characters.
    indexInformation = meta.getIndexInfo(conn.getCatalog(), null, "survey", true, true);
    while (indexInformation.next()) {
        short type = indexInformation.getShort("TYPE");
        switch (type) {
        case DatabaseMetaData.tableIndexClustered:
            System.out.println("tableIndexClustered");
        case DatabaseMetaData.tableIndexHashed:
            System.out.println("tableIndexHashed");
        case DatabaseMetaData.tableIndexOther:
            System.out.println("tableIndexOther");
        case DatabaseMetaData.tableIndexStatistic:
            System.out.println("tableIndexStatistic");
        default://from  ww  w .  j a v a 2 s. co m
            System.out.println("tableIndexOther");
        }

    }

    st.close();
    conn.close();
}

---

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    ResultSet indexInformation = null;
    DatabaseMetaData meta = conn.getMetaData();

    // The '_' character represents any single character.
    // The '%' character represents any sequence of zero
    // or more characters.
    indexInformation = meta.getIndexInfo(conn.getCatalog(), null, "survey", true, true);
    while (indexInformation.next()) {
        String dbCatalog = indexInformation.getString("TABLE_CATALOG");
        String dbSchema = indexInformation.getString("TABLE_SCHEMA");
        String dbTableName = indexInformation.getString("TABLE_NAME");
        boolean dbNoneUnique = indexInformation.getBoolean("NON_UNIQUE");
        String dbIndexQualifier = indexInformation.getString("INDEX_QUALIFIER");
        String dbIndexName = indexInformation.getString("INDEX_NAME");
        short dbType = indexInformation.getShort("TYPE");
        short dbOrdinalPosition = indexInformation.getShort("ORDINAL_POSITION");
        String dbColumnName = indexInformation.getString("COLUMN_NAME");
        String dbAscOrDesc = indexInformation.getString("ASC_OR_DESC");
        int dbCardinality = indexInformation.getInt("CARDINALITY");
        int dbPages = indexInformation.getInt("PAGES");
        String dbFilterCondition = indexInformation.getString("FILTER_CONDITION");

        System.out.println("index name=" + dbIndexName);
        System.out.println("table=" + dbTableName);
        System.out.println("column=" + dbColumnName);
        System.out.println("catalog=" + dbCatalog);
        System.out.println("schema=" + dbSchema);
        System.out.println("nonUnique=" + dbNoneUnique);
        System.out.println("indexQualifier=" + dbIndexQualifier);
        System.out.println("type=" + dbType);
        System.out.println("ordinalPosition=" + dbOrdinalPosition);
        System.out.println("ascendingOrDescending=" + dbAscOrDesc);
        System.out.println("cardinality=" + dbCardinality);
        System.out.println("pages=" + dbPages);
        System.out.println("filterCondition=" + dbFilterCondition);
    }/*from  www. j  a  va2  s.  c o  m*/

    st.close();
    conn.close();
}

---

From source file:com.mirth.connect.server.util.DatabaseUtil.java

/**
 * Tell whether or not the given index exists in the database
 *//* ww w .j  ava  2  s.  c o  m*/
public static boolean indexExists(Connection connection, String tableName, String indexName) {
    if (!tableExists(connection, tableName)) {
        return false;
    }

    ResultSet resultSet = null;

    try {
        DatabaseMetaData metaData = connection.getMetaData();
        resultSet = metaData.getIndexInfo(null, null, tableName.toUpperCase(), false, false);

        while (resultSet.next()) {
            if (indexName.equalsIgnoreCase(resultSet.getString("INDEX_NAME"))) {
                return true;
            }
        }

        resultSet = metaData.getIndexInfo(null, null, tableName.toLowerCase(), false, false);

        while (resultSet.next()) {
            if (indexName.equalsIgnoreCase(resultSet.getString("INDEX_NAME"))) {
                return true;
            }
        }

        return false;
    } catch (SQLException e) {
        throw new DonkeyDaoException(e);
    } finally {
        DbUtils.closeQuietly(resultSet);
    }
}

---

From source file:net.ontopia.topicmaps.cmdlineutils.rdbms.RDBMSIndexTool.java

protected static Map getIndexes(String table_name, DatabaseMetaData dbm) throws SQLException {
    // returns { table_name(colname,...) : index_name }
    Map result = new HashMap(5);
    ResultSet rs = dbm.getIndexInfo(null, null, table_name, false, false);
    String prev_index_name = null;
    String columns = null;/*from  w  w  w  . j a va2s.  c  o  m*/

    while (rs.next()) {
        String index_name = rs.getString(6);

        if (prev_index_name != null && !prev_index_name.equals(index_name)) {
            result.put(table_name + '(' + columns + ')', prev_index_name);
            columns = null;
        }
        // column_name might be quoted, so unquote it before proceeding
        String column_name = unquote(rs.getString(9), dbm.getIdentifierQuoteString());

        if (columns == null)
            columns = column_name;
        else
            columns = columns + "," + column_name;

        prev_index_name = index_name;
    }
    rs.close();

    if (prev_index_name != null)
        result.put(table_name + '(' + columns + ')', prev_index_name);

    return result;
}

---

From source file:com.splicemachine.derby.impl.sql.execute.operations.EmbeddedCallStatementOperationIT.java

@Test
public void testCallGetIndexInfo() throws Exception {
    DatabaseMetaData dmd = methodWatcher.getOrCreateConnection().getMetaData();
    ResultSet rs = dmd.getIndexInfo(null, "SYS", "SYSSCHEMAS", false, true);
    int count = 0;
    while (rs.next()) {
        count++;//from w  w w.  j a va2s.c o  m
        LOG.trace(rs.getString(1));
    }
    Assert.assertTrue(count > 0);
    DbUtils.closeQuietly(rs);
}

---

From source file:com.clican.pluto.orm.tool.TableMetadata.java

private void initIndexes(DatabaseMetaData meta) throws SQLException {
    ResultSet rs = null;/*w w  w  . j  a  v a2s  . co m*/

    try {
        rs = meta.getIndexInfo(catalog, schema, name, false, true);

        while (rs.next()) {
            if (rs.getShort("TYPE") == DatabaseMetaData.tableIndexStatistic)
                continue;
            addIndex(rs);
        }
    } finally {
        if (rs != null)
            rs.close();
    }
}

---

From source file:com.splicemachine.derby.impl.sql.execute.operations.CallStatementOperationIT.java

@Test
public void testCallIndexInfo() throws Exception {
    DatabaseMetaData dmd = methodWatcher.getOrCreateConnection().getMetaData();
    ResultSet rs = dmd.getIndexInfo(null, "SYS", "SYSSCHEMAS", false, true);
    while (rs.next()) { // TODO No Test
    }/*from   w  ww. ja  va  2s. c om*/
    DbUtils.closeQuietly(rs);
}

---

From source file:com.oracle2hsqldb.dialect.GenericDialect.java

public Iterator getUniqueKeys(DataSource dataSource, final String schemaName) {
    final List result = new LinkedList();
    MetaDataJdbcTemplate template = new MetaDataJdbcTemplate(dataSource) {
        protected ResultSet getResults(DatabaseMetaData metaData) throws SQLException {
            return metaData.getIndexInfo(null, schemaName, null, true, true);
        }//w  w  w. ja  v  a  2 s.co m
    };
    template.query(new RowCallbackHandler() {
        public void processRow(ResultSet uniqueIndexes) throws SQLException {
            boolean isNonUnique = uniqueIndexes.getBoolean("NON_UNIQUE");
            if (!isNonUnique) {
                String columnName = uniqueIndexes.getString("COLUMN_NAME");
                String constraintName = uniqueIndexes.getString("INDEX_NAME");
                String tableName = uniqueIndexes.getString("TABLE_NAME");

                result.add(new UniqueConstraint.Spec(tableName, columnName, constraintName));
            }
        }
    });
    return result.iterator();
}

---

From source file:org.jtalks.poulpe.util.databasebackup.persistence.DbTableKeys.java

/**
 * Obtain from the database a list of tables' unique keys.
 * /*  w w w  .  j a  va2 s  .  c  o m*/
 * @return A list of {@link UniqueKey} object represented unique keys.
 * @throws SQLException
 *             Is thrown in case any errors during work with database occur.
 */
@SuppressWarnings("unchecked")
public Set<UniqueKey> getUniqueKeys() throws SQLException {
    Set<UniqueKey> tableUniqueKeySet = null;
    try {
        tableUniqueKeySet = (Set<UniqueKey>) JdbcUtils.extractDatabaseMetaData(dataSource,
                new KeyListProcessor(tableName, new TableKeyPerformer() {
                    @Override
                    public ResultSet getResultSet(DatabaseMetaData dmd, String tableName) throws SQLException {
                        return dmd.getIndexInfo(null, null, tableName, true, true);
                    }

                    @Override
                    public void addKeyToSet(ResultSet rs, Set<TableKey> keySet) throws SQLException {
                        if (rs.getString(INDEX_NAME) != null && rs.getString(COLUMN_NAME) != null) {
                            UniqueKey key = new UniqueKey(rs.getString(INDEX_NAME), rs.getString(COLUMN_NAME));
                            if (!isPrimaryKey(key)) {
                                keySet.add(key);
                            }
                        }

                    }
                }));

        Map<String, UniqueKey> resultMap = new HashMap<String, UniqueKey>();
        for (UniqueKey uniqueKey : tableUniqueKeySet) {
            if (resultMap.containsKey(uniqueKey.getIndexName())) {
                Set<String> existingColumns = new HashSet<String>(
                        resultMap.get(uniqueKey.getIndexName()).getColumnNameSet());
                existingColumns.addAll(uniqueKey.getColumnNameSet());
                resultMap.put(uniqueKey.getIndexName(),
                        new UniqueKey(uniqueKey.getIndexName(), existingColumns));
            } else {
                resultMap.put(uniqueKey.getIndexName(), uniqueKey);
            }
        }
        tableUniqueKeySet.clear();
        for (UniqueKey key : resultMap.values()) {
            tableUniqueKeySet.add(key);
        }

    } catch (MetaDataAccessException e) {
        throw new SQLException(e);
    }
    return tableUniqueKeySet;
}

---

From source file:com.micromux.cassandra.jdbc.JdbcRegressionTest.java

/**
 * Previously this was <i>Issue 76</i>.
 *//*from ww  w.jav a  2 s.  c  o  m*/
@Test
public void testDatabaseMetaViaResultSet() throws Exception {
    DatabaseMetaData md = con.getMetaData();

    // test various retrieval methods
    ResultSet result = md.getIndexInfo(con.getCatalog(), KEYSPACE, TABLE, false, false);
    assertTrue("Make sure we have found an index", result.next());

    // check the column name from index
    String cn = result.getString("COLUMN_NAME");
    assertEquals("Column name match for index", "ivalue", cn);
    System.out.println("Found index via dmd on :   " + cn);
}

---

• «
• 1
• 2
• 3
• 4
• »