How to implement an SQL join — with JPA

In my last blog post about Spring JPA, I went through a very simple example to demonstrate what Spring JPA is and how to use it.

I want to elaborate on this very simple example and show what to do in JPA if you want to create a query where you join tables, which is something you have to do a lot when fetching data from a relational database.

The database implementation

We’ll supply the Customer entity from my previous example with an Order entity.
As you might recall, an Entity represents a table in a relational database.
So let’s say we now have a Customers table and an Orders table.
We want to join these tables because we want to see, for instance, the status of a customer’s orders.

The tables could look something like this, with a one-to-many-relation:

A customer can have multiple orders; an order belongs to only one customer.

If we want to fetch all orders for every customer the SQL could then be:

SELECT c.customer_id,
o.order_id,
o.order_status
FROM Customers c
INNER JOIN Orders o
ON c.customer_id = o.customer_id;

How to implement the join with JPA

We now want to implement this SQL statement using JPA.

To keep the example simple I will concentrate on the one-to-many-relation we started with.

I will also keep my example “unidirectional”, which means that I only want to fetch Order data from Customer, not the other way round.

First, we have to add an OrderEntity to the application that looks much like the CustomerEntity we already have:

@Entity, @Table, @Column and @Id are all JPA annotations.

Back to CustomerEntity, we add a private field called ordersList that points to the new Order entity — or table to be joined on.

Since this is a many-relation to the other entity, we’ll use the datatype “Set” so it can hold multiple objects.

Above it, we add @JoinColumn to tell JPA that this is a join:

This variable is now prepared to hold a list of entity objects representing the table Orders.

We add a get method as well; getOrdersList().

The second step is to add a JPA @OneToMany notation to state that in this case, it is a one-to-many relation.

It goes above the new attribute like so:

The “name” parameter is the name we want to give the referenced column for JPA to use; the “referencedColumnName” is what the column is called in the database.

Let me quickly explain the parameter “fetch” that I added.

Fetch
FetchType.EAGER describes that data should be initialized on the spot.
FetchType.LAZY describes that data should be initialized as late as possible.

So the EAGER or LAZY value describes whether or not Orders are loaded in as soon as Customers are loaded into the memory (EAGER) — or if the load of Orders is postponed until fetched by an explicit call (LAZY).

Both types have their pros and cons.

The default FetchType for a one-to-many relation is LAZY, however, if you don’t have a lot of entities to fetch (in this case many orders) then maybe it’s a better strategy to use FetchType.EAGER.

This is just a point to consider when you implement the join.

How to fetch data with our new join

That’s it — we can now use the getter method we created in CustomerEntity, anywhere in the code, for instance:

Which should give us a list of the orders belonging to the particular customer — this is what it looks like if we test with Postman:

As you can see, JPA takes care of everything behind the scenes, including creating the SQL query.

Conclusion

This example was kept simple to leave you with an idea of how to perform a join with JPA.

As I implied, there is so much more to it.

If, for instance, you want the join to be bidirectional, you might want to take a look at this explanation:

https://www.baeldung.com/jpa-joincolumn-vs-mappedby

If you want to know more about FetchType.LAZY and FetchType.EAGER, I recommend this explanation:

https://www.baeldung.com/hibernate-lazy-eager-loading

Hopefully, you now have just enough of an idea to start looking into it!

Check my code example out here:

https://github.com/anna-knudsen/jpa-spring-simple-example-join

About me

As a software developer at Bankdata, I help make a difference for thousands of bank customers in Denmark.

In my team, we primarily develop API rest services for everything concerning credit cards — on Bankdata’s new Open Shift platform. It is still quite new to me, but I try to learn all the new technologies, that comes with this particular platform and I find the possibilities very exciting. Before working with API rest, I was a Java developer and throughout the years, I have worked a lot with web technologies. I believe that sharing technological knowledge — hence this blog — can make a difference for the future developers and users of digital banking. Besides expanding my knowledge of new technologies, I cherish singing and playing in a band in my spare time as a nice opposite to what I do for a living.