I stumbled upon a very interesting question on Stack Overflow about how to use jOOQ's MULTISET operator to nest a collection, and then filter the result by whether that nested collection contains a value. The question is jOOQ specific, but imagine, you have a query that nests collections using JSON in PostgreSQL. Assuming, as always, … Continue reading How to Filter a SQL Nested Collection by a Value
Tag: array
Use MULTISET Predicates to Compare Data Sets
Questions that might be a bit more difficult to solve using ordinary SQL are questions of the kind: What films have the same actors as a given film X? As always, we're using the sakila database for this example. What would be a possible way to solve this with SQL (for example, PostgreSQL, to be … Continue reading Use MULTISET Predicates to Compare Data Sets
Could we Have a Language That Hides Collections From Us?
I just fixed a bug. The fix required me to initialise an Object[] array with the init values for each type, instead of just null, i.e. false for boolean, 0 for int, 0.0 for double, etc. So, instead of just doing: Object[] converted = new Object[parameterTypes.length]; I needed: Object[] converted = new Object[parameterTypes.length]; for (int … Continue reading Could we Have a Language That Hides Collections From Us?
Beautiful SQL: Lateral Unnesting of Array Columns
Sometimes, SQL can just be so beautiful. One of the less mainstream features in SQL is the array type (or nested collections). In fact, it's so not mainstream that only 2 major databases actually support it: Oracle and PostgreSQL (and HSQLDB and H2 in the Java ecosystem). In PostgreSQL, you can write: CREATE TABLE blogs … Continue reading Beautiful SQL: Lateral Unnesting of Array Columns
What if every object was an array? No more NullPointerExceptions!
To NULL or not to NULL? Programming language designers inevitably have to decide whether they support NULLs or not. And they've proven to have a hard time getting this right. NULL is not intuitive in any language, because NULL is an axiom of that language, not a rule that can be derived from lower-level axioms. … Continue reading What if every object was an array? No more NullPointerExceptions!
How to Execute Something Multiple Times in Java
When writing unit / integration tests, you often want to execute something multiple times, with different configurations / parameters / arguments every time. For instance, if you want to pass a "limit" or "timeout" or any other argument value of 1, 10, and 100, you could do this: @Test public void test() { runCode(1); runCode(10); … Continue reading How to Execute Something Multiple Times in Java
The power of REF CURSOR types
Many RDBMS have started implementing support for some sort of CURSOR, REF CURSOR or ARRAY/TABLE types. These types have roughly the same semantics as JDBC's java.sql.Array and java.sql.ResultSet. In principle, such types can appear anywhere in SQL, even if some RDBMS limit support to a certain functionality subset. What exactly are these types? ARRAY types … Continue reading The power of REF CURSOR types