Example usage for java.sql DatabaseMetaData tableIndexOther

List of usage examples for java.sql DatabaseMetaData tableIndexOther

Introduction

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

Prototype

short tableIndexOther

To view the source code for java.sql DatabaseMetaData tableIndexOther.

Click Source Link

Document

Indicates that this table index is not a clustered index, a hashed index, or table statistics; it is something other than these.

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:/*ww  w.j a  va2 s . co m*/
            System.out.println("tableIndexOther");
        }

    }

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

From source file:com.nextep.designer.sqlgen.helpers.CaptureHelper.java

/**
 * Converts a JDBC index type code into a neXtep {@link IndexType} enumeration.
 * //from w  w  w. j  a  v  a 2 s.c o m
 * @param type JDBC code of the index type
 * @return a corresponding {@link IndexType}
 */
public static IndexType getIndexType(short type) {
    switch (type) {
    case DatabaseMetaData.tableIndexHashed:
        return IndexType.HASH;
    case DatabaseMetaData.tableIndexStatistic:
    case DatabaseMetaData.tableIndexClustered:
    case DatabaseMetaData.tableIndexOther:
    default:
        return IndexType.NON_UNIQUE;
    }
}

From source file:io.vitess.jdbc.VitessMySQLDatabaseMetadata.java

@SuppressWarnings("StringBufferReplaceableByString")
public ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate)
        throws SQLException {

    ArrayList<ArrayList<String>> data = new ArrayList<>();
    final SortedMap<IndexMetaDataKey, ArrayList<String>> sortedRows = new TreeMap<>();
    VitessStatement vitessStatement = new VitessStatement(this.connection);
    ResultSet resultSet = null;//  ww  w  .ja va 2s .c om
    try {
        resultSet = vitessStatement.executeQuery("SHOW INDEX FROM " + this.quotedId + table + this.quotedId
                + " " + "FROM " + this.quotedId + catalog + this.quotedId);

        while (resultSet.next()) {
            ArrayList<String> row = new ArrayList<>();
            row.add(0, catalog);
            row.add(1, null);
            row.add(2, resultSet.getString("Table"));

            boolean indexIsUnique = resultSet.getInt("Non_unique") == 0;

            row.add(3, !indexIsUnique ? "true" : "false");
            row.add(4, "");
            row.add(5, resultSet.getString("Key_name"));
            short indexType = DatabaseMetaData.tableIndexOther;
            row.add(6, Integer.toString(indexType));
            row.add(7, resultSet.getString("Seq_in_index"));
            row.add(8, resultSet.getString("Column_name"));
            row.add(9, resultSet.getString("Collation"));

            long cardinality = resultSet.getLong("Cardinality");
            if (cardinality > Integer.MAX_VALUE) {
                cardinality = Integer.MAX_VALUE;
            }

            row.add(10, String.valueOf(cardinality));
            row.add(11, "0");
            row.add(12, null);

            IndexMetaDataKey indexInfoKey = new IndexMetaDataKey(!indexIsUnique, indexType,
                    resultSet.getString("Key_name").toLowerCase(), resultSet.getShort("Seq_in_index"));
            sortedRows.put(indexInfoKey, row);
        }

        for (ArrayList<String> row : sortedRows.values()) {
            data.add(row);
        }

    } finally {
        if (null != resultSet) {
            resultSet.close();
        }
        vitessStatement.close();
    }
    String[] columnName = new String[] { "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "Non_unique",
            "INDEX_QUALIFIER", "INDEX_NAME", "TYPE", "ORDINAL_POSITION", "COLUMN_NAME", "ASC_OR_DESC",
            "CARDINALITY", "PAGES", "FILTER_CONDITION" };

    Query.Type[] columnType = new Query.Type[] { Query.Type.CHAR, Query.Type.CHAR, Query.Type.CHAR,
            Query.Type.BIT, Query.Type.CHAR, Query.Type.CHAR, Query.Type.INT16, Query.Type.INT16,
            Query.Type.CHAR, Query.Type.CHAR, Query.Type.INT32, Query.Type.INT32, Query.Type.CHAR };

    return new VitessResultSet(columnName, columnType, data, this.connection);
}

From source file:org.apache.ddlutils.platform.oracle.Oracle8ModelReader.java

/**
 * {@inheritDoc}//w  w  w.j a  va 2 s.  com
 */
protected Collection readIndices(DatabaseMetaDataWrapper metaData, String tableName) throws SQLException {
    // Oracle has a bug in the DatabaseMetaData#getIndexInfo method which fails when
    // delimited identifiers are being used
    // Therefore, we're rather accessing the user_indexes table which contains the same info
    // This also allows us to filter system-generated indices which are identified by either
    // having GENERATED='Y' in the query result, or by their index names being equal to the
    // name of the primary key of the table

    final String query = "SELECT a.INDEX_NAME, a.INDEX_TYPE, a.UNIQUENESS, b.COLUMN_NAME, b.COLUMN_POSITION FROM USER_INDEXES a, USER_IND_COLUMNS b WHERE "
            + "a.TABLE_NAME=? AND a.GENERATED=? AND a.TABLE_TYPE=? AND a.TABLE_NAME=b.TABLE_NAME AND a.INDEX_NAME=b.INDEX_NAME AND "
            + "a.INDEX_NAME NOT IN (SELECT DISTINCT c.CONSTRAINT_NAME FROM USER_CONSTRAINTS c WHERE c.CONSTRAINT_TYPE=? AND c.TABLE_NAME=a.TABLE_NAME)";
    final String queryWithSchema = query.substring(0, query.length() - 1)
            + " AND c.OWNER LIKE ?) AND a.TABLE_OWNER LIKE ?";

    Map indices = new ListOrderedMap();
    PreparedStatement stmt = null;

    try {
        stmt = getConnection().prepareStatement(metaData.getSchemaPattern() == null ? query : queryWithSchema);
        stmt.setString(1, getPlatform().isDelimitedIdentifierModeOn() ? tableName : tableName.toUpperCase());
        stmt.setString(2, "N");
        stmt.setString(3, "TABLE");
        stmt.setString(4, "P");
        if (metaData.getSchemaPattern() != null) {
            stmt.setString(5, metaData.getSchemaPattern().toUpperCase());
            stmt.setString(6, metaData.getSchemaPattern().toUpperCase());
        }

        ResultSet rs = stmt.executeQuery();
        Map values = new HashMap();

        while (rs.next()) {
            values.put("INDEX_NAME", rs.getString(1));
            values.put("INDEX_TYPE", new Short(DatabaseMetaData.tableIndexOther));
            values.put("NON_UNIQUE", "UNIQUE".equalsIgnoreCase(rs.getString(3)) ? Boolean.FALSE : Boolean.TRUE);
            values.put("COLUMN_NAME", rs.getString(4));
            values.put("ORDINAL_POSITION", new Short(rs.getShort(5)));

            readIndex(metaData, values, indices);
        }
    } finally {
        closeStatement(stmt);
    }
    return indices.values();
}

From source file:org.apache.ddlutils.task.DumpMetadataTask.java

/**
 * Dumps the indexes of the indicated table.
 * // w  ww.jav a 2  s . c om
 * @param xmlWriter   The xml writer to write to
 * @param metaData    The database metadata
 * @param catalogName The catalog name
 * @param schemaName  The schema name
 * @param tableName   The table name
 */
private void dumpIndexes(PrettyPrintingXmlWriter xmlWriter, final DatabaseMetaData metaData,
        final String catalogName, final String schemaName, final String tableName) throws SQLException {
    performResultSetXmlOperation(xmlWriter, null, new ResultSetXmlOperation() {
        public ResultSet getResultSet() throws SQLException {
            return metaData.getIndexInfo(catalogName, schemaName, tableName, false, false);
        }

        public void handleRow(PrettyPrintingXmlWriter xmlWriter, ResultSet result) throws SQLException {
            Set columns = getColumnsInResultSet(result);

            xmlWriter.writeElementStart(null, "index");

            addStringAttribute(xmlWriter, "name", result, columns, "INDEX_NAME");
            addBooleanAttribute(xmlWriter, "nonUnique", result, columns, "NON_UNIQUE");
            addStringAttribute(xmlWriter, "indexCatalog", result, columns, "INDEX_QUALIFIER");
            if (columns.contains("TYPE")) {
                try {
                    switch (result.getShort("TYPE")) {
                    case DatabaseMetaData.tableIndexStatistic:
                        xmlWriter.writeAttribute(null, "type", "table statistics");
                        break;
                    case DatabaseMetaData.tableIndexClustered:
                        xmlWriter.writeAttribute(null, "type", "clustered");
                        break;
                    case DatabaseMetaData.tableIndexHashed:
                        xmlWriter.writeAttribute(null, "type", "hashed");
                        break;
                    case DatabaseMetaData.tableIndexOther:
                        xmlWriter.writeAttribute(null, "type", "other");
                        break;
                    default:
                        xmlWriter.writeAttribute(null, "type", "unknown");
                        break;
                    }
                } catch (SQLException ex) {
                    log("Could not read the TYPE value for an index of table '" + tableName
                            + "' from the result set: " + ex.getStackTrace(), Project.MSG_ERR);
                }
            }
            addStringAttribute(xmlWriter, "column", result, columns, "COLUMN_NAME");
            addShortAttribute(xmlWriter, "sequenceNumberInIndex", result, columns, "ORDINAL_POSITION");
            if (columns.contains("ASC_OR_DESC")) {
                try {
                    String value = result.getString("ASC_OR_DESC");

                    if ("A".equalsIgnoreCase(value)) {
                        xmlWriter.writeAttribute(null, "sortOrder", "ascending");
                    } else if ("D".equalsIgnoreCase(value)) {
                        xmlWriter.writeAttribute(null, "sortOrder", "descending");
                    } else {
                        xmlWriter.writeAttribute(null, "sortOrder", "unknown");
                    }
                } catch (SQLException ex) {
                    log("Could not read the ASC_OR_DESC value for an index of table '" + tableName
                            + "' from the result set: " + ex.getStackTrace(), Project.MSG_ERR);
                }
            }
            addIntAttribute(xmlWriter, "cardinality", result, columns, "CARDINALITY");
            addIntAttribute(xmlWriter, "pages", result, columns, "PAGES");
            addStringAttribute(xmlWriter, "filter", result, columns, "FILTER_CONDITION");
        }

        public void handleError(SQLException ex) {
            log("Could not read the indexes for table '" + tableName + "' from the result set: "
                    + ex.getStackTrace(), Project.MSG_ERR);
        }
    });
}

From source file:org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.java

@Override
public ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate)
        throws SQLException {
    if (unique) { // No unique indexes
        return emptyResultSet;
    }// w w w . j  av a2  s. c  o m
    StringBuilder buf = new StringBuilder("select \n" + TENANT_ID + " " + TABLE_CAT + ",\n" + // use this column for column family name
            TABLE_SCHEM + ",\n" + DATA_TABLE_NAME + " " + TABLE_NAME + ",\n" + "true NON_UNIQUE,\n"
            + "null INDEX_QUALIFIER,\n" + TABLE_NAME + " INDEX_NAME,\n" + DatabaseMetaData.tableIndexOther
            + " TYPE,\n" + ORDINAL_POSITION + ",\n" + COLUMN_NAME + ",\n" + "CASE WHEN " + COLUMN_FAMILY
            + " IS NOT NULL THEN null WHEN " + SORT_ORDER + " = " + (SortOrder.DESC.getSystemValue())
            + " THEN 'D' ELSE 'A' END ASC_OR_DESC,\n" + "null CARDINALITY,\n" + "null PAGES,\n"
            + "null FILTER_CONDITION,\n" +
            // Include data type info, though not in spec
            ExternalSqlTypeIdFunction.NAME + "(" + DATA_TYPE + ") AS " + DATA_TYPE + ",\n"
            + SqlTypeNameFunction.NAME + "(" + DATA_TYPE + ") AS " + TYPE_NAME + ",\n" + DATA_TYPE + " "
            + TYPE_ID + ",\n" + COLUMN_FAMILY + ",\n" + COLUMN_SIZE + ",\n" + ARRAY_SIZE + "\nfrom "
            + SYSTEM_CATALOG + "\nwhere ");
    buf.append(TABLE_SCHEM
            + (schema == null || schema.length() == 0 ? " is null" : " = '" + escapePattern(schema) + "'"));
    buf.append("\nand " + DATA_TABLE_NAME + " = '" + escapePattern(table) + "'");
    buf.append("\nand " + COLUMN_NAME + " is not null");
    addTenantIdFilter(buf, catalog);
    buf.append("\norder by INDEX_NAME," + ORDINAL_POSITION);
    Statement stmt = connection.createStatement();
    return stmt.executeQuery(buf.toString());
}

From source file:org.executequery.databaseobjects.impl.TableColumnIndex.java

private String translateType(Short value) {

    String translated = String.valueOf(value);
    switch (value) {
    case DatabaseMetaData.tableIndexStatistic:
        return translated + " - tableIndexStatistic";

    case DatabaseMetaData.tableIndexClustered:
        return translated + " - tableIndexClustered";

    case DatabaseMetaData.tableIndexHashed:
        return translated + " - tableIndexHashed";

    case DatabaseMetaData.tableIndexOther:
        return translated + " - tableIndexOther";
    }//from ww  w. jav  a 2s  . c o  m
    return translated;
}

From source file:org.jumpmind.db.platform.oracle.OracleDdlReader.java

@Override
protected Collection<IIndex> readIndices(Connection connection, DatabaseMetaDataWrapper metaData,
        String tableName) throws SQLException {
    // Oracle bug 4999817 causes a table analyze to execute in response to a
    // call to/*w  ww  . j a  v  a2 s.co  m*/
    // DatabaseMetaData#getIndexInfo.
    // The bug is fixed in driver version 10.2.0.4. The bug is present in at
    // least
    // driver versions 10.2.0.1.0, 10.1.0.2.0, and 9.2.0.5.
    // To avoid this bug, we will access user_indexes view.
    // This also allows us to filter system-generated indices which are
    // identified by either
    // having GENERATED='Y' in the query result, or by their index names
    // being equal to the
    // name of the primary key of the table

    StringBuilder query = new StringBuilder();

    query.append(
            "SELECT a.INDEX_NAME, a.INDEX_TYPE, a.UNIQUENESS, b.COLUMN_NAME, b.COLUMN_POSITION FROM USER_INDEXES a, USER_IND_COLUMNS b WHERE ");
    query.append(
            "a.TABLE_NAME=? AND a.GENERATED=? AND a.TABLE_TYPE=? AND a.TABLE_NAME=b.TABLE_NAME AND a.INDEX_NAME=b.INDEX_NAME AND ");
    query.append(
            "a.INDEX_NAME NOT IN (SELECT DISTINCT c.CONSTRAINT_NAME FROM USER_CONSTRAINTS c WHERE c.CONSTRAINT_TYPE=? AND c.TABLE_NAME=a.TABLE_NAME");
    if (metaData.getSchemaPattern() != null) {
        query.append(" AND c.OWNER LIKE ?) AND a.TABLE_OWNER LIKE ?");
    } else {
        query.append(")");
    }

    Map<String, IIndex> indices = new LinkedHashMap<String, IIndex>();
    PreparedStatement stmt = null;

    try {
        stmt = connection.prepareStatement(query.toString());
        stmt.setString(1, getPlatform().getDdlBuilder().isDelimitedIdentifierModeOn() ? tableName
                : tableName.toUpperCase());
        stmt.setString(2, "N");
        stmt.setString(3, "TABLE");
        stmt.setString(4, "P");
        if (metaData.getSchemaPattern() != null) {
            stmt.setString(5, metaData.getSchemaPattern().toUpperCase());
            stmt.setString(6, metaData.getSchemaPattern().toUpperCase());
        }

        ResultSet rs = stmt.executeQuery();
        Map<String, Object> values = new HashMap<String, Object>();

        while (rs.next()) {
            String name = rs.getString(1);
            String type = rs.getString(2);
            // Only read in normal oracle indexes
            if (type.startsWith("NORMAL")) {
                values.put("INDEX_TYPE", new Short(DatabaseMetaData.tableIndexOther));
                values.put("INDEX_NAME", name);
                values.put("NON_UNIQUE",
                        "UNIQUE".equalsIgnoreCase(rs.getString(3)) ? Boolean.FALSE : Boolean.TRUE);
                values.put("COLUMN_NAME", rs.getString(4));
                values.put("ORDINAL_POSITION", new Short(rs.getShort(5)));

                readIndex(metaData, values, indices);
            } else if (log.isDebugEnabled()) {
                log.debug("Skipping index " + name + " of type " + type);
            }
        }

        rs.close();
    } finally {
        if (stmt != null) {
            stmt.close();
        }
    }
    return indices.values();
}