Spring Data JPA Join Query for Like Search on One to Many and Many to Many Examples

Details
Written by  
Last Updated on 18 August 2021   |   Print  Email

In this Spring article, I’d like to share with you some examples about writing join queries in Spring Data JPA for like search on one-to-many and many-to-many entity relationships. The join queries which I’m going to share are based on JPQL (Java Persistence Query Language) – not regular relational SQL queries.

 

1. Join Query for Like Search on Many-to-Many Relationship between User and Role

Let’s get started with a very common entity relationship between User and Role, as depicted in the following class diagram:

User Role Class Diagram

In this entity relationship, a user can have one or more roles, and a role can be assigned to one or many users. The Java code of these entity classes would be like this:

User.java:

@Entity
@Table(name = "users")
public class User {
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	protected Integer id;
	
	private String email;
	
	@ManyToMany(cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
	@JoinTable(
			name = "users_roles",
			joinColumns = @JoinColumn(name = "user_id"),
			inverseJoinColumns = @JoinColumn(name = "role_id")
			)
	private Set<Role> roles = new HashSet<>();
	
	// constructors, getters and setters are not shown
}

Role.java:

@Entity
@Table(name = "roles")
public class Role {
	
	private String name;
	
	private String description;

	// constructors, getters and setters are not shown
}

In relational database, the corresponding tables would be generated like this:

Tables users and roles

So, the question is How to search for users by a specific role name?

Here’s an example join query for that like search:

SELECT u FROM User u JOIN u.roles r WHERE r.name LIKE '%role name%'

And an example for using this JPQL in a repository:

public interface UserRepository extends JpaRepository<User, Integer> {
	
	@Query("SELECT u FROM User u JOIN u.roles r WHERE r.name LIKE %?1%")
	public Page<User> findAll(String keyword, Pageable pageable);	
}

If the query results in duplicated User entities, you can use the DISTINCT keyword like this:

SELECT DISTINCT u FROM User u JOIN u.roles r WHERE r.name LIKE '%role name%'

  

2. Join Query for Like Search on Many-to-Many Relationship between Brand and Category

Suppose that we have another one-to-many entity relationship between Brand and Category. A brand can belong to one or many categories, and a category can have one or many brands, as illustrated in the following class diagram:

Brand Category Class Diagram

The Java code of these entity classes and database tables are similar to the previous example with user and role.

So, the question is How to list brands by a specific category?

Here’s an answer using JPQL:

SELECT DISTINCT b FROM Brand b JOIN b.categories c WHERE c.name LIKE '%category name%'

Use this join query in the BrandRepository interface as follows:

public interface BrandRepository extends JpaRepository<Brand, Integer> {

	@Query("SELECT DISTINCT b FROM Brand b JOIN b.categories c WHERE c.name LIKE %?1%")
	public Page<Brand> findAll(String keyword, Pageable pageable);
	
}

Note that the DISTINCT keyword is used to remove duplicate rows which is result of join query.


3. Join Query for Like Search on One-to-Many Relationship between Multiple Entities (Multiple Tables)

Let’s come to a more complex entity relationship with 3 entities: Order, OrderDetail and Product:

Order Order Detail Product Class Diagram

Here, the entity relationship between Order and OrderDetail is one to many, and so is the association between Product and OrderDetail.

The corresponding tables in database would be something like this:

Order Repository Layer Database Diagram

Code of these entity classes are as follows.

Order.java:

@Entity
@Table(name = "orders")
public class Order {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	protected Integer id;
	
	
	@OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true)
	private Set<OrderDetail> orderDetails = new HashSet<>();

	// constructors, getters and setters are not shown	
}

 

OrderDetail.java:

@Entity
@Table(name = "order_details")
public class OrderDetail {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;
	
	@ManyToOne
	@JoinColumn(name = "order_id")
	private Order order;
	
	@ManyToOne
	@JoinColumn(name = "product_id")
	private Product product;

	// constructors, getters and setters are not shown	
}

 

Product.java:

@Entity
@Table(name = "products")
public class Product {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	// constructors, getters and setters are not shown	
}

 

Now, the question is How to search for orders by product name? or How to get orders that contain a specific product?

Below is the answer using JPQL:

SELECT DISTINCT o FROM Order o JOIN o.orderDetails d JOIN d.product p WHERE p.name LIKE ‘%product name%’

You see, this query joins 3 entities together and performs like search by product name. And use it in the OrderRepository as follows:

@Repository
public interface OrderRepository extends JpaRepository<Order, Integer> {

	@Query("SELECT DISTINCT o FROM Order o JOIN o.orderDetails od JOIN od.product p"
			+ " WHERE p.name LIKE %?1%")
	public Page<Order> findAll(String keyword, Pageable pageable);	

}

So that’s a few examples of Spring Data JPA join query for like search on one-to-many and many-to-many entity relationships. To see real-world scenarios, I recommend you to watch this video:

 

Related Spring and Database Tutorials:

 

Other Spring Boot Tutorials:

 


About the Author:

is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.