{{announcement.body}}
{{announcement.title}}

Database Queries as Streams: Intro to Speedment Part 2

DZone 's Guide to

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.

· Java Zone ·
Free Resource

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:

Java


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:

Java


The next time you run your program, along with the output produced by the Stream, you should see some SQL that Speedment generates:

MySQL


Query Conditions

Adding 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:

Java


If we execute this and look at the output, you'll find that it works as it should:

Plain Text


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:

SQL


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:

Java


The output of this Stream is still the same as before, but the generated SQL should be different:

MS SQL


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:

Java


The output should now contain 10 entries:

Plain Text


If we look at the generated SQL, we can see that a LIMIT clause has been added:

MS SQL


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:

Java


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:

Plain Text


The newly generated SQL should contain an OFFSET clause:

MySQL


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.

Query Sorting

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:

Java


This is the output of our new Stream:

Plain Text


And this is the SQL that Speedment generated:

MySQL


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:

Java


If we run this, we can see that the actors that have matching last names are sorted based on their first name:

Plain Text


This is the SQL that Speedment generated:

MySQL


Summary

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.

Topics:
java, java stream, orm, speedment, sql, tutorial

Published at DZone with permission of Mislav Milicevic . See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}