Java Code Examples for java.sql.DatabaseMetaData#getIndexInfo()

/**
 * Tests fix for Bug#8812, DBMD.getIndexInfo() returning inverted values for
 * 'NON_UNIQUE' column.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug8812() throws Exception {
    String tableName = "testBug8812";

    try {
        createTable(tableName, "(field1 INT, field2 INT, INDEX(field1), UNIQUE INDEX(field2))");

        DatabaseMetaData dbmd = this.conn.getMetaData();
        this.rs = dbmd.getIndexInfo(this.conn.getCatalog(), null, tableName, true, false);
        assertTrue(this.rs.next()); // there should be one row that meets
        // this requirement
        assertEquals(this.rs.getBoolean("NON_UNIQUE"), false);

        this.rs = dbmd.getIndexInfo(this.conn.getCatalog(), null, tableName, false, false);
        assertTrue(this.rs.next()); // there should be two rows that meets
        // this requirement
        assertEquals(this.rs.getBoolean("NON_UNIQUE"), false);
        assertTrue(this.rs.next());
        assertEquals(this.rs.getBoolean("NON_UNIQUE"), true);

    } finally {
        dropTable(tableName);
    }
}
 

public static void main(String[] args) throws InputException,
		InstantiationException, IllegalAccessException, SQLException {
	new Activator();

	DBSetting setting = new DBSetting("Oracle", "localhost", 1521, "XE",
			"nakajima", "nakajima", true, null, null);

	Connection con = null;
	try {
		con = setting.connect();
		DatabaseMetaData metaData = con.getMetaData();

		metaData.getIndexInfo(null, "SYS", "ALERT_QT", false, false);

	} finally {
		if (con != null) {
			con.close();
		}
	}
}
 

private void initIndexes(DatabaseMetaData meta) throws SQLException {
	ResultSet rs = null;

	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();
		}
	}
}
 

/**
 * Returns the number of rows, or -1 if unknown. Note that some database drivers returns 0,
 * so it is better to consider 0 as "unknown" too. We do not cache this count because it may
 * change at any time.
 *
 * @param  metadata     information about the database.
 * @param  approximate  whether approximate or outdated values are acceptable.
 * @return number of rows (may be approximate), or -1 if unknown.
 */
final long countRows(final DatabaseMetaData metadata, final boolean approximate) throws SQLException {
    long count = -1;
    final String[] names = TableReference.splitName(featureType.getName());
    try (ResultSet reflect = metadata.getIndexInfo(names[2], names[1], names[0], false, approximate)) {
        while (reflect.next()) {
            final long n = reflect.getLong(Reflection.CARDINALITY);
            if (!reflect.wasNull()) {
                if (reflect.getShort(Reflection.TYPE) == DatabaseMetaData.tableIndexStatistic) {
                    return n;       // "Index statistic" type provides the number of rows in the table.
                }
                if (n > count) {    // Other index types may be inaccurate.
                    count = n;
                }
            }
        }
    }
    return count;
}
 

protected final boolean columnInAnyIndex(String tablename, String colname)
        throws Exception {
    tablename = fixIdentifier(tablename);
    colname = fixIdentifier(colname);
    DatabaseMetaData md = getConnection().getMetaData();
    ResultSet rs = md.getIndexInfo(null, getSchema(), tablename, false, false);

    // printResults(rs, "columnInIndex(" + viewname + ", " + colname +
    //    ", " + indexname + ")");

    while ( rs.next() ) {
        String ixColName = rs.getString(9);
        if ( ixColName.equals(colname) ) {
            return true;
        }
    }

    return false;        
}
 

public boolean columnInIndex(Connection conn, String schemaName, String tableName, String colname, String indexName)
        throws Exception {
    DatabaseMetaData md = conn.getMetaData();
    ResultSet rs = md.getIndexInfo(null, schemaName, tableName, false, false);

    while ( rs.next() ) {
        String ixName = rs.getString(6);
        if ( ixName != null && ixName.equals(indexName)) {
            String ixColName = rs.getString(9);
            if ( ixColName.equals(colname) ) {
                return true;
            }
        }
    }

    return false;
    
}
 

private void runIndexMetaDataTests() throws SQLException {
  String currentKey = "";
  DatabaseMetaData metadata = connection.getMetaData();
  for (String table : TABLES) {
    int columnIndex = 0;
    try (ResultSet indexes = metadata.getIndexInfo("", "", table, false, false)) {
      while (indexes.next()) {
        String key = indexes.getString("TABLE_NAME") + "." + indexes.getString("INDEX_NAME");
        if (!currentKey.equals(key)) {
          columnIndex = 0;
          currentKey = key;
        }
        String[] columns = INDEX_COLUMNS.get(key);
        Boolean unique = INDEX_UNIQUE.get(key);
        Assert.assertNotNull(columns);
        assertEquals(columns[columnIndex], indexes.getString("COLUMN_NAME"));
        assertEquals(unique, !indexes.getBoolean("NON_UNIQUE"));
        columnIndex++;
      }
    }
  }
}
 

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;

  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;
}
 

/**
 * Test getIndexInfo further; does modify database
 * @throws SQLException
 */
public void testMoreGetIndexInfo() throws SQLException
{
    // test to see that we are correctly returning D for ASC_OR_DESC.
    // As Derby only supports tableIndexHashed Type, and
    // CARDINALITY, PAGES, nor FILTER_CONDITION get set, no further
    // tests seem necessary.
    Statement st = createStatement();

    // First, create the test table and indexes/keys
    st.execute("create table iit (i int not null, j int)");
    st.execute("create unique index iii on iit(i asc, j desc)");
    DatabaseMetaData dmd = getDMD();
    ResultSet rs = dmd.getIndexInfo("","APP","IIT",false,false);
    rs.next();
    if (rs != null)
        assertEquals("A",rs.getString(10));
    rs.next();
    if (rs != null)
        assertEquals("D",rs.getString(10));

    rs = getIndexInfoODBC("","APP","IIT",false,false);
    rs.next();
    if (rs != null)
        assertEquals("A",rs.getString(10));
    rs.next();
    if (rs != null)
        assertEquals("D",rs.getString(10));

    st.execute("drop index iii");
    st.execute("drop table iit");

    st.close();
}
 

private List<DataSetIndex> getActualIndexes(final Connection connection, final String tableName) throws SQLException {
    DatabaseMetaData metaData = connection.getMetaData();
    try (ResultSet resultSet = metaData.getIndexInfo(null, null, tableName, false, false)) {
        List<DataSetIndex> result = new LinkedList<>();
        while (resultSet.next()) {
            DataSetIndex each = new DataSetIndex();
            each.setName(resultSet.getString("INDEX_NAME"));
            each.setUnique(!resultSet.getBoolean("NON_UNIQUE"));
            each.setColumns(resultSet.getString("COLUMN_NAME"));
            result.add(each);
        }
        return result;
    }
}
 

protected final boolean indexIsUnique(String tablename, String indexName)
        throws Exception {
    tablename = fixIdentifier(tablename);
    indexName = fixIdentifier(indexName);
    DatabaseMetaData md = getConnection().getMetaData();
    ResultSet rs = md.getIndexInfo(null, getSchema(), tablename, false, false);
    
    // TODO - Parse results
    
    rs.close();
    return true;
}
 

private void createTaskIdIndexIfNeeded(final Connection connection) throws SQLException {
    DatabaseMetaData dbMetaData = connection.getMetaData();
    try (ResultSet resultSet = dbMetaData.getIndexInfo(connection.getCatalog(), null, TABLE_JOB_STATUS_TRACE_LOG, false, false)) {
        boolean hasTaskIdIndex = false;
        while (resultSet.next()) {
            if (TASK_ID_STATE_INDEX.equals(resultSet.getString("INDEX_NAME"))) {
                hasTaskIdIndex = true;
            }
        }
        if (!hasTaskIdIndex) {
            createTaskIdAndStateIndex(connection);
        }
    }
}
 

/**
 * Helper method for testing getIndexInfo - calls dmd.getIndexInfo for the
 * JDBC call, and getIndexInfoODBC for the ODBC procedure
 * @throws SQLException
 */
private ResultSet[] getIndexInfo(String catalog, String schema, String table,
    boolean unique, boolean approximate) throws SQLException
{
    ResultSet[] rss = new ResultSet[2];
    DatabaseMetaData dmd = getDMD();
    rss[0]= dmd.getIndexInfo(catalog, schema, table, unique, approximate);
    rss[1]= getIndexInfoODBC(catalog, schema, table, unique, approximate);

    String [] columnNames = {"TABLE_CAT","TABLE_SCHEM","TABLE_NAME",
            "NON_UNIQUE","INDEX_QUALIFIER","INDEX_NAME","TYPE",
            "ORDINAL_POSITION","COLUMN_NAME","ASC_OR_DESC","CARDINALITY",
            "PAGES","FILTER_CONDITION"};
    int [] columnTypes = {
            Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,
            Types.BOOLEAN,Types.VARCHAR,Types.VARCHAR,Types.SMALLINT,
            // ASC_OR_DESC is Types.CHAR rather than VARCHAR...
            Types.SMALLINT,Types.VARCHAR,Types.CHAR,Types.INTEGER,
            Types.INTEGER,Types.VARCHAR};

    // types.boolean is not supported with networkserver
    if (usingDerbyNetClient())
      columnTypes[4 - 1] = Types.SMALLINT;

    boolean [] nullability = {false,false,false,
        false,false,true,true,true,false,false,true,true,true};

    // JDBC result set
    assertMetaDataResultSet(rss[0], columnNames, columnTypes, nullability);

    // Change shape for ODBC.
    columnTypes[4 - 1] = Types.SMALLINT; // types.boolean is not supported with ODBC

    // ODBC result set
    assertMetaDataResultSet(rss[1], columnNames, columnTypes, nullability);

    return rss;
}
 

@Override
public Set<String> withHandle(final Handle handle) throws Exception {
    try (Connection con = handle.getConnection()) {
        final DatabaseMetaData metaData = con.getMetaData();
        try (ResultSet rs = metaData.getIndexInfo(con.getCatalog(), con.getSchema(), "JSONDB", false, false)) {
            final Set<String> indexes = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
            while (rs.next()) {
                final String indexName = rs.getString("INDEX_NAME");
                indexes.add(indexName);
            }

            return indexes;
        }
    }
}
 

private static Set<String> extractDatabaseIndices(DatabaseMetaData databaseMetaData)
    throws SQLException {
  HashSet<String> result = new HashSet<>();
  ResultSet indexResultSet = databaseMetaData.getIndexInfo(
      null, null, null, false, false);

  while (indexResultSet.next()) {
    String name = indexResultSet.getString("INDEX_NAME");
    result.add(name);
  }
  indexResultSet.close();

  return result;
}
 

private static Map<String, metaColumn> getPrimaryKeysAccess( Map<String, metaColumn> HT, DatabaseMetaData metaData, String _dbInst, String _tableName ){
	ResultSet RES;
	
	try{
		RES	= metaData.getIndexInfo( null, null, _tableName, false, false );
		metaColumn mC;
		while ( RES.next() ){
			String columnName = RES.getString( "COLUMN_NAME" );
			String indexName 	= RES.getString( "INDEX_NAME" );
			
			if ( columnName == null )
				continue;
			
			if ( HT.containsKey( columnName ) ){
				mC =	HT.get( columnName );
			}else{
				mC	= new metaColumn();
				mC.NAME					= columnName;
				mC.DEFAULTVALUE	= "";
				HT.put( columnName, mC );
			}

			// The check for indexName.startsWith( "Index_" ) was added to fix bug 1193 for
			// BD/Java connected to an Access database.
			if ( ( indexName != null ) &&
			     ( indexName.equals( "PrimaryKey" ) || indexName.startsWith( "Index_" ) ) )
				mC.PRIMARYKEY	= true;
		}
		RES.close();
		
		return HT;
	}catch(Exception E){
		com.nary.Debug.printStackTrace( E );
		return null;
	}
}
 

/**
 * WL#6555 - Online rename index
 * 
 * ALTER TABLE syntax changed in 5.7.1
 * 
 * Alter table allows to rename indexes. ALTER TABLE ... RENAME INDEX x TO y
 * 
 * @throws SQLException
 */
public void testRenameIndex() throws Exception {
    if (!versionMeetsMinimum(5, 7, 1)) {
        return;
    }

    createTable("testRenameIndex", "(col1 INT, col2 INT, INDEX (col1)) ENGINE=InnoDB");
    this.stmt.execute("CREATE INDEX testIdx ON testRenameIndex (col2)");

    DatabaseMetaData dbmd = this.conn.getMetaData();

    this.rs = dbmd.getIndexInfo(this.dbName, null, "testRenameIndex", false, true);
    assertTrue("Expected 1 (of 2) indexes.", this.rs.next());
    assertEquals("Wrong index name for table 'testRenameIndex'.", "col1", this.rs.getString(6));
    assertTrue("Expected 2 (of 2) indexes.", this.rs.next());
    assertEquals("Wrong index name for table 'testRenameIndex'.", "testIdx", this.rs.getString(6));
    assertFalse("No more indexes expected for table 'testRenameIndex'.", this.rs.next());

    this.stmt.execute("ALTER TABLE testRenameIndex RENAME INDEX col1 TO col1Index");
    this.stmt.execute("ALTER TABLE testRenameIndex RENAME INDEX testIdx TO testIndex");

    this.rs = dbmd.getIndexInfo(this.dbName, null, "testRenameIndex", false, true);
    assertTrue("Expected 1 (of 2) indexes.", this.rs.next());
    assertEquals("Wrong index name for table 'testRenameIndex'.", "col1Index", this.rs.getString(6));
    assertTrue("Expected 2 (of 2) indexes.", this.rs.next());
    assertEquals("Wrong index name for table 'testRenameIndex'.", "testIndex", this.rs.getString(6));
    assertFalse("No more indexes expected for table 'testRenameIndex'.", this.rs.next());
}
 

/**
 * WL#6555 - Online rename index
 * 
 * ALTER TABLE syntax changed in 5.7.1
 * 
 * Alter table allows to rename indexes. ALTER TABLE ... RENAME INDEX x TO y
 * 
 * @throws SQLException
 */
public void testRenameIndex() throws Exception {

    if (!versionMeetsMinimum(5, 7, 1)) {
        return;
    }

    createTable("testRenameIndex", "(col1 INT, col2 INT, INDEX (col1)) ENGINE=InnoDB");
    this.stmt.execute("CREATE INDEX testIdx ON testRenameIndex (col2)");

    DatabaseMetaData dbmd = this.conn.getMetaData();

    this.rs = dbmd.getIndexInfo(null, null, "testRenameIndex", false, true);
    assertTrue("Expected 1 (of 2) indexes.", this.rs.next());
    assertEquals("Wrong index name for table 'testRenameIndex'.", "col1", this.rs.getString(6));
    assertTrue("Expected 2 (of 2) indexes.", this.rs.next());
    assertEquals("Wrong index name for table 'testRenameIndex'.", "testIdx", this.rs.getString(6));
    assertFalse("No more indexes expected for table 'testRenameIndex'.", this.rs.next());

    this.stmt.execute("ALTER TABLE testRenameIndex RENAME INDEX col1 TO col1Index");
    this.stmt.execute("ALTER TABLE testRenameIndex RENAME INDEX testIdx TO testIndex");

    this.rs = dbmd.getIndexInfo(null, null, "testRenameIndex", false, true);
    assertTrue("Expected 1 (of 2) indexes.", this.rs.next());
    assertEquals("Wrong index name for table 'testRenameIndex'.", "col1Index", this.rs.getString(6));
    assertTrue("Expected 2 (of 2) indexes.", this.rs.next());
    assertEquals("Wrong index name for table 'testRenameIndex'.", "testIndex", this.rs.getString(6));
    assertFalse("No more indexes expected for table 'testRenameIndex'.", this.rs.next());
}
 

/**
 * WL#411 - Generated columns.
 * 
 * Test for new syntax and support in DatabaseMetaData.getColumns().
 * 
 * New syntax for CREATE TABLE, introduced in MySQL 5.7.6:
 * -col_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment] [[NOT] NULL] [[PRIMARY] KEY]
 */
public void testGeneratedColumns() throws Exception {
    if (!versionMeetsMinimum(5, 7, 6)) {
        return;
    }

    // Test GENERATED columns syntax.
    createTable("pythagorean_triple", "(side_a DOUBLE NULL, side_b DOUBLE NULL, "
            + "side_c_vir DOUBLE AS (SQRT(side_a * side_a + side_b * side_b)) VIRTUAL UNIQUE KEY COMMENT 'hypotenuse - virtual', "
            + "side_c_sto DOUBLE GENERATED ALWAYS AS (SQRT(POW(side_a, 2) + POW(side_b, 2))) STORED UNIQUE KEY COMMENT 'hypotenuse - stored' NOT NULL "
            + "PRIMARY KEY)");

    // Test data for generated columns.
    assertEquals(1, this.stmt.executeUpdate("INSERT INTO pythagorean_triple (side_a, side_b) VALUES (3, 4)"));
    this.rs = this.stmt.executeQuery("SELECT * FROM pythagorean_triple");
    assertTrue(this.rs.next());
    assertEquals(3d, this.rs.getDouble(1));
    assertEquals(4d, this.rs.getDouble(2));
    assertEquals(5d, this.rs.getDouble(3));
    assertEquals(5d, this.rs.getDouble(4));
    assertEquals(3d, this.rs.getDouble("side_a"));
    assertEquals(4d, this.rs.getDouble("side_b"));
    assertEquals(5d, this.rs.getDouble("side_c_sto"));
    assertEquals(5d, this.rs.getDouble("side_c_vir"));
    assertFalse(this.rs.next());

    Properties props = new Properties();
    props.setProperty(PropertyKey.nullCatalogMeansCurrent.getKeyName(), "true");

    for (String useIS : new String[] { "false", "true" }) {
        Connection testConn = null;
        props.setProperty(PropertyKey.useInformationSchema.getKeyName(), useIS);

        testConn = getConnectionWithProps(props);
        DatabaseMetaData dbmd = testConn.getMetaData();

        String test = "Case [" + props.toString() + "]";

        // Test columns metadata.
        this.rs = dbmd.getColumns(null, null, "pythagorean_triple", "%");
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_a", this.rs.getString("COLUMN_NAME"));
        assertEquals(test, "YES", this.rs.getString("IS_NULLABLE"));
        assertEquals(test, "NO", this.rs.getString("IS_AUTOINCREMENT"));
        assertEquals(test, "NO", this.rs.getString("IS_GENERATEDCOLUMN"));
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_b", this.rs.getString("COLUMN_NAME"));
        assertEquals(test, "YES", this.rs.getString("IS_NULLABLE"));
        assertEquals(test, "NO", this.rs.getString("IS_AUTOINCREMENT"));
        assertEquals(test, "NO", this.rs.getString("IS_GENERATEDCOLUMN"));
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_c_vir", this.rs.getString("COLUMN_NAME"));
        assertEquals(test, "YES", this.rs.getString("IS_NULLABLE"));
        assertEquals(test, "NO", this.rs.getString("IS_AUTOINCREMENT"));
        assertEquals(test, "YES", this.rs.getString("IS_GENERATEDCOLUMN"));
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_c_sto", this.rs.getString("COLUMN_NAME"));
        assertEquals(test, "NO", this.rs.getString("IS_NULLABLE"));
        assertEquals(test, "NO", this.rs.getString("IS_AUTOINCREMENT"));
        assertEquals(test, "YES", this.rs.getString("IS_GENERATEDCOLUMN"));
        assertFalse(test, this.rs.next());

        // Test primary keys metadata.
        this.rs = dbmd.getPrimaryKeys(null, null, "pythagorean_triple");
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_c_sto", this.rs.getString("COLUMN_NAME"));
        assertEquals(test, "PRIMARY", this.rs.getString("PK_NAME"));
        assertFalse(test, this.rs.next());

        // Test indexes metadata.
        this.rs = dbmd.getIndexInfo(null, null, "pythagorean_triple", false, true);
        assertTrue(test, this.rs.next());
        assertEquals(test, "PRIMARY", this.rs.getString("INDEX_NAME"));
        assertEquals(test, "side_c_sto", this.rs.getString("COLUMN_NAME"));
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_c_sto", this.rs.getString("INDEX_NAME"));
        assertEquals(test, "side_c_sto", this.rs.getString("COLUMN_NAME"));
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_c_vir", this.rs.getString("INDEX_NAME"));
        assertEquals(test, "side_c_vir", this.rs.getString("COLUMN_NAME"));
        assertFalse(test, this.rs.next());

        testConn.close();
    }
}
 

private static ResultSet getMetaDataIndexInfo(Connection connection, String tableName)
        throws SQLException {
    DatabaseMetaData metaData = connection.getMetaData();
    return metaData.getIndexInfo(null, null, convert(metaData, tableName), false, false);
}