Blog

Hey guys in this post, we will write Join query in Spring Data JPA with Example. We will create a Spring boot project step by step and connect it to the MySQL database. Follow this tutorial till the end to understand the Data JPA join query. This is the continuation of the previous post, please follow that post before proceeding with this.

Complete example

Assume that we have two tables tbl_laptops and tbl_brands. Now tbl_laptops having a foreign key reference to the tbl_brands. Now we need to retrieve the list of laptops that are having the same brand.

Read More:

1. Check this how to write JPQL select query
2. Check this how to write a JPQL delete query

Create spring boot project

There are many different ways to create a spring boot application, you can follow the below articles to create one –

>> Create spring boot application using Spring initializer
>> Create spring boot application in Spring tool suite [STS]
>> Create spring boot application in IntelliJ IDEA

Add maven dependencies

Open pom.xml and add the following dependencies –

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.4.4</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>in.bushansirgur</groupId>
	<artifactId>findbyfieldname</artifactId>
	<version>v1</version>
	<name>findbyfieldname</name>
	<description>Spring boot data jpa find by field name</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

spring-boot-starter-web dependency for building web applications using Spring MVC. It uses the tomcat as the default embedded container.

spring-boot-devtools dependency for automatic reloads or live reload of applications. spring-boot-starter-data-jpa dependency is a starter for using Spring Data JPA with Hibernate. lombok dependency is a java library that will reduce the boilerplate code that we usually write inside every entity class like setters, getters, and toString()

Configure the datasource

spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=scbushan05
spring.datasource.password=scbushan05

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Create an entity class

Create Laptop.java inside the in.bushansirgur.springboot.entity package and add the following content

package in.bushansirgur.springboot.entity;

import java.math.BigDecimal;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;

import in.bushansirgur.springboot.request.LaptopRequest;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Entity
@Table(name="tbl_laptops")
@Getter
@Setter
@NoArgsConstructor
public class Laptop {
	
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;
	
	private String name;
	
	@OneToOne
	@JoinColumn(name = "brand_id")
	private Brand brand;
	
	private BigDecimal price;
	
	public Laptop(LaptopRequest request) {
		this.name = request.getName();
		this.price = request.getPrice(); 
	}
	
}

• @OneToOne annotation is used to add one to one relationship between two tables
• @JoinColumn annotation is used to join a new column, this will be the foreign key column of the tbl_laptops table
• @Data annotation which is a Lombok annotation, that will automatically create setters, getters, toString(), and equals() for us.
• @Entity is a mandatory annotation that indicates that this class is a JPA entity and is mapped with a database table.
• @Table annotation is an optional annotation that contains the table info like table name.

• @Id annotation is a mandatory annotation that marks a field as the primary key

Create Brand.java inside the in.bushansirgur.springboot.entity package and add the following content


package in.bushansirgur.springboot.entity;

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

import lombok.Getter;
import lombok.Setter;

@Entity
@Table(name = "tbl_brand")
@Setter
@Getter
public class Brand {
	
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;
	
	@Column(name = "brand")
	private String name;
}

Create a Repository

Create an interface LaptopRepository.java inside the in.bushansirgur.springboot.repos package and add the following content

package in.bushansirgur.springboot.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import in.bushansirgur.springboot.entity.Laptop;

@Repository
public interface LaptopRepository extends JpaRepository<Laptop, Long>{
	
	List<Laptop> findByBrandName(String name);
	
}

When we call this method, behind the scenes JPA will create and execute the following join query

Hibernate: select laptop0_.id as id1_1_, laptop0_.brand_id as brand_id4_1_, laptop0_.name as name2_1_, laptop0_.price as price3_1_ from tbl_laptops laptop0_ left outer join tbl_brand brand1_ on laptop0_.brand_id=brand1_.id where brand1_.brand=?


Check the JPQL join query with Spring Data JPA

Create an interface BrandRepository.java inside the in.bushansirgur.springboot.repos package and add the following content

package in.bushansirgur.springboot.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import in.bushansirgur.springboot.entity.Brand;

@Repository
public interface BrandRepository extends JpaRepository<Brand, Long>{

}

Create a Rest controller

Create LaptopController.java inside the in.bushansirgur.springboot.controller package and add the following content

package in.bushansirgur.springboot.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.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import in.bushansirgur.springboot.entity.Brand;
import in.bushansirgur.springboot.entity.Laptop;
import in.bushansirgur.springboot.repository.BrandRepository;
import in.bushansirgur.springboot.repository.LaptopRepository;
import in.bushansirgur.springboot.request.LaptopRequest;

@RestController
public class LaptopController {
	
	@Autowired
	private LaptopRepository lRepo;
	
	@Autowired
	private BrandRepository bRepo;
	
	@GetMapping("/laptops")
	public ResponseEntity<List<Laptop>> getData (@RequestParam String brand) {
		return new ResponseEntity<List<Laptop>>(lRepo.findByBrandName(brand), HttpStatus.OK);
	}
}

Run the app

Run the application using the below maven command –

mvn spring-boot:run

Open the browser and enter the following URL –

• localhost:8080/laptops?brand=apple

That’s it for this post. I hope you guys liked it, if you did then please share this post with your friends and colleagues. Also, share this post in any of your social media profiles. Thanks, I will see you in the next post.