How to join two tables with a group query using Hibernate (5.4) Criteria?

Question

This is my Project Entity: ( Project.java )

@Entity
@Table(name = "project")
public class Project implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column
    private String name;

    @Column
    private String description;

    @OneToMany(
            mappedBy = "project",
            cascade = CascadeType.REMOVE,
            orphanRemoval = true,
            fetch = FetchType.LAZY
    )
    private List<Task> tasks = new ArrayList<>();

    public Project() {}

    public Project(String name, String description) {
        this.name = name;
        this.description = description;
    }

    // getter setter here
}

This is my Task Entity: ( Task.java )

@Entity
@Table(name = "task")
public class Task implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(name = "title")
    private String title;

    @Column(name = "description")
    private String description;

    @ManyToOne
    @JoinColumn(name = "project_id", referencedColumnName="id")
    private Project project;

    public Task() {}

    public Task(String title, String description) {
        this.title = title;
        this.description = description;
    }

    // getter setter here

}

Desired DTO: ( ProjectWithSumOfTaskDto.java )

public class ProjectWithSumOfTaskDto {

    private int projectId;
    private String name;
    private long totalTasks;

    public ProjectWithSumOfTaskDto(int id, String name, long totalTasks) {
        this.projectId = id;
        this.name = name;
        this.totalTasks = totalTasks;
    }

    // getter setter here
}

Table structure in database:

tasks:

id
title
description
project_id

projects:

id
name
description

The main question:

What I need now is to join the "projects" table and "tasks" table grouping by the "project_id" column. And obtain List as output.

I have done it with HQL, Now I have to learn how to do it in hibernate criteria.

I'm using hibernate version 5.4 (latest)

(Thanks for reading and many love for open source community)

Farhat Shahir Farhat Shahir · Accepted Answer · 2020-10-07T14:55:51

After spending countless hours, I came by this solution below:

Steps:

Made Task table as Root.

Root<Task> task = criteria.from(Task.class);

Joined Project with Task, and made the JoinType as Left join.

Join<Task, Project> projectJoin = task.join(Task_.project, JoinType.LEFT);

Grouped by with "project_id" of "tasks" table.

criteria.groupBy(task.get(Task_.project));

Used multi-select and selected the task.project_id, project.name, and the count of task rows after group by.

criteria.multiselect(projectJoin.get(Project_.ID).alias("projectId"),
                     projectJoin.get(Project_.NAME).alias("name"),
                     builder.count(task).alias("totalTasks"));

And finally returned the list of my desired DTO like this:

return session.createQuery(criteria).getResultList();

Together, the code will look like this:

public List<ProjectWithSumOfTaskDto> projectsWithTaskCount() {
    return criteriaBuilderContext((session, builder) -> {

        CriteriaQuery<ProjectWithSumOfTaskDto> criteria = builder.createQuery(
                ProjectWithSumOfTaskDto.class
        );

        Root<Task> task = criteria.from(Task.class);
        Join<Task, Project> projectJoin = task.join(Task_.project, JoinType.LEFT);
        
        criteria.groupBy(task.get(Task_.project));

        criteria.multiselect(
            projectJoin.get(Project_.ID).alias("projectId"),
            projectJoin.get(Project_.NAME).alias("name"),
            builder.count(task).alias("totalTasks")
        );

        return session.createQuery(criteria).getResultList();
    });
}