This tutorial will show you Spring Boot Data JPA Left, Right, Inner and Cross Join Examples. We will create here 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 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.

Prerequisites

Eclipse, JDK 10, Gradle 4.4.1

MySQL database

Spring Boot dependencies

Knowledge of Spring, Java, SQL

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

Let’s see what are all these joins tell about:

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: Return all records from the left table, and the matched records from the right table. Pictorial representation is given below:

left join

RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table. Pictorial representation is given below:

right join

CROSS JOIN: Return 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

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

Department

spring data jpa join

Employee

spring data jpa join

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

INNER JOIN

SQL: 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: 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: 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: 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 SQL will give you the same result as we have seen in INNER JOIN.

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

Let’s say we have following entity classes for our database tables employee and department.

@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
}

 

@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
}

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.

public interface DepartmentRepository extends JpaRepository<Department, Integer> {

	@Query("SELECT new com.jeejava.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.jeejava.dto.DeptEmpDto(d.name, e.name, e.email, e.address) "
			+ "FROM Department d RIGHT JOIN d.employees e")
	List<DeptEmpDto> fetchEmpDeptDataRightJoin();

}

 

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

	@Query("SELECT new com.jeejava.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.jeejava.dto.DeptEmpDto(d.name, e.name, e.email, e.address) "
			+ "FROM Department d, Employee e")
	List<DeptEmpDto> fetchEmpDeptDataCrossJoin();

}

DTO class

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.

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

2 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();
      }

      //...

      }

Leave a Reply

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