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

| 我正在尝试从Java代码(使用基本JDBC)检索我的Oracle DB的另一种元数据。例如,如果我想检索后缀为“ 0”的表的列表,则可以执行以下操作:
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
的序列)。 我没有看到sequences3ѭ中与序列有关的任何内容,该怎么办? 我是否必须运行类似
select * from user_sequences
的查询?     
已邀请:
您无法通过JDBC API进行此操作,因为某些数据库(仍然)不支持序列。 获得它们的唯一方法是查询您的DBMS的系统目录(您提到“ 5”时,我猜是Oracle)     
有同样的问题。这很容易。只需将\“ SEQUENCE \”传递到getMetaData()。getTables()类型参数中即可。 在您的特定情况下,它将类似于: meta.getTables(connection.getCatalog(),null,\“%_ FOO \”,新String [] {\“ SEQUENCE \”}));     
您可以使用休眠方言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();
                }

            }
        }
    }
如果不想使用休眠模式,则必须创建自定义顺序特定的实现。 自定义实现的示例代码
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();
                }
            }
        }
}
    
鉴于最新版本的Oracle JDBC驱动程序(例如12.1.0.2)在将``9''设置为``10''时调用``8''时不会返回序列信息,因此最好的选择是自己运行必要的查询,例如:
  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
运算符配合使用的模式(例如
%
匹配任何内容)。 该查询基本上与驱动程序本身运行的查询相同,只是它查询类型为“ 16”的对象。     

要回复问题请先登录注册