Home > Software engineering >  Query dsl with group by and Projection
Query dsl with group by and Projection

Time:12-07

I have a project written in Spring where I've created Technology entity:

package com.example.technologyradar.model;
import com.example.technologyradar.dto.constant.TechnologyStatus;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.GenericGenerator;

import javax.persistence.*;
import java.util.List;

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Technology {
@Id
@GeneratedValue(strategy = GenerationType.AUTO, generator = "native")
@GenericGenerator(name="native", strategy = "native")
private Long id;

private String name;

@Enumerated(EnumType.STRING)
private TechnologyStatus technologyStatus;

@OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.PERSIST, targetEntity = Category.class)
@JoinColumn(name="category_id", referencedColumnName = "id", nullable = false)
private Category category;

@OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.PERSIST, targetEntity = Coordinate.class)
@JoinColumn(name="coordinate_id", referencedColumnName = "id", nullable = false)
private Coordinate coordinate;

@ManyToMany
@JoinTable(
        name = "projects_technologies",
        joinColumns = @JoinColumn(name="technology_id"),
        inverseJoinColumns = @JoinColumn(name="project_id")
)
private List<Project> projects;

}

I would like to obtain technologies grouped by technologyStatus. That's why Iv'e created 2 DTO objects:

TechnologyBasicDataDTO:

package com.example.technologyradar.dto;

import com.example.technologyradar.dto.constant.TechnologyStatus;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class TechnologyByStatusDTO {
    TechnologyStatus status;
    List<TechnologyBasicDataDTO> technologies;
}

TechnologyByStatusDTO:

package com.example.technologyradar.dto;

import com.example.technologyradar.dto.constant.TechnologyStatus;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class TechnologyByStatusDTO {
    TechnologyStatus status;
    List<TechnologyBasicDataDTO> technologies;
}

Afterwards I started to write query with using QueryDSL library. I've written something like:

 @Override
    public List<TechnologyByStatusDTO> getTechnologyByStatus() {
        return jpaQueryFactory.from(technology).innerJoin(technology.coordinate, coordinate)
                .groupBy(technology.technologyStatus)
                .select(Projections.constructor(TechnologyByStatusDTO.class,
                        technology.technologyStatus, list(TechnologyBasicDataDTO.class))
                .fetch();
    }

technology and coordinate have definition in extended class BaseRepositoryImpl:

    QTechnology technology = QTechnology.technology;
    QCoordinate coordinate = QCoordinate.coordinate;

The crucial issue is in moment with: list(TechnologyBasicDataDTO.class) This code has a problem with compilation. I don't now how to map in this place data to List<TechnologyBasicDataDTO> technologies;

Has anyone idea how to achieve that? I would be grateful for help Thanks!

CodePudding user response:

Querydsl factory expressions don't accept group expressions. You have to use either of them. I.e.

Map<TechnologyStatus, List<Technology>> result = 

queryFactory.transform(GroupBy.map(technology.technologyStatus).list(technology))

For more advanced nested DTO projections with JPA, you can look into Blaze-Persistence Entity Views.

  • Related