How to write this PostgreSQL query in jpa specification or criteria query?

user_17253597 0
Viewed 287

i need to implement the following query as a jpa specification or criteria query?

Please let me know how this can be done using inner joins and also a group by clause.

select SUM(r.story_points) as SP,count(r.user_id) as mx,r.user_id,e.component_id from task r inner join component e on r.component_id =e.component_id group by r.user_id ,e.component_id;

My Task Entity:

package com.sysco.sprintreporting.entity;

import com.fasterxml.jackson.annotation.JsonBackReference;

import lombok.*;

import javax.persistence.*;
import java.time.LocalDate;

@Entity
@Table(name = "Task")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString

public class Task {

    @Id
    @Column(name = "issue_key")
    private String issueKey;

    @Column(name = "issue_id")
    private long issueId;

    @Column(name = "issue_type")
    private String issueType;

    @Column(name = "storyPoints")
    private Double storyPoints;

    @ManyToOne(cascade = CascadeType.PERSIST)
    @JoinColumn(name = "user_id")
    @JsonBackReference
    private User assignee_id;

    @ManyToOne(cascade = CascadeType.PERSIST)
    @JoinColumn(name = "component_id")
    @JsonBackReference
    private Component component_id;

}

My Component Entity:

package com.sysco.sprintreporting.entity;

import java.util.Set;

import javax.persistence.*;


import lombok.*;

@Entity
@Table(name = "Component")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class Component {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "component_id")
    private int id;

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

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


    @OneToMany(mappedBy = "component_id", cascade = CascadeType.ALL)
    private Set<Task> Task;

}
criteria-api jpa hibernate spring java
1 Answers

To do an aggregated query with joins with Criteria Query API you can use Tuple Query. Tuple Query allows to select multiple values/columns with multiselect method.

Something like this (not tested):

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> mainQuery = cb.createTupleQuery();

Root<Task> rootTask = mainQuery.from(Task.class);
Join<Task, Component> componentJoin = rootTask.join("component_id");

mainQuery.multiselect(
    rootTask.get("user_id"),
    componentJoin.get("component_id"),
    cb.sum(rootTask.get("story_points").alias("SP"),
    cb.count(rootTask.get("user_id")).alias("mx")
);

mainQuery.groupBy(
    rootTask.get("user_id"), 
    componentJoin.get("component_id")
);

TypedQuery<Tuple> query = entityManager.createQuery(mainQuery);

List<Tuple> tasks = query.getResultList();

You can see some examples of using Tuple Query: JPA Criteria API - Multiple Query selection and use of Tuple

Regards.

user_60877570
Related