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.
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
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.
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.
Introduction In this article, we are going to see how a relational database executes SQL statements and prepared statements.
Introduction As I explained in this article, multitenancy is an architectural pattern which allows you to isolate customers even if they are using the same hardware or software components. There are multiple ways you can achieve multitenancy, and in this article, we are going to see how you can implement a multitenancy architecture using the database catalog as the unit of isolation.
Introduction In software terminology, multitenancy is an architectural pattern which allows you to isolate customers even if they are using the same hardware or software components. Multitenancy has become even more attractive with the widespread adoption of cloud computing. A relational database system provides a hierarchy structure of objects which, typically, looks like this: catalog -> schema -> table. In this article, we are going to see how we can use each of these database object structures to accommodate a multitenancy architecture.
Introduction When executing an entity query (e.g. JPQL, HQL or Criteria API), you can use any SQL function without having to register it as long as the function is passed directly to the WHERE clause of the underlying SQL statement. However, if the SQL function is used in the SELECT clause, and Hibernate has not registered the SQL function (be it a database-specific or user-defined function), you will have to register the function prior to using it in an entity query. In this article, you are going to learn various ways to… Read More
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.
Introduction Although there is an SQL Standard, every relational database is ultimately unique, and you need to adjust your data access layer so that you get the most out of the relational database in use. In this article, we are going to see what you can do to boost up performance when using MySQL with JPA and Hibernate.