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

Java Developer and enthusiast