Tuesday, June 21, 2011

Sequence generator with PlayFramework, JPA, and PostgreSQL

I have been recently making some websites using PlayFramework but has just noticed something about auto-generated Id number behavior when using PostgreSQL as database.

I usually create a Model (which is a JPA entity) by extending play.db.jpa.Model which actually have an auto generated Id property and a lot of very useful database related operations. PostgreSQL uses sequence for auto-generated number. By extending the play.db.jpa.Model, you actually use only one sequence generator for all your models. Thats making my first insert to a table (second or so, if you have more than one table) does not always start from 1.

I then decided to use one sequence per table by doing this following changes:
1. Extends play.db.jpa.GenericModel instead
2. Add an Id property to each model class

@Entity
public class Token extends GenericModel {

  @Id
  @SequenceGenerator(name = "Token_generator", sequenceName = "Token_sequence")
  @GeneratedValue(generator = "Token_generator")
  public Long id;

  ...omitted...

3. Recreate all the table

This happens only when you are using sequence as number generator (PostgreSQL and Oracle AFAIK?), MySQL should not have this kind of behavior because MySQL use AUTO_INCREMENT instead of sequence generator.

2 comments:

  1. Hello, I am now about to do the same.
    Had hoped I could override @GeneratedValue() somehow
    in my classes, but it really seems impossible.
    That's quite disappointing, having to do this, after all
    development with "db=file" worked fine...
    And the problem exists not only with postgres, but with
    all DBs that use sequences rather than autoincrement...
    Cheers, Tom.

    ReplyDelete
  2. Added a bug report.
    Let's see what they say :-)
    https://play.lighthouseapp.com/projects/57987/tickets/1417-allow-per-model-sequence-value#ticket-1417-2

    ReplyDelete