Hibernate JPA Sequence (non-Id)

Question

Is it possible to use a DB sequence for some column that is not the identifier/is not part of a composite identifier?

I'm using hibernate as jpa provider, and I have a table that has some columns that are generated values (using a sequence), although they are not part of the identifier.

What I want is to use a sequence to create a new value for an entity, where the column for the sequence is NOT (part of) the primary key:

@Entity
@Table(name = "MyTable")
public class MyEntity {

    //...
    @Id //... etc
    public Long getId() {
        return id;
    }

   //note NO @Id here! but this doesn't work...
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "myGen")
    @SequenceGenerator(name = "myGen", sequenceName = "MY_SEQUENCE")
    @Column(name = "SEQ_VAL", unique = false, nullable = false, insertable = true, updatable = true)
    public Long getMySequencedValue(){
      return myVal;
    }

}

Then when I do this:

em.persist(new MyEntity());

the id will be generated, but the mySequenceVal property will be also generated by my JPA provider.

Just to make things clear: I want Hibernate to generate the value for the mySequencedValue property. I know Hibernate can handle database-generated values, but I don't want to use a trigger or any other thing other than Hibernate itself to generate the value for my property. If Hibernate can generate values for primary keys, why can't it generate for a simple property?

Answer 1

According to the documentation it should be possible (haven't tried it myself): http://www.hibernate.org/hib_docs/v3/reference/en-US/html_single/#mapping-generated

Answer 2

I've been in a situation like you (JPA/Hibernate sequence for non @Id field) and I ended up creating a trigger in my db schema that add a unique sequence number on insert. I just never got it to work with JPA/Hibernate

Answer 3

Hibernate definitely supports this. From the docs:

"Generated properties are properties which have their values generated by the database. Typically, Hibernate applications needed to refresh objects which contain any properties for which the database was generating values. Marking properties as generated, however, lets the application delegate this responsibility to Hibernate. Essentially, whenever Hibernate issues an SQL INSERT or UPDATE for an entity which has defined generated properties, it immediately issues a select afterwards to retrieve the generated values."

For properties generated on insert only, your property mapping (.hbm.xml) would look like:

<property name="foo" generated="insert"/>

For properties generated on insert and update your property mapping (.hbm.xml) would look like:

<property name="foo" generated="always"/>

Unfortunately, I don't know JPA, so I don't know if this feature is exposed via JPA (I suspect possibly not)

Alternatively, you should be able to exclude the property from inserts and updates, and then "manually" call session.refresh( obj ); after you have inserted/updated it to load the generated value from the database.

This is how you would exclude the property from being used in insert and update statements:

<property name="foo" update="false" insert="false"/>

Again, I don't know if JPA exposes these Hibernate features, but Hibernate does support them.

Answer 4

"I don't want to use a trigger or any other thing other than Hibernate itself to generate the value for my property"

In that case, how about creating an implementation of UserType which generates the required value, and configuring the metadata to use that UserType for persistence of the mySequenceVal property?

Answer 5

I run in the same situation like you and I also didn't find any serious answers if it is basically possible to generate non-id propertys with JPA or not.

My solution is to call the sequence with a native JPA query to set the property by hand before persisiting it.

This is not satisfying but it works as a workaround for the moment.

Mario

Answer 6

Hi,

Looking for answers to this problem, i stumbled upon this link: http://forum.hibernate.org/viewtopic.php?p=2405140

It seems that Hibernate/JPA isnt able to automatically create a value for your non-id-properties. The @GeneratedValue-annotation is only used in conjunction with @Id to create auto-numbers.

The @Generated-annotation just tells Hibernate that the database is generating this value itself.

The solution (or work-around) suggested in that forum is to create a seperate entity with a generated Id, something like this:

@Entity
public class GeneralSequenceNumber {
  @Id
  @GeneratedValue(...)
  private Long number;
}

@Entity 
public class MyEntity {
  @Id ..
  private Long id;

  @OneToOne(...)
  private GeneralSequnceNumber myVal;
}

Answer 7

Now this topic is quite old. Still I ran into the same problem. Has anybody found a solution, why Hibernate doesn't support this or what the correct configuration is?

Creating a separate entity or a explicit call to the sequence cannot be the solution.

alex

Answer 8

This is not the same as using a sequence. When using a sequence, you are not inserting or updating anything. You are simply retrieving the next sequence value. It looks like hibernate does not support it.

Answer 9

I found this helpful: https://forum.hibernate.org/viewtopic.php?f=1&t=994299&start=0

Answer 10

As a followup here's how I got it to work: @Override public Long getNextExternalId() { BigDecimal seq = (BigDecimal)((List)em.createNativeQuery("select col_msd_external_id_seq.nextval from dual").getResultList()).get(0); return seq.longValue(); }