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 ?

Answer 1

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

Answer 2

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)