Apache DdlUtils :: how to obtain the sequence name for auto-increment keys?

Question

I am using Apache DdlUtils to query a PostgreSQL database for table and column meta-data (the end-aim being to automatically generate javax.persistence-annotated entity beans). However it seems to me that the DdlUtils library does not provide a way to obtain the names of sequences used in auto-increment columns. The Column class provides an isAutoIncrement method to query for auto-increment status but I couldn't find a way to get the sequence name associated with it. This is part of the DDL in PostgreSQL, eg:

orders=# \dS customer
                         Table "public.customer"
    Column     |       Type        |                    Modifiers
---------------+-------------------+--------------------------------------------------
 id            | integer           | not null default nextval('cst_id_seq'::regclass)
 name          | character varying | not null
 (...)

Should I directly query some metadata / catalogue table instead to obtain that bit of information?

Not every database implements auto-increment columns by autogenerating a sequence to back them. (IIRC, neither MySQL nor SQL Server do.) It's possible that DdlUtils prefers portability over flexibility here and just doesn't provide a special API for the ones that do. — millimoose, Aug 05 '12 at 01:26

score 0 · Accepted Answer · answered Aug 06 '12 at 08:31

Answering my own question ... Yeap, just like millimoose suggested, it can't be done with DdlUtils and you have to query the information_schema.columns table:

public String getPostgreSQLSequenceForColumn(String database, String table, String column) {
    try {
        Statement stmt = connection.createStatement();
        String sql="select column_default from information_schema.columns where "+
                   "table_catalog='"+database+"' and "                           +
                   "table_schema='public' and "                                  +
                   "table_name='"+table+"' and "                                 +
                   "column_name='"+column+"'";
        ResultSet rs = stmt.executeQuery(sql);
        rs.next();
        String sequenceName = rs.getString(1);
        panicIf(rs.next());
        return sequenceName;
    } catch (Exception e) {
        throw new ExceptionAdapter(e);
    }
}

Apache DdlUtils :: how to obtain the sequence name for auto-increment keys?

1 Answers1