Any way to check for the existence of a SEQUENCE using JDBC?

Question

I need to programmatically generate sequences in a datastore, but need to be able to detect their existence and not create if they are already there. Does anybody know of the JDBC metadata necessary to extract this information?

A cursory scan of DatabaseMetadata doesn't reveal a suitable method; I can get all tables/views and the associated keys/indexes etc but not sequences for that schema. Does anybody know of a way, preferably database-independent, but if not then for as many databases as possible (think oracle has a user_sequence table? but this is only one database and I need to support others).

Thanks in advance

As said I need to support many, I use H2 (which has CREATE SEQUENCE ... IF NOT EXISTS) but that is non-standard, but need to support all common RDBMS that support SEQUENCE (SQLServer, PostgreSQL, Oracle, H2, NuoDB, Derby, Firebird, etc) — Neil Stockton, Jan 06 '14 at 11:08
possible duplicate of [How to retrieve sequences metadata from JDBC?](http://stackoverflow.com/questions/5637423/how-to-retrieve-sequences-metadata-from-jdbc) — Stephen C, Jan 06 '14 at 11:10
@NeilStockton - If you actually read the answers to that Oracle-specific Question, you will realize that they also answer your more general Question. But I've also answered it for you below. — Stephen C, Jan 06 '14 at 11:29
@NeilStockton - 1) If you were hoping to get answers about how to query the database schemas, you should have *asked that* ... not "can JDBC do it". 2) The latest JDBC APIs are publicly available. You don't need to ask a SO question to find them. — Stephen C, Jan 06 '14 at 11:43

Stephen C · Answer 1 · 2014-01-06T11:34:25.007

Any way to check for the existence of a SEQUENCE using JDBC?

The answer is simply No.

Support for SEQUENCE metadata is not part of the JDBC specification. If you want to find this information, you will need to make your code aware of the type of database it is dealing with and perform the relevant queries against the vendor specific tables that are used to represent database schemas, etcetera.

You might be able to find a 3rd-party Java library that does this ... but I'm not aware of one.

^{Actually, in theory you could test if a SEQUENCE exists by attempting to create one with the same name. But then you've got all sorts of other problems, like dealing with different syntaxes for the CREATE, removing the SEQUENCE you created as a test, diagnosing the vendor-specific error codes to determine why the CREATE failed. You are probably better off querying vendor specific schema tables.}

score 2 · Answer 2 · answered Mar 21 '14 at 07:38

You can use the hibernate dialect api for retrieving sequence. 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 details

public static void main(String[] args) {
        Connection jdbcConnection = null;
        try {
            jdbcConnection = DriverManager.getConnection("", "", "");
            String sequenceName = "xyz" ; // name of sequence for check
        System.out.println("Check Sequence :" + checkSequenceName(sequenceName, 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 boolean checkSequenceName(String sequenceName, 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() ) {
                    if(sequenceName.equals(rs.getString(1))) {
                        return true;
                    }
                }
            }
            finally {
                if (rs!=null) rs.close();
                if (statement!=null) statement.close();
            }

        }
    }
    return false;
}

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 boolean checkSequence (String sequenceName, 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() ) {
                    if(sequenceName.equals(rs.getString(1))) {
                        return true;
                    }
                }
            }
            finally {
                if (rs!=null) rs.close();
                if (statement!=null) statement.close();
            }

        }
        return false;
    }

public static void main(String[] args) {
        Connection jdbcConnection = null;
        try {
            jdbcConnection = DriverManager.getConnection("", "", "");
            String sequenceName = "xyz" ; // name of sequence for check
            System.out.println(checkSequence(sequenceName, 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();
                }
            }
        }
}

In Hibernate >= 4.x it's necessary to wrap `Connection.getMetaData` in a `DatabaseMetaDataDialectResolutionInfoAdapter`. — Kalle Richter, Jan 12 '17 at 17:47
I recommend to throw an exception if any of the `Dialect.get...String` methods returns null because `null` it's not a documented return value. — Kalle Richter, Jan 12 '17 at 18:22

avijendr · Accepted Answer · 2014-01-06T11:36:44.990

1

There isn't any direct way as I am aware of. Because each database has it's own way of generating/handling sequence. When it's sequence in Oracle, it's auto_incriment(not a sequence but close to it or achieve somewhat same results) in mysql, Identity Columns in SQL Server etc.

I would do somethign like this - You'll have to make an interface:

interface ISequenceChecker{ // or some name which suits you
    SequenceObject getSequence();
}

Implementation for different database/store (E.G for oracle given below):

public class OracleSequenceChecker implements ISequenceChecker{
   OracleSequenceObject getSequence(){
    // some jdbc or similar call
    // to get SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL
   }
}

edited Jan 06 '14 at 11:36

answered Jan 06 '14 at 11:15

avijendr

3,958
2
31
46

2

Thanks for your reply. Yes, having a variant per datastore is likely the way I'll go. BTW "auto-increment" in MySQL is not equivalent to a (SQL) SEQUENCE, which is a standalone named value generator, whereas auto-increment is an identity for a particular column and has no name. There are RDBMS that support both IDENTITY (auto-increment) and SEQUENCE, hence the distinction. – Neil Stockton Jan 06 '14 at 11:21
Thanks Neil. I know it's not equivalent but it's the closest or else you'll have to use interlock or something like that (but still not sequence). I can't think of any other solution than I mentioned above. Happy hacking! – avijendr Jan 06 '14 at 11:26

score 1 · Answer 4 · answered Jan 06 '14 at 11:25

You Don't. Every RDBMS has your own way to store your metada information. Some of then can be similar to others but you hardely will find exactly same info on thoose tables.

The best you can do is some sort of a data dictionary to identify the RDBMS and from there go to the specific configuration on your dictionary to get this info.

The idea is to have a table which will store the database and if it has support to sequence, then another table with the configuration necessary to load that sequence info, something like sequence table, sequence column, etc.

Then implement a way of get this info. I would go for @avijendr answer (he post it while i'm writing this one)

score 0 · Answer 5 · answered Jul 27 '17 at 09:05

I made a test with Postgres 9, Java 8 and JDBC 4.2.

For retrieving the list of sequences here's what I did :

First, list entities types with java.sql.DatabaseMetaData#getTableTypes

The result was :

 * FOREIGN TABLE
 * INDEX
 * MATERIALIZED VIEW
 * SEQUENCE
 * SYSTEM INDEX
 * SYSTEM TABLE
 * SYSTEM TOAST INDEX
 * SYSTEM TOAST TABLE
 * SYSTEM VIEW
 * TABLE
 * TEMPORARY INDEX
 * TEMPORARY SEQUENCE
 * TEMPORARY TABLE
 * TEMPORARY VIEW
 * TYPE
 * VIEW

So with Postgres JDBC Driver, there's a table type "SEQUENCE".

Then I used java.sql.DatabaseMetaData#getTables like this :

String catalog = "";
String tableNamePattern = "%";
String schemaPattern = "my_schema";
ResultSet tablesRS = cnx.getMetaData().getTables(catalog, schemaPattern, tableNamePattern, new String[] {"SEQUENCE"});

tablesRS.getString("TABLE_NAME") gives the name of each sequence found.

I made a utility class ExtractMetadataUtil for testing this (on GitHub).

Any way to check for the existence of a SEQUENCE using JDBC?

5 Answers5