Multiple Hibernate sequence generators for one entity with PostgreSQL

Question

Can I use Multiple sequence generators for one entity, like

@Id
@SequenceGenerator(name=”subscription_id_seq”,sequenceName=”subscription_id_seq”, allocationSize=7)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator=”subscription_id_seq”)
@Column(unique=true, nullable=false)
private Integer id

@Column(name="code", nullable=false, unique=true )
@SequenceGenerator(name="subscription_code_1_seq",sequenceName="subscription_code_1_seq", allocationSize=7)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="subscription_code_1_seq")
private Integer code;

score 8 · Accepted Answer · answered Dec 30 '15 at 13:21

No you can not. The generator are applicable for identifier columns only.

Make sure you create this sequence with a script (e.g. hibernate.hbm2ddl.import_files):

create sequence subscription_code_1_seq start 1 increment 7

Then use a mapping like this:

@Id
@SequenceGenerator(
        name="subscription_id_seq",
        sequenceName="subscription_id_seq",
        allocationSize=7
)
@GeneratedValue(
        strategy=GenerationType.SEQUENCE,
        generator="subscription_id_seq"
)
@Column(unique=true, nullable=false)
private Integer id;

@Column(
        name="code",
        nullable=false,
        unique=true,
        insertable = false,
        updatable = false,
        columnDefinition = "BIGINT DEFAULT nextval('subscription_code_1_seq')"
)
@Generated(GenerationTime.INSERT)
private Integer code;

score 4 · Answer 2 · answered Dec 30 '15 at 12:36

In nutshell, you can use multiple sequence generators for one entity but for primary keys only (composite primary key).

From SequenceGenerator documentation:

Defines a primary key generator that may be referenced by name when a generator element is specified for the GeneratedValue annotation. A sequence generator may be specified on the entity class or on the primary key field or property. The scope of the generator name is global to the persistence unit (across all generator types).

Code example:

public class TestPK implements Serializable {

    private Integer test1;

    private Integer test2;

    ...
}

@Entity
@IdClass(TestPK.class)
public class Test implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "seq_test1", sequenceName = "seq_test1", allocationSize = 7)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_test1")
    @Column(name = "test1", unique = true, nullable = false)
    private Integer test1;

    @Id
    @Column(name = "test2", nullable = false, unique = true)
    @SequenceGenerator(name = "seq_test2", sequenceName = "seq_test2", allocationSize = 7)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_test2")
    private Integer test2;

    ...

    @Override
    public String toString() {
        return "Test{" +
                "test1=" + test1 +
                ", test2=" + test2 +
                '}';
    }
}

public interface TestRepository extends Repository<Test, String> {

    Page<Test> findAll(Pageable pageable);

    void save(Test test);
}

@SpringBootApplication
public class Application implements CommandLineRunner {

    @Autowired
    private TestRepository testRepository;

    @Override
    public void run(String... args) throws Exception {
        testRepository.save(new Test());
        Page<Test> all = testRepository.findAll(null);
        System.out.println(all.iterator().next());
    }

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

score 0 · Answer 3 · answered Dec 17 '22 at 11:20

An alternative solution would be to use @GeneratorType. With this annotation, you can provide a custom implementation during insert and update of the hosting entity. In this case, adding a sequence

First create this sequence and add it during the creation or altering of the table:

CREATE SEQUENCE project_code_seq INCREMENT 1 START 1;
ALTER TABLE project
    ADD COLUMN key bigint NOT NULL DEFAULT nextval('project_code_seq');

Create a class that implements the ValueGenerator:

public class ProjectCodeGenerator implements ValueGenerator<Long> {

    private static final String SELECT_NEXTVAL = "select nextval('project_code_seq')";

    @Override
    public Long generateValue(Session session, Object object) {
        if (object instanceof ProjectEntity project) {

                var result = (BigInteger) session.createNativeQuery(SELECT_NEXTVAL)
                        .setHibernateFlushMode(
                                FlushMode.COMMIT).getSingleResult();
                return result.longValue();
                
        } else {
            throw new RuntimeException("Can only be used for " + ProjectEntity.class);
        }
    }

}

Then on the entity you can add the @GeneratorType annotation:

@GeneratorType(type = ProjectCodeGenerator.class, when = GenerationTime.INSERT)
private Long code;

Note that the ProjectCodeGenerator can be tuned to whatever you desire and more logic can be added.

Multiple Hibernate sequence generators for one entity with PostgreSQL

3 Answers3

Linked