Database Queries as Streams: Intro to Speedment Part 2
Database Queries as Streams: Intro to Speedment Part 2
Java Streams and SQL share a lot of similarities in their core, allowing us to express SQL queries as pure Java Streams.
Join the DZone community and get the full member experience.Join For Free
Since their introduction in Java 8, Streams have become a turning point for Java. Streams introduced a declarative approach to Collection processing which makes its API extremely easy to use, allowing developers to express their thoughts in seconds.
The Stream API is not exclusive to Java Collections. In reality, anyone can adapt the API for their specific purpose as long as they know how to implement it. This is the case with Speedment. Speedment is a Java ORM that uses the Stream API to query relational databases.
In the first part of the Intro to Speedment guide we created a fresh Speedment project and at the very end, we executed a simple query to show that everything is working as it should. In this continuation of the previous article, we will be introducing a bit of complexity to our queries.
Logging Your Streams
At the end of the previous article we executed the following bit of code:
If we didn't create a Speedment project from scratch, we could've just used a regular Java Collection and claim it was reading from a database. So, to make sure that we're not trying to pull a fast one on you (and for you to see what Speedment is actually generating), we will log all of our Streams.
To enable Stream logging, we need to hop back over to our ApplicationBuilder and add the following line:
The next time you run your program, along with the output produced by the Stream, you should see some SQL that Speedment generates:
WHERE clauses to your queries is rather simple with Speedment. Just use the Stream::filter operation. Let's say we want to find all actors whose first name starts with an A and print their ID, first name, and last name. Normally, if we were to use a Stream provided by a Java Collection, we could do something like this:
If we execute this and look at the output, you'll find that it works as it should:
But wait! Is it really working as it should? Well, all of the actors we see here have a first name that starts with the letter A, so it must be working correctly. Right? NOPE! Even though the final result is correct, the way we got to this result is not. Let's look at the SQL that Speedment generated:
Would you look at that, a
WHERE clause nowhere to be found. This means that the filtering is being handled by our application rather than the RBMS itself. Thankfully, it's very easy to avoid these situations.
When you generated your project for the first time, we talked about the Java Entities that Speedment generates. Those entities contain a bunch of Field objects that represent columns in the table you are working with. You can use those fields to create specialized predicates that Speedment can optimize when constructing SQL queries.
Let's replace the old predicate with the Speedment one. The Stream should look like this now:
The output of this Stream is still the same as before, but the generated SQL should be different:
Now, all the filtering is handled by the RDBMS, rather than our own application, leading to a performance boost.
Query Limits and Offsets
To put a limit on your query, you can use the Stream::limit operation. Let's say we wanted to limit the results of our previous query to only 10 entries. We can do something like this:
The output should now contain 10 entries:
If we look at the generated SQL, we can see that a
LIMIT clause has been added:
Offsets are applied in a similar manner, only instead of using Stream::limit, use Stream::skip. Let's say we want to offset the previous query by 5 entries, we could do something like this:
The output now contains 8 entries because there were no more actors with a name that starts with A, but the first 5 entries from the previous query were skipped:
The newly generated SQL should contain an OFFSET clause:
One thing to note before we continue is the order the skip and limit operations were applied, as their semantics are not the same. Using .skip().limit() applies an offset and then limits the result set, allowing Speedment to generate a LIMIT X OFFSET Y clause. Using .limit().skip() first limits the result set and then skips the entries in the limited result set. In this case, Speedment will only apply the LIMIT X clause and skip the entries through your application.
Sorting in SQL is done using the ORDER BY clause. The Stream equivalent is Stream::sorted. As an example, we will be sorting our result in an alphabetical order. The Stream::sorted operation takes a Comparator as an input. Luckily, our generated fields implement the Comparator interface, so we can pass them directly to the method. If we were to sort by the actor's first name, we would do something like this:
This is the output of our new Stream:
And this is the SQL that Speedment generated:
Let's say we wanted to sort by the actor's full name, meaning we would sort by their last name and then by their first name, if there were duplicate last names. This can be done easily using Comparator::thenComparing:
If we run this, we can see that the actors that have matching last names are sorted based on their first name:
This is the SQL that Speedment generated:
Java Streams and SQL share a lot of similarities in their core, allowing us to express SQL queries as pure Java Streams. Speedment takes advantage of this fact by providing a great Stream API for database interaction.
I hope the second part of my introduction to Speedment has been interesting to follow along. In the next and final part, we will be covering Joins in Speedment, taking the knowledge from this article to the next level.
Published at DZone with permission of Mislav Milicevic . See the original article here.
Opinions expressed by DZone contributors are their own.