How to Emulate Partial Indexes in Oracle

A very interesting feature of the SQL Server and PostgreSQL databases (and some others, including SQLite) is the partial index (sometimes also called "filtered index"). That's an index that contains only "parts" of the table data. For instance, we can write the following index in SQL Server and PostgreSQL: CREATE INDEX i ON message WHERE … Continue reading How to Emulate Partial Indexes in Oracle

A Functional Programming Approach to Dynamic SQL with jOOQ

Typesafe embedded DSLs like jOOQ are extremely powerful for dynamic SQL, because the query you're constructing with the jOOQ DSL is a dynamic query by nature. You're constructing a query expression tree using a convenient API (the "DSL"), even if you think your SQL statement is static. For instance: for (Record rec : ctx.select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME) … Continue reading A Functional Programming Approach to Dynamic SQL with jOOQ

A Probably Incomplete, Comprehensive Guide to the Many Different Ways to JOIN Tables in SQL

Perhaps the most powerful SQL feature is the JOIN operation. It is the envy of all non-relational databases, because the concept is so simple, yet so universally applicable, when you want to "combine" two data sets. Put simply, when joining two tables, you're combining every row from one table with every row from another table, … Continue reading A Probably Incomplete, Comprehensive Guide to the Many Different Ways to JOIN Tables in SQL

jOOQ 3.10 Supports SQL Server’s Table Valued Parameters

SQL Server has this nice feature called table-valued parameters (TVP), where users can pass table variables to a stored procedure for bulk data processing. This is particularly nice when the stored procedure is an inline table valued function, i.e. a function that returns a table as well. For instance: CREATE TYPE numbers AS TABLE (i … Continue reading jOOQ 3.10 Supports SQL Server’s Table Valued Parameters

How to Write a Quick and Dirty Converter in jOOQ

One of jOOQ's most powerful features is the capability of introducing custom data types, pretending the database actually understands them. For instance, when working with SQL TIMESTAMP types, users mostly want to use the new JSR-310 LocalDateTime, rather than the JDBC java.sql.Timestamp type. In jOOQ 3.9+, this is a no brainer, as we've finally introduced … Continue reading How to Write a Quick and Dirty Converter in jOOQ

How to Prevent JDBC Resource Leaks with JDBC and with jOOQ

In a recent consulting gig, I was analysing a client's connection pool issue in a productive system, where during some peak loads, all the Java processes involving database interactions just started queueing up until nothing really worked anymore. No exceptions, though, and when the peak load was gone in the evening, everything returned back to … Continue reading How to Prevent JDBC Resource Leaks with JDBC and with jOOQ