How to map Oracle JSON columns using JPA and Hibernate

Introduction In this article, we are going to see how to map the Oracle JSON column storage types when using JPA and Hibernate. My hibernate-types project has long been supporting JSON for PostgreSQL and MySQL. However, while developing my new and awesome SQL Master Class training, I realized that the current implementation does not handle Oracle JSON column types properly. Luckily, the fix was easy and starting with the 2.7.0 version you can now persist and fetch JSON attributes on Oracle using JPA and Hibernate.

How to get the SQL execution plan on Oracle

Introduction In this article, we are going to see how we can get the SQL execution plan on Oracle. When tuning performance, the execution plan is indispensable as it allows you to understand the operations done by the database when executing a certain SQL query.

How to limit the SQL query result set to Top-N rows only

Introduction In this article, we are going to see how we can limit the SQL query result set to the Top-N rows only. Limiting the SQL result set is very important when the underlying query could end up fetching a very large number of records, which can have a significant impact on application performance.

SQL ORDER BY RANDOM

Introduction In this article, we are going to see how we can sort an SQL query result set using an ORDER BY clause that takes a RANDOM function provided by a database-specific function. This is a very handy trick, especially when you want to shuffle a given result set. Note that sorting a large result set using a RANDOM function might turn out to be very slow, so make sure you do that on small result sets. If you have to shuffle a large result set and limit it afterward, then it’s… Read More

How to implement a database job queue using SKIP LOCKED

Introduction In this article, we are going to see how we can implement a database job queue using SKIP LOCKED. I decided to write this article while answering this Stack Overflow question asked by Rafael Winterhalter. Since SKIP LOCKED is a lesser-known SQL feature, it’s a good opportunity to show you how to use it and why you should employ it, especially when implementing a job queue task.

How to get the actual execution plan for an Oracle SQL query using Hibernate query hints

Introduction While answering this question on the Hibernate forum, I realized that it’s a good idea to write an article about getting the actual execution plan for an Oracle SQL query using Hibernate query hints feature.

JDBC Driver Connection URL strings

Introduction Ever wanted to connect to a relational database using Java and didn’t know the URL connection string? Then, this article is surely going to help you from now on.

The fastest way to update a table row when using Hibernate and Oracle

Introduction Oracle provides several pseudocolumns, and ROWID is one of them. The ROWID pseudocolumn specifies the address of the underlying database record, and according to Oracle documentation, it’s the fastest way to reference a table row. As explained on Ask TOM, there are some operations that might lead to a ROWID change (e.g. partitioning or compacting tables). If that’s the case, then you should not rely on the ROWID pseudocolumn since its value iss no longer consistent. If your database never executes an operation that triggers a ROWID modification, then you should… 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.

How to call Oracle stored procedures and functions with JPA and Hibernate

Introduction This article is part of a series of posts related to calling various relational database systems stored procedures and database functions from Hibernate. The reason for writing this down is because there are many peculiarities related to the underlying JDBC driver support and not every JPA or Hibernate feature is supported on every relational database.