postgresql - Is it possible to have autoincrement number without it being an ID?

Question

I keep googling and find that the only way is to use

@Id
@GeneratedValue(strategy = GenerationType.Identity)

But I already have a primary key, I just need another field that auto increments. It's really hard to code it by manual calculation.

Answer 1

I see the following options:

1) You can use @Generated annotation.

You should have a table declared in the following way:

create sequence TST_DATA_SEQ increment by 1 start with 1;

create table TST_DATA (
   ...
   dat_auto integer default nextval('TST_DATA_SEQ'),
   ...
);

and appropriate column in the entity:

   @Generated(value = GenerationTime.INSERT)
   @Column(name = "dat_auto", insertable = false, updatable = false)
   private Long auto;

Note that according to the documentation:

Properties marked as generated must additionally be non-insertable and non-updateable.

So, hibernate will make additional query to populate the auto field after flushing.

   Data data = new Data();
   // filling fields except data.auto
   session.persist(data);
   session.flush();

insert into TST_DATA (dat_name, dat_id) 
values (?, ?)

Hibernate: /* get generated state com.example.hibernate.Data */
  select data_.dat_auto as dat_auto_0_ 
  from TST_DATA data_ 
  where data_.dat_id=?

2) You can use @GeneratorType annotation.

You should have an implementation of hibernate ValueGenerator. The simple example you can see below.

import java.math.BigInteger;
import org.hibernate.Session;
import org.hibernate.tuple.ValueGenerator;

public class MyGenerator implements ValueGenerator<Long> 
{
   public Long generateValue(Session session, Object owner)
   {
      return (
         (BigInteger) session
            .createNativeQuery("select nextval('TST_DATA_SEQ')")
            .getSingleResult()
         ).longValue();
   }
}

And then you can use it like this:

   @GeneratorType(type = MyGenerator.class, when = GenerationTime.INSERT)
   @Column(name = "dat_auto")
   private Long auto;

In this case you should not provide the default value for the column declaration as was required in n1. and the appropriate entity field should not have @Column(... insertable = false, updatable = false). Each time when this entity will be persisted, hibernate generates query:

select nextval('TST_DATA_SEQ')

and populate this value to the auto field.