List of usage examples for java.sql DatabaseMetaData tableIndexOther
short tableIndexOther
To view the source code for java.sql DatabaseMetaData tableIndexOther.
Click Source Link
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(); }