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

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)

Answer

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

Steps:

1. Made Task table as Root.

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

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

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

3. Grouped by with “project_id” of “tasks” table.

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

4. 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"));

5. 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();
    });
}