Well designed databases are normalized to reduce the redundancy. This separates the data in a plethora of tables and you must rely on the JOIN operator to reconnect the dots between the data entries. JOINs will add complexity to the query and the syntax required to express the desired result can become messy and error-prone. In this final part of my Intro to Speedment guide, I will show you how JOINs can be  expressed in a type-safe and intuitive manner with Java Streams and Speedment.

Before we can start using Joins, we need to add the JoinBundle to our Application. To do so, we need to add the following line to our ApplicationBuilder:

.withBundle(JoinBundle.class)

This gives us access to a JoinComponent, which is a Speedment component responsible for modeling Joins. We can access it like so:

final JoinComponent joinComponent = application.getOrThrow(JoinComponent.class);

Creating a simple Join

Creating Joins with Speedment is rather simple. As an example, let’s say that we want to join the store and staff tables based on the id of the store and the store_id column in the staff table. 

The JoinComponent contains a single method called from which takes in a table identifier. The table identifier tells Speedment which table to use as the base. In our case that would be the store table. All Managers hold TableIdentifier references to their respective tables, which can be statically accessed by calling Manager#IDENTIFIER.

To start building our Join, we can write the following:

joinComponent.from(StoreManager.IDENTIFIER);

This returns a JoinBuilder which is used to construct Joins and add them to our query. The table below shows the types of Joins that Speedment supports and their equivalent in Java:

SQL	Speedment
INNER JOIN	JoinBuilder::innerJoinOn
LEFT JOIN	JoinBuilder::leftJoinOn
RIGHT JOIN	JoinBuilder::rightJoinOn
CROSS JOIN	JoinBuilder::crossJoinOn

All of the methods above (except crossJoinOn) take a Field as a parameter, which in turn is used to determine which column to join on and which table to include in the query. We’ll be joining our tables using an inner join:

joinComponent

     .from(StoreManager.IDENTIFIER)

     .innerJoinOn(Staff.STORE_ID);

The next step is to add a condition for our Join. We want to match the store_id column in the store table and the store_id column in the staff table. We’re already joining on the store_id column in the staff table from our previous step, the only thing left to do is add an equality comparison with the store_id column in the store table. That can be done like so:

joinComponent

     .from(StoreManager.IDENTIFIER)

     .innerJoinOn(Staff.STORE_ID).equal(Store.STORE_ID);

We’re nearly done. Since we’re using a builder pattern to construct Joins, you can repeat the same steps to add more. For our example we’ll leave it as is and build the join:

final Join<Tuple2OfNullables<Store,Staff>> join = joinComponent

     .from(StoreManager.IDENTIFIER)

     .innerJoinOn(Staff.STORE_ID).equal(Store.STORE_ID)

     .build(); 

This Join object doesn’t do anything on its own, but it can be seen as a reusable object to create Streams. A simple example would be:

join.stream().forEach(entry -> {

    entry.get0().ifPresent(store -> 

        System.out.printf("Store ID: %d - ", store.getStoreID()));

    entry.get1().ifPresent(staff ->

        System.out.printf("Staff Name: %s %s%n", staff.getFirstName(), staff.getLastName()));
});

The output of the Stream above would be:

Store ID: 1 - Staff Name: Mike Hillyer

Store ID: 2 - Staff Name: Jon Stephens

And Speedment generated the following SQL:

SELECT A.`store_id`,A.`manager_staff_id`,A.`address_id`,A.`last_update`, 
B.`staff_id`,B.`first_name`,B.`last_name`,B.`address_id`,
B.`picture`,B.`email`,B.`store_id`,B.`active`,B.`username`,B.`password`,B.`last_update`

FROM `sakila`.`store

AS A INNER JOIN `sakila`.`staff`

AS B ON (B.`store_id` = A.`store_id`) []

Specifying Join constructors

The Join we created above contains a Tuple2OfNullables as its generic parameter, this is the default result holder when the .build() gets called. This variation of a Tuple is not the easiest to work with as it contains a lot of Optional instances.

The JoinBuilder has an overload of the .build() method which allows us to pass a Function with an appropriate number of inputs as a parameter. Let’s say we still wanted to return a Tuple, but wanted to avoid all of the Optional instances. We can do exactly that by using this overload:

final Join<Tuple2<Store,Staff>> join = joinComponent

     .from(StoreManager.IDENTIFIER)

     .innerJoinOn(Staff.STORE_ID).equal(Store.STORE_ID)

     .build(Tuples::of); 

Since this new Tuple variant doesn’t contain any Optional instances, the Stream we wrote earlier becomes a lot shorter:

join.stream().forEach(entry -> {

    System.out.printf("Store ID: %d - ", entry.get0().getStoreID());

    System.out.printf("Staff Name: %s %s%n", entry.get1().getFirstName(), entry.get1().getLastName());
});

Even though we’ve changed the result holder, the output and the generated SQL remains the same.

If the Tuple classes provided by Speedment don’t suit your needs for some reason, you’re always welcome to create your own result holders. In this example we’re only using the store id and the name of the staff. We can create a class that will only store these things:

 private final class JoinResultHolder {

    private final int storeId;

    private final String firstName;

    private final String lastName;



    private JoinResultHolder(final Store store, final Staff staff) {

        this.storeId = store.getStoreId();

        this.firstName = staff.getFirstName();

        this.lastName = staff.getLastName();

    }



    public int getStoreId() {

        return storeId;

    }



    public String getFirstName() {

        return firstName;

    }



    public String getLastName() {

        return lastName;

    }

}

We can use this new result holder like so:

final Join<JoinResultHolder> join = joinComponent

     .from(StoreManager.IDENTIFIER)

     .innerJoinOn(Staff.STORE_ID).equal(Store.STORE_ID)

     .build(JoinResultHolder::new); 

Because we’re returning only the properties we need, the Stream we created earlier becomes even shorter and cleaner:

join.stream().forEach(entry -> {

    System.out.printf("Store ID: %d - ", entry.getStoreID());

    System.out.printf("Staff Name: %s %s%n", entry.getFirstName(), entry.getLastName());
});

Join Filtering

In our previous article, we’ve talked about how you can apply certain conditions to your queries using Stream::filter. A similar thing can be done when working with Joins by using JoinBuilder::where.

Let’s say we wanted to only retrieve entries that contained a first_name column with the value of ‘Mike’.  We can do that like so:

final Join<JoinResultHolder> join = joinComponent

     .from(StoreManager.IDENTIFIER)

     .innerJoinOn(Staff.STORE_ID).equal(Store.STORE_ID)

     .where(Staff.FIRST_NAME.equal("Mike"))

     .build(JoinResultHolder::new); 

If we execute this, you’ll notice that our output only contains one entry now:

Store ID: 1 - Staff Name: Mike Hillyer

Looking at the newly generated SQL we can see that a WHERE clause has been added:

SELECT A.`store_id`,A.`manager_staff_id`,A.`address_id`,A.`last_update`, 
B.`staff_id`,B.`first_name`,B.`last_name`,B.`address_id`,
B.`picture`,B.`email`,B.`store_id`,B.`active`,B.`username`,B.`password`,B.`last_update`

FROM `sakila`.`store

AS A INNER JOIN `sakila`.`staff`

AS B ON (B.`store_id` = A.`store_id`)

WHERE(B.`first_name`=? COLLATE utf8_bin) [String Mike]

Summary

Joins are a fundamental part of SQL. Expressing them in an object oriented manner makes them extremely easy to use with Speedment.

I hope you’ve enjoyed this introductory guide to Speedment. If you wish to learn more about the Stream ORM, make sure to visit the Speedment manual.

Resources

• The Speedment Initializer 
• Speedment OpenSource on GitHub
• Speedment Online Manual
• Sakila sample database