Hi, I am trying to generate a sequence ID unique by another column in the same table and am trying to do this with @Generated and @Formula, but somehow I am always getting null. Can any expert here please give me a hand and let me know if I am completely on the wrong track?

Here's my code:

@Column( nullable=false, insertable=true, updatable=false)
@Generated(value="insert")
@Formula(value="(select (max(r.itemId)+1) from item r where r.merchantId=merchantId)")
public Integer getItemId() {
return itemId;
}

After a save, itemId is always empty!
Testing the select statement (replace merchantId with a valid one) in DB works fine!

Thanks in adv for any help!

It seems like you are trying to mix things that are used for different purposes. First, @Formula properties are used for properties that doesn't have a column of their own. The value is always calculated using the formula that you have specified. But I guess this is is not what you want since the 'select max(r.itemId)...' is likely to change over time.

Second, the @Generated annotation is used when there is some mechanism on the database that is generating the value for you. So, if you have a trigger or something in the database that generates (eg. executes the something like the sql in the formula) the value for you the correct annotations to use would be:



I guess that you don't have a trigger in the database either, but that you want to use the formula as some kind of Hibernate-triggered action that is evaluated at insertion time only and then it behaves as a regular property. There is currently no support for this in Hibernate.

If you have the possibility to create a trigger in the database, this is the easiest solution. Otherwise you would have to map it as a regular property and create your own code that initializes new objects with the proper value. Just be sure that you have a strategy for how to avoid duplicates being generated.

I CAN create a trigger for this. It is just that I was searching for a non-DB specific way of handling this. I thought this is such a very common problem that hibernate might have an easy way to do it. :p

Thanks for clarifying that this is not possible yet!