Occasionally, you want to write a SQL query and fetch a hierarchy of data, whose flat representation may look like this: SELECT id, parent_id, label FROM t_directory; The result might be: |id |parent_id|label | |---|---------|-------------------| |1 | |C: | |2 |1 |eclipse | |3 |2 |configuration | |4 |2 |dropins | |5 |2 |features | … Continue reading How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ
Tag: Collector
The Many Different Ways to Fetch Data in jOOQ
The jOOQ API is all about convenience, and as such, an important operation (the most important one?) like fetch() must come with convenience, too. The default way to fetch data is this: Result<Record1<String>> result = ctx.select(BOOK.TITLE) .from(BOOK) .fetch(); for (Record1<String> record : result) { // ... } It fetches the entire result set into memory … Continue reading The Many Different Ways to Fetch Data in jOOQ
Using JDK Collectors to De-duplicate parent/child nested collections
In classic SQL (i.e. before jOOQ's awesome MULTISET operator), nested collections were fetched using ordinary (outer) joins. An example of such a query would be a query running against the sakila database to fetch actors and their films. Using jOOQ: Result<?> result = ctx.select( ACTOR.ACTOR_ID, ACTOR.FIRST_NAME, ACTOR.LAST_NAME, FILM.FILM_ID, FILM.TITLE) .from(ACTOR) .leftJoin(FILM_ACTOR).on(ACTOR.ACTOR_ID.eq(FILM_ACTOR.ACTOR_ID)) .leftJoin(FILM).on(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID)) .orderBy( ACTOR.ACTOR_ID, FILM.FILM_ID) … Continue reading Using JDK Collectors to De-duplicate parent/child nested collections
Use ResultQuery.collect() to Implement Powerful Mappings
In our opinion, any Iterable<T> should offer a <R> collect(Collector<T, ?, R>) method to allow for transforming the the content to something else using standard JDK collectors, jOOλ collectors from org.jooq.lambda.Agg or your own. When using jOOQ, you don't have to wait for the JDK to finally add these useful utilities to the Iterable API. … Continue reading Use ResultQuery.collect() to Implement Powerful Mappings
Writing Custom Aggregate Functions in SQL Just Like a Java 8 Stream Collector
All SQL databases support the standard aggregate functions COUNT(), SUM(), AVG(), MIN(), MAX(). Some databases support other aggregate functions, like: EVERY() STDDEV_POP() STDDEV_SAMP() VAR_POP() VAR_SAMP() ARRAY_AGG() STRING_AGG() But what if you want to roll your own? Java 8 Stream Collector When using Java 8 streams, we can easily roll our own aggregate function (i.e. a … Continue reading Writing Custom Aggregate Functions in SQL Just Like a Java 8 Stream Collector