write join queries using jpa criteria queries
Tags: jpa, java
Published: 2024-02-04 18:08:26

---
title: write join queries using jpa criteria queries
published: true
date: 2023-08-13 00:00:00 UTC
tags: jpa,java
canonical_url: http://www.initgrep.com/posts/java/jpa/write-joins-using-jpa-criteria-queries
---

In this blog post, we’ll explore how to effectively use JPA’s criteria API to combine information from different database tables, enhancing your ability to retrieve and work with interconnected data.

### The Essence of Joins

At its core, a join merges rows from two or more tables based on a related column between them. This operation provides a link between data that would otherwise exist in isolated silos. By seamlessly integrating information from different tables, joins facilitate comprehensive data analysis and enhance the granularity of queries.

### Left Joins: Bridging the Data Divide

**Scenario: Customer-Order Association**

Consider a situation where you’re managing a database of customers and their corresponding orders. In some instances, customers may not have placed any orders yet. To retrieve a list of all customers and their orders, regardless of whether orders exist, a left join comes to the rescue.

```
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Customer> query = cb.createQuery(Customer.class);
Root<Customer> customerRoot = query.from(Customer.class);
Join<Customer, Order> orderJoin = customerRoot.join("orders", JoinType.LEFT);
query.select(customerRoot).distinct(true);

```

In this code snippet:

1. **`orderJoin: Join<Customer, Order>`** establishes a left join between the **`Customer`** and **`Order`** entities, ensuring customers are fetched alongside their orders.
2. The argument **`'orders'`** passed to **`join()`** identifies the property on the **`Customer`** entity representing the relationship with the **`Order`** entity.
3. **`JoinType.LEFT`** specifies a left join operation.
4. **`query.select(customerRoot).distinct(true)`** signifies the selection of **`Customer`** entities and the utilization of the **`distinct`** modifier to eliminate duplicate customers from the result set.

The generated SQL query resembles this:

```
SELECT DISTINCT c.*
FROM customer c
LEFT JOIN order o ON c.id = o.customer_id;

```

### Right Joins: Shifting the Focus

**Scenario: Product-Order Association**

Imagine a scenario where you’re interested in determining products that have yet to receive any orders. This situation calls for a right join, focusing on data from the associated table to lead the way.

```
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root<Product> productRoot = query.from(Product.class);
Join<Product, Order> orderJoin = productRoot.join("orders", JoinType.RIGHT);
query.select(productRoot).distinct(true);

```

Here, the spotlight shifts to products, and the right join captures products that are yet to find placement within an order. The corresponding SQL query takes form:

```
SELECT DISTINCT p.*
FROM product p
RIGHT JOIN order o ON p.id = o.product_id;

```

### Full Joins: Embracing Data Wholeness

**Scenario: Author-Book Relationship**

Imagine a situation where you’re examining the relationship between authors and their published books. A full join brings together the benefits of left and right joins, combining information from both tables to provide a comprehensive overview.

```
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Author> query = cb.createQuery(Author.class);
Root<Author> authorRoot = query.from(Author.class);
Join<Author, Book> bookJoin = authorRoot.join("books", JoinType.FULL);
query.select(authorRoot).distinct(true);

```

With a full join, the endeavor of gathering insights about authors and their books encompasses both published and unpublished works. The SQL counterpart takes shape:

```
SELECT DISTINCT a.*
FROM author a
FULL JOIN book b ON a.id = b.author_id;

```

- [Write programmatic criteria queries](https://www.initgrep.com/posts/java/jpa/select-values-in-criteria-queries). It explains the JPA Criteria Api.
- [how to select values in criteria Queries](https://www.initgrep.com/posts/java/jpa/select-values-in-criteria-queries). This post explains various methods provided in Criteria API to select single or multiple values. It also explains tuple criteria queries.