Hibernate does not generate Sequence when it's already available on a different schema

user_1084239 0
Viewed 1400

I have a "simple" Spring Boot application with a single datasource. The following configuration is present in my configuration:

spring:
  jpa:
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        default_schema: CORE
flyway:
  schemas:
    - CORE

and the following ID Generator

@Id
@GeneratedValue(strategy = SEQUENCE, generator = "seq-pooled-lo")
@GenericGenerator(
        name = "seq-pooled-lo",
        strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator",
        parameters = {
                @org.hibernate.annotations.Parameter(
                        name = SequenceStyleGenerator.INCREMENT_PARAM,
                        value = "50"
                ),
                @org.hibernate.annotations.Parameter(
                        name = SequenceStyleGenerator.OPT_PARAM,
                        value = "pooled"
                ),
                @org.hibernate.annotations.Parameter(
                        name = SequenceStyleGenerator.SEQUENCE_PARAM,
                        value = "seq_pooled_lo_sequence"
                )
        })

Now my issue is the following:

When I launch this app, it will create a sequence on the "CORE" schema. Everything works fine. Storing, retrieving data is no problem. When I then launch a second instance of the app but override the YAML file to define a different default_schema: SECOND it will not generate a new sequence on the "SECOND" schema. If I would first start the app with the "SECOND" schema defined and then start the one with "CORE" it would create the sequence on the "SECOND" schema and not on "CORE".

I would expect it to create different sequences on both schemas. Why does it not do that?

I also tried to add the sequence manually to the schema where it's missing but sadly that did not seem to help.

jpa hibernate spring
1 Answers

If you add the sequence manually to the schema. You can create CustomIdGenerator Class as the example for return sequence id by defaultSchema

public class CustomIdGenerator implements IdentifierGenerator {

    @Override
    public Serializable generate(SessionImplementor sessionImplementor, Object o) throws HibernateException {
        SequenceRepository sequenceRepository = ApplicationContextProvider.getApplicationContext().getBean(SequenceRepository.class);
        Environment env = ApplicationContextProvider.getApplicationContext().getBean(Environment.class);

        String defaultSchema = env.getProperty("yourproperty.default_schema");

        return sequenceRepository.getSequence(defaultSchema);

    }
}

Let's create SequenceRepository class for generate sequence. I assume that my database is Oracle.

NEXTVAL: Increments the sequence and returns the next value

public interface SequenceRepository extends JpaRepository<Object,Long> {
    @Query(value = "SELECT ?1.NEXTVAL FROM DUAL", nativeQuery = true)
    Long getSequence(String sequenceName); 
}

and your Entity just edit strategy to using CustomGenerator Class

@Id
@GenericGenerator(name = "sequence_generator", strategy = "yourpackage.CustomIdGenerator")
@GeneratedValue(generator = "sequence_generator")
@Column(name  = "id")
private Long id;
user_20905680
Related