TP6.3 - Airport-Flight-Passenger Exercise

Objectives

This evaluation lab will allow you to:

• Model complex relationships between JPA entities
• Create queries with Spring Data JPA naming conventions
• Use @Query with JPQL for advanced queries
• Test your skills in Spring Data JPA

Estimated duration: 2h30 - 3h

Level: Intermediate to Advanced

---

Context

You need to create an airport management application with:

• Airports
• Flights
• Passengers

---

Part 1: Entity Modeling

1.1 Relationship Diagram

1.2 Airport Entity

Create 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 Flight Entity

Create 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 Passenger Entity

Create 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;
}

---

Part 2: Test Data

2.1 data.sql File

Create 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', 'London', 'UK');
INSERT INTO airports (code, name, city, country) VALUES ('FRA', 'Frankfurt', 'Frankfurt', '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);

---

Part 3: The 20 Queries to Implement

EASY Level (Questions 1-7)

Create the methods in the appropriate repositories.

---

Question 1: Find an airport by its code

Repository: AirportRepository

Signature:

Optional<Airport> findByCode(String code);

Test: Find the airport "YUL"

---

Question 2: List all airports in a country

Repository: AirportRepository

Signature:

List<Airport> findByCountry(String country);

Test: Find all airports in Canada

---

Question 3: Find a passenger by email

Repository: PassengerRepository

Signature:

Optional<Passenger> findByEmail(String email);

Test: Find the passenger with the email "jean.dupont@email.com"

---

Question 4: List passengers by last name

Repository: PassengerRepository

Signature:

List<Passenger> findByLastName(String lastName);

Test: Find all passengers named "Smith"

---

Question 5: Find a flight by its number

Repository: FlightRepository

Signature:

Optional<Flight> findByFlightNumber(String flightNumber);

Test: Find the flight "AC301"

---

Question 6: List flights with a price less than a value

Repository: FlightRepository

Signature:

List<Flight> findByPriceLessThan(Double maxPrice);

Test: Find flights under $300

---

Question 7: List passengers born before a date

Repository: PassengerRepository

Signature:

List<Passenger> findByBirthDateBefore(LocalDate date);

Test: Find passengers born before 1990

---

MEDIUM Level (Questions 8-14)

---

Question 8: List flights between two prices

Repository: FlightRepository

Signature:

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

Test: Flights between $200 and $500

---

Question 9: List flights departing from a specific airport

Repository: FlightRepository

Signature:

List<Flight> findByDepartureAirport_Code(String airportCode);

Test: Flights departing from "YUL"

---

Question 10: List flights arriving in a country

Repository: FlightRepository

Signature:

List<Flight> findByArrivalAirport_Country(String country);

Test: Flights arriving in France

---

Question 11: Search passengers by name (contains)

Repository: PassengerRepository

Signature:

List<Passenger> findByLastNameContainingIgnoreCase(String keyword);

Test: Passengers whose name contains "art"

---

Question 12: List flights after a date/time

Repository: FlightRepository

Signature:

List<Flight> findByDepartureTimeAfter(LocalDateTime dateTime);

Test: Flights departing after June 16, 2025

---

Question 13: Airports in a specific city sorted by name

Repository: AirportRepository

Signature:

List<Airport> findByCityOrderByNameAsc(String city);

Test: Airports in New York sorted by name

---

Question 14: Flights with capacity greater than a value

Repository: FlightRepository

Signature:

List<Flight> findByCapacityGreaterThanEqual(Integer minCapacity);

Test: Flights with at least 250 seats

---

HARD Level (Questions 15-20)

These queries require @Query with JPQL.

---

Question 15: Find a passenger's flights by their 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: Flights of the passenger "jean.dupont@email.com"

---

Question 16: Count the number of passengers on a flight

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: Number of passengers on flight "AC301"

---

Question 17: Find passengers on a specific flight

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: Passengers on flight "AF001"

---

Question 18: Flights between two airports (by 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: Flights from "YUL" to "CDG"

---

Question 19: Average price of flights departing from a country

Repository: FlightRepository

Signature:

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

Test: Average price of flights departing from Canada

---

Question 20: Passengers who have taken more than N flights

Repository: PassengerRepository

Signature:

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

Test: Passengers who have taken at least 2 flights

---

Part 4: Complete Solution

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: Find an airport by its code
    Optional<Airport> findByCode(String code);

    // Q2: List all airports in a country
    List<Airport> findByCountry(String country);

    // Q13: Airports in a city sorted by name
    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: Find a passenger by email
    Optional<Passenger> findByEmail(String email);

    // Q4: List passengers by last name
    List<Passenger> findByLastName(String lastName);

    // Q7: Passengers born before a date
    List<Passenger> findByBirthDateBefore(LocalDate date);

    // Q11: Search by name (contains)
    List<Passenger> findByLastNameContainingIgnoreCase(String keyword);

    // Q17: Passengers on a specific flight
    @Query("SELECT p FROM Passenger p JOIN p.flights f WHERE f.flightNumber = :flightNumber")
    List<Passenger> findPassengersByFlightNumber(@Param("flightNumber") String flightNumber);

    // Q20: Frequent travelers (having taken N+ flights)
    @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: Find a flight by its number
    Optional<Flight> findByFlightNumber(String flightNumber);

    // Q6: Flights with a price less than a value
    List<Flight> findByPriceLessThan(Double maxPrice);

    // Q8: Flights between two prices
    List<Flight> findByPriceBetween(Double minPrice, Double maxPrice);

    // Q9: Flights departing from an airport (by code)
    List<Flight> findByDepartureAirport_Code(String airportCode);

    // Q10: Flights arriving in a country
    List<Flight> findByArrivalAirport_Country(String country);

    // Q12: Flights after a date/time
    List<Flight> findByDepartureTimeAfter(LocalDateTime dateTime);

    // Q14: Flights with minimum capacity
    List<Flight> findByCapacityGreaterThanEqual(Integer minCapacity);

    // Q15: Flights of a passenger by email
    @Query("SELECT f FROM Flight f JOIN f.passengers p WHERE p.email = :email")
    List<Flight> findFlightsByPassengerEmail(@Param("email") String email);

    // Q16: Count passengers on a flight
    @Query("SELECT COUNT(p) FROM Flight f JOIN f.passengers p WHERE f.flightNumber = :flightNumber")
    Long countPassengersByFlightNumber(@Param("flightNumber") String flightNumber);

    // Q18: Flights between two airports
    @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: Average price of flights by departure country
    @Query("SELECT AVG(f.price) FROM Flight f WHERE f.departureAirport.country = :country")
    Double findAveragePriceByDepartureCountry(@Param("country") String country);
}

---

Part 5: Tests and 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

Test Class

Create a test class to validate your queries:

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("Flights of 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("Frequent travelers: " + travelers.size());
    }
}

---

Evaluation Criteria

Criterion	Points
Correctly modeled entities	15%
Correct JPA relationships (@ManyToOne, @ManyToMany)	15%
Questions 1-7 (Easy)	21% (3% each)
Questions 8-14 (Medium)	28% (4% each)
Questions 15-20 (Hard)	18% (3% each)
Tests pass	3%
Total	100%

---

Tips

For naming conventions:

• findBy + PropertyName = simple search
• _ allows navigating relationships (e.g., findByDepartureAirport_Code)
• Containing = LIKE %keyword%
• IgnoreCase = case-insensitive search

For @Query:

• JPQL uses Java class names, not SQL table names
• JOIN to navigate relationships
• SIZE() to count elements in a collection
• AVG(), COUNT(), SUM() for aggregations

---

Resources

• Spring Data JPA Reference
• JPQL Reference

---

Back to Module 6