How to retrieve sequences metadata from JDBC?

Question

I am trying to retrieve different kind of metadata of my Oracle DB from Java code (using basic JDBC). For example, if I want to retrieve the list of tables with _FOO suffix, I can do something like:

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...

Now, I want to retrieve all the sequences from my database (for example all sequence named S_xxx_FOO).

How would I do that, as I don't see anything in DatabaseMetaData related to sequences?

Do I have to run a query like select * from user_sequences ?

Phile

Answer 1

You can't do this through the JDBC API, because some databases (still) do not support sequences.

The only way to get them is to query the system catalog of your DBMS (I guess it's Oracle in your case as you mention user_sequences)

Formosa

For the details of how to do this, see my answer. :-) – Dent

Answer 2

Had the same question. It's fairly easy. Just pass in "SEQUENCE" into the getMetaData().getTables() types param.

In your specific case it would be something like:

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

Pastorale

This doesn't work with odjbc5.jar and ojdbc6.jar drivers which limits types to 'SYNONYM', 'TABLE' and 'VIEW' and raises an exception if called with something else. – Kho

It worked OK in version 12.1.0.1 of the ojdbc7 driver, but stopped working in version 12.1.0.2 because as you say, it now ignores anything in the "types" parameter that's not one of the types returned by getTableTypes(). I've asked a question in their forum about whether this is intentional: community.oracle.com/message/14134247#14134247 – Dent

Answer 3

Given that recent versions of the Oracle JDBC drivers (e.g. 12.1.0.2) don't return sequence information when you call DatabaseMetaData#getTables with types set to ["SEQUENCE"], your best bet is to run the necessary query yourself, e.g.:

  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

... where someOwnerPattern and someNamePattern are SQL patterns like the ones you'd use with the LIKE operator (e.g. % matches anything).

This is basically the same as the query run by the driver itself, except that it queries for objects of type SEQUENCE.

Dent

Answer 4

You can use the hibernate dialect api for retrieving sequence Name. see : http://docs.jboss.org/hibernate/orm/3.2/api/org/hibernate/dialect/Dialect.html

From below example, you can see how to use dialect to get sequence names

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

            }
        }
    }

If you don't desire to use hibernate, then you have to crate custom sequential specific implementation.

Sample code for custom implementation

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

Subminiaturize