Introduction

This tutorial will show you Spring Boot Data JPA Left, Right, Inner and Cross Join Examples. We will tell you here how to use this example in Spring Boot application, where we will use Spring Data JPA Repository to query our database tables.

We will use here custom query using @Query annotation to fetch the data. We will also fetch the columns which are required to fetch for displaying purpose. We will create a DTO or VO class that will map the columns to the Java attributes.

You may also fetch the column data into Object[] but in this case you need to extract the column value using array index from Object[].

We will also see here how to write SQLs for fetching data from database tables using different join queries with the help of Spring Data JPA Repository.

Prerequisites

Eclipse, JDK 10, Gradle 4.4.1

MySQL database

Spring Boot dependency

Knowledge of Spring Data JPA, Java, SQL

Let’s move on to the example of Spring Boot Data JPA Left, Right, Inner and Cross Join Examples…

Definitions of different Joins

A JOIN clause is used to combine rows from two or more tables, based on a related columns between them.

(INNER) JOIN

Returns records that have matching values in both tables. Pictorial representation is given below:

inner join

LEFT (OUTER) JOIN

Returns all records from the left table, and the matched records from the right table. Pictorial representation is given below:

left join

RIGHT (OUTER) JOIN

Returns all records from the right table, and the matched records from the left table. Pictorial representation is given below:

right join

CROSS JOIN

Returns a record set in which the number of rows in the left table multiplied by the number of rows in the right table. If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN. Pictorial representation is given below:

cross join

Examples of different Joins

Now let’s say you have two tables – department and employee, in you MySQL database with the following data.

Table – department

spring data jpa join

Table – employee

spring data jpa join

Now we will perform each join on the above two tables.

INNER JOIN

SQL Statement:

SELECT d.name, e.name, e.email, e.address FROM department d INNER JOIN employee e ON d.id = e.dept_id;

Result:

inner join sql

LEFT JOIN or LEFT OUTER JOIN

SQL Statement:

SELECT d.name, e.name, e.email, e.address FROM department d LEFT JOIN employee e ON d.id = e.dept_id;

Result:

left join sql

RIGHT JOIN or RIGHT OUTER JOIN

SQL Statement:

SELECT d.name, e.name, e.email, e.address FROM department d RIGHT JOIN employee e ON d.id = e.dept_id;

Result:

inner join sql

CROSS JOIN

SQL Statement:

SELECT d.name, e.name, e.email, e.address FROM department d CROSS JOIN employee e;

Result:

cross join sql

If you use where clause in cross join, such as:

SELECT d.name, e.name, e.email, e.address FROM department d CROSS JOIN employee e ON d.id = e.dept_id;

The above SQL statement will give you the same result as we have seen in INNER JOIN.

Spring Data JPA Dependency

In order to working with Spring Data JPA Repository to get full benefits out of the box you just need only one dependency to be added either to the pom.xml or build.gradle.

pom.xml

<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-jpa</artifactId>
    <version>2.1.6.RELEASE</version>
</dependency>

build.gradle

compile group: 'org.springframework.data', name: 'spring-data-jpa', version: '2.1.6.RELEASE'

Apart from the above dependency you need to add database specific dependency for your database.

So we have seen how to write join queries at database level. Now we will see how we can write join queries in Spring Data JPA Repository.

Entity classes

A JPA entity class is a POJO (Plain Old Java Object) class, marked with annotation @Entity and having the ability to represent object in the database.

Entity classes here implement Serializable interface in order to store the data into database directly.

Let’s say we have following entity classes – Employee and Department – for our database tables employee and department, respectively.

The below entity class – Employee – maps Java object to corresponding table employee.

@Entity
@Table(name = "employee")
public class Employee implements Serializable {
	private static final long serialVersionUID = 1L;
	@Id
	@Column(name = "id")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private long id;
	@Column(name = "name")
	private String name;
	@Column(name = "email")
	private String email;
	@Column(name = "address")
	private String address;
	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "dept_id", insertable = false, updatable = false)
	@Fetch(FetchMode.JOIN)
	private Department department;
        //getters and setters
}

The below entity class – Department – maps Java object to corresponding table department.

@Entity
@Table(name = "department")
public class Department implements Serializable {
	private static final long serialVersionUID = 1L;
	@Id
	@Column(name = "id")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private long id;
	@Column(name = "name")
	private String name;
	@Column(name = "description")
	private String description;
	@OneToMany(targetEntity = Employee.class, mappedBy = "id", orphanRemoval = false, fetch = FetchType.LAZY)
	private Set<Employee> employees;
        //getters and setters
}

Spring Data JPA Repository

We know that Spring Data JPA provides repository support for the Java Persistence API (JPA) and it eases development of applications that need to access JPA data sources.

Spring Data JPA is an abstraction over JPA, which is an abstraction over JDBC. Using Spring Data JPA Repository API has many advantages:

  • Spring Data JPA provides find methods out of the box. So based on naming conventions findBy will be provided by Spring Data JPA dynamically and will result to an entity result where all the entities will have for their field the corresponding parameter value.
  • Other useful features like pagination, sorting, Criteria API that is required for your search screens.

Repository Interfaces

We have following Spring Data JPA Repositories where we write our JOIN queries using @Query annotation. I have written queries in both repository interfaces. If you want, you may also write in any one of the repositories. We are returning data as a custom DTO object because we cannot return entity object due to we are fetching selected columns from database tables.

We have defined INNER, LEFT (OUTER), RIGHT (OUTER) and CROSS JOIN in the below repositories.

We have defined two repositories – DepartmentRepository and EmployeeRepository. As we are performing join operations to fetch data from two tables, so it is also possible to use any one of the below repositories to fetch the data from the database.

Department Repository

The following Spring Data JPA Repository defines LEFT and RIGHT joins.

public interface DepartmentRepository extends JpaRepository<Department, Integer> {
	@Query("SELECT new com.roytuts.dto.DeptEmpDto(d.name, e.name, e.email, e.address) "
			+ "FROM Department d LEFT JOIN d.employees e")
	List<DeptEmpDto> fetchEmpDeptDataLeftJoin();
	@Query("SELECT new com.roytuts.dto.DeptEmpDto(d.name, e.name, e.email, e.address) "
			+ "FROM Department d RIGHT JOIN d.employees e")
	List<DeptEmpDto> fetchEmpDeptDataRightJoin();
}

Employee Repository

The following Spring Data JPA Repository defines INNER and CROSS joins.

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
	@Query("SELECT new com.roytuts.dto.DeptEmpDto(d.name, e.name, e.email, e.address) "
			+ "FROM Department d INNER JOIN d.employees e")
	List<DeptEmpDto> fetchEmpDeptDataInnerJoin();
	@Query("SELECT new com.roytuts.dto.DeptEmpDto(d.name, e.name, e.email, e.address) "
			+ "FROM Department d, Employee e")
	List<DeptEmpDto> fetchEmpDeptDataCrossJoin();
}

Data Transfer Object

A data transfer object (DTO) is an object that carries data between processes. We are using DTO object to represent data or send data to the remote call. It is not a good idea to return the entity object to the client side or remote call.

A DTO does not have any behavior except for storage, retrieval, serialization and deserialization of its own data.

In other words, DTOs are simple objects that should not contain any business logic but may contain serialization and deserialization mechanisms for transferring data over the wire.

The below is the DTO class, EmpDto, which was used in the above repositories.

public class DeptEmpDto {
	private String empDept;
	private String empName;
	private String empEmail;
	private String empAddress;
	public DeptEmpDto(String empDept, String empName, String empEmail, String empAddress) {
		this.empDept = empDept;
		this.empName = empName;
		this.empEmail = empEmail;
		this.empAddress = empAddress;
	}
        //getters and setters
}

Now when you call your queries from your service class, you would receive the same results as I have shown using MySQL SQL queries.

Service Class

A Service class is used by a client to interact with some functionality in your application. Usually it is public, and has some business meaning.

Example is given below how to call query methods from service class

@Service
public class ServiceClass {
	@Autowired
	private DepartmentRepository departmentRepository;
	@Autowired
	private EmployeeRepository employeeRepository;
	public List fetchEmpDeptDataLeftJoin() {
		return departmentRepository.fetchEmpDeptDataLeftJoin();
	}
	public List fetchEmpDeptDataInnerJoin() {
		return employeeRepository.fetchEmpDeptDataInnerJoin();
	}
	//...
}

Source Code

Thanks for reading.

Tags:

8 thoughts on “Spring Boot Data JPA Left, Right, Inner and Cross Join Examples

    1. @Service
      public class ServiceClass {
      @Autowired
      private DepartmentRepository departmentRepository;
      @Autowired
      private EmployeeRepository employeeRepository;
      public List fetchEmpDeptDataLeftJoin() {
      return departmentRepository.fetchEmpDeptDataLeftJoin();
      }
      public List
      fetchEmpDeptDataInnerJoin() {
      return employeeRepository.fetchEmpDeptDataInnerJoin();
      }
      //...
      }

  1. Very nice blog. Thanks.
    Can you tell how to add conditional check in left join query?I want to add a condion on top for particular emp.
    query:
    SELECT d.name, e.name, e.email, e.address FROM department d LEFT JOIN employee e ON d.id = e.dept_id and e.Id=’1′.
    How to do it in Repository.
    Appriciate your help.
    @Query(“SELECT new com.roytuts.dto.DeptEmpDto(d.name, e.name, e.email, e.address) ”
    + “FROM Department d INNER JOIN d.employees e and e.id=?1”)——> this is not working.
    List fetchEmpDeptDataInnerJoin();
    Giving org.hibernate.hql.internal.ast.QuerySyntaxException exception.

    1. first of all you want left join and doing inner join. you should check my left join. now in your case, you can do similar thing with condition as below
      @Query(“SELECT new com.roytuts.dto.DeptEmpDto(d.name, e.name, e.email, e.address) ”
      + “FROM Department d LEFT JOIN d.employees e ON e.id = 1”)
      List fetchEmpDeptDataLeftJoin();

Leave a Reply

Your email address will not be published. Required fields are marked *