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

Expressing Joins: Intro to Speedment - Part 3

DZone 's Guide to

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.

· Database Zone ·
Free Resource

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:

Java
 




x


1
.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:

Java
 




xxxxxxxxxx
1


 
1
joinComponent
2
  .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:

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:

Java
 




xxxxxxxxxx
1


1
joinComponent
2
  .from(StoreManager.IDENTIFIER)
3
  .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:

Java
 




xxxxxxxxxx
1


 
1
joinComponent
2
  .from(StoreManager.IDENTIFIER)
3
  .innerJoinOn(Staff.STORE_ID)
4
  .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:

Java
 




x


1
final Join<Tuple2OfNullables<Store, Staff>> join = joinComponent
2
    .from(StoreManager.IDENTIFIER) 
3
    .innerJoinOn(Staff.STORE_ID).equal(Store.STORE_ID)
4
    .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:

Java
 




x


1
join.stream().forEach(entry -> {
2
  entry.get0().ifPresent(store -> 
3
    System.out.printf("Store ID: %d - ", store.getStoreId()));
4
  entry.get1().ifPresent(staff -> 
5
    System.out.printf("Staff Name: %s %s%n", staff.getFirstName(), staff.getLastName())); 
6
});



The output of the Stream above would be:

Plain Text
 




xxxxxxxxxx
1


1
Store ID: 1 - Staff Name: Mike Hillyer Store ID: 2 - Staff Name: Jon Stephens 


And Speedment generated the following SQL:

MySQL
 




xxxxxxxxxx
1


1
SELECT 
2
    A.`store_id`,A.`manager_staff_id`,A.`address_id`,A.`last_update`, 
3
    B.`staff_id`,B.`first_name`,B.`last_name`,B.`address_id`,
4
    B.`picture`,B.`email`,B.`store_id`,B.`active`,
5
    B.`username`,B.`password`,B.`last_update` 
6
FROM 
7
    `sakila`.`store` AS A 
8
INNER JOIN `sakila`.`staff` AS B 
9
    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:

Java
 




xxxxxxxxxx
1


1
final Join<Tuple2<Store, Staff>> join = joinComponent 
2
    .from(StoreManager.IDENTIFIER) 
3
    .innerJoinOn(Staff.STORE_ID).equal(Store.STORE_ID)
4
    .build(Tuples::of); 



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

Java
 




xxxxxxxxxx
1


1
join.stream().forEach(entry -> {
2
  System.out.printf("Store ID: %d - ", entry.get0().getStoreId());
3
  System.out.printf("Staff Name: %s %s%n", entry.get1().getFirstName(), entry.get1().getLastName()); 
4
});



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:


Java
 




xxxxxxxxxx
1
10


1
public final class JoinResultHolder { 
2
  private final int storeId; 
3
  private final String firstName; 
4
  private final String lastName; 
5
  
6
  public JoinResultHolder(final Store store, final Staff staff) {
7
    this.storeId = store.getStoreId();
8
    this.firstName = staff.getFirstName();
9
    this.lastName = staff.getLastName();
10
  }
11
  
12
  public int getStoreId() { return storeId; }
13
  public String getFirstName() { return firstName; } 
14
  public String getLastName() { return lastName; } 
15
} 



We can use this new result holder like so:

Java
 




xxxxxxxxxx
1


1
final Join<JoinResultHolder> join = joinComponent
2
  .from(StoreManager.IDENTIFIER)
3
  .innerJoinOn(Staff.STORE_ID).equal(Store.STORE_ID) 
4
  .build(JoinResultHolder::new);



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

Java
 




xxxxxxxxxx
1


1
join.stream().forEach(entry -> { 
2
  System.out.printf("Store ID: %d - ", entry.getStoreId()); 
3
  System.out.printf("Staff Name: %s %s%n", entry.getFirstName(), entry.getLastName()); 
4
}); 



Join Filtering

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:

Java
 




xxxxxxxxxx
1


1
final Join<JoinResultHolder> join = joinComponent
2
  .from(StoreManager.IDENTIFIER) 
3
  .innerJoinOn(Staff.STORE_ID).equal(Store.STORE_ID) 
4
  .where(Staff.FIRST_NAME.equal("Mike")) 
5
  .build(JoinResultHolder::new); 



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

Plain Text
 




xxxxxxxxxx
1


 
1
Store ID: 1 - Staff Name: Mike Hillyer 


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

MySQL
 




xxxxxxxxxx
1
10


 
1
SELECT
2
    A.`store_id`,A.`manager_staff_id`,A.`address_id`,A.`last_update`,
3
    B.`staff_id`,B.`first_name`,B.`last_name`,B.`address_id`,
4
    B.`picture`,B.`email`,B.`store_id`,B.`active`,
5
    B.`username`,B.`password`,B.`last_update` 
6
FROM
7
    `sakila`.`store` AS A 
8
INNER JOIN `sakila`.`staff` AS B 
9
    ON (B.`store_id` = A.`store_id`) 
10
    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 website and the Speedment manual.

Topics:
java, join, semantic join, 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 }}