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

Introduction

In this guide we will walk through Spring Boot Data JPA left, right, inner and cross join examples on three tables. In pas we have seen similar example on two tables but I got some requests on how to perform similar joins on three tables. Therefore I am going to show you how to perform joins – left, right, inner, cross – on three tables.

We are going to build custom query using @Query annotation to fetch the data from database tables. We also fetch the columns which are required to fetch for displaying purpose.

We are going to 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 API.

Left join or right join also called left outer join or right outer join respectively.

Prerequisites

Eclipse 4.12, Java 8 or 12, Spring Boot 2.2.1, MySQL 8.0.17, Gradle 5.6

Definitions of Different Joins

before going further down we need to know about the different joins we are going to present here. I won’t explain here about the definition of different joins but you can find those definitions here.

Examples of Different Join Queries

We are going to show you how to build different join queries on three tables.

In this section we are going to see joins with examples on three tables.

Create Tables

Before we perform any join on tables we need to create the required tables. Naturally, we will create three tables and insert some sample data for testing purpose.

Below DDL statements will create three tables in the MySQL database:

CREATE TABLE `company` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `food` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `company_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`company_id`)
      REFERENCES `company`(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sale` (
  `id` int NOT NULL AUTO_INCREMENT,
  `quantity` int DEFAULT 0,
  `rate` float DEFAULT 0.0,
  `amount` float DEFAULT 0.0,
  `food_id` int NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`food_id`)
      REFERENCES `food`(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I think the above table structures need no explanations as these are straight forward and easily understandable what data to be stored.

Dumping Data

In order to run some tests on built queries we need some data to be present into these tables. So insert some sample data into these tables as given below:

insert into
`company`(`id`,`name`)
values
(1,'Tom & Jerry'),
(2,'Order All'),
(3,'Akash Food'),
(4,'Chinese Food'),
(5,'Roy Food');

insert into
`food`(`id`,`name`,`company_id`)
values
(1,'Momo',4),
(2,'Cheez',1),
(3,'Biscuit',2),
(4,'Jerry',1),
(5,'Veg Dish',3),
(6,'Non-veg Dish',2),
(7,'Sweet',5);

insert into
`food`(`id`,`name`)
values
(8,'Laddu');

insert  into
`sale`(`id`,`quantity`, `rate`, `amount`, `food_id`)
values
(1, 5, 5, 25, 1),
(2, 3, 30, 150, 2),
(3, 1, 16, 16, 3),
(4, 4, 45, 180, 4),
(5, 2, 40, 80, 5),
(6, 3, 90, 270, 6),
(7, 10, 10, 100, 7),
(8, 6, 7, 42, 8);

Left Outer Join Query

Now we will first build left join query on three tables and see the results.

SELECT s.id, f.name,c.name, s.amount 
FROM sale s
LEFT JOIN food f ON s.food_id=f.id 
LEFT JOIN company c ON f.company_id=c.id

Or

SELECT s.id, f.name,c.name, s.amount 
FROM sale s
LEFT JOIN food f ON s.food_id=f.id 
LEFT JOIN company c ON f.company_id=c.id
ORDER BY s.id;

Using first or second query in the above we will get the same results because now all records in sale table are in sorted order. Using the second query we are fetching records from three tables sorted by sale id.

The above query returns null value for the company name for the last record because in the food table for the last record we don’t have company name.

Spring Boot Data JPA Left Right Inner and Cross Join Examples on three tables
SELECT s.id, f.name,c.name, s.amount 
FROM sale s
LEFT JOIN food f ON s.food_id=f.id 
LEFT JOIN company c ON f.company_id=c.id
WHERE c.name IS NOT NULL
ORDER BY s.id;

In the above query we have added where condition to fetch records from food table if company name is not null. We are also sorting fetching the records on sale id.

Spring Boot Data JPA Left Right Inner and Cross Join Examples on three tables

So depending on your requirements you can build the left or left outer join query.

Right Outer Join

We will build right or right outer join to fetch data from three tables.

SELECT s.id, f.name,c.name, s.amount 
FROM sale s
RIGHT JOIN food f ON s.food_id=f.id 
RIGHT JOIN company c ON f.company_id=c.id
ORDER BY s.id;

The above query give you the following output:

Spring Boot Data JPA Left Right Inner and Cross Join Examples on three tables

For right or right outer join you see we don’t get row for null value because right outer join happens from right most table of the join and right most table, i.e., company table does not have any null value for a column.

Inner Join

We will see output from inner join. Inner join outputs only the matched rows from all tables.

SELECT s.id, f.name,c.name, s.amount 
FROM sale s
INNER JOIN food f ON s.food_id=f.id 
INNER JOIN company c ON f.company_id=c.id
ORDER BY s.id;

The above query will output as below:

Spring Boot Data JPA Left Right Inner and Cross Join Examples on three tables

The above output is same as we got for right outer join query.

Cross Join

We will build cross join query on three tables. So if you do not put conditions then it will give you lots of rows in the output.

SELECT s.id, f.name,c.name, s.amount 
FROM sale s
CROSS JOIN food f ON s.food_id=f.id 
CROSS JOIN company c ON f.company_id=c.id
ORDER BY s.id;

The above query will give you below output:

In this case the output is same as inner and right outer join queries.

So we have seen examples on different joins, now we will see how implement the same things using Spring Boot Data JPA API.

Creating Project

We will create a gradle based project in Eclipse. The name of the project is spring-data-jpa-left-right-inner-cross-join-three-tables.

If you had overlooked Prerequisites section above, you can go back and check the required libraries.

Updating Build Script

We need to include the required dependencies in build.gradle script for working on the join queries using Spring Boot JPA.

buildscript {
	ext {
		springBootVersion = '2.2.1.RELEASE'
	}
	
    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}")
	implementation('mysql:mysql-connector-java:8.0.17')
	runtimeOnly('javax.xml.bind:jaxb-api:2.4.0-b180830.0359')
}

Notice in the above build script I have added jaxb-api. This API is required by the Java application. Up to Java 8 you don’t need to add this API explicitly in your build script because this API is available by default up to Java 8. After Java 9 you have to add it in the build script or build file manually to avoid JAXB related exceptions.

Related Posts:

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 – Company, Food and Sale – for our database tables company, food and sale, respectively.

The following entity classes include bidirectional one-to-many or many-to-one relationships.

Below is the Company entity class:

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 = "company")
public class Company 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;

	@OneToMany(targetEntity = Food.class, mappedBy = "id", orphanRemoval = false, fetch = FetchType.LAZY)
	private Set<Food> foods;

	// getters and setters
}

Below is the Food entity class:

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.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;

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

@Entity
@Table(name = "food")
public class Food 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;

	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "company_id", insertable = false, updatable = false)
	@Fetch(FetchMode.JOIN)
	private Company company;

	@OneToMany(targetEntity = Sale.class, mappedBy = "id", orphanRemoval = false, fetch = FetchType.LAZY)
	private Set<Sale> sales;

	// getters and setters

}

Below is the Sale entity class:

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 = "sale")
public class Sale implements Serializable {

	private static final long serialVersionUID = 1L;

	@Id
	@Column(name = "id")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private long id;

	@Column(name = "quantity")
	private int quantity;

	@Column(name = "rate")
	private double rate;

	@Column(name = "amount")
	private double amount;

	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "food_id", insertable = false, updatable = false)
	@Fetch(FetchMode.JOIN)
	private Food food;

	// getters and setters

}

Spring Data JPA Repository

Spring Data JPA API 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 findByXyyy() 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 Interface

We have following Spring Data JPA Repositories where we write our JOIN queries using @Query annotation.

I have created here only one repository interface and written queries in this repository interface. If you want, you may create three different repository interfaces and distribute your queries into all of the repository interfaces.

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 on three tables in the below repository interface.

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.JoinDto;
import com.roytuts.spring.data.jpa.left.right.inner.cross.join.entity.Company;

public interface JoinRepository extends JpaRepository<Company, Integer> {

	@Query("SELECT new com.roytuts.spring.data.jpa.left.right.inner.cross.join.dto.JoinDto(s.id, f.name, c.name, s.amount) "
			+ "FROM Sale s LEFT JOIN s.food f LEFT JOIN f.company c")
	List<JoinDto> fetchDataLeftJoin();

	@Query("SELECT new com.roytuts.spring.data.jpa.left.right.inner.cross.join.dto.JoinDto(s.id, f.name, c.name, s.amount) "
			+ "FROM Sale s RIGHT JOIN s.food f RIGHT JOIN f.company c")
	List<JoinDto> fetchDataRightJoin();

	@Query("SELECT new com.roytuts.spring.data.jpa.left.right.inner.cross.join.dto.JoinDto(s.id, f.name, c.name, s.amount) "
			+ "FROM Sale s INNER JOIN s.food f INNER JOIN f.company c")
	List<JoinDto> fetchDataInnerJoin();

	@Query("SELECT new com.roytuts.spring.data.jpa.left.right.inner.cross.join.dto.JoinDto(s.id, f.name, c.name, s.amount) "
			+ "FROM Sale s, Food f, Company c WHERE s.food.id = f.id AND f.company.id = c.id")
	List<JoinDto> fetchDataCrossJoin();

}

DTO Class

As we already mentioned why do we need this DTO or VO class:

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

public class JoinDto {

	private long saleId;
	private String foodName;
	private String companyName;
	private double amount;

	public JoinDto(long saleId, String foodName, String companyName, double amount) {
		this.saleId = saleId;
		this.foodName = foodName;
		this.companyName = companyName;
		this.amount = amount;
	}

	//getters and setters

}

Service Class

Service class sits between controller and DAO layers and sends response accordingly. Generally we perform business logic in this service layer.

We get the results of join queries from repository and pass on to REST controller class.

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

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.roytuts.spring.data.jpa.left.right.inner.cross.join.dto.JoinDto;
import com.roytuts.spring.data.jpa.left.right.inner.cross.join.repository.JoinRepository;

@Service
public class JoinService {

	@Autowired
	private JoinRepository joinRepository;

	public List<JoinDto> leftJoinData() {
		return joinRepository.fetchDataLeftJoin();
	}

	public List<JoinDto> rightJoinData() {
		return joinRepository.fetchDataRightJoin();
	}

	public List<JoinDto> innerJoinData() {
		return joinRepository.fetchDataInnerJoin();
	}

	public List<JoinDto> crossJoinData() {
		return joinRepository.fetchDataCrossJoin();
	}
}

REST Controller Class

Spring provides wonderful API to build REST resources with little efforts. We mark the class as @RestController and we mark the method with appropriate annotation for http methods with endpoint.

This controller layer class handles incoming requests and outgoing response from/to clients or end users.

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.JoinDto;
import com.roytuts.spring.data.jpa.left.right.inner.cross.join.service.JoinService;

@RestController
public class JoinRestController {

	@Autowired
	private JoinService joinService;

	@GetMapping("/join/left")
	public ResponseEntity<List<JoinDto>> getLeftJoinData() {
		return new ResponseEntity<List<JoinDto>>(joinService.leftJoinData(), HttpStatus.OK);
	}

	@GetMapping("/join/right")
	public ResponseEntity<List<JoinDto>> getRightJoinData() {
		return new ResponseEntity<List<JoinDto>>(joinService.rightJoinData(), HttpStatus.OK);
	}

	@GetMapping("/join/inner")
	public ResponseEntity<List<JoinDto>> getLeftInnerData() {
		return new ResponseEntity<List<JoinDto>>(joinService.innerJoinData(), HttpStatus.OK);
	}

	@GetMapping("/join/cross")
	public ResponseEntity<List<JoinDto>> getLeftCrossData() {
		return new ResponseEntity<List<JoinDto>>(joinService.crossJoinData(), HttpStatus.OK);
	}

}

Main Class

The beauty of Spring Boot application is to create a main class and mark with @SpringBootApplication and executing this main class will deploy your 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;

@SpringBootApplication(scanBasePackages = "com.roytuts.spring.data.jpa.left.right.inner.cross.join")
public class SpringDataJpaLeftrightinnerCrossJoinApp {

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

}

Application Configuration

We are using here MySQL database server for storing records. Therefore we need to connect to MySQL database to query data from tables.

So create application.properties file with the below content. Don’t forget to update the database configuration values according to yours.

#datasource
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
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.

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.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;

@Configuration
@EnableJpaRepositories(basePackages = "com.roytuts.spring.data.jpa.left.right.inner.cross.join.repository")
public class JoinConfig {

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

}

In the above class we have let Spring know where our repository interface and entity classes are present.

Testing the Application

Now executing the main class will deploy your application into Tomcat server.

Now we can hit the URL from any REST client or simply in the browser will do the same thing because all REST endpoints are exposed on http GET method.

Left Join

Request: GET

URL: http://localhost:8080/join/left

Response:

[{"saleId":1,"foodName":"Momo","companyName":"Chinese Food","amount":25.0},{"saleId":2,"foodName":"Cheez","companyName":"Tom & Jerry","amount":150.0},{"saleId":3,"foodName":"Biscuit","companyName":"Order All","amount":16.0},{"saleId":4,"foodName":"Jerry","companyName":"Tom & Jerry","amount":180.0},{"saleId":5,"foodName":"Veg Dish","companyName":"Akash Food","amount":80.0},{"saleId":6,"foodName":"Non-veg Dish","companyName":"Order All","amount":270.0},{"saleId":7,"foodName":"Sweet","companyName":"Roy Food","amount":100.0},{"saleId":8,"foodName":"Laddu","companyName":null,"amount":42.0}]

If you see the above output then you will find that it is same as we had seen through SQL join query we had built earlier.

Right Join

GET http://localhost:8080/join/right

Response:

[{"saleId":2,"foodName":"Cheez","companyName":"Tom & Jerry","amount":150.0},{"saleId":4,"foodName":"Jerry","companyName":"Tom & Jerry","amount":180.0},{"saleId":3,"foodName":"Biscuit","companyName":"Order All","amount":16.0},{"saleId":6,"foodName":"Non-veg Dish","companyName":"Order All","amount":270.0},{"saleId":5,"foodName":"Veg Dish","companyName":"Akash Food","amount":80.0},{"saleId":1,"foodName":"Momo","companyName":"Chinese Food","amount":25.0},{"saleId":7,"foodName":"Sweet","companyName":"Roy Food","amount":100.0}]

Here also in the above output you got the same output but not in the order because I have not applied order on sale id.

Inner Join

GET http://localhost:8080/join/inner

Response:

[{"saleId":2,"foodName":"Cheez","companyName":"Tom & Jerry","amount":150.0},{"saleId":4,"foodName":"Jerry","companyName":"Tom & Jerry","amount":180.0},{"saleId":3,"foodName":"Biscuit","companyName":"Order All","amount":16.0},{"saleId":6,"foodName":"Non-veg Dish","companyName":"Order All","amount":270.0},{"saleId":5,"foodName":"Veg Dish","companyName":"Akash Food","amount":80.0},{"saleId":1,"foodName":"Momo","companyName":"Chinese Food","amount":25.0},{"saleId":7,"foodName":"Sweet","companyName":"Roy Food","amount":100.0}]

Here also in the above output you got the same output but not in the order because I have not applied order on sale id.

Cross Join

GET http://localhost:8080/join/cross

Response:

[{"saleId":2,"foodName":"Cheez","companyName":"Tom & Jerry","amount":150.0},{"saleId":4,"foodName":"Jerry","companyName":"Tom & Jerry","amount":180.0},{"saleId":3,"foodName":"Biscuit","companyName":"Order All","amount":16.0},{"saleId":6,"foodName":"Non-veg Dish","companyName":"Order All","amount":270.0},{"saleId":5,"foodName":"Veg Dish","companyName":"Akash Food","amount":80.0},{"saleId":1,"foodName":"Momo","companyName":"Chinese Food","amount":25.0},{"saleId":7,"foodName":"Sweet","companyName":"Roy Food","amount":100.0}]

Here in the above output you got almost the same output but not in the order because I have not applied order on sale id.

I have used where clause in the query for cross join in order to reduce the returned results otherwise it will fetch 320 entries.

That’s all. Hope you had a chance to understand how to work with Spring Boot Data JPA left, right, inner and cross joins on three tables.

Source Code

Download

Thanks for reading.

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

  1. Thanks for the blog it’s really good, I am getting table not mapped error in repository so , unable to validate service error is coming…please do reply

  2. Sir,

    I am getting below error, could you pls help me in this..

    “errors”: [{
    “message”: “could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet”,

Leave a Reply

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