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:


actors.stream().forEach(actor -> System.out.printf(

    "ID: %d, First name: %s, Last name: %s%n",

    actor.getActorId(),

    actor.getFirstName(),

    actor.getLastName()

));

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:


.withLogging(LogType.STREAM)

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


SELECT `actor_id`,`first_name`,`last_name`,`last_update`

FROM `sakila`.`actor` []

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:


actors.stream()

    .filter(actor -> actor.getFirstName().startsWith("A"))

    .forEach(actor -> System.out.printf(

       "ID: %d, First name: %s, Last name: %s%n",

        actor.getActorId(),

        actor.getFirstName(),

        actor.getLastName()

));

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


ID: 29, First name: ALEC, Last name: WAYNE

ID: 34, First name: AUDREY, Last name: OLIVIER

ID: 49, First name: ANNE, Last name: CRONYN

ID: 65, First name: ANGELA, Last name: HUDSON

ID: 71, First name: ADAM, Last name: GRANT

ID: 76, First name: ANGELINA, Last name: ASTAIRE

ID: 125, First name: ALBERT, Last name: NOLTE

ID: 132, First name: ADAM, Last name: HOPPER

ID: 144, First name: ANGELA, Last name: WITHERSPOON

ID: 146, First name: ALBERT, Last name: JOHANSSON

ID: 165, First name: AL, Last name: GARLAND

ID: 173, First name: ALAN, Last name: DREYFUSS

ID: 190, First name: AUDREY, Last name: BAILEY

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:

SELECT `actor_id`,`first_name`,`last_name`,`last_update`

FROM `sakila`.`actor` []

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:


actors.stream()

    .filter(Actor.FIRST_NAME.startsWith("A"))

    .forEach(actor -> System.out.printf(

        "ID: %d, First name: %s, Last name: %s%n",

        actor.getActorId(),

        actor.getFirstName(),

        actor.getLastName()

));

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


SELECT `actor_id`,`first_name`,`last_name`,`last_update`

FROM `sakila`.`actor`

WHERE (`first_name` LIKE BINARY CONCAT(? ,'%')) [String A]

Now all the filtering is handled by the RDBMS, rather than our 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:


actors.stream()

    .filter(Actor.FIRST_NAME.startsWith("A"))

    .limit(10)

    .forEach(actor -> System.out.printf(

        "ID: %d, First name: %s, Last name: %s%n",

        actor.getActorId(),

        actor.getFirstName(),

        actor.getLastName()

));

The output should now contain 10 entries:


ID: 29, First name: ALEC, Last name: WAYNE

ID: 34, First name: AUDREY, Last name: OLIVIER

ID: 49, First name: ANNE, Last name: CRONYN

ID: 65, First name: ANGELA, Last name: HUDSON

ID: 71, First name: ADAM, Last name: GRANT

ID: 76, First name: ANGELINA, Last name: ASTAIRE

ID: 125, First name: ALBERT, Last name: NOLTE

ID: 132, First name: ADAM, Last name: HOPPER

ID: 144, First name: ANGELA, Last name: WITHERSPOON

ID: 146, First name: ALBERT, Last name: JOHANSSON

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


SELECT `actor_id`,`first_name`,`last_name`,`last_update`

FROM `sakila`.`actor`

WHERE (`first_name` LIKE BINARY CONCAT(? ,'%'))

LIMIT ? [String A, Long 10]

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:


actors.stream()

    .filter(Actor.FIRST_NAME.startsWith("A"))

    .skip(5)

    .limit(10)

    .forEach(actor -> System.out.printf(

       "ID: %d, First name: %s, Last name: %s%n",

        actor.getActorId(),

        actor.getFirstName(),

        actor.getLastName()

));

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:


ID: 76, First name: ANGELINA, Last name: ASTAIRE

ID: 125, First name: ALBERT, Last name: NOLTE

ID: 132, First name: ADAM, Last name: HOPPER

ID: 144, First name: ANGELA, Last name: WITHERSPOON

ID: 146, First name: ALBERT, Last name: JOHANSSON

ID: 165, First name: AL, Last name: GARLAND

ID: 173, First name: ALAN, Last name: DREYFUSS

ID: 190, First name: AUDREY, Last name: BAILEY

The newly generated SQL should contain an OFFSET clause:


SELECT `actor_id`,`first_name`,`last_name`,`last_update`

FROM `sakila`.`actor`

WHERE (`first_name` LIKE BINARY CONCAT(? ,'%'))

LIMIT ? OFFSET ? [String A, Long 10, Long 5]

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:


actors.stream()

    .sorted(Actor.LAST_NAME)

    .forEach(actor -> System.out.printf(

       "ID: %d, First name: %s, Last name: %s%n",

        actor.getActorId(),

        actor.getFirstName(),

        actor.getLastName()

));

This is the output of our new Stream:


ID: 76, First name: CHRISTIAN, Last name: AKROYD

ID: 125, First name: KIRSTEN, Last name: AKROYD

ID: 132, First name: DEBBIE, Last name: AKROYD

ID: 144, First name: CUBA, Last name: ALLEN

ID: 146, First name: KIM, Last name: ALLEN

ID: 165, First name: MERYL, Last name: ALLEN

ID: 173, First name: ANGELINA, Last name: ASTAIRE

ID: 190, First name: RUSELL, Last name: BACALL

ID: 173, First name: JESSICA, Last name: BAILEY

ID: 173, First name: AUDREY, Last name: BAILEY

-----------TRUNCATED-----------

And this is the SQL that Speedment generated:


SELECT `actor_id`,`first_name`,`last_name`,`last_update`

FROM `sakila`.`actor`

ORDER BY `last_name` COLLATE utf8_bin ASC []

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:


actors.stream()

    .sorted(Actor.LAST_NAME.thenComparing(Actor.FIRST_NAME.comparator()))

    .forEach(actor -> System.out.printf(

       "ID: %d, First name: %s, Last name: %s%n",

        actor.getActorId(),

        actor.getFirstName(),

        actor.getLastName()

));

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


ID: 76, First name: CHRISTIAN, Last name: AKROYD

ID: 132, First name: DEBBIE, Last name: AKROYD

ID: 125, First name: KIRSTEN, Last name: AKROYD

ID: 144, First name: CUBA, Last name: ALLEN

ID: 146, First name: KIM, Last name: ALLEN

ID: 165, First name: MERYL, Last name: ALLEN

ID: 173, First name: ANGELINA, Last name: ASTAIRE

ID: 190, First name: RUSELL, Last name: BACALL

ID: 173, First name: AUDREY, Last name: BAILEY

ID: 173, First name: JESSICA, Last name: BAILEY

-----------TRUNCATED-----------

This is the SQL that Speedment generated:


SELECT `actor_id`,`first_name`,`last_name`,`last_update`

FROM `sakila`.`actor`

ORDER BY `last_name`

COLLATE utf8_bin ASC, `first_name`

COLLATE utf8_bin ASC []

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.

Resources

About

Mislav Miličević

Mislav Miličević started to develop at the age of 11. When he was 14 years old he began making custom client and server modifications for the game Minecraft. Today he is an experienced Java developer and blogger working as a software developer at Speedment.