How to JOIN unrelated entities with JPA and Hibernate

Introduction Answering questions on the Hibernate forum is a neverending source of inspiration. The following question is about joining unrelated entities using entity queries, and this post is going to explain how you can do it when using JPA and Hibernate.

How to map Java and SQL arrays with JPA and Hibernate

Introduction In this article, we are going to see how you can map SQL arrays to JPA entity attributes when using Hibernate. Hibernate custom Types allow you to map all sorts of database-specific column types, like IP address, JSON columns, bit sets, or SQL arrays. However, while you can create your own custom Hibernate Types, for mapping PostgreSQL arrays, you don’t need to implement your own Hibernate Type. All you need to do is use the Hibernate Types open-source project.

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 a relational database work

Introduction While doing my High-Performance Java Persistence training, I came to realize that it’s worth explaining how a relational database works, as otherwise, it is very difficult to grasp many transaction-related concepts like atomicity, durability, and checkpoints. In this post, I’m going to give a high-level explanation of how a relational database works internally while also hinting some database-specific implementation details.

Book Review – SQL Antipatterns

Introduction I’ve just finished the wonderful SQL Antipatterns book by Bill Karwin. The book is a must-have reference for any developer that has to interact with a relational database system. This post is a review of what this book is all about and why you should be interested in reading it.

How to map table rows to columns using SQL PIVOT or CASE expressions

Introduction While reading the wonderful SQL Antipatterns book by Bill Karwin, which is a great reference for any developer that needs to interact with a Relational Database System, I found an example where the SQL PIVOT clause would work like a charm. In this post, I’m going to explain how to transpose a ResultSet using PIVOT so that rows become columns.

How to customize an entity association JOIN ON clause with Hibernate @JoinFormula

Introduction As I explained in this previous article, you can map calculated properties using Hibernate @Formula, and the value is generated at query time. In this post, you’ll see how you can use a custom SQL fragment to customize the JOIN relationship between two entities, or an entity and a collection of embeddable types.

A beginner’s guide to SQL injection and how you should prevent it

Introduction One of my readers asked me to answer the following StackOverflow question. Right from the start, I noticed that the entity query was constructed by concatenating strings, which can expose your application to SQL Injection attacks. Unfortunately, I’ve been seeing this problem over and over throughout my career, and not all developers are aware of the serious risk associated with SQL Injection. For this reason, this post is going to demonstrate what damage can SQL Injection do to your system.

How to resolve the Hibernate global database schema and catalog for native SQL queries

Introduction When your relation database system uses multiple schemas, then you can instruct Hibernate to use a global schema using the hibernate.default_schema configuration property: While Hibernate can imply the default schema whenever dealing with entity queries, for native queries, you need a little trick. This post is going to demonstrate how you can imply the default schema for native SQL queries as well.

JOOQ Facts: SQL functions made easy

Introduction The JDBC API has always been cumbersome and error-prone and I’ve never been too fond of using it. The first major improvement was brought by the Spring JDBC framework which simply revitalized the JDBC usage with its JdbcTemplate or the SqlFunction classes, to name a few. But Spring JDBC doesn’t address the shortcoming of using string function or input parameters names and this opened the door for type-safe SQL wrappers such as jOOQ. JOOQ is the next major step towards a better JDBC API and ever since I started using it… Read More