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.

How to map Java and SQL arrays with JPA and Hibernate

Introduction Hibernate custom Types allow you to map all sorts of database specific column types, like IP address, JSON columns, bit sets or SQL arrays. There are two ways to define a custom Hibernate Type: the UserType interface Java and SQL descriptors The latter option is preferred since it allows you to better split the Java-to-JDBC and the JDBC-to-SQL type handling. In this article, we are going to see how you can map SQL arrays to their Java counterpart.

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.