The best way to map a @OneToMany relationship with JPA and Hibernate

Introduction While adding a @OneToMany relationship is very easy with JPA and Hibernate, knowing the right way to map such an association so that it generates very efficient SQL statements is definitely not a trivial thing to do. In a relational database system, a one-to-many association links two tables based on a Foreign Key column so that the child table record references the Primary Key of the parent table row. As straightforward as it might be in a relational database, when it comes to JPA, the one-to-many database association can be represented… Read More

How to find which statement failed in a JDBC Batch Update

Introduction Yesterday, my Danish friend, Flemming Harms, asked my a very interesting question related to when a JDBC batch update fails. Basically, considering we are going to group several DML statements in a batch, we need a way to tell which statement is the cause of the failure. This post is going to answer this question in more detail.

How to store date, time, and timestamps in UTC time zone with JDBC and Hibernate

Introduction Dealing with time zones is always challenging. As a rule of thumb, it’s much easier if all date/time values are stored in the UTC format, and, if necessary, dealing with time zone conversions in the UI only. This article is going to demonstrate how you can accomplish this task with JDBC and the awesome hibernate.jdbc.time_zone configuration property.

The best way to soft delete with Hibernate

Introduction Each database application is unique. While most of the time, deleting a record is the best approach, there are times when the application requirements demand that database records should never be physically deleted. So who uses this technique? For instance, StackOverflow does it for all Posts (e.g. Questions and Answers). The StackOverflow Posts table has a ClosedDate column which acts as a soft delete mechanism since it hides an Answer for all users who have less than 10k reputation. If you’re using Oracle, you can take advantage of its Flashback capabilities,… Read More

How does MVCC (Multi-Version Concurrency Control) work

Introduction In this article, I’m going to explain how the MVCC (Multi-Version Concurrency Control) mechanism works using PostgreSQL as a reference implementation. In Concurrency Control theory, there are two ways you can deal with conflicts: You can avoid them, by employing a pessimistic locking mechanism (e.g. Read/Write locks, Two-Phase Locking) You can allow conflicts to occur, but you need to detect them using an optimistic locking mechanism (e.g. logical clock, MVCC) Because MVCC (Multi-Version Concurrency Control) is such a prevalent Concurrency Control technique (not only in relational database systems, in this article,… Read More