MariaDB 10.3 supports database sequences

Introduction

Traditionally, both MySQL and MariaDB relied on AUTO_INCREMENT columns to generate an IDENTITY Primary Key. Although IDENTITY columns are very efficient in generating the Primary Key value, when it comes to using JPA and Hibernate, the IDENTITY generator prevents us from using JDBC batch inserts.

To automatically enroll multiple INSERT, UPDATE or DELETE statements, Hibernate requires delaying the SQL statement until the Persistence Context is flushed. This works very well for the SEQUENCE identifier since the entity identifier can be fetched prior to executing the INSERT statement.

However, for IDENTITY columns, the only way to know the entity identifier is if we execute the SQL INSERT statement. And, Hibernate needs the entity identifier when persisting an entity because otherwise, it cannot build the key which is used for locating an entity in the currently running Persistence Context.

Continue reading “MariaDB 10.3 supports database sequences”

How to replace the TABLE identifier generator with either SEQUENCE or IDENTITY in a portable way

Introduction

As previously explained, the TABLE identifier generator does not scale, so you should avoid id. However, some enterprise applications might need to run on both MySQL (which does not support database sequences), as well as Oracle, PostgreSQL, and SQL Server 2012.

This is article is going to explain how easily you can achieve this goal using the JPA mapping overriding.

Continue reading “How to replace the TABLE identifier generator with either SEQUENCE or IDENTITY in a portable way”

How to implement a custom String-based sequence identifier generator with Hibernate

Introduction

One of my blog readers bumped into the assigned generator with a sequence or an identity column post and wondered if it was possible to generate String-based identifiers instead.

I accepted the challenge and answered his question on StackOverflow. However, this post is going to explain this topic in greater detail, so there we go.

Continue reading “How to implement a custom String-based sequence identifier generator with Hibernate”

How to combine the Hibernate assigned generator with a sequence or an identity column

Introduction

The entity identifier can either be manually assigned, or it can be automatically generated by an identity column or a database sequence.

In this post, I’ll show you how you can mix the assigned generator with an identity column or a database sequence.

Continue reading “How to combine the Hibernate assigned generator with a sequence or an identity column”

Hibernate pooled and pooled-lo identifier generators

Introduction

In this post, we’ll uncover a sequence identifier generator combining identifier assignment efficiency and interoperability with other external systems (concurrently accessing the underlying database system).

Traditionally there have been two sequence identifier strategies to choose from.

  • The sequence identifier, always hitting the database for every new value assignment. Even with database sequence preallocation we have a significant database round-trip cost.
  • The seqhilo identifier, using the hi/lo algorithm. This generator calculates some identifier values in-memory, therefore reducing the database round-trip calls. The problem with this optimization technique is that the current database sequence value no longer reflects the current highest in-memory generated value. The database sequence is used as a bucket number, making it difficult for other systems to interoperate with the database table in question. Other applications must know the inner-workings of the hi/lo identifier strategy to properly generate non-clashing identifiers.

The enhanced identifiers

Hibernate offers a new class of identifier generators, addressing many shortcomings of the original ones. The enhanced identifier generators don’t come with a fixed identifier allocation strategy. The optimization strategy is configurable and we can even supply our own optimization implementation. By default Hibernate comes with the following built-in optimizers:

  • none: every identifier is fetched from the database, so it’s equivalent to the original sequence generator.
  • hi/lo: it uses the hi/lo algorithm and it’s equivalent to the original seqhilo generator.
  • pooled: This optimizer uses a hi/lo optimization strategy, but the current in-memory identifiers highest boundary is extracted from an actual database sequence value.
  • pooled-lo: It’s similar to the pooled optimizer but the database sequence value is used as the current in-memory lowest boundary

Continue reading “Hibernate pooled and pooled-lo identifier generators”