Multiline String literals with Java Text Blocks

Introduction In this article, show you how the new Java Text Blocks can help you increase the readability of JPQL, SQL queries, or JSON string values. Back in 2005, I was developing a .NET project and really liked the C# Verbatim String Literal feature since it allows you to write a multiline string value without having to concatenate each individual line. The Java 13 Text Blocks feature now offers the possibility of creating a multiline Java String values.

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.

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

MySQL 8 support for custom SQL CHECK constraints

Introduction In this article, we are going to test the MySQL 8 implementation of custom SQL CHECK constraints. Although the CHECK clause is a standard SQL feature, prior to MySQL 8.0.16, the clause was parsed and ignored, so this functionality could only be emulated via BEFORE INSERT and UPDATE triggers.

How does a relational database execute SQL statements and prepared statements

Introduction In this article, we are going to see how a relational database executes SQL statements and prepared statements.

How to query parent rows when all children must match the filtering criteria with SQL and Hibernate

Introduction The Hibernate forum is a never-ending source of inspiration when it comes to solving real-life problems you might bump into when developing an enterprise application. For instance, this post asks about a JPQL query which should fetch a given parent entity when all its child entities match the given filtering criteria.

How to enable multi-row inserts with the PostgreSQL reWriteBatchedInserts configuration property

Introduction Vladimir Sitnikov has been working on many optimizations to the PostgreSQL JDBC Driver, and one of these is the reWriteBatchedInserts configuration property which he recently told me about. In this article, you will see how the reWriteBatchedInserts JDBC configuration property works in PostgreSQL, and how it allows you to rewrite INSERT statements into a multi-VALUE INSERT.

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.