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.
PostgreSQL reWriteBatchedInserts configuration property
Introduction The PostgreSQL JDBC Driver has been adding a lot of very useful optimizations, and one of the lesser-known ones is the reWriteBatchedInserts configuration property. 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 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 Hypersistence Utils 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.
