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