Aller au contenu principal

TP6.3 - Exercice Airport-Flight-Passenger

Objectifs​

Ce TP d'évaluation vous permettra de:

  • Modéliser des relations complexes entre entités JPA
  • Créer des requêtes avec les conventions de nommage Spring Data JPA
  • Utiliser @Query avec JPQL pour des requêtes avancées
  • Tester vos compétences en Spring Data JPA

Durée estimée: 2h30 - 3h

Niveau: Intermédiaire à Avancé

Contexte​

Vous devez créer une application de gestion d'aéroport avec:

  • Airports (Aéroports)
  • Flights (Vols)
  • Passengers (Passagers)

Partie 1: Modélisation des Entités​

1.1 Diagramme de Relations​

1.2 Entité Airport​

Créez src/main/java/com/example/airport/model/Airport.java:

package com.example.airport.model;

import jakarta.persistence.*;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;

import java.util.List;

@Entity
@Table(name = "airports")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Airport {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(unique = true, nullable = false, length = 3)
    private String code;  // Ex: "YUL", "JFK", "CDG"

    @Column(nullable = false)
    private String name;  // Ex: "Montréal-Trudeau"

    @Column(nullable = false)
    private String city;  // Ex: "Montréal"

    @Column(nullable = false)
    private String country;  // Ex: "Canada"

    @OneToMany(mappedBy = "departureAirport")
    private List<Flight> departingFlights;

    @OneToMany(mappedBy = "arrivalAirport")
    private List<Flight> arrivingFlights;
}

1.3 Entité Flight​

Créez src/main/java/com/example/airport/model/Flight.java:

package com.example.airport.model;

import jakarta.persistence.*;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;

import java.time.LocalDateTime;
import java.util.List;

@Entity
@Table(name = "flights")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Flight {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(unique = true, nullable = false)
    private String flightNumber;  // Ex: "AC301"

    @ManyToOne
    @JoinColumn(name = "departure_airport_id", nullable = false)
    private Airport departureAirport;

    @ManyToOne
    @JoinColumn(name = "arrival_airport_id", nullable = false)
    private Airport arrivalAirport;

    @Column(nullable = false)
    private LocalDateTime departureTime;

    @Column(nullable = false)
    private LocalDateTime arrivalTime;

    @Column(nullable = false)
    private Double price;

    @Column(nullable = false)
    private Integer capacity;

    @ManyToMany
    @JoinTable(
        name = "flight_passengers",
        joinColumns = @JoinColumn(name = "flight_id"),
        inverseJoinColumns = @JoinColumn(name = "passenger_id")
    )
    private List<Passenger> passengers;
}

1.4 Entité Passenger​

Créez src/main/java/com/example/airport/model/Passenger.java:

package com.example.airport.model;

import jakarta.persistence.*;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;

import java.time.LocalDate;
import java.util.List;

@Entity
@Table(name = "passengers")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Passenger {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String firstName;

    @Column(nullable = false)
    private String lastName;

    @Column(unique = true, nullable = false)
    private String email;

    @Column(unique = true, nullable = false)
    private String passportNumber;

    @Column(nullable = false)
    private LocalDate birthDate;

    @ManyToMany(mappedBy = "passengers")
    private List<Flight> flights;
}

Partie 2: Données de Test​

2.1 Fichier data.sql​

Créez src/main/resources/data.sql:

-- Airports
INSERT INTO airports (code, name, city, country) VALUES ('YUL', 'Montréal-Trudeau International', 'Montréal', 'Canada');
INSERT INTO airports (code, name, city, country) VALUES ('YYZ', 'Toronto Pearson International', 'Toronto', 'Canada');
INSERT INTO airports (code, name, city, country) VALUES ('JFK', 'John F. Kennedy International', 'New York', 'USA');
INSERT INTO airports (code, name, city, country) VALUES ('LAX', 'Los Angeles International', 'Los Angeles', 'USA');
INSERT INTO airports (code, name, city, country) VALUES ('CDG', 'Charles de Gaulle', 'Paris', 'France');
INSERT INTO airports (code, name, city, country) VALUES ('LHR', 'Heathrow', 'Londres', 'UK');
INSERT INTO airports (code, name, city, country) VALUES ('FRA', 'Frankfurt', 'Francfort', 'Germany');

-- Flights
INSERT INTO flights (flight_number, departure_airport_id, arrival_airport_id, departure_time, arrival_time, price, capacity) 
VALUES ('AC301', 1, 2, '2025-06-15 08:00:00', '2025-06-15 09:30:00', 250.00, 180);

INSERT INTO flights (flight_number, departure_airport_id, arrival_airport_id, departure_time, arrival_time, price, capacity) 
VALUES ('AC401', 1, 3, '2025-06-15 10:00:00', '2025-06-15 11:45:00', 450.00, 200);

INSERT INTO flights (flight_number, departure_airport_id, arrival_airport_id, departure_time, arrival_time, price, capacity) 
VALUES ('AF001', 1, 5, '2025-06-15 18:00:00', '2025-06-16 06:30:00', 850.00, 300);

INSERT INTO flights (flight_number, departure_airport_id, arrival_airport_id, departure_time, arrival_time, price, capacity) 
VALUES ('BA123', 6, 3, '2025-06-16 09:00:00', '2025-06-16 12:00:00', 550.00, 250);

INSERT INTO flights (flight_number, departure_airport_id, arrival_airport_id, departure_time, arrival_time, price, capacity) 
VALUES ('LH456', 7, 5, '2025-06-16 14:00:00', '2025-06-16 15:30:00', 200.00, 150);

INSERT INTO flights (flight_number, departure_airport_id, arrival_airport_id, departure_time, arrival_time, price, capacity) 
VALUES ('AA789', 3, 4, '2025-06-17 07:00:00', '2025-06-17 10:30:00', 350.00, 180);

INSERT INTO flights (flight_number, departure_airport_id, arrival_airport_id, departure_time, arrival_time, price, capacity) 
VALUES ('AC501', 2, 1, '2025-06-17 16:00:00', '2025-06-17 17:30:00', 250.00, 180);

INSERT INTO flights (flight_number, departure_airport_id, arrival_airport_id, departure_time, arrival_time, price, capacity) 
VALUES ('AF002', 5, 1, '2025-06-18 22:00:00', '2025-06-19 06:00:00', 900.00, 300);

-- Passengers
INSERT INTO passengers (first_name, last_name, email, passport_number, birth_date) 
VALUES ('Jean', 'Dupont', 'jean.dupont@email.com', 'FR123456', '1985-03-15');

INSERT INTO passengers (first_name, last_name, email, passport_number, birth_date) 
VALUES ('Marie', 'Martin', 'marie.martin@email.com', 'FR789012', '1990-07-22');

INSERT INTO passengers (first_name, last_name, email, passport_number, birth_date) 
VALUES ('John', 'Smith', 'john.smith@email.com', 'US345678', '1978-11-08');

INSERT INTO passengers (first_name, last_name, email, passport_number, birth_date) 
VALUES ('Emma', 'Wilson', 'emma.wilson@email.com', 'UK901234', '1995-01-30');

INSERT INTO passengers (first_name, last_name, email, passport_number, birth_date) 
VALUES ('Pierre', 'Bernard', 'pierre.bernard@email.com', 'CA567890', '1982-09-12');

INSERT INTO passengers (first_name, last_name, email, passport_number, birth_date) 
VALUES ('Sophie', 'Tremblay', 'sophie.tremblay@email.com', 'CA123789', '2000-05-25');

INSERT INTO passengers (first_name, last_name, email, passport_number, birth_date) 
VALUES ('Michael', 'Brown', 'michael.brown@email.com', 'US456123', '1970-12-03');

INSERT INTO passengers (first_name, last_name, email, passport_number, birth_date) 
VALUES ('Anna', 'Schmidt', 'anna.schmidt@email.com', 'DE789456', '1988-04-18');

-- Flight-Passenger associations
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (1, 1);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (1, 2);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (1, 5);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (2, 1);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (2, 3);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (3, 1);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (3, 2);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (3, 4);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (3, 8);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (4, 3);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (4, 4);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (5, 8);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (6, 3);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (6, 7);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (7, 5);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (7, 6);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (8, 1);
INSERT INTO flight_passengers (flight_id, passenger_id) VALUES (8, 2);

Partie 3: Les 20 Requêtes à Implémenter​

Niveau FACILE (Questions 1-7)​

Créez les méthodes dans les repositories appropriés.

Question 1: Trouver un aéroport par son code​

Repository: AirportRepository

Signature:

Optional<Airport> findByCode(String code);

Test: Trouver l'aéroport "YUL"

Question 2: Lister tous les aéroports d'un pays​

Repository: AirportRepository

Signature:

List<Airport> findByCountry(String country);

Test: Trouver tous les aéroports du Canada

Question 3: Trouver un passager par email​

Repository: PassengerRepository

Signature:

Optional<Passenger> findByEmail(String email);

Test: Trouver le passager avec l'email "jean.dupont@email.com"

Question 4: Lister les passagers par nom de famille​

Repository: PassengerRepository

Signature:

List<Passenger> findByLastName(String lastName);

Test: Trouver tous les passagers nommés "Smith"

Question 5: Trouver un vol par son numéro​

Repository: FlightRepository

Signature:

Optional<Flight> findByFlightNumber(String flightNumber);

Test: Trouver le vol "AC301"

Question 6: Lister les vols dont le prix est inférieur à une valeur​

Repository: FlightRepository

Signature:

List<Flight> findByPriceLessThan(Double maxPrice);

Test: Trouver les vols à moins de 300$

Question 7: Lister les passagers nés avant une date​

Repository: PassengerRepository

Signature:

List<Passenger> findByBirthDateBefore(LocalDate date);

Test: Trouver les passagers nés avant 1990

Niveau MOYEN (Questions 8-14)​

Question 8: Lister les vols entre deux prix​

Repository: FlightRepository

Signature:

List<Flight> findByPriceBetween(Double minPrice, Double maxPrice);

Test: Vols entre 200$ et 500$

Question 9: Lister les vols partant d'un aéroport spécifique​

Repository: FlightRepository

Signature:

List<Flight> findByDepartureAirport_Code(String airportCode);

Test: Vols partant de "YUL"

Question 10: Lister les vols arrivant dans un pays​

Repository: FlightRepository

Signature:

List<Flight> findByArrivalAirport_Country(String country);

Test: Vols arrivant en France

Question 11: Rechercher des passagers par nom (contient)​

Repository: PassengerRepository

Signature:

List<Passenger> findByLastNameContainingIgnoreCase(String keyword);

Test: Passagers dont le nom contient "art"

Question 12: Lister les vols après une date/heure​

Repository: FlightRepository

Signature:

List<Flight> findByDepartureTimeAfter(LocalDateTime dateTime);

Test: Vols partant après le 16 juin 2025

Question 13: Aéroports d'une ville spécifique avec tri par nom​

Repository: AirportRepository

Signature:

List<Airport> findByCityOrderByNameAsc(String city);

Test: Aéroports de New York triés par nom

Question 14: Vols avec capacité supérieure à une valeur​

Repository: FlightRepository

Signature:

List<Flight> findByCapacityGreaterThanEqual(Integer minCapacity);

Test: Vols avec au moins 250 places

Niveau DIFFICILE (Questions 15-20)​

Ces requêtes nécessitent @Query avec JPQL.

Question 15: Trouver les vols d'un passager par son email​

Repository: FlightRepository

Signature:

@Query("SELECT f FROM Flight f JOIN f.passengers p WHERE p.email = :email")
List<Flight> findFlightsByPassengerEmail(@Param("email") String email);

Test: Vols du passager "jean.dupont@email.com"

Question 16: Compter le nombre de passagers sur un vol​

Repository: FlightRepository

Signature:

@Query("SELECT COUNT(p) FROM Flight f JOIN f.passengers p WHERE f.flightNumber = :flightNumber")
Long countPassengersByFlightNumber(@Param("flightNumber") String flightNumber);

Test: Nombre de passagers sur le vol "AC301"

Question 17: Trouver les passagers d'un vol spécifique​

Repository: PassengerRepository

Signature:

@Query("SELECT p FROM Passenger p JOIN p.flights f WHERE f.flightNumber = :flightNumber")
List<Passenger> findPassengersByFlightNumber(@Param("flightNumber") String flightNumber);

Test: Passagers du vol "AF001"

Question 18: Vols entre deux aéroports (par code)​

Repository: FlightRepository

Signature:

@Query("SELECT f FROM Flight f WHERE f.departureAirport.code = :departureCode AND f.arrivalAirport.code = :arrivalCode")
List<Flight> findFlightsBetweenAirports(@Param("departureCode") String departureCode, 
                                        @Param("arrivalCode") String arrivalCode);

Test: Vols de "YUL" vers "CDG"

Question 19: Prix moyen des vols partant d'un pays​

Repository: FlightRepository

Signature:

@Query("SELECT AVG(f.price) FROM Flight f WHERE f.departureAirport.country = :country")
Double findAveragePriceByDepartureCountry(@Param("country") String country);

Test: Prix moyen des vols partant du Canada

Question 20: Passagers ayant pris plus de N vols​

Repository: PassengerRepository

Signature:

@Query("SELECT p FROM Passenger p WHERE SIZE(p.flights) >= :minFlights")
List<Passenger> findFrequentTravelers(@Param("minFlights") int minFlights);

Test: Passagers ayant pris au moins 2 vols

Partie 4: Solutionnaire Complet​

AirportRepository.java​

package com.example.airport.repository;

import com.example.airport.model.Airport;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface AirportRepository extends JpaRepository<Airport, Long> {

    // Q1: Trouver un aéroport par son code
    Optional<Airport> findByCode(String code);

    // Q2: Lister tous les aéroports d'un pays
    List<Airport> findByCountry(String country);

    // Q13: Aéroports d'une ville avec tri
    List<Airport> findByCityOrderByNameAsc(String city);
}

PassengerRepository.java​

package com.example.airport.repository;

import com.example.airport.model.Passenger;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

@Repository
public interface PassengerRepository extends JpaRepository<Passenger, Long> {

    // Q3: Trouver un passager par email
    Optional<Passenger> findByEmail(String email);

    // Q4: Lister les passagers par nom de famille
    List<Passenger> findByLastName(String lastName);

    // Q7: Passagers nés avant une date
    List<Passenger> findByBirthDateBefore(LocalDate date);

    // Q11: Recherche par nom (contient)
    List<Passenger> findByLastNameContainingIgnoreCase(String keyword);

    // Q17: Passagers d'un vol spécifique
    @Query("SELECT p FROM Passenger p JOIN p.flights f WHERE f.flightNumber = :flightNumber")
    List<Passenger> findPassengersByFlightNumber(@Param("flightNumber") String flightNumber);

    // Q20: Passagers fréquents (ayant pris N+ vols)
    @Query("SELECT p FROM Passenger p WHERE SIZE(p.flights) >= :minFlights")
    List<Passenger> findFrequentTravelers(@Param("minFlights") int minFlights);
}

FlightRepository.java​

package com.example.airport.repository;

import com.example.airport.model.Flight;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface FlightRepository extends JpaRepository<Flight, Long> {

    // Q5: Trouver un vol par son numéro
    Optional<Flight> findByFlightNumber(String flightNumber);

    // Q6: Vols dont le prix est inférieur à une valeur
    List<Flight> findByPriceLessThan(Double maxPrice);

    // Q8: Vols entre deux prix
    List<Flight> findByPriceBetween(Double minPrice, Double maxPrice);

    // Q9: Vols partant d'un aéroport (par code)
    List<Flight> findByDepartureAirport_Code(String airportCode);

    // Q10: Vols arrivant dans un pays
    List<Flight> findByArrivalAirport_Country(String country);

    // Q12: Vols après une date/heure
    List<Flight> findByDepartureTimeAfter(LocalDateTime dateTime);

    // Q14: Vols avec capacité minimale
    List<Flight> findByCapacityGreaterThanEqual(Integer minCapacity);

    // Q15: Vols d'un passager par email
    @Query("SELECT f FROM Flight f JOIN f.passengers p WHERE p.email = :email")
    List<Flight> findFlightsByPassengerEmail(@Param("email") String email);

    // Q16: Compter les passagers d'un vol
    @Query("SELECT COUNT(p) FROM Flight f JOIN f.passengers p WHERE f.flightNumber = :flightNumber")
    Long countPassengersByFlightNumber(@Param("flightNumber") String flightNumber);

    // Q18: Vols entre deux aéroports
    @Query("SELECT f FROM Flight f WHERE f.departureAirport.code = :departureCode AND f.arrivalAirport.code = :arrivalCode")
    List<Flight> findFlightsBetweenAirports(@Param("departureCode") String departureCode, 
                                            @Param("arrivalCode") String arrivalCode);

    // Q19: Prix moyen des vols par pays de départ
    @Query("SELECT AVG(f.price) FROM Flight f WHERE f.departureAirport.country = :country")
    Double findAveragePriceByDepartureCountry(@Param("country") String country);
}

Partie 5: Tests et Validation​

application.properties​

spring.datasource.url=jdbc:h2:mem:airportdb
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.jpa.defer-datasource-initialization=true

spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

spring.sql.init.mode=always

Classe de Test​

Créez une classe de test pour valider vos requêtes:

package com.example.airport;

import com.example.airport.model.*;
import com.example.airport.repository.*;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
class AirportApplicationTests {

    @Autowired
    private AirportRepository airportRepository;

    @Autowired
    private FlightRepository flightRepository;

    @Autowired
    private PassengerRepository passengerRepository;

    @Test
    void q1_findAirportByCode() {
        var airport = airportRepository.findByCode("YUL");
        assertTrue(airport.isPresent());
        assertEquals("Montréal", airport.get().getCity());
    }

    @Test
    void q2_findAirportsByCountry() {
        var airports = airportRepository.findByCountry("Canada");
        assertEquals(2, airports.size());
    }

    @Test
    void q6_findFlightsByPriceLessThan() {
        var flights = flightRepository.findByPriceLessThan(300.0);
        assertFalse(flights.isEmpty());
        flights.forEach(f -> assertTrue(f.getPrice() < 300));
    }

    @Test
    void q15_findFlightsByPassengerEmail() {
        var flights = flightRepository.findFlightsByPassengerEmail("jean.dupont@email.com");
        assertFalse(flights.isEmpty());
        System.out.println("Vols de Jean Dupont: " + flights.size());
    }

    @Test
    void q16_countPassengersByFlight() {
        Long count = flightRepository.countPassengersByFlightNumber("AC301");
        assertEquals(3, count);
    }

    @Test
    void q20_findFrequentTravelers() {
        var travelers = passengerRepository.findFrequentTravelers(2);
        assertFalse(travelers.isEmpty());
        System.out.println("Voyageurs fréquents: " + travelers.size());
    }
}

Critères d'Évaluation​

CritèrePoints
Entités correctement modélisées15%
Relations JPA correctes (@ManyToOne, @ManyToMany)15%
Questions 1-7 (Facile)21% (3% chacune)
Questions 8-14 (Moyen)28% (4% chacune)
Questions 15-20 (Difficile)18% (3% chacune)
Tests passent3%
Total100%

Conseils​

Pour les conventions de nommage:​

  • findBy + PropertyName = recherche simple
  • _ permet de naviguer dans les relations (ex: findByDepartureAirport_Code)
  • Containing = LIKE %keyword%
  • IgnoreCase = recherche insensible à la casse

Pour @Query:​

  • JPQL utilise les noms des classes Java, pas les noms de tables SQL
  • JOIN pour naviguer dans les relations
  • SIZE() pour compter les éléments d'une collection
  • AVG(), COUNT(), SUM() pour les agrégations

Ressources​

Retour au Module 6