Expressing Joins: Intro to Speedment - Part 3
Expressing Joins: Intro to Speedment - Part 3
Well designed databases are normalized to reduce the redundancy. Read this article to learn how to join normalized tables effortlessly in Java using semantic joins.
Join the DZone community and get the full member experience.Join For Free
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:
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:
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:
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:
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:
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:
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:
The output of the Stream above would be:
And Speedment generated the following SQL:
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:
Since this new Tuple variant doesn't contain any Optional instances, the Stream we wrote earlier becomes a lot shorter:
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:
We can use this new result holder like so:
Because we're returning only the properties we need, the Stream we created earlier becomes even shorter and cleaner:
In my previous article, I'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:
If we execute this, you'll notice that our output only contains one entry now:
Looking at the newly generated SQL we can see that a
WHERE clause has been added:
Joins are a fundamental part of SQL. Expressing them in an object-oriented manner makes them extremely easy to use with Speedment.
Published at DZone with permission of Mislav Milicevic . See the original article here.
Opinions expressed by DZone contributors are their own.