Example usage for java.sql DatabaseMetaData getIndexInfo

List of usage examples for java.sql DatabaseMetaData getIndexInfo

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. DatabaseMetaData
  6. 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:org.ramadda.repository.database.DatabaseManager.java

/**
 * _more_//from  w  w w  .ja  va2 s  .c o m
 *
 * @param connection _more_
 * @param all _more_
 *
 * @return _more_
 *
 * @throws Exception _more_
 */
public List<TableInfo> getTableInfos(Connection connection, boolean all) throws Exception {

    DatabaseMetaData dbmd = connection.getMetaData();
    ResultSet tables = dbmd.getTables(null, null, null, new String[] { "TABLE" });

    ResultSetMetaData rsmd = tables.getMetaData();
    for (int col = 1; col <= rsmd.getColumnCount(); col++) {
        //                System.err.println (rsmd.getColumnName(col));
    }
    List<TableInfo> tableInfos = new ArrayList<TableInfo>();
    HashSet<String> seen = new HashSet<String>();

    while (tables.next()) {
        String tableName = tables.getString("TABLE_NAME");
        String tn = tableName.toLowerCase();

        if (tn.equals("participant")) {
            //a hack due to some old bad derby db I have
            continue;
        }

        //Just in case
        if (seen.contains(tn)) {
            System.err.println("Warning: duplicate table:" + tableName);

            continue;
        }
        seen.add(tn);

        boolean ok = true;
        for (TypeHandler typeHandler : getRepository().getTypeHandlers()) {
            if (!typeHandler.shouldExportTable(tn)) {
                ok = false;

                break;
            }
        }

        if (!ok) {
            continue;
        }
        String tableType = tables.getString("TABLE_TYPE");

        if ((tableType == null) || tableType.startsWith("SYSTEM") || Misc.equals(tableType, "INDEX")) {
            continue;
        }

        ResultSet indices = dbmd.getIndexInfo(null, null, tableName, false, false);
        List<IndexInfo> indexList = new ArrayList<IndexInfo>();
        while (indices.next()) {
            indexList.add(new IndexInfo(indices.getString("INDEX_NAME"), indices.getString("COLUMN_NAME")));

        }

        ResultSet cols = dbmd.getColumns(null, null, tableName, null);
        rsmd = cols.getMetaData();
        List<ColumnInfo> columns = new ArrayList<ColumnInfo>();
        while (cols.next()) {
            String colName = cols.getString("COLUMN_NAME");
            int type = cols.getInt("DATA_TYPE");
            String typeName = cols.getString("TYPE_NAME");
            int size = cols.getInt("COLUMN_SIZE");
            if (type == -1) {
                if (typeName.toLowerCase().equals("mediumtext")) {
                    type = java.sql.Types.CLOB;
                    //Just come up with some size

                    if (size <= 0) {
                        size = 36000;
                    }
                } else if (typeName.toLowerCase().equals("longtext")) {
                    type = java.sql.Types.CLOB;
                    //Just come up with some size
                    if (size <= 0) {
                        size = 36000;
                    }
                }
            }
            if (typeName.equalsIgnoreCase("text")) {
                if (size <= 0) {
                    size = 36000;
                }
            }

            columns.add(new ColumnInfo(colName, typeName, type, size));
            if (tn.indexOf("wiki") >= 0) {
                System.err.println("COLS:" + columns);
            }
        }

        tableInfos.add(new TableInfo(tn, indexList, columns));
    }

    return tableInfos;

}

From source file:org.seasar.dbflute.logic.jdbc.metadata.basic.DfIndexExtractor.java

public static ResultSet delegateExtractIndexInfoMetaData(DatabaseMetaData metaData, UnifiedSchema unifiedSchema,
        String tableName, boolean uniqueKeyOnly, boolean retry, DfDatabaseTypeFacadeProp prop)
        throws SQLException {
    final String catalogName = unifiedSchema.getPureCatalog();
    final String schemaName = unifiedSchema.getPureSchema();
    try {/* w w  w .  ja  v a 2  s  .  co m*/
        return metaData.getIndexInfo(catalogName, schemaName, tableName, uniqueKeyOnly, true);
    } catch (SQLException e) {
        if (prop.isDatabaseOracle() && !Srl.isQuotedDouble(tableName)) {
            // Oracle JDBC Driver does not allow Japanese table names
            // about index info so retry it with quoted here
            // (however PK, FK are allowed about it...)
            final String quoted = Srl.quoteDouble(tableName);
            try {
                return metaData.getIndexInfo(catalogName, schemaName, quoted, uniqueKeyOnly, true);
            } catch (SQLException ignored) {
            }
        }
        if (retry) {
            // because the exception may be thrown when the table is not found
            // (for example, Sybase)
            return null;
        } else {
            throw e;
        }
    }
}

From source file:ro.nextreports.designer.dbviewer.DefaultDBViewer.java

public DBInfo getDBInfo(String schemaName, int mask, Connection con) throws NextSqlException {

    String info = "";
    List<String> keywords = new ArrayList<String>();
    List<DBTable> tables = new ArrayList<DBTable>();
    List<DBProcedure> procedures = new ArrayList<DBProcedure>();
    Dialect dialect;//from w w  w .  ja  v a 2  s  .c  o m

    try {
        dialect = DialectUtil.getDialect(con);
    } catch (Exception ex) {
        ex.printStackTrace();
        throw new NextSqlException("Could not get Dialect.", ex);
    }

    try {
        DatabaseMetaData dbmd = con.getMetaData();

        if ((mask & DBInfo.INFO) == DBInfo.INFO) {
            StringBuffer sb = new StringBuffer();
            sb.append(I18NSupport.getString("database.product")).append(dbmd.getDatabaseProductName())
                    .append("\r\n");
            sb.append(I18NSupport.getString("database.product.version"))
                    .append(dbmd.getDatabaseProductVersion()).append("\r\n");
            sb.append(I18NSupport.getString("database.driver.name")).append(dbmd.getDriverName())
                    .append("\r\n");
            sb.append(I18NSupport.getString("database.driver.version")).append(dbmd.getDriverVersion())
                    .append("\r\n");
            info = sb.toString();
        }

        if ((mask & DBInfo.SUPPORTED_KEYWORDS) == DBInfo.SUPPORTED_KEYWORDS) {
            StringTokenizer st = new StringTokenizer(dbmd.getSQLKeywords(), ",");
            while (st.hasMoreTokens()) {
                keywords.add(st.nextToken());
            }
        }

        // Get a ResultSet that contains all of the tables in this database
        // We specify a table_type of "TABLE" to prevent seeing system tables,
        // views and so forth
        boolean tableMask = ((mask & DBInfo.TABLES) == DBInfo.TABLES);
        boolean viewMask = ((mask & DBInfo.VIEWS) == DBInfo.VIEWS);
        if (tableMask || viewMask) {
            String[] tableTypes;
            if (tableMask && viewMask) {
                tableTypes = new String[] { "TABLE", "VIEW" };
            } else if (tableMask) {
                tableTypes = new String[] { "TABLE" };
            } else {
                tableTypes = new String[] { "VIEW" };
            }

            String pattern = tableMask ? Globals.getTableNamePattern() : Globals.getViewNamePattern();
            ResultSet allTables = dbmd.getTables(null, schemaName, pattern, tableTypes);
            try {
                while (allTables.next()) {
                    String table_name = allTables.getString("TABLE_NAME");
                    String table_type = allTables.getString("TABLE_TYPE");

                    // discard recycle bin tables
                    String ignoreTablePrefix = dialect.getRecycleBinTablePrefix();
                    if ((table_name == null)
                            || ((ignoreTablePrefix != null) && table_name.startsWith(ignoreTablePrefix))) {
                        continue;
                    }

                    if ((mask & DBInfo.INDEXES) == DBInfo.INDEXES) {
                        ResultSet indexList = null;
                        try {
                            // Get a list of all the indexes for this table
                            indexList = dbmd.getIndexInfo(null, schemaName, table_name, false, false);
                            List<DBIndex> indexes = new ArrayList<DBIndex>();
                            while (indexList.next()) {
                                String index_name = indexList.getString("INDEX_NAME");
                                String column_name = indexList.getString("COLUMN_NAME");
                                if (!index_name.equals("null")) {
                                    DBIndex index = new DBIndex(index_name, column_name);
                                    indexes.add(index);
                                }
                            }
                            DBTable table = new DBTable(schemaName, table_name, table_type, indexes);
                            tables.add(table);

                        } catch (SQLException e) {
                            throw new NextSqlException("SQL Exception: " + e.getMessage(), e);
                        } finally {
                            closeResultSet(indexList);
                        }

                    } else {
                        DBTable table = new DBTable(schemaName, table_name, table_type);
                        tables.add(table);
                    }
                }
            } catch (SQLException e) {
                throw new NextSqlException("SQL Exception: " + e.getMessage(), e);
            } finally {
                closeResultSet(allTables);
            }

        }

        boolean procedureMask = ((mask & DBInfo.PROCEDURES) == DBInfo.PROCEDURES);
        if (procedureMask) {
            String pattern = Globals.getProcedureNamePattern();
            if (pattern == null) {
                pattern = "%";
            }
            ResultSet rs = dbmd.getProcedures(null, schemaName, pattern);
            try {
                while (rs.next()) {
                    String spName = rs.getString("PROCEDURE_NAME");
                    int spType = rs.getInt("PROCEDURE_TYPE");
                    String catalog = rs.getString("PROCEDURE_CAT");
                    //                        System.out.println("Stored Procedure Name: " + spName);
                    //                        if (spType == DatabaseMetaData.procedureReturnsResult) {
                    //                            System.out.println("procedure Returns Result");
                    //                        } else if (spType == DatabaseMetaData.procedureNoResult) {
                    //                            System.out.println("procedure No Result");
                    //                        } else {
                    //                            System.out.println("procedure Result unknown");
                    //                        }
                    procedures.add(new DBProcedure(schemaName, catalog, spName, spType));
                }
            } catch (SQLException e) {
                throw new NextSqlException("SQL Exception: " + e.getMessage(), e);
            } finally {
                closeResultSet(rs);
            }
        }

    } catch (SQLException e) {
        LOG.error(e.getMessage(), e);
        e.printStackTrace();
        throw new NextSqlException("SQL Exception: " + e.getMessage(), e);
    }

    return new DBInfo(info, tables, procedures, keywords);
}

From source file:ro.nextreports.designer.dbviewer.DefaultDBViewer.java

public List<DBColumn> getColumns(String schema, String table)
        throws NextSqlException, MalformedTableNameException {

    Connection con;/*  w  ww  . j  a  v  a  2 s.  c om*/
    List<DBColumn> columns = new ArrayList<DBColumn>();
    String schemaName;
    String escapedTableName;
    try {
        con = Globals.getConnection();
        if (schema == null) {
            schemaName = Globals.getConnection().getMetaData().getUserName();
        } else {
            schemaName = schema;
        }

        Dialect dialect = Globals.getDialect();
        if (dialect.isKeyWord(table)) {
            escapedTableName = dialect.getEscapedKeyWord(table);
        } else {
            escapedTableName = table;
        }

    } catch (Exception e) {
        throw new NextSqlException("Could not retrieve connection.", e);
    }

    ResultSet rs = null;
    Statement stmt = null;
    List<String> keyColumns = new ArrayList<String>();
    try {
        // primary keys
        DatabaseMetaData dbmd = con.getMetaData();
        rs = dbmd.getPrimaryKeys(null, schemaName, table);
        while (rs.next()) {
            keyColumns.add(rs.getString("COLUMN_NAME"));
        }
        closeResultSet(rs);

        // foreign keys
        rs = dbmd.getImportedKeys(null, schemaName, table);
        List<String> foreignColumns = new ArrayList<String>();
        HashMap<String, DBForeignColumnInfo> fkMap = new HashMap<String, DBForeignColumnInfo>();
        while (rs.next()) {
            String fkSchema = rs.getString("FKTABLE_SCHEM");
            String fkTable = rs.getString("FKTABLE_NAME");
            String fkColumn = rs.getString("FKCOLUMN_NAME");
            String pkSchema = rs.getString("PKTABLE_SCHEM");
            String pkTable = rs.getString("PKTABLE_NAME");
            String pkColumn = rs.getString("PKCOLUMN_NAME");
            DBForeignColumnInfo fkInfo = new DBForeignColumnInfo(fkSchema, fkTable, fkColumn, pkSchema, pkTable,
                    pkColumn);
            //System.out.println("fkInfo :  " + fkInfo);
            foreignColumns.add(fkColumn);
            fkMap.put(fkColumn, fkInfo);
        }
        closeResultSet(rs);

        // column names with index
        rs = dbmd.getIndexInfo(null, schemaName, table, false, true);
        List<String> indexes = new ArrayList<String>();
        while (rs.next()) {
            String indexName = rs.getString(9);
            if (indexName != null) {
                indexes.add(indexName);
            }
        }
        closeResultSet(rs);

        DataSource ds = DefaultDataSourceManager.getInstance().getConnectedDataSource();
        String header = "";
        stmt = con.createStatement();
        try {
            // workaround if a table name contains spaces
            if (escapedTableName.indexOf(" ") != -1) {
                escapedTableName = "\"" + escapedTableName + "\"";
            }
            String prefix = "";
            if (!NO_SCHEMA_NAME.equals(schemaName)) {
                prefix = schemaName;
            }
            if (prefix.indexOf(" ") != -1) {
                prefix = "\"" + prefix + "\"";
            }
            if (!"".equals(prefix)) {
                prefix = prefix + ".";
            }

            if (ds.getDriver().equals(CSVDialect.DRIVER_CLASS)) {
                header = (String) ds.getProperties().get("headerline");
                if (header == null) {
                    header = "";
                }
            }
            if (header.isEmpty()) {
                String s = "SELECT * FROM " + prefix + escapedTableName + " WHERE 1 = 0";
                LOG.info("getColumns[ " + s + "]");
                rs = stmt.executeQuery(s);
            }

        } catch (SQLException e) {
            e.printStackTrace();
            throw new MalformedTableNameException(e);
        }

        if (header.isEmpty()) {
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            for (int col = 1; col <= columnCount; col++) {
                String name = rsmd.getColumnLabel(col);
                int length = rsmd.getColumnDisplaySize(col);
                int precision = rsmd.getPrecision(col);
                int scale = rsmd.getScale(col);
                boolean isPrimaryKey = false;
                boolean isForeignKey = false;
                boolean isIndex = false;
                if (keyColumns.contains(name)) {
                    isPrimaryKey = true;
                }
                DBForeignColumnInfo fkInfo = null;
                if (foreignColumns.contains(name)) {
                    isForeignKey = true;
                    fkInfo = fkMap.get(name);
                }
                if (indexes.contains(name)) {
                    isIndex = true;
                }
                DBColumn column = new DBColumn(schemaName, table, name, rsmd.getColumnTypeName(col),
                        isPrimaryKey, isForeignKey, isIndex, fkInfo, length, precision, scale);
                columns.add(column);
            }
        } else {
            String columnTypes = (String) ds.getProperties().get("columnTypes");
            String[] names = header.split(",");
            String[] types = new String[names.length];
            for (int i = 0; i < types.length; i++) {
                types[i] = "String";
            }
            if ((columnTypes != null) && !columnTypes.isEmpty()) {
                types = columnTypes.split(",");
            }
            for (int i = 0; i < names.length; i++) {
                DBColumn column = new DBColumn(schemaName, table, names[i], types[i], false, false, false, null,
                        20, 0, 0);
                columns.add(column);
            }

        }
    } catch (SQLException e) {
        LOG.error(e.getMessage(), e);
        e.printStackTrace();
        throw new NextSqlException("SQL Exception: " + e.getMessage(), e);
    } finally {
        closeResultSet(rs);
        closeStatement(stmt);
    }
    return columns;
}

From source file:ua.utility.kfsdbupgrade.App.java

/**
 * Constructs a tables {@link TableIndexInfo} from the
 * {@link DatabaseMetaData} for a specific table name <code>tname</code>.
 * //from  ww  w.  j a  v a2s . co  m
 * @param dmd
 *            {@link DatabaseMetaData} describing the database
 * @param tname
 *            {@link String} of the table name to construct the
 *            {@link TableIndexInfo} for
 * @return {@link TableIndexInfo} for the table with the given table name
 *         <code>tname</code>
 * @throws Exception
 *             Any {@link Exception}s encountered will be rethrown
 */
private TableIndexInfo loadTableIndexInfo(DatabaseMetaData dmd, String tname) throws Exception {
    TableIndexInfo retval = new TableIndexInfo(tname);
    ResultSet res = null;

    try {
        Map<String, IndexInfo> imap = new HashMap<String, IndexInfo>();

        res = dmd.getIndexInfo(null, getSchema(), tname, false, true);

        while (res.next()) {
            /*
             * INDEX_NAME String => index name; null when TYPE is
             * tableIndexStatistic
             */
            String iname = res.getString(6);

            if (iname != null) {
                /*
                 * COLUMN_NAME String => column name; null when TYPE is
                 * tableIndexStatistic
                 */
                String cname = res.getString(9);

                IndexInfo i = imap.get(iname);

                if (i == null) {
                    imap.put(iname, i = new IndexInfo(iname));
                }

                i.addColumn(cname);
            }
        }

        retval.getIndexes().addAll(imap.values());

        for (IndexInfo i : retval.getIndexes()) {
            String indexName = i.getIndexName();

            int indx = 1;
            for (int j = indexName.length() - 1; j >= 0; --j) {
                if (!Character.isDigit(indexName.charAt(j))) {
                    try {
                        indx = Integer.parseInt(indexName.substring(j + 1));
                    } catch (NumberFormatException ex) {
                    }
                    ;

                    break;
                }
            }

            if (retval.getMaxIndexSuffix() < indx) {
                retval.setMaxIndexSuffix(indx);
            }
        }
    } finally {
        closeDbObjects(null, null, res);
    }

    return retval;
}

From source file:uk.ac.ed.epcc.webapp.model.data.Repository.java

synchronized private void setIndexes() {
    try {/*from  ww w. jav a 2s .  co  m*/
        Map<String, IndexInfo> result = new LinkedHashMap<String, Repository.IndexInfo>();
        Connection c = getContext().getService(DatabaseService.class).getSQLContext().getConnection();
        DatabaseMetaData md = c.getMetaData();
        ResultSet rs = md.getIndexInfo(null, null, table_name, false, true);
        while (rs.next()) {
            String name = rs.getString("INDEX_NAME");
            if (!name.equals("PRIMARY")) {
                boolean unique = !rs.getBoolean("NON_UNIQUE");
                IndexInfo info = result.get(name);
                if (info == null) {
                    info = new IndexInfo(name, unique);
                    result.put(name, info);
                }
                int pos = rs.getInt("ORDINAL_POSITION");
                String col = rs.getString("COLUMN_NAME");
                info.addCol(pos, col);
            }

        }

        indexes = result;
    } catch (SQLException e) {
        throw new DataError("Error getting index names", e);
    }
}