Join in Custom Query in Spring Boot JPA

Create Spring Boot Project

On the Eclipse, create a Spring Boot project

Enter Project Information:

• Name: SpringBootDataJPA
• Group: com.demo
• Artifact: SpringBootDataJPA
• Description: Spring Boot Data JPA
• Package: com.demo

Select the technologies and libraries to be used:

• JPA
• MySQL

Click Next button to show Site Information for project

Click Finish button to finish create Spring Boot project

Configure pom.xml

<?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>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.0.M1</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.demo</groupId>
	<artifactId>SpringBootDataJPA</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>SpringBootDataJPA</name>
	<description>Spring Boot Data JPA</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>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

		<dependency>
			<groupId>net.bytebuddy</groupId>
			<artifactId>byte-buddy</artifactId>
			<version>1.9.12</version>
		</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>
			</plugin>
		</plugins>
	</build>

	<repositories>
		<repository>
			<id>spring-snapshots</id>
			<name>Spring Snapshots</name>
			<url>https://repo.spring.io/snapshot</url>
			<snapshots>
				<enabled>true</enabled>
			</snapshots>
		</repository>
		<repository>
			<id>spring-milestones</id>
			<name>Spring Milestones</name>
			<url>https://repo.spring.io/milestone</url>
		</repository>
	</repositories>
	<pluginRepositories>
		<pluginRepository>
			<id>spring-snapshots</id>
			<name>Spring Snapshots</name>
			<url>https://repo.spring.io/snapshot</url>
			<snapshots>
				<enabled>true</enabled>
			</snapshots>
		</pluginRepository>
		<pluginRepository>
			<id>spring-milestones</id>
			<name>Spring Milestones</name>
			<url>https://repo.spring.io/milestone</url>
		</pluginRepository>
	</pluginRepositories>

</project>

Create Database

Create a database with the name is springbootdatajpa. This database have 2 tables: Product and Category

--
-- Table structure for table `category`
--

CREATE TABLE `category` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `category`
--

INSERT INTO `category` (`name`) VALUES('Mobile');
INSERT INTO `category` (`name`) VALUES('Computer');
INSERT INTO `category` (`name`) VALUES('Laptop');

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `price` decimal(10,1) NOT NULL,
  `quantity` int(11) NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `photo` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `featured` tinyint(1) NOT NULL,
  `categoryid` int(11) NOT NULL,
  FOREIGN KEY (`categoryid`) REFERENCES `category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `categoryid`, `featured`) VALUES('Mobile 1', '2.0', 2, 'description 1', 'thumb1.gif', 1, 0);
INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `categoryid`, `featured`) VALUES('Mobile 2', '1.0', 5, 'description 2', 'thumb2.gif', 1, 1);
INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `categoryid`, `featured`) VALUES('Mobile 3', '3.0', 9, 'description 3', 'thumb3.gif', 1, 0);
INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `categoryid`, `featured`) VALUES('Computer 1', '5.0', 12, 'description 4', 'thumb1.gif', 2, 1);
INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `categoryid`, `featured`) VALUES('Computer 2', '7.0', 5, 'description 5', 'thumb1.gif', 2, 0);
INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `categoryid`, `featured`) VALUES('Computer 3', '12.0', 2, 'description 6', 'thumb2.gif', 2, 1);
INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `categoryid`, `featured`) VALUES('Laptop 1', '3.0', 8, 'description 7', 'thumb2.gif', 3, 0);
INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `categoryid`, `featured`) VALUES('Laptop 2', '4.0', 11, 'description 8', 'thumb3.gif', 3, 1);
INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `categoryid`, `featured`) VALUES('Laptop 3', '2.0', 15, 'description 9', 'thumb2.gif', 3, 0);

Category Table

Product Table

Database Configuration

Open application.properties file in src/main/resources folder and add configurations connect to database as below:

spring.datasource.url= jdbc:mysql://localhost:3306/springbootdatajpa
spring.datasource.username=root
spring.datasource.password=123456

Entities Class

Create new package named com.demo.entities. In this package, create new java classes as below:

Category Entity

In com.demo.entities package, create new java class named Category.java as below:

package com.demo.entities;

import java.util.HashSet;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import static javax.persistence.GenerationType.IDENTITY;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name = "category")
public class Category implements java.io.Serializable {

	private Integer id;
	private String name;
	private Set<Product> products = new HashSet<Product>(0);

	@Id
	@GeneratedValue(strategy = IDENTITY)
	public Integer getId() {
		return this.id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return this.name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@OneToMany(fetch = FetchType.LAZY, mappedBy = "category")
	public Set<Product> getProducts() {
		return this.products;
	}

	public void setProducts(Set<Product> products) {
		this.products = products;
	}

}

Product Entity

In com.demo.entities package, create new java class named Product.java as below:

package com.demo.entities;

import java.math.BigDecimal;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import static javax.persistence.GenerationType.IDENTITY;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "product")
public class Product implements java.io.Serializable {

	private Integer id;
	private String name;
	private BigDecimal price;
	private int quantity;
	private String description;
	private String photo;
	private boolean featured;
	private Category category;

	@Id
	@GeneratedValue(strategy = IDENTITY)
	public Integer getId() {
		return this.id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return this.name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public BigDecimal getPrice() {
		return this.price;
	}

	public void setPrice(BigDecimal price) {
		this.price = price;
	}

	public int getQuantity() {
		return this.quantity;
	}

	public void setQuantity(int quantity) {
		this.quantity = quantity;
	}

	public String getDescription() {
		return this.description;
	}

	public void setDescription(String description) {
		this.description = description;
	}

	public String getPhoto() {
		return this.photo;
	}

	public void setPhoto(String photo) {
		this.photo = photo;
	}

	public boolean isFeatured() {
		return this.featured;
	}

	public void setFeatured(boolean featured) {
		this.featured = featured;
	}

	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "categoryid", nullable = false)
	public Category getCategory() {
		return category;
	}

	public void setCategory(Category category) {
		this.category = category;
	}

}

ProductJoin Entity

In com.demo.entities package, create new java class named ProductJoin.java as below:

package com.demo.entities;

import java.math.BigDecimal;

public class ProductJoin implements java.io.Serializable {

	private Integer id;
	private String name;
	private BigDecimal price;
	private Integer categoryId;
	private String categoryName;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public BigDecimal getPrice() {
		return price;
	}

	public void setPrice(BigDecimal price) {
		this.price = price;
	}

	public Integer getCategoryId() {
		return categoryId;
	}

	public void setCategoryId(Integer categoryId) {
		this.categoryId = categoryId;
	}

	public String getCategoryName() {
		return categoryName;
	}

	public void setCategoryName(String categoryName) {
		this.categoryName = categoryName;
	}

	public ProductJoin(Integer id, String name, BigDecimal price, Integer categoryId, String categoryName) {
		this.id = id;
		this.name = name;
		this.price = price;
		this.categoryId = categoryId;
		this.categoryName = categoryName;
	}

	public ProductJoin() {
	}

}

ProductRepository Interface

Create new package named com.demo.repositories. In this package, create new interface named ProductRepository.java implement from CrudRepository interface of Spring Framework as below:

package com.demo.repositories;

import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.demo.entities.ProductJoin;

@Repository("productRepository")
public interface ProductRepository extends CrudRepository<Product, Integer> {

	@Query("select new com.demo.entities.ProductJoin(p.id, p.name, p.price, p.category.id, p.category.name) from Product p, Category c where p.category = c")
	public List<ProductJoin> join();

}

ProductService Interface

Create new package named com.demo.services. In this package create new interface named ProductService.java as below:

package com.demo.services;

import java.util.List;
import com.demo.entities.ProductJoin;

public interface ProductService {

	public List<ProductJoin> join();

}

ProductServiceImpl Class

In com.demo.services package, create new java class named ProductServiceImpl.java implement from ProductService interface

package com.demo.services;

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.demo.entities.ProductJoin;
import com.demo.repositories.ProductRepository;

@Transactional
@Service("productService")
public class ProductServiceImpl implements ProductService {

	@Autowired
	private ProductRepository productRepository;

	@Override
	public List<ProductJoin> join() {
		return productRepository.join();
	}

}

JPA Configuration

In com.demo package, create new java class named JPAConfiguration.java as below:

package com.demo;

import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.demo.services.ProductService;
import com.demo.services.ProductServiceImpl;

@Configuration
@EnableAutoConfiguration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = { "com.demo.repositories" })
@ComponentScan("com.demo")
@PropertySource("classpath:application.properties")
public class JPAConfiguration {

	@Bean
	public ProductService productService() {
		return new ProductServiceImpl();
	}

}

Project Structure

Application

Create new package named com.demo.main. In this package, create new java file named Demo.java as below:

package com.demo.main;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.support.AbstractApplicationContext;

import com.demo.entities.ProductJoin ;
import com.demo.JPAConfiguration;
import com.demo.services.ProductService;

public class Demo {

	public static void main(String[] args) {
		try {
			AbstractApplicationContext context = new AnnotationConfigApplicationContext(JPAConfiguration.class);
			ProductService productService = context.getBean(ProductService.class);

			for (ProductJoin productJoin : productService.join()) {
				System.out.println("Id: " + productJoin.getId());
				System.out.println("Name: " + productJoin.getName());
				System.out.println("Price: " + productJoin.getPrice());
				System.out.println("Category Id: " + productJoin.getCategoryId());
				System.out.println("Category Name: " + productJoin.getCategoryName());
				System.out.println("==================");
			}

			context.close();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}

}

Output

Id: 1
Name: Mobile 1
Price: 2.0
Category Id: 1
Category Name: Mobile
==================
Id: 2
Name: Mobile 2
Price: 1.0
Category Id: 1
Category Name: Mobile
==================
Id: 3
Name: Mobile 3
Price: 3.0
Category Id: 1
Category Name: Mobile
==================
Id: 4
Name: Computer 1
Price: 5.0
Category Id: 2
Category Name: Computer
==================
Id: 5
Name: Computer 2
Price: 7.0
Category Id: 2
Category Name: Computer
==================
Id: 6
Name: Computer 3
Price: 12.0
Category Id: 2
Category Name: Computer
==================
Id: 7
Name: Laptop 1
Price: 3.0
Category Id: 3
Category Name: Laptop
==================
Id: 12
Name: Laptop 2
Price: 4.0
Category Id: 3
Category Name: Laptop
==================
Id: 13
Name: Laptop 3
Price: 2.0
Category Id: 3
Category Name: Laptop
==================