Contexte

Effetcuer des requêtes combinées sur trois tibles liées entre elles

Procédure

1. Créer une classe intermédiaire pour recevoir les résultats
2. Rédiger la requête HQL dans l'annotation @Query
3. Exécuter la requête depuis la base de données

Implémentation

Classe intermédiaire pour les résultats

ProjectSummaryDto.java

/**
 * ProjectSummaryDto
 * @description DTO contenant les informations détaillées des projets
 */
public class ProjectSummaryDto implements Serializable {
    private String departmentName;
    private String teamName;
    private Long projectId;
    private Long departmentId;
    private Long teamId;
    private String projectName;
    private String projectCode;
    private String projectType;
    private String description;
    private String branchConfiguration;
    private LocalDateTime creationDate;

    public ProjectSummaryDto(ProjectEntity project, String deptName, String teamName) {
        this.projectId = project.getProjectId();
        this.teamId = project.getTeamId();
        this.departmentId = project.getDepartmentId();
        this.projectType = project.getProjectType();
        this.projectCode = project.getProjectCode();
        this.projectName = project.getProjectName();
        this.branchConfiguration = project.getBranchConfig();
        this.description = project.getDescription();
        this.creationDate = project.getCreationDate();

        this.departmentName = deptName;
        this.teamName = teamName;
    }
    
    // Méthodes setter...
    // Méthodes getter...
}

ProjectEntity.java

@Entity
@Table(name = "project")
@EntityListeners(AuditingEntityListener.class)
@DynamicInsert
@DynamicUpdate
public class ProjectEntity {
    private Long projectId;
    private Long departmentId;
    private Long teamId;
    private String projectName;
    private String projectCode;
    private String creatorName;
    private String updaterName;
    private String projectType;
    private String description;
    private LocalDateTime creationDate;
    private LocalDateTime updateDate;
    private String branchConfig;
    private Integer status;
    private Boolean isActive;
    private Boolean allowDeployment;
}

Requête HQL avec @Query

ProjectRepository.java

/**
 * ProjectRepository
 * @description Opérations d'accès aux données projet
 */
@Repository
public interface ProjectRepository extends JpaRepository<projectentity long=""> {
    // Requête paginée
    @Query(value = "SELECT new com.example.dto.ProjectSummaryDto(project, dept.name, team.name) FROM ProjectEntity project, DepartmentEntity dept, TeamEntity team WHERE project.departmentId = dept.id AND project.teamId = team.id")
    Page<projectsummarydto> fetchProjectDetails(Pageable pageable);
    
    // Requête par identifiant
    @Query(value = "SELECT new com.example.dto.ProjectSummaryDto(project, dept.name, team.name) FROM ProjectEntity project, DepartmentEntity dept, TeamEntity team WHERE project.departmentId = dept.id AND project.teamId = team.id AND project.projectId = :projectId")
    ProjectSummaryDto fetchProjectDetailsById(@Param("projectId") Long projectId);
}
</projectsummarydto></projectentity>

Exécution des requêtes

Après injection via @Autwoired, utiliser les méthodes directement

Appel en mode pagination dans le service :

public Page<projectsummarydto> retrieveProjectCollection(Integer pageNumber, Integer pageSize) {
    Pageable pagination = PageRequest.of(pageNumber, pageSize, Sort.Direction.DESC, "creationDate");
    return projectRepository.fetchProjectDetails(pagination);
}
</projectsummarydto>