SQL Operation Order
Introduction In this article, we are going to see what is the standard SQL operation order. Once you understand the order in which SQL operations are executed, then it will be clear why the Oracle legacy pagination query required a Derived Table in order to make sure that the ROWNUM pseudocolumn is computed after executing the ORDER BY clause.
The best way to call SQL Server stored procedures with jOOQ
Introduction In this article, we are going to see what is the best way to call SQL Server stored procedures with jOOQ. I decided to write this article because stored procedures and database functions are extremely useful for data-intensive applications, and sometimes, they are the only solution to process data efficiently. While SQL remains the de-facto way to query data, when it comes to processing records, stored procedures allow us to control the transaction boundaries so that we can release the locks acquired for the modified records sooner and make sure that… Read More
Testcontainers Database Integration Testing
Introduction In this article, we are going to see how we can use Testcontainers for database integration testing. If you are still using HSQLDB or H2 to test your Spring Boot application that runs on Oracle, SQL Server, PostgreSQL or MySQL in production, then you are better off switching to Testcontainers.
The best way to map MonetaryAmount with JPA and Hibernate
Introduction In this article, we are going to see what is the best way to map the MonetaryAmount object from Java Money and the Currency API when using JPA and Hibernate. While the Java Money and Currency API define the specification, like the MonetaryAmount interface, the Moneta project provides a reference implementation for this API.
Maximum number of database connections
Introduction Have you ever wondered what the maximum number of database connections provided by a given RDBMS is? In this article, we are going to see what limits the number of database connections, no matter if you’re using Oracle, SQL Server, PostgreSQL, or MySQL.
Default Database Primary, Foreign, and Unique Key Indexing
Introduction In this article, we are going to see what is the default database Primary, Foreign, and Unique Key indexing strategy when using Oracle, SQL Server, PostgreSQL, and MySQL. So, if you are wondering whether one of those top relational databases creates a default index whenever you are adding a Primary Key, Foreign Key, or Unique Key constraint, then this article is going to provide you with the answer you’ve been waiting for.
SQL Recursive WITH CTE queries
Introduction In this article, we are going to see how SQL Recursive WITH CTE (Common Table Expression) queries work and how we can apply them for processing hierarchical data models.
SQL CROSS APPLY – A Beginner’s Guide
Introduction In this article, we are going to see how the SQL CROSS APPLY works and how we can use it to cross-reference rows from a subquery with rows in the outer table and build compound result sets. CROSS APPLY is basically equivalent to the LATERAL JOIN, and it’s been supported by SQL Server since version 2005 and Oracle since version 12c.
SQL Derived Table or Inline View
Introduction In this article, we are going to see what an SQL Derived Table or Inline View is and how you can use it to bypass the standard SQL operation order. While the SQL Standard uses the term Derived Table for this feature, some relational database systems call it Inline View.
