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

Answer 1

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

Answer 2

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