Group By Clause 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;
	}

}

CategoryGroup Entity

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

package com.demo.entities;

import java.math.BigDecimal;

public class CategoryGroup {

	private Integer categoryId;
	private BigDecimal minPrice;
	private BigDecimal maxPrice;
	private Long sumQuantity;
	private Long countProduct;
	private Double avgPrice;

	public Integer getCategoryId() {
		return categoryId;
	}

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

	public BigDecimal getMinPrice() {
		return minPrice;
	}

	public void setMinPrice(BigDecimal minPrice) {
		this.minPrice = minPrice;
	}

	public BigDecimal getMaxPrice() {
		return maxPrice;
	}

	public void setMaxPrice(BigDecimal maxPrice) {
		this.maxPrice = maxPrice;
	}

	public Long getSumQuantity() {
		return sumQuantity;
	}

	public void setSumQuantity(Long sumQuantity) {
		this.sumQuantity = sumQuantity;
	}

	public Long getCountProduct() {
		return countProduct;
	}

	public void setCountProduct(Long countProduct) {
		this.countProduct = countProduct;
	}

	public Double getAvgPrice() {
		return avgPrice;
	}

	public void setAvgPrice(Double avgPrice) {
		this.avgPrice = avgPrice;
	}

	public CategoryGroup(Integer categoryId, BigDecimal minPrice, BigDecimal maxPrice, Long sumQuantity, Long countProduct, Double avgPrice) {
		this.categoryId = categoryId;
		this.minPrice = minPrice;
		this.maxPrice = maxPrice;
		this.sumQuantity = sumQuantity;
		this.countProduct = countProduct;
		this.avgPrice = avgPrice;
	}

	public CategoryGroup() {
	}

}

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.CategoryGroup;

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

	@Query("select new com.demo.entities.CategoryGroup(p.category.id as categoryId, "
									+ "min(p.price) as minPrice, "
									+ "max(p.price) as maxPrice, "
									+ "sum(p.quantity) as sumQuantity, "
									+ "count(p.id) as countProduct, "
									+ "avg(p.price) as avgPrice) "
									+ "from Product p "
									+ "group by p.category.id")
	public List<CategoryGroup> groupBy();

}

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.CategoryGroup;

public interface ProductService {

	public List<CategoryGroup> groupBy();

}

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.CategoryGroup;
import com.demo.repositories.ProductRepository;

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

	@Autowired
	private ProductRepository productRepository;

	@Override
	public List<CategoryGroup> groupBy() {
		return productRepository.groupBy();
	}

}

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.CategoryGroup ;
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 (CategoryGroup categoryGroup : productService.groupBy()) {
				System.out.println("Category Id: " + categoryGroup.getCategoryId());
				System.out.println("Min Price: " + categoryGroup.getMinPrice());
				System.out.println("Max Price: " + categoryGroup.getMaxPrice());
				System.out.println("Sum Quantity: " + categoryGroup.getSumQuantity());
				System.out.println("Count Product: " + categoryGroup.getCountProduct());
				System.out.println("Avg Price: " + categoryGroup.getAvgPrice());
				System.out.println("==================");
			}

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

}

Output

Category Id: 1
Min Price: 1.0
Max Price: 3.0
Sum Quantity: 16
Count Product: 3
Avg Price: 2.0
==================
Category Id: 2
Min Price: 5.0
Max Price: 12.0
Sum Quantity: 19
Count Product: 3
Avg Price: 8.0
==================
Category Id: 3
Min Price: 2.0
Max Price: 4.0
Sum Quantity: 34
Count Product: 3
Avg Price: 3.0
==================