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?

Solution 1

Looking for answers to this problem, I stumbled upon this link

It seems that Hibernate/JPA isn't 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 @GeneratedValue 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 separate 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;
}

Solution 2

I found that @Column(columnDefinition="serial") works perfect but only for PostgreSQL. For me this was perfect solution, because second entity is "ugly" option.

A call to saveAndFlush on the entity is also necessary, and save won't be enough to populate the value from the DB.

Solution 3

I know this is a very old question, but it's showed firstly upon the results and jpa has changed a lot since the question.

The right way to do it now is with the @Generated annotation. You can define the sequence, set the default in the column to that sequence and then map the column as:

@Generated(GenerationTime.INSERT)
@Column(name = "column_name", insertable = false)

Solution 4

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.

Solution 5

I fixed the generation of UUID (or sequences) with Hibernate using @PrePersist annotation:

@PrePersist
public void initializeUUID() {
    if (uuid == null) {
        uuid = UUID.randomUUID().toString();
    }
}

View more solutions

Comments

[ruslanys]

From the java doc of @GeneratedValue: "The GeneratedValue annotation may be applied to a primary key property or field of an entity or mapped superclass in conjunction with the Id annotation"

[Sergey Vedernikov]

I found that @Column(columnDefinition="serial") works perfect but only for PostgreSQL. For me this was perfect solution, because second entity is "ugly" option

[Matt Ball]

@SergeyVedernikov that was extremely helpful. Would you mind posting that as a separate answer? It solved my problem very very simply and effectively.

[Sergey Vedernikov]

@MattBall i've posted this as separate answer :) stackoverflow.com/a/10647933/620858

[Andrea Ligios]

I'm doing as suggested, but MyEntity.id is always null Any help would be greatly appreciated.

[Emaborsa]

Hi, i'd need an explanation on it. Could you tell me more please?

[Patrick]

@Emaborsa The columnDefinition= bit basically tells Hiberate to not try to generate the column definition and instead use the text you've given. Essentially, your DDL for the column will literally just be name + columnDefinition. In this case (PostgreSQL), mycolumn serial is a valid column in a table.

[Bernie]

This still requires the value to be generated by the database, which doesn't really answer the question. For Oracle databases prior to 12c, you would still need to write a database trigger to generate the value.

[caarlos0]

also, this is a Hibernate annotation, not JPA.

[Richard Kennard]

The equivalent for MySQL is @Column(columnDefinition = "integer auto_increment")

[Eric]

The @Generated annotation corresponds to the above XML configuration. See this section of the hibernate docs for more details.

[tecoup]

A varient with Hibernate 4.2.19 and oracle: SQLQuery sqlQuery = getSession().createSQLQuery("select NAMED_SEQ.nextval seq from dual"); sqlQuery.addScalar("seq", LongType.INSTANCE); return (Long) sqlQuery.uniqueResult();

[KyelJmD]

Does this auto generate its value? I tried persisting an entity with a field definition like this but it did not generate a value. it threw an null value in column <column> violates non null constraint

[Sergey Vedernikov]

@KyelJmD for me it had worked and was returning a generated value with PostgreDB. Try flushing the session

[Petar Tahchiev]

I have opened a proposal to allow @GeneratedValue on fields that are not id. Please vote to be included in 2.2 java.net/jira/browse/JPA_SPEC-113

[Jacob van Lingen]

@PetarTahchiev: New link: github.com/javaee/jpa-spec/issues/113

[Robert Di Paolo]

I used @Column(insertable = false, updatable = false, columnDefinition="serial") to prevent hibernate from trying to insert null values or updating the field. You then need to re-query the db to get the generated id after an insert if you need to use it straight away.

[OJVM]

It worked for me too, I am using spring boot 2.1.6.RELEASE, Hibernate 5.3.10.Final, In addition to what has already pointed out, I had to create a secuence seq_order and make reference form the field, nextval('seq_order'::regclass)

[Pavel_K]

@JacobvanLingen The link is broken again.

[Reginaldo Santos]

This is definetly the cleaner solution IMHO.