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

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:

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:

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:

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:

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:

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:

• Understand Spring Data JPA with Simple Example
• Spring Boot - Spring Data JPA - MySQL Example
• Spring MVC with JdbcTemplate Example
• How to configure Spring MVC JdbcTemplate with JNDI Data Source in Tomcat
• Spring and Hibernate Integration Tutorial (XML Configuration)
• Spring MVC + Spring Data JPA + Hibernate - CRUD Example

Other Spring Boot Tutorials:

• Spring Boot Hello World Example
• Spring Boot automatic restart using Spring Boot DevTools
• Spring Boot Form Handling Tutorial with Spring Form Tags and JSP
• How to create a Spring Boot Web Application (Spring MVC with JSP/ThymeLeaf)
• Spring Boot CRUD Example with Spring MVC – Spring Data JPA – ThymeLeaf - Hibernate - MySQL
• Spring Boot Hello World RESTful Web Services Tutorial
• How to use JDBC with Spring Boot
• Spring Boot CRUD Web Application with JDBC - Thymeleaf - Oracle
• Spring Boot RESTful CRUD API Examples with MySQL database
• How to package Spring Boot application to JAR and WAR

About the Author:

Nam Ha Minh 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.