CODEWITHSUNDEEP
javajpajoingroup-byhibernate-criteria
Farhat Shahir
Farhat Shahir

Reputation: 184

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:

projects:

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)

Upvotes: 2

Views: 2793

Answers (2)

Farhat Shahir
Farhat Shahir

Reputation: 184

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

Steps:

  1. Made Task table as Root.
Root<Task> task = criteria.from(Task.class);
  1. Joined Project with Task, and made the JoinType as Left join.
Join<Task, Project> projectJoin = task.join(Task_.project, JoinType.LEFT);
  1. Grouped by with "project_id" of "tasks" table.
criteria.groupBy(task.get(Task_.project));
  1. 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"));
  1. 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();
    });
}

Upvotes: 3

Partho
Partho

Reputation: 2715

i am not using your dto what i am using is your base class and i also dont run it, the solution is :

Criteria cr = session.createCriteria(Project.class, "p");
cr.createAlias("p.tasks", "t", Criteria.INNER_JOIN);
cr.add(Restrictions.eq("p.id",id);
return cr.list();

i hope this is helpful

Upvotes: 1

Related Questions