Spring Boot Data JPA Left, Right, Inner and Cross Join Examples

Introduction

This tutorial will show you Spring Boot Data JPA Left Right Inner and Cross Join Examples. I will tell you here how to use this example in Spring Boot application, where you will use Spring Data JPA Repository to query your database tables. I will build the project using both maven and gradle build tools.

I will use here custom query using @Query annotation to fetch the data. I will also fetch the columns which are required to fetch for displaying purpose. I 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[].

Related Posts:

Spring Boot Data JPA Left, Right, Inner and Cross Join Examples on Three Tables

I will also see here how to write SQL (Structured Query Language) for fetching data from database tables using different join queries with the help of Spring Data JPA Repository.

Prerequisites

Java at least 1.8, Gradle 6.1.1 – 6.7.1, Maven 3.6.3, Spring Boot 2.2.6 – 2.4.2, MySQL 8.0.17/8.0.22

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

Create Tables

Create two tables – employee and department under roytuts database in MySQL server.

Table – department

CREATE TABLE IF NOT EXISTS `department` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `department` (`id`, `name`, `description`) VALUES
	(1, 'IT', 'Information Technology'),
	(2, 'TelComm', 'Telecommunication'),
	(3, 'Ins', 'Insurance'),
	(4, 'HR', 'Human Resources');

Table – employee

CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dept_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `employee` (`id`, `name`, `email`, `address`, `dept_id`) VALUES
	(1, 'Soumitra', 'soumitra@gmail.com', NULL, 1),
	(2, 'Suman', 'suman@gmail.com', NULL, 2),
	(3, 'Avisek', 'avisek@gmail.com', NULL, 3);

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:

LEFT (OUTER) JOIN

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

RIGHT (OUTER) JOIN

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

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:

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 left right inner cross joins

Table – employee

Now I will perform each type of join query on the above two tables.

INNER JOIN

The following SQL Statement performs the inner join.

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

The above SQL query gives the following result:

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:

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:

CROSS JOIN

SQL Statement:

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

Result:

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 you have seen in INNER JOIN.

Project Setup

Create a Spring Boot project in your favorite IDE or tool and the name of the project is spring-data-jpa-left-right-inner-cross-join.

You can create gradle or maven based project in Eclipse and accordingly you need to use build.gradle script or pom.xml file from the below:

build.script

You can use below build.gradle script for your gradle based project:

buildscript {
	ext {
		springBootVersion = '2.2.6.RELEASE' to '2.4.2'
	}
	
    repositories {
    	mavenLocal()
    	mavenCentral()
    }
    
    dependencies {
    	classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

apply plugin: 'java'
apply plugin: 'org.springframework.boot'

sourceCompatibility = 12
targetCompatibility = 12

repositories {
	mavenLocal()
    mavenCentral()
}

dependencies {
	implementation("org.springframework.boot:spring-boot-starter-web:${springBootVersion}")
	implementation("org.springframework.boot:spring-boot-starter-data-jpa:${springBootVersion}") {
		exclude group: 'com.zaxxer', module: 'HikariCP'
	}
	implementation('mysql:mysql-connector-java:8.0.17/22')
	implementation('org.apache.tomcat:tomcat-jdbc:9.0.27')
	runtimeOnly('javax.xml.bind:jaxb-api:2.4.0-b180830.0359')
}

pom.xml

You can use below pom.xml file for your maven based project:

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>spring-data-jpa-left-right-inner-cross-join</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.6.RELEASE to 2.4.2</version>
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>12</maven.compiler.source>
		<maven.compiler.target>12</maven.compiler.target>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>

		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<scope>runtime</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

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.

Related Posts:

Spring Boot Data JPA Left, Right, Inner and Cross Join Examples on Three Tables

Database Configuration

I am using MySQL database, so I will see how to configure our database.

Create src/main/resources/application.properties file to put database settings.

If you get the following issue (in Spring Boot 2.4.x) then use spring.datasource.jdbcUrl instead of spring.datasource.url:

java.lang.IllegalArgumentException: jdbcUrl is required with driverClassName

The whole content is given below:

#datasource
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost/roytuts
#spring.datasource.jdbcUrl=jdbc:mysql://localhost/roytuts
spring.datasource.username=root
spring.datasource.password=root

#disable schema generation from Hibernate
spring.jpa.hibernate.ddl-auto=none

Create below config class to create Datasource and EntityManagerFactory beans:

package com.roytuts.spring.data.jpa.left.right.inner.cross.join.config;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;

@Configuration
public class DbConfig {

	@Bean
	@ConfigurationProperties(prefix = "spring.datasource")
	public DataSource dataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean
	public EntityManagerFactory entityManagerFactory(DataSource dataSource) {
		HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
		vendorAdapter.setDatabase(Database.MYSQL);
		LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();

		factory.setJpaVendorAdapter(vendorAdapter);
		factory.setPackagesToScan("com.roytuts.spring.data.jpa.left.right.inner.cross.join.entity");
		factory.setDataSource(dataSource);
		factory.afterPropertiesSet();
		return factory.getObject();
	}

}

You need to enable JPA repository by setting the full package of the Entity classes.

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.

package com.roytuts.spring.data.jpa.left.right.inner.cross.join.entity;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;

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

package com.roytuts.spring.data.jpa.left.right.inner.cross.join.entity;

import java.io.Serializable;
import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@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

You 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

You have following Spring Data JPA Repositories where you need to write your 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. I am returning data as a custom DTO object because I cannot return entity object due to we are fetching selected columns from database tables.

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

I have defined two repositories – DepartmentRepository and EmployeeRepository. As I am 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.

package com.roytuts.spring.data.jpa.left.right.inner.cross.join.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.roytuts.spring.data.jpa.left.right.inner.cross.join.dto.DeptEmpDto;
import com.roytuts.spring.data.jpa.left.right.inner.cross.join.entity.Department;

public interface DepartmentRepository extends JpaRepository<Department, Integer> {

	@Query("SELECT new com.roytuts.spring.data.jpa.left.right.inner.cross.join.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.spring.data.jpa.left.right.inner.cross.join.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.

package com.roytuts.spring.data.jpa.left.right.inner.cross.join.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.roytuts.spring.data.jpa.left.right.inner.cross.join.dto.DeptEmpDto;
import com.roytuts.spring.data.jpa.left.right.inner.cross.join.entity.Employee;

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

	@Query("SELECT new com.roytuts.spring.data.jpa.left.right.inner.cross.join.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.spring.data.jpa.left.right.inner.cross.join.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. I am 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, DeptEmpDto, which was used in the above repositories.

package com.roytuts.spring.data.jpa.left.right.inner.cross.join.dto;

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

	@Override
	public String toString() {
		return "DeptEmpDto [empDept=" + empDept + ", empName=" + empName + ", empEmail=" + empEmail + ", empAddress="
				+ empAddress + "]";
	}

}

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

package com.roytuts.spring.data.jpa.left.right.inner.cross.join.service;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.roytuts.spring.data.jpa.left.right.inner.cross.join.dto.DeptEmpDto;
import com.roytuts.spring.data.jpa.left.right.inner.cross.join.repository.DepartmentRepository;
import com.roytuts.spring.data.jpa.left.right.inner.cross.join.repository.EmployeeRepository;

@Service
public class JoinQueryService {

	@Resource
	private DepartmentRepository departmentRepository;

	@Resource
	private EmployeeRepository employeeRepository;

	public List<DeptEmpDto> getDeptEmployeesLeftJoin() {
		List<DeptEmpDto> list = departmentRepository.fetchEmpDeptDataLeftJoin();
		list.forEach(l -> System.out.println(l));
		return list;
	}

	public List<DeptEmpDto> getDeptEmployeesRightJoin() {
		List<DeptEmpDto> list = departmentRepository.fetchEmpDeptDataRightJoin();
		list.forEach(l -> System.out.println(l));
		return list;
	}

	public List<DeptEmpDto> getDeptEmployeesInnerJoin() {
		List<DeptEmpDto> list = employeeRepository.fetchEmpDeptDataInnerJoin();
		list.forEach(l -> System.out.println(l));
		return list;
	}

	public List<DeptEmpDto> getDeptEmployeesCrossJoin() {
		List<DeptEmpDto> list = employeeRepository.fetchEmpDeptDataCrossJoin();
		list.forEach(l -> System.out.println(l));
		return list;
	}

}

Spring REST Controller

I will create REST controller class to show how to invoke the service class method to get the results on different join queries.

package com.roytuts.spring.data.jpa.left.right.inner.cross.join.rest.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import com.roytuts.spring.data.jpa.left.right.inner.cross.join.dto.DeptEmpDto;
import com.roytuts.spring.data.jpa.left.right.inner.cross.join.service.JoinQueryService;

@RestController
public class JoinQueryController {

	@Autowired
	private JoinQueryService joinQueryService;

	@GetMapping("/dept/employees/left")
	public ResponseEntity<List<DeptEmpDto>> getDeptEmployeesLeftJoin() {
		return new ResponseEntity<List<DeptEmpDto>>(joinQueryService.getDeptEmployeesLeftJoin(), HttpStatus.OK);
	}

	@GetMapping("/dept/employees/right")
	public ResponseEntity<List<DeptEmpDto>> getDeptEmployeesRightJoin() {
		return new ResponseEntity<List<DeptEmpDto>>(joinQueryService.getDeptEmployeesRightJoin(), HttpStatus.OK);
	}

	@GetMapping("/dept/employees/inner")
	public ResponseEntity<List<DeptEmpDto>> getDeptEmployeesInnerJoin() {
		return new ResponseEntity<List<DeptEmpDto>>(joinQueryService.getDeptEmployeesInnerJoin(), HttpStatus.OK);
	}

	@GetMapping("/dept/employees/cross")
	public ResponseEntity<List<DeptEmpDto>> getDeptEmployeesCrossJoin() {
		return new ResponseEntity<List<DeptEmpDto>>(joinQueryService.getDeptEmployeesCrossJoin(), HttpStatus.OK);
	}

}

Spring Boot Main Class

A class with main method and @SpringBootApplication annotation is enough to deploy the application into embedded Tomcat server.

package com.roytuts.spring.data.jpa.left.right.inner.cross.join;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@SpringBootApplication
@EnableJpaRepositories("com.roytuts.spring.data.jpa.left.right.inner.cross.join.repository")
public class Application {

	public static void main(String[] args) {
		SpringApplication.run(Application.class, args);
	}

}

Testing the Application

Left Join

Hit the URL http://localhost:8080/dept/employees/left in browser or any REST client tool (Postman).to test left join. You will see the following output:

[
    {
        "empDept": "IT",
        "empName": "Soumitra",
        "empEmail": "soumitra@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "TelComm",
        "empName": "Suman",
        "empEmail": "suman@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "Ins",
        "empName": "Avisek",
        "empEmail": "avisek@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "HR",
        "empName": null,
        "empEmail": null,
        "empAddress": null
    }
]

Right Join

To test right outer join, use the URL http://localhost:8080/dept/employees/right:

[
    {
        "empDept": "IT",
        "empName": "Soumitra",
        "empEmail": "soumitra@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "TelComm",
        "empName": "Suman",
        "empEmail": "suman@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "Ins",
        "empName": "Avisek",
        "empEmail": "avisek@gmail.com",
        "empAddress": null
    }
]

Inner Join

For inner join, use the URL http://localhost:8080/dept/employees/inner:

[
    {
        "empDept": "IT",
        "empName": "Soumitra",
        "empEmail": "soumitra@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "TelComm",
        "empName": "Suman",
        "empEmail": "suman@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "Ins",
        "empName": "Avisek",
        "empEmail": "avisek@gmail.com",
        "empAddress": null
    }
]

Cross Join

For cross join use the URL http://localhost:8080/dept/employees/cross:

[
    {
        "empDept": "IT",
        "empName": "Soumitra",
        "empEmail": "soumitra@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "IT",
        "empName": "Suman",
        "empEmail": "suman@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "IT",
        "empName": "Avisek",
        "empEmail": "avisek@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "TelComm",
        "empName": "Soumitra",
        "empEmail": "soumitra@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "TelComm",
        "empName": "Suman",
        "empEmail": "suman@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "TelComm",
        "empName": "Avisek",
        "empEmail": "avisek@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "Ins",
        "empName": "Soumitra",
        "empEmail": "soumitra@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "Ins",
        "empName": "Suman",
        "empEmail": "suman@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "Ins",
        "empName": "Avisek",
        "empEmail": "avisek@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "HR",
        "empName": "Soumitra",
        "empEmail": "soumitra@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "HR",
        "empName": "Suman",
        "empEmail": "suman@gmail.com",
        "empAddress": null
    },
    {
        "empDept": "HR",
        "empName": "Avisek",
        "empEmail": "avisek@gmail.com",
        "empAddress": null
    }
]

The below youtube video shows how to test the application and what are the expected output from different join queries.

Source Code

Download

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

Binh Thanh Nguyen says:

14th Sep, 2020 at 9:18 am

Thanks, nice tips

SR says:

28th Jul, 2020 at 11:14 pm

This is a very good tutorial with step by step detailed instructions. Thanks.

rim says:

3rd May, 2020 at 9:43 pm

it wooooorks thank uuu a lot

Divya says:

29th Apr, 2020 at 5:29 pm

Hi i need a help. here in service u calling dao and returning List. what if have to do take each field data from the list and show it in JSP.

1. Soumitra Roy Sarkar says:
  
  29th Apr, 2020 at 5:52 pm
  
  You don’t need REST controller instead, you need Controller where you will pass your List of POJO data to iterate on the JSP page.
  
Mohamed says:

23rd Apr, 2020 at 4:18 am

Hey i have a problem with my code and i would like if you can check it for me because i have searched and haven’t found a solution

Ashutosh Kumar says:

20th Apr, 2020 at 12:54 am

Dear sir,
Can you help, its not working. Please
@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 = :param”)
List fetchEmpDeptDataLeftJoin(int param);

Test says:

13th Aug, 2019 at 7:45 pm

how to get non matching records

Jenny says:

25th Jul, 2019 at 2:38 am

Really Thanks Buddy. Working nice.

Jose Arcila says:

14th May, 2019 at 5:49 pm

How could you do a join of three or more tables? It seems this would only work with two tables only

Riddhi says:

11th Apr, 2019 at 3:14 pm

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. Soumitra Roy Sarkar says:
  
  12th Apr, 2019 at 2:35 am
  
  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();
  
RM says:

22nd Nov, 2018 at 9:29 am

Thankyou ! It was very helpful.

RK says:

22nd Oct, 2018 at 4:59 am

Can you show us how to write service class for this employee department join queries?

1. Soumitra Roy Sarkar says:
  
  25th Oct, 2018 at 3:49 am
  
  @Service public class ServiceClass { @Autowired private DepartmentRepository departmentRepository; @Autowired private EmployeeRepository employeeRepository; public List fetchEmpDeptDataLeftJoin() { return departmentRepository.fetchEmpDeptDataLeftJoin(); } public List fetchEmpDeptDataInnerJoin() { return employeeRepository.fetchEmpDeptDataInnerJoin(); } //... }

Roy Tutorials

Spring Boot Data JPA Left, Right, Inner and Cross Join Examples

Introduction

Prerequisites

Create Tables

Table – department

Table – employee

Definitions of Different Joins

(INNER) JOIN

LEFT (OUTER) JOIN

RIGHT (OUTER) JOIN

CROSS JOIN

Examples of Different Joins

Table – department

Table – employee

INNER JOIN

LEFT JOIN or LEFT OUTER JOIN

RIGHT JOIN or RIGHT OUTER JOIN

CROSS JOIN

Project Setup

build.script

pom.xml

Database Configuration

Entity Classes

Spring Data JPA Repository

Repository Interfaces

Department Repository

Employee Repository

Data Transfer Object

Service Class

Spring REST Controller

Spring Boot Main Class

Testing the Application

Left Join

Right Join

Inner Join

Cross Join

Source Code

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

Leave a Reply Cancel reply