PostgreSQL triggers and isolation levels

Introduction In this article, we are going to see how the PostgreSQL isolation levels guarantee read and write consistency when executing database triggers. While relational database systems provide strong data integrity guarantees, it’s very important to understand how the underlying transactional engine works in order to choose the right design for your data access layer.

How to map a PostgreSQL ARRAY to a Java List with JPA and Hibernate

Introduction In this article, I’m going to show you how to map PostgreSQL ARRAY column types (e.g., text, int, double, enum, date, timestamp, UUID) to Java List entity attributes with JPA and Hibernate. While the Hibernate Types project has been supporting PostgreSQL ARRAY column types since the very first version, prior to version 2.9, you could only map database ARRAY columns to Java Array entity attributes, as illustrated by this article. Mapping database ARRAY columns to java.util.List entity attributes is a much better approach than using the previous Java Array option since,… Read More

Java and Database Newsletter, Issue 2

Introduction Welcome to a new issue of the Java and Database newsletter in which we share articles, announcements, and StackOverflow answers that are very relevant to any developer who interacts with a database system from Java.

SQL EXISTS and NOT EXISTS

Introduction In this article, we are going to see how the SQL EXISTS operator works and when you should use it. Although the EXISTS operator has been available since SQL:86, the very first edition of the SQL Standard, I found that there are still many application developers who don’t realize how powerful SQL subquery expressions really are when it comes to filtering a given table based on a condition evaluated on a different table.

Java and Database Newsletter, Issue 1

Introduction I started my newsletter in November 2015, and every time I was publishing a new article, I would send it to my newsletter subscribers as well. This year, I decided to change the format of my newsletter. Instead of creating a new issue for every article, the newsletter will now contain links to the best articles I read over the past two weeks.

A beginner’s guide to SQL CROSS JOIN

Introduction In this article, we are going to see how a CROSS JOIN works, and we will also make use of this SQL join type to build a poker card game.

PostgreSQL trigger consistency check

Introduction In this article, we are going to see how to implement a non-trivial consistency check using a PostgreSQL INSERT and UPDATE trigger. By using a database trigger after executing an INSERT or UPDATE, we can ensure that the sum of salaries in a given department does not exceed the maximum budget allocated for the given department.

A beginner’s guide to database deadlock

Introduction In this article, we are going to see how a deadlock can occur in a relational database system, and how Oracle, SQL Server, PostgreSQL, or MySQL recover from a deadlock situation.

JDBC Driver Maven dependency list

Introduction Ever wanted to connect to a relational database using Java and didn’t know which JDBC Driver Maven dependency to use? If so, this article is surely going to help you from now on.

Mapping PostgreSQL Interval to Java Duration with Hibernate

Introduction In this article, we are going to see how to map the PostgreSQL interval column type to a Java Duration object using Hibernate and the hibernate-types project. Another very useful feature introduced by the hibernate-types project is that all types extending the ImmutableType can now be treated as standard org.hibernate.type.Type, therefore enabling a much better Hibernate Core API integration.