Introduction Hibernate shifts the developer mindset from SQL statements to entity state transitions. Once an entity is actively managed by Hibernate, all changes are going to be automatically propagated to the database. Manipulating domain model entities (along with their associations) is much easier than writing and maintaining SQL statements. Without an ORM tool, adding a new column requires modifying all associated INSERT/UPDATE statements. But Hibernate is no silver bullet either. Hibernate doesn’t free us from ever worrying about the actual executed SQL statements. Controlling Hibernate is not as straightforward as one might… Read More
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: sequence and seqhilo. 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 hilo algorithm. This generator calculates some identifier values in-memory, therefore reducing the database round-trip calls. The problem with this optimization technique is that… Read More
JPA identifier generators JPA defines the following identifier strategies: Strategy Description AUTO The persistence provider picks the most appropriate identifier strategy supported by the underlying database IDENTITY Identifiers are assigned by a database IDENTITY column SEQUENCE The persistence provider uses a database sequence for generating identifiers TABLE The persistence provider uses a separate database table to emulate a sequence object In my previous post I exampled the pros and cons of all these surrogate identifier strategies.
Introduction In my previous post I talked about different database identifier strategies. This post will compare the most common surrogate primary key strategies: IDENTITY SEQUENCE TABLE (SEQUENCE) IDENTITY The IDENTITY type (included in the SQL:2003 standard) is supported by: Oracle 12c SQL Server MySQL (AUTO_INCREMENT) DB2 HSQLDB The IDENTITY generator allows an integer/bigint column to be auto-incremented on demand. The increment process happens outside of the current running transaction, so a roll-back may end-up discarding already assigned values (value gaps may happen). The increment process is very efficient since it uses a… Read More
Introduction In my previous post I talked about UUID surrogate keys and the use cases when there are more appropriate than the more common auto-incrementing identifiers. A UUID database type There are several ways to represent a 128-bit UUID, and whenever in doubt I like to resort to Stack Exchange for an expert advice. Because table identifiers are usually indexed, the more compact the database type the less space will the index require. From the most efficient to the least, here are our options: Some databases (PostgreSQL, SQL Server) offer a dedicated… Read More