JPA- How To Generate Sequences Of Identifiers Via Hibernate hi/lo Algorithm

If systems external to your application need to insert rows in your tables then don’t rely on hi/lo algorithm since, in such cases, it may cause errors resulted from generating duplicated identifiers. Rely on pooled or pooled-lo algorithms (optimizations of hi/lo).

Description: This is a Spring Boot example of using the hi/lo algorithm for generating 1000 identifiers in 10 database roundtrips for batching 1000 inserts in batches of 30.

Key points:

• use the SEQUENCE generator type (e.g., in PostgreSQL)
• configure the hi/lo algorithm as in Author.java entity

Author.java

@Data
@Entity
public class Author implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "hilo")
    @GenericGenerator(name = "hilo", strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator",
            parameters = {
                @Parameter(name = "sequence_name", value = "hilo_sequence"),
                @Parameter(name = "initial_value", value = "1"),
                @Parameter(name = "increment_size", value = "100"),
                @Parameter(name = "optimizer", value = "hilo")
            }
    )
    private Long id;
    private String name;
}

AuthorRepository.java

@Repository
public interface AuthorRepository extends JpaRepository<Author, Long> {
}

BookStoreService.java

@RequiredArgsConstructor
@Service
public class BookstoreService {

    private final AuthorRepository authorRepository;

    public void batch1000Authors() {
        List<Author> authors = new ArrayList<>();
        for (int i = 1; i <= 1000; i++) {
            Author author = new Author();
            author.setName("Author_" + i);
            authors.add(author);
        }
        authorRepository.saveAll(authors);
    }
}

MainApplication.java

@RequiredArgsConstructor
@SpringBootApplication
public class MainApplication {

    private final BookstoreService bookstoreService;

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

    @Bean
    public ApplicationRunner init() {
        return args -> {         
            bookstoreService.batch1000Authors();
        };
    }
}

application.properties

spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=postgres

spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

spring.jpa.properties.hibernate.jdbc.batch_size = 30
#spring.jpa.properties.hibernate.generate_statistics=true

logging.level.org.hibernate.type.descriptor.sql=TRACE

Console Output

Hibernate: insert into author (name, id) values (?, ?)
        2021-06-23 10:54:36.038 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Author_993]
        2021-06-23 10:54:36.038 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [BIGINT] - [993]
        Hibernate: insert into author (name, id) values (?, ?)
        2021-06-23 10:54:36.038 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Author_994]
        2021-06-23 10:54:36.038 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [BIGINT] - [994]
        Hibernate: insert into author (name, id) values (?, ?)
        2021-06-23 10:54:36.038 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Author_995]
        2021-06-23 10:54:36.057 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [BIGINT] - [995]
        Hibernate: insert into author (name, id) values (?, ?)
        2021-06-23 10:54:36.057 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Author_996]
        2021-06-23 10:54:36.057 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [BIGINT] - [996]
        Hibernate: insert into author (name, id) values (?, ?)
        2021-06-23 10:54:36.057 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Author_997]
        2021-06-23 10:54:36.057 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [BIGINT] - [997]
        Hibernate: insert into author (name, id) values (?, ?)
        2021-06-23 10:54:36.057 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Author_998]
        2021-06-23 10:54:36.057 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [BIGINT] - [998]
        Hibernate: insert into author (name, id) values (?, ?)
        2021-06-23 10:54:36.057 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Author_999]
        2021-06-23 10:54:36.057 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [BIGINT] - [999]
        Hibernate: insert into author (name, id) values (?, ?)
        2021-06-23 10:54:36.058 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Author_1000]
        2021-06-23 10:54:36.058 TRACE 23168 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [BIGINT] - [1000]

— — — — — — — — — — — — — — — — — — — — — — -

Hibernate hi/lo Algorithm And External Systems Issue

Description: This is a Spring Boot sample that exemplifies how the hi/lo algorithm may cause issues when the database is used by external systems as well. Such systems can safely generate non-duplicated identifiers (e.g., for inserting new records) only if they know about the hi/lo presence and its internal work. So, better rely on pooled or pooled-lo algorithm which doesn't cause such issues.

Key points:

• use the SEQUENCE generator type (e.g., in PostgreSQL)
• configure the hi/lo algorithm as in Author.java entity
• insert a few records via hi/lo
• insert a few records natively (this acts as an external system that relies on NEXTVAL('hilo_sequence') and is not aware of hi/lo presence and/or behavior)

Output sample: Running this application should result in the following error:
ERROR: duplicate key value violates unique constraint "author_pkey"
Detail: Key (id)=(2) already exists.

Author.java

@Data
@Entity
public class Author implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "hilo")
    @GenericGenerator(name = "hilo", strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator",
            parameters = {
                @Parameter(name = "sequence_name", value = "hilo_sequence"),
                @Parameter(name = "initial_value", value = "1"),
                @Parameter(name = "increment_size", value = "100"),
                @Parameter(name = "optimizer", value = "hilo")
            }
    )
    private Long id;
    private String name;
}

AuthorRepository.java

@Repository
public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Modifying
    @Query(value = "INSERT INTO author (id, name) VALUES (NEXTVAL('hilo_sequence'), ?1)",
            nativeQuery = true)
    public void saveNative(String name);
}

BookStoreService.java

@RequiredArgsConstructor
@Service
public class BookstoreService {

    private final AuthorRepository authorRepository;

    @Transactional
    public void save3Authors() {
        for (int i = 1; i <= 3; i++) {
            Author author = new Author();
            author.setName("Author_" + i);
            authorRepository.save(author); // uses ids: 1, 2 and 3
        }
    }

    @Transactional
    public void saveAuthorNative() {
        // ERROR: duplicate key value violates unique constraint "author_pkey"
        // Detail: Key (id)=(2) already exists.
        authorRepository.saveNative("Author_" + 4); // try to insert at id 2 and fails
    }
}

MainApp.java

/*
   Running this application should result in the following error:
     ERROR: duplicate key value violates unique constraint "author_pkey"
     Detail: Key (id)=(2) already exists.
*/

@RequiredArgsConstructor
@SpringBootApplication
public class MainApplication {

    private final BookstoreService bookstoreService;

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

    @Bean
    public ApplicationRunner init() {
        return args -> {
            bookstoreService.save3Authors();
            bookstoreService.saveAuthorNative();
        };
    }
}

application.properties

spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=postgres

spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

logging.level.org.hibernate.type.descriptor.sql=TRACE

Console Output —

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “author_pkey”
Detail: Key (id)=(2) already exists