添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
冷冷的火锅  ·  三年亏175亿，“卖铲子”的地平线上市很赶时 ...·  1 月前    · 
愤怒的伤疤  ·  CorelCAD ...·  3 月前    · 
伤情的遥控器  ·  塞拉利昂开通签证申请网上通道·  5 月前    · 
欢快的罐头  ·  怎么在C++中实现string的乘法运算 ...·  6 月前    · 
安静的海龟  ·  Python sqlparse ...·  6 月前    · 
link管理  ›  JPA - Criteria GROUP BY Clause - GeeksforGeeks
jpa string root criteria
https://www.geeksforgeeks.org/jpa-criteria-group-by-clause/
大气的凳子
6 月前

JPA in Java can be called the Java Persistence API. It provides the Criteria API as a programming mechanism for creating queries dynamically. One of these important features is the GROUP BY clause which allows developers to group query results based on specific criteria.

GROUP BY Clause

The Group BY clause of the JPA Criteria API is used to create groups of queries based on one or more fields. It can be used with aggregation functions such as COUNT and SUM to calculate aggregate data.

Steps to Implement GROUP BY Clause

  • Define the CriteriaQuery object of the JPA application.
  • Specify the root entity and select the desired fields.
  • Applying the grouping using the groupBy( ) method in the application.
  • Optionally, we can apply the aggregate functions using the CriteriaBuilder object.
  • After that, we can execute the query and retrieve the grouped results.

Aggregate Functions

The aggregation function can calculate aggregated data, producing a single result for each group. These functions, like COUNT SUM, AVG, MIN, and MAX, can be performed using the GROUP BY clause.

Example : Calculating the total salary and the number of employees per department. 

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
Root<Employee> root = query.from(Employee.class);

// Select department, total salary, average salary, and count of employees
query.multiselect(
    root.get("department"),
    cb.sum(root.get("salary")),
    cb.avg(root.get("salary")),
    cb.count(root)
);

// Group by department
query.groupBy(root.get("department"));

// Execute the query and retrieve results
List<Object[]> results = entityManager.createQuery(query).getResultList();

// Print grouped results
for (Object[] result : results) {
    Department department = (Department) result[0];
    Double totalSalary = (Double) result[1];
    Double avgSalary = (Double) result[2];
    Long employeeCount = (Long) result[3];
    System.out.println("Department: " + department.getName() + ", Total Salary: " + totalSalary + ", Average Salary: " + avgSalary + ", Employee Count: " + employeeCount);
}

Having Clause:

The HAVING clause that can be used to filters the grouped data based on the specified conditions and it is similar to the WHERE clause but the operates on the grouped results rather than the individual rows.

Example : Retrieving the departments with the more than three employees. 

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
Root<Employee> root = query.from(Employee.class);

// Select department and count of employees
query.multiselect(
    root.get("department"),
    cb.count(root)
);

// Group by department
query.groupBy(root.get("department"));

// Apply having clause to filter departments with more than three employees
query.having(cb.gt(cb.count(root), 3L));

// Execute the query and retrieve results
List<Object[]> results = entityManager.createQuery(query).getResultList();

// Print grouped results
for (Object[] result : results) {
    Department department = (Department) result[0];
    Long employeeCount = (Long) result[1];
    System.out.println("Department: " + department.getName() + ", Employee Count: " + employeeCount);
}

Steps to Implement the GROUP BY Clause of JPA

We can develop the simple JPA application that can demonstrate the GROUP BY clause of the application.

Step 1 : Create the new JPA project using the Intellj Idea named as jpa-groupby-demo. Once the project created successfully, the file structure looks like the below image.

Step 2 : Open the open.xml and add the below dependencies into the project. 

        <dependency>
            <groupId>org.hibernate.orm</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>6.0.2.Final</version>
        </dependency>
        <dependency>
            <groupId>org.glassfish.jaxb</groupId>
            <artifactId>jaxb-runtime</artifactId>
            <version>3.0.2</version>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>




    

        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-engine</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>


Step 3 : Open the persistence.xml and put the below code into the project and it can configure the database of the project. 

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_0.xsd"
             version="3.0">
    <persistence-unit name="persistence-unit-group">
        <class>model.Department</class>
        <class>model.Employees</class>
        <properties>
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/example"/>
            <property name="javax.persistence.jdbc.user" value="root"/>
            <property name="javax.persistence.jdbc.password" value=""/>
            <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect"/>
            <property name="hibernate.hbm2ddl.auto" value="update"/>
        </properties>
    </persistence-unit>
</persistence>

Step 4: Create the tables in MySQL database using the below SQL query:


Department Table Query:


-- Create 'departments' table
CREATE TABLE departments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);
Employees Table Query:


-- Create 'employees' table with foreign key reference to 'departments' table
CREATE TABLE employees (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    salary DECIMAL(10, 2),
    department_id BIGINT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);



Step 5: Create the new Java package named as model in that package create the new Entity Java class named as the Employees.


Go to src > main > java > model > Employees and put the below code.

    
import jakarta.persistence.*;
 * @Entity annotation represents an Employee entity in the application.
@Entity
@Table(name = "employees")
public class Employee {
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id; // Unique identifier for the employee.
    @Column(name = "first_name")
    private String firstName;
    @Column(name = "last_name")
    private String lastName; 
    @Column(name = "salary")
    private double salary; 
    @Column(name = "department")
    private String department; 
    // Constructors, getters, and setters
    public Employee() {
    public Employee(String firstName, String lastName, double salary, String department) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.salary = salary;
        this.department = department;
    public Long getId() {
        return id;
    public void setId(Long id) {
        this.id = id;
    public String getFirstName() {
        return firstName;
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    public String getLastName() {
        return lastName;
    public void setLastName(String lastName) {
        this.lastName = lastName;
    public double getSalary() {
        return salary;
    public void setSalary(double salary) {
        this.salary = salary;
    public String getDepartment() {
        return department;
    public void setDepartment(String department) {
        this.department = department;

Step 6: Create the new Java package named as model in that package create the new Entity Java class named as the Department.


Go to src > main > java > model > Department and put the below code.

    
package model;
import jakarta.persistence.*;
 * Represents an Department entity in the application.
@Entity
@Table(name = "departments")
public class Department {
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id; // Unique identifier for the address.
    @Column(name = "name") //mapping to name column of department table




    

    private String name;
    public Department() {  // default constructor of the department
    public Long getId() {  
        return id;
    public void setId(Long id) {
        this.id = id;
    public String getName() {
        return name;
    public void setName(String name) {
        this.name = name;
  //parameterized constructor with fields of Id and name
    public Department(Long id, String name) {
        this.id = id;
        this.name = name;

Step 7: Create the new Java main class named as the MainApplication.


Go to src > main > java > MainApplication and put the below code.

    
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.Persistence;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Join;
import jakarta.persistence.criteria.Root;
import model.Department;
import model.Employees;
import java.util.List;
public class MainApplication 
    public static void main(String[] args) 
        // Create an EntityManagerFactory using persistence unit name
        EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("JpaGroupByExample");
        // Create an EntityManager
        EntityManager entityManager = entityManagerFactory.createEntityManager();
        // Begin a transaction
        entityManager.getTransaction().begin();
        // Example data initialization
        Department itDepartment = new Department();
        itDepartment.setName("IT Department");
        entityManager.persist(itDepartment);
        Department salesDepartment = new Department();
        salesDepartment.setName("Sales Department");
        entityManager.persist(salesDepartment);
        Employees employee1 = new Employees();
        employee1.setFirstName("Mahesh");
        employee1.setLastName("Kadambala");
        employee1.setSalary(80000);
        employee1.setDepartment(itDepartment);
        entityManager.persist(employee1);
        Employees employee2 = new Employees();
        employee2.setFirstName("Eswar");
        employee2.setLastName("Betha");
        employee2.setSalary(75000);
        employee2.setDepartment(salesDepartment);
        entityManager.persist(employee2);
        // Commit the transaction
        entityManager.getTransaction().commit();
        // Use Criteria API to group employees by department and calculate total salary
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
        Root<Employees> root = query.from(Employees.class);
        Join<Employees, Department> departmentJoin = root.join("department");
        query.multiselect(
                departmentJoin,
                cb.sum(root.get("salary"))
        query.groupBy(departmentJoin);
        // Execute the query and retrieve results
        List<Object[]> results = entityManager.createQuery(query).getResultList();
        // Print grouped results
        for (Object[] result : results) {
            Department department = (Department) result[0];
            Double totalSalary = (Double) result[1];
            System.out.println("Department: " + department.getName() + ", Total Salary: " + totalSalary);
        // Close the EntityManager and EntityManagerFactory
        entityManager.close();
        entityManagerFactory.close();

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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>org.example</groupId>
    <artifactId>jpa-groupby-demo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>jpa-groupby-demo</name>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.target>11</maven.compiler.target>
        <maven.compiler.source>11</maven.compiler.source>
        <junit.version>




    
5.9.2</junit.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.hibernate.orm</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>6.0.2.Final</version>
        </dependency>
        <dependency>
            <groupId>org.glassfish.jaxb</groupId>
            <artifactId>jaxb-runtime</artifactId>
            <version>3.0.2</version>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-engine</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
        </plugins>
    </build>
</project>

Step 8: Once the project is completed, run the application. It will show the Employee’s department name and salary by the descending order as output. Refer the below output image for the better understanding of the concept.

                
JPA - Criteria WHERE Clause

                
JPA in Java is defined as the Java Persistence API and the Criteria API provides a structured and type-safe way to build dynamic queries at runtime. The WHERE clause is an integral part of any SQL query and allows us to modify records based on specified conditions. Similarly, the WHERE clause in the JPA Criteria API is used to specify filtering cri

                
JPA - Criteria Having Clause

                
The JPA Criteria API in Java provides a systematic way to build dynamic queries at runtime without relying on static string-based queries. The JPA Criteria Having Clause is used to extract query results based on the conditions used on the collected data with aggregation functions such as COUNT, SUM, and AVG. It allows the developers to specify the

                
Difference between JPA and Spring Data JPA

                
JPA provides specifications for persisting, reading, and maintaining data from Java objects to relational tables in a database. The JPA defines rules and principles for conducting standard interfaces. Spring data repository significantly reduces the extra code necessary to provide a data access layer for multiple persistence stores. Spring Data JPA

                
JPA - Criteria API

                
In Java, JPA is defined as Java Persistence API, and the Criteria API provides a powerful tool for constructing the queries dynamically at the runtime. Unlike the JPQL (Java Persistence Query Language) which uses the strings to represent the queries programmatically. Criteria API in JPAThe JPA Criteria API allows the developers to create queries us

                
JPA - ORDER BY Clause

                
JPA in Java can be defined as the Java Persistence API and consists of Java instructions for accessing and managing data between Java objects and related databases. JPA provides ways for us to access object-oriented and complex databases through SQL queries and database connections. There are many clauses in JPA and the ORDER BY clause is one of th

                
How to Set the Schema Name Dynamically in Spring JPA?

                
Spring JPA provides a convenient way to save Java objects to relational databases. However, it typically assumes that you have a single schema for your database. If you need to work with multiple schemas, you can use a custom naming strategy to set the schema name dynamically. Creating a Custom Naming StrategyTo create a custom naming strategy, you

                
Spring Boot - Build a Dynamic Full Text Search API Using JPA Queries

                
A Global Full-Text Entity Search is a search functionality that allows users to find entities such as records or objects within a dataset by searching through the entirety of their content. Global Full-Text Entity Search is like having a super-smart search companion that digs deep into the entire content of entities. Consider the below examples to

                
Spring Data JPA vs Spring JDBC Template

                
In this article, we will learn about the difference between Spring Data JPA vs Spring JDBC Template. Spring Data JPATo implement JPA-based repositories, Spring Data JPA, a piece of the Spring Data family, takes out the complexity. With the help of spring data JPA the process of creating Spring-powered applications that support data access technolog

                
Spring Boot Batch Processing Using Spring Data JPA to CSV File

                
The Spring Batch is a framework in the Spring Boot ecosystem It can provide a lot of functionalities for Batch processing. The Spring Batch framework simplifies the batch development of applications by providing reliable components and other patterns for common batch processing concerns. Mostly, batch processing is used to read and write data in bu

                
Show SQL from Spring Data JPA/Hibernate in Spring Boot

                
In Spring Boot, Spring Data JPA is part of the larger Spring Data Project that can simplify the development of the data access layers in the spring applications using the Java Persistence API and it can provide a higher-level abstraction over the JPA API. It can reduce the boilerplate code and make it easier to work with the database. Spring Data J

          
        
 
        We use cookies to ensure you have the best browsing experience on our website. By using our site, you
        acknowledge that you have read and understood our
        Cookie Policy &
        Privacy Policy
        Got It !
                    
Please go through our recently updated Improvement Guidelines before submitting any improvements.

                    
This improvement is locked by another user right now. You can suggest the changes for now and it will be under 'My Suggestions' Tab on Write.

                    
You will be notified via email once the article is available for improvement.
                        Thank you for your valuable feedback!
                        
                  
Please go through our recently updated Improvement Guidelines before submitting any improvements.

                      
Suggest Changes

                      
Help us improve. Share your suggestions to enhance the article. Contribute your expertise and make a difference in the GeeksforGeeks portal.
 
推荐文章
冷冷的火锅  ·  三年亏175亿，“卖铲子”的地平线上市很赶时间-中国能源网
1 月前
愤怒的伤疤  ·  CorelCAD 2021中文破解版下载(注册机) 最新版破解补丁 wim/Mac百度云下载
3 月前
伤情的遥控器  ·  塞拉利昂开通签证申请网上通道
5 月前
欢快的罐头  ·  怎么在C++中实现string的乘法运算 - 编程语言 - 亿速云
6 月前
安静的海龟  ·  Python sqlparse 模块：你需要知道的一切
6 月前
Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
link管理 - 链接快照平台