Friday, November 2, 2012

Using sequences in non-ID hibernate properties

Sometimes, you want to provide non-technical but still unique sequence based properties for your hibernate managed entities without making them the table's index. In my case, I find it really convenient to have globally unique IDs within my whole database and don't want to make exceptions for a single table.

For example, one requirement for a project of mine was to have an auto-incremented immutable numeric property in a certain entity. IF you are willing to make this field your database index, you're fine using hibernates @GeneratedValue annotation, which won't work on non-Id fields. The solution in this case is to make the field non-insertable and let the database provide a default value on the first insert using the custom sequence:

@Column(nullable = false, insertable=false, updatable=false,
        columnDefinition = "integer DEFAULT nextval('my_sequence')")
@Generated(GenerationTime.INSERT) // this is important
private Integer sequencedNumber;

You have to keep in mind, though, that this will break db-vendor independancy, as the columnDefinition syntax is vendor specific. (In this case it's postgres.)