使用Spring的JDBC缓慢的元数据获取Oracle

问题描述：

我使用Spring JdbcUtils.extractDatabaseMetaData（）方法来分析数据库。该函数调用回调并移交 DatabaseMetaData 对象。该对象提供 getColumns（String catalog，String schemaPattern，String tableNamePattern，String columnNamePattern）。

I am using the Spring JdbcUtils.extractDatabaseMetaData() method to analyze the database. The function calls a callback and hands over a DatabaseMetaData object. This object provides the getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern).

我称之为像这样 getColumns（，TABLE_OWNER_USERNAME，null，null）并获得400列结果。这些正是我想要的结果，但请求需要1分钟。

I call it like this getColumns("",TABLE_OWNER_USERNAME,null,null) and get 400 columns as a result. These are exactly the results that I want, but the request takes over 1 minute.

我可以以某种方式优化此查询以加快速度吗？拉动400行应该在1秒而不是1分钟内完成。

Can I somehow optimize this query to be fast? Pulling 400 rows should happen in 1 seconds and not one minute.

编辑：我不怀疑Spring部分是否缓慢。更近的分析表明，获取 DatabaseMetaData 需要几秒钟的时间来执行 getColumns（）需要很长时间。

I don't suspect the Spring part being slow. Closer analysis showed that fetching the DatabaseMetaData takes a few seconds butexecuting the getColumns() takes really long.

答

也许这是查询ALL_TAB_COLUMNS的更好方法。以下是一个示例：

Maybe it's a better approach to query ALL_TAB_COLUMNS. Here is an example:

public final List<Column> getColumnsByOwner(final String owner) {
    final String sql = "SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, "
            + " DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT"
            + " FROM ALL_TAB_COLUMNS"
            + " WHERE OWNER = ? ORDER BY COLUMN_ID";

    return jdbcTemplate.query(sql,
            new Object[] { owner },
            new RowMapper<Column>() {
                @Override
                public Column mapRow(final ResultSet res, final int rowNum)
                        throws SQLException {
                    final Column reg = new Column();

                    reg.setColumnName(res.getString("COLUMN_NAME"));
                    //Read other properties
                    reg.setNullable(res.getString("NULLABLE").equals("Y"));
                    return reg;
                }
            });
}

如果您需要按表格过滤，只需添加AND TABLE_NAME =？将sql和tableName作为另一个参数。

If you need to filter by table simply add " AND TABLE_NAME = ?" to sql and tableName as another parameter.

希望它有所帮助。

使用Spring的JDBC缓慢的元数据获取Oracle

相关推荐