如何从 JDBC 检索序列元数据？

Question

我正在尝试从 Java 代码（使用基本 JDBC）检索 Oracle DB 的不同类型的元数据。例如，如果我想检索带有 _FOO 后缀的表列表，我可以执行以下操作：

Connection connection = dataSource.getConnection();
DatabaseMetaData meta = connection.getMetaData();
ResultSet tables = meta.getTables(connection.getCatalog(), null, "%_FOO", new String[] { "TABLE" });
// Iterate on the ResultSet to get information on tables...

现在，我想从数据库中检索所有序列（例如例如所有名为 S_xxx_FOO 的序列）。

我该怎么做，因为我在 DatabaseMetaData 与序列相关？

我是否必须运行 select * from user_sequences 这样的查询？

Answer 1

有同样的问题。这相当容易。只需将“SEQUENCE”传递到 getMetaData().getTables() 类型参数中即可。

在您的具体情况下，它会是这样的：

meta.getTables(connection.getCatalog(), null, "%_FOO", new String[] { "SEQUENCE" });

Answer 2

您无法通过 JDBC API 执行此操作，因为某些数据库（仍然）不支持序列。

获取它们的唯一方法是查询 DBMS 的系统目录（我猜您提到的 user_sequences 是 Oracle）

Answer 3

鉴于最新版本的 Oracle JDBC 驱动程序（例如 12.1.0.2）不返回序列信息< /a> 当您调用 DatabaseMetaData#g​​etTables，其中 types 设置为 ["SEQUENCE"]，最好的办法是自己运行必要的查询，例如：

  SELECT o.owner AS sequence_owner,
       o.object_name AS sequence_name
  FROM all_objects o
  WHERE o.owner LIKE 'someOwnerPattern' ESCAPE '/'
    AND o.object_name LIKE 'someNamePattern' ESCAPE '/'
    AND o.object_type = 'SEQUENCE'
  ORDER BY 1, 2

... 其中 someOwnerPattern 和 someNamePattern 是 SQL 模式，例如与LIKE 运算符一起使用的那些（例如% 匹配任何内容）。

这与驱动程序本身运行的查询基本相同，只是它查询 SEQUENCE 类型的对象。

Answer 4

您可以使用 hibernate dialect api 来检索序列名称。请参阅：http://docs.jboss.org /hibernate/orm/3.2/api/org/hibernate/dialect/Dialect.html

从下面的示例中，您可以看到如何使用方言来获取序列名称

public static void main(String[] args) {
        Connection jdbcConnection = null;
        try {
            jdbcConnection = DriverManager.getConnection("", "", "");
            printAllSequenceName(jdbcConnection);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if(jdbcConnection != null) {
                try {
                    jdbcConnection.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
}

public static void printAllSequenceName(Connection conn) throws JDBCConnectionException, SQLException {
        DialectResolver dialectResolver = new StandardDialectResolver();
        Dialect dialect =  dialectResolver.resolveDialect(conn.getMetaData());

        if ( dialect.supportsSequences() ) {
            String sql = dialect.getQuerySequencesString();
            if (sql!=null) {

                Statement statement = null;
                ResultSet rs = null;
                try {
                    statement = conn.createStatement();
                    rs = statement.executeQuery(sql);

                    while ( rs.next() ) {
                        System.out.println("Sequence Name : " +  rs.getString(1));
                    }
                }
                finally {
                    if (rs!=null) rs.close();
                    if (statement!=null) statement.close();
                }

            }
        }
    }

如果您不想使用 hibernate，那么您可以创建自定义顺序特定实现。

自定义实现的示例代码

interface SequenceQueryGenerator {
    String getSelectSequenceNextValString(String sequenceName);
    String getCreateSequenceString(String sequenceName, int initialValue, int incrementSize); 
    String getDropSequenceStrings(String sequenceName); 
    String getQuerySequencesString(); 
}


class OracleSequenceQueryGenerator implements SequenceQueryGenerator {

    @Override
    public String getSelectSequenceNextValString(String sequenceName) {
        return "select " + getSelectSequenceNextValString( sequenceName ) + " from dual";
    }

    @Override
    public String getCreateSequenceString(String sequenceName,
            int initialValue, int incrementSize) {
        return "create sequence " + sequenceName +  " start with " + initialValue + " increment by " + incrementSize;
    }

    @Override
    public String getDropSequenceStrings(String sequenceName) {
        return "drop sequence " + sequenceName;
    }

    @Override
    public String getQuerySequencesString() {
        return "select sequence_name from user_sequences";
    }

}


class PostgresSequenceQueryGenerator implements SequenceQueryGenerator {

    @Override
    public String getSelectSequenceNextValString(String sequenceName) {
        return "select " + getSelectSequenceNextValString( sequenceName );
    }

    @Override
    public String getCreateSequenceString(String sequenceName,
            int initialValue, int incrementSize) {
        return "create sequence " + sequenceName + " start " + initialValue + " increment " + incrementSize;
    }

    @Override
    public String getDropSequenceStrings(String sequenceName) {
        return "drop sequence " + sequenceName;
    }

    @Override
    public String getQuerySequencesString() {
        return "select relname from pg_class where relkind='S'";
    }

}

public void printSequenceName (SequenceQueryGenerator queryGenerator, Connection conn) throws SQLException {
        String sql = queryGenerator.getQuerySequencesString();
        if (sql!=null) {

            Statement statement = null;
            ResultSet rs = null;
            try {
                statement = conn.createStatement();
                rs = statement.executeQuery(sql);

                while ( rs.next() ) {
                    System.out.println("Sequence Name : " +  rs.getString(1));
                }
            }
            finally {
                if (rs!=null) rs.close();
                if (statement!=null) statement.close();
            }

        }
    }

public static void main(String[] args) {
        Connection jdbcConnection = null;
        try {
            jdbcConnection = DriverManager.getConnection("", "", "");
            printAllSequenceName(new OracleSequenceQueryGenerator(), jdbcConnection);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            if(jdbcConnection != null) {
                try {
                    jdbcConnection.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
}