In the past, we’ve been providing you with a new article every Friday about what’s new in Java 8. It has been a
very exciting blog series, but we would like to focus again more on our core content, which is Java and SQL. We will still be occasionally blogging about Java 8, but no longer every Friday (as some of you have already notice).
In this last, short post of the Java 8 Friday series, we’d like to re-iterate the fact that we believe that the future belongs to
functional relational data transformation (as opposed to ORM). We’ve spent about 20 years now using the
object-oriented software development paradigm. Many of us have been very dogmatic about it. In the last 10 years, however, a “new” paradigm has started to get increasing traction in programming communities:
Functional programming.
Functional programming is not
that new, however. Lisp has been a very early functional programming language. XSLT and SQL are also somewhat functional (and declarative!). As we’re big fans of SQL’s functional (and declarative!) nature, we’re quite excited about the fact that we now have sophisticated tools in Java to transform tabular data that has been extracted from SQL databases. Streams!
SQL ResultSets are very similar to Streams
As we’ve pointed out before,
JDBC ResultSets and Java 8 Streams are quite similar. This is even more true when you’re using jOOQ, which replaces the JDBC ResultSet by an
org.jooq.Result
, which extends
java.util.List
, and thus automatically inherits all Streams functionality. Consider the following query that allows fetching a one-to-many relationship between BOOK and AUTHOR records:
Map<Record2<String, String>,
List<Record2<Integer, String>>> booksByAuthor =
// This work is performed in the database
// --------------------------------------
ctx.select(
BOOK.ID,
BOOK.TITLE,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME
)
.from(BOOK)
.join(AUTHOR)
.on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.orderBy(BOOK.ID)
.fetch()
// This work is performed in Java memory
// -------------------------------------
.stream()
// Group BOOKs by AUTHOR
.collect(groupingBy(
// This is the grouping key
r -> r.into(AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME),
// This is the target data structure
LinkedHashMap::new,
// This is the value to be produced for each
// group: A list of BOOK
mapping(
r -> r.into(BOOK.ID, BOOK.TITLE),
toList()
)
));
The fluency of the Java 8 Streams API is very idiomatic to someone who has been used to writing SQL with jOOQ. Obviously, you can also use something other than jOOQ, e.g. Spring’s JdbcTemplate, or Apache Commons DbUtils, or just wrap the JDBC ResultSet in an Iterator…
What’s very nice about this approach, compared to ORM is the fact that there is no magic happening at all. Every piece of mapping logic is explicit and, thanks to Java generics, fully typesafe. The type of the
booksByAuthor
output is complex, and a bit hard to read / write, in this example, but it is also fully descriptive and useful.
The same functional transformation with POJOs
If you aren’t too happy with using jOOQ’s
Record2
tuple types, no problem. You can specify your own data transfer objects like so:
class Book {
public int id;
public String title;
@Override
public String toString() { ... }
@Override
public int hashCode() { ... }
@Override
public boolean equals(Object obj) { ... }
}
static class Author {
public String firstName;
public String lastName;
@Override
public String toString() { ... }
@Override
public int hashCode() { ... }
@Override
public boolean equals(Object obj) { ... }
}
With the above DTO, you can now leverage
jOOQ’s built-in POJO mapping to transform the jOOQ records into your own domain classes:
Map<Author, List<Book>> booksByAuthor =
ctx.select(
BOOK.ID,
BOOK.TITLE,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME
)
.from(BOOK)
.join(AUTHOR)
.on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.orderBy(BOOK.ID)
.fetch()
.stream()
.collect(groupingBy(
// This is the grouping key
r -> r.into(Author.class),
LinkedHashMap::new,
// This is the grouping value list
mapping(
r -> r.into(Book.class),
toList()
)
));
Explicitness vs. implicitness
At Data Geekery, we believe that a new time has started for Java developers. A time where Annotatiomania™ (finally!) ends and people stop assuming all that implicit behaviour through annotation magic. ORMs depend on a huge amount of specification to explain how each annotation works with each other annotation. It is hard to reverse-engineer (or debug!) this kind of not-so-well-understood annotation-language that JPA has brought to us.
On the flip side, SQL is pretty well understood. Tables are an easy-to-handle data structure, and if you need to transform those tables into something more object-oriented, or more hierarchically structured, you can simply apply functions to those tables and group values yourself! By grouping those values explicitly, you stay in full control of your mapping, just as with jOOQ, you stay in full control of your SQL.
This is why we believe that in the next 5 years, ORMs will lose relevance and people start embracing
explicit,
stateless and
magicless data transformation techniques again, using Java 8 Streams.
Like this:
Like Loading...
How are BOOK and AUTHOR defined?
They’re generated from your database meta data. As the examples are using jOOQ, they’re generated (and then static imported) using the jOOQ code generator
Thank you for the tutorial!
Is it correct, that Book has to have an empty constructor? Otherwise it does not work for me. I would like to have an “immutable” class, is it possible to omit der empty constructor somehow? (Author works well with no empty constructor)
Thank you for your feedback. The
Result.into(Class)
andRecord.into(Class)
methods simply apply jOOQ’s https://www.jooq.org/javadoc/latest/org/jooq/impl/DefaultRecordMapper.html. You can find the mapping rules in that Javadoc. Immutable POJOs are supported as well.Perfect! Thank you very much, missed that one :) Is there a possibility to omit the right side in case of a leftJoin, so that the collection does not contain one object filled with null values?
Hmm, I’m not sure why you’d want to omit the right side in a left join. Why would you even join then? What’s the expected result / behaviour?
Oh sorry, sorry for my question being ambiguous. I just want to omit the right side, if it is a null value (so that in this case there is not a collection created with a single element with null values inside). Thank you!
Hmm, you mean as in an ordinary inner join?
Not an inner join, because I want to get the left side (as key) with an empty collection (as value)
Oh, I understand, e.g. when using
Result.intoGroups()
or something like that? I’m afraid that’s not possible out of the box, as the NULL produced by left join cannot be distinguished from “legitimate” NULL, automatically. If you’re using PostgreSQL or Oracle, you could produce nested collections with PostgreSQL’sarray_agg()
or Oracle’sCOLLECT()
, directly in the database.