Get Primary Keys Easily with DBvolution 0.9.30 and @DBAutoIncrement

One thing that has bugged me for ages in database programming is getting primary keys.

Primary keys are vital to database programming and at some point every DBA just starts adding a "pk_uid integer not null primary key" like statement to their tables without even noticing. Unfortunately this leads to conflicts with developers about exactly how they make a unique integer for the primary key. The simplest solution these days is to add an auto increment statement, or a sequence and a default.

Again there is a problem. Inserting the rows is now easier because the PK just happens but pretty much every user interface I've seen IMMEDIATELY wants that PK so the row can be worked on. However retrieving that primary key has never been an obvious operation and, JDBC being the loose protocol that it is, all the vendors have a slightly different version of it.

I had a look at this problem a long while ago but it didn't seem to work. However I left the code there because it wasn't causing any harm. Until I start testing against PostgreSQL that is. PostgreSQL actually worked. This lead me to actually investigate the other databases and the ANSI specification. Turns out most of the databases will work but the column needs to be an actual auto-generated column.

So DBvolution will now insert your row and AUTOMATICALLY set the primary key to the generated value.

This means you can insert rows and then retrieve them straight away, without any hassles, hacks, or issues. Your PK is unique and maintained by the database but available for use immediately. You can also use database.getDBTable(row).getOnlyRow() to get a fresh version of the row straight from the database.

The main caveat is that the primary key needs to be auto-generated by the database, usually with "auto_increment", "identity", or a sequence. The primary key field also needs to be a number type, probably DBInteger

If you're creating tables using DBDatabase.createTable() then DBV can provide the auto-increment set up if you add the @DBAutoIncrement annotation:

    @DBColumn
    @DBPrimaryKey
    @DBAutoIncrement
    public DBInteger pk_uid = new DBInteger();

Adding the annotation is probably a good idea anyway so that it's obvious how it is supposed to work and will work that way if you change database.

So there you go, the biggest annoyance in DB programming has been sorted. I can't wait to use it :)

[Gregory Graham]

Currently this definitely works for MySQL, PostgrSQL, and H2. Other databases need to support ANSI 2003.

Interestingly H2 works because it supports ANSI SQL. I'm getting very impressed with that little DB.