TP6.3 - Exercício Airport-Flight-Passenger

Objetivos

Este exercício de avaliação permitirá que você:

• Modele relações complexas entre entidades JPA
• Crie consultas utilizando as convenções de nomenclatura do Spring Data JPA
• Utilize @Query com JPQL para consultas avançadas
• Teste suas habilidades em Spring Data JPA

Duração estimada: 2h30 - 3h

Nível: Intermediário a Avançado

---

Contexto

Você deve criar uma aplicação de gestão de aeroporto com:

• Airports (Aeroportos)
• Flights (Voos)
• Passengers (Passageiros)

---

Parte 1: Modelagem das Entidades

1.1 Diagrama de Relações

1.2 Entidade Airport

Crie 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 Entidade Flight

Crie 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 Entidade Passenger

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

---

Parte 2: Dados de Teste

2.1 Arquivo data.sql

Crie 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);

---

Parte 3: As 20 Consultas a Implementar

Nível FÁCIL (Questões 1-7)

Crie os métodos nos repositórios apropriados.

---

Questão 1: Encontrar um aeroporto pelo seu código

Repository: AirportRepository

Signature:

Optional<Airport> findByCode(String code);

Teste: Encontrar o aeroporto "YUL"

---

Questão 2: Listar todos os aeroportos de um país

Repository: AirportRepository

Signature:

List<Airport> findByCountry(String country);

Teste: Encontrar todos os aeroportos do Canadá

---

Questão 3: Encontrar um passageiro pelo email

Repository: PassengerRepository

Signature:

Optional<Passenger> findByEmail(String email);

Teste: Encontrar o passageiro com o email "jean.dupont@email.com"

---

Questão 4: Listar passageiros por sobrenome

Repository: PassengerRepository

Signature:

List<Passenger> findByLastName(String lastName);

Teste: Encontrar todos os passageiros chamados "Smith"

---

Questão 5: Encontrar um voo pelo seu número

Repository: FlightRepository

Signature:

Optional<Flight> findByFlightNumber(String flightNumber);

Teste: Encontrar o voo "AC301"

---

Questão 6: Listar voos cujo preço é inferior a um valor

Repository: FlightRepository

Signature:

List<Flight> findByPriceLessThan(Double maxPrice);

Teste: Encontrar voos por menos de 300$

---

Questão 7: Listar passageiros nascidos antes de uma data

Repository: PassengerRepository

Signature:

List<Passenger> findByBirthDateBefore(LocalDate date);

Teste: Encontrar passageiros nascidos antes de 1990

---

Nível MÉDIO (Questões 8-14)

---

Questão 8: Listar voos entre dois preços

Repository: FlightRepository

Signature:

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

Teste: Voos entre 200$ e 500$

---

Questão 9: Listar voos partindo de um aeroporto específico

Repository: FlightRepository

Signature:

List<Flight> findByDepartureAirport_Code(String airportCode);

Teste: Voos partindo de "YUL"

---

Questão 10: Listar voos chegando em um país

Repository: FlightRepository

Signature:

List<Flight> findByArrivalAirport_Country(String country);

Teste: Voos chegando na França

---

Questão 11: Buscar passageiros por sobrenome (contém)

Repository: PassengerRepository

Signature:

List<Passenger> findByLastNameContainingIgnoreCase(String keyword);

Teste: Passageiros cujo sobrenome contém "art"

---

Questão 12: Listar voos após uma data/hora

Repository: FlightRepository

Signature:

List<Flight> findByDepartureTimeAfter(LocalDateTime dateTime);

Teste: Voos partindo após 16 de junho de 2025

---

Questão 13: Aeroportos de uma cidade específica ordenados por nome

Repository: AirportRepository

Signature:

List<Airport> findByCityOrderByNameAsc(String city);

Teste: Aeroportos de Nova York ordenados por nome

---

Questão 14: Voos com capacidade superior a um valor

Repository: FlightRepository

Signature:

List<Flight> findByCapacityGreaterThanEqual(Integer minCapacity);

Teste: Voos com pelo menos 250 lugares

---

Nível DIFÍCIL (Questões 15-20)

Essas consultas exigem @Query com JPQL.

---

Questão 15: Encontrar os voos de um passageiro pelo 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);

Teste: Voos do passageiro "jean.dupont@email.com"

---

Questão 16: Contar o número de passageiros em um voo

Repository: FlightRepository

Signature:

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

Teste: Número de passageiros no voo "AC301"

---

Questão 17: Encontrar os passageiros de um voo específico

Repository: PassengerRepository

Signature:

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

Teste: Passageiros do voo "AF001"

---

Questão 18: Voos entre dois aeroportos (por código)

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);

Teste: Voos de "YUL" para "CDG"

---

Questão 19: Preço médio dos voos partindo de um país

Repository: FlightRepository

Signature:

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

Teste: Preço médio dos voos partindo do Canadá

---

Questão 20: Passageiros que fizeram mais de N voos

Repository: PassengerRepository

Signature:

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

Teste: Passageiros que fizeram pelo menos 2 voos

---

Parte 4: Gabarito Completo

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: Encontrar um aeroporto pelo seu código
    Optional<Airport> findByCode(String code);

    // Q2: Listar todos os aeroportos de um país
    List<Airport> findByCountry(String country);

    // Q13: Aeroportos de uma cidade com ordenação
    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: Encontrar um passageiro pelo email
    Optional<Passenger> findByEmail(String email);

    // Q4: Listar passageiros por sobrenome
    List<Passenger> findByLastName(String lastName);

    // Q7: Passageiros nascidos antes de uma data
    List<Passenger> findByBirthDateBefore(LocalDate date);

    // Q11: Busca por sobrenome (contém)
    List<Passenger> findByLastNameContainingIgnoreCase(String keyword);

    // Q17: Passageiros de um voo específico
    @Query("SELECT p FROM Passenger p JOIN p.flights f WHERE f.flightNumber = :flightNumber")
    List<Passenger> findPassengersByFlightNumber(@Param("flightNumber") String flightNumber);

    // Q20: Passageiros frequentes (que fizeram N+ voos)
    @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: Encontrar um voo pelo seu número
    Optional<Flight> findByFlightNumber(String flightNumber);

    // Q6: Voos cujo preço é inferior a um valor
    List<Flight> findByPriceLessThan(Double maxPrice);

    // Q8: Voos entre dois preços
    List<Flight> findByPriceBetween(Double minPrice, Double maxPrice);

    // Q9: Voos partindo de um aeroporto (por código)
    List<Flight> findByDepartureAirport_Code(String airportCode);

    // Q10: Voos chegando em um país
    List<Flight> findByArrivalAirport_Country(String country);

    // Q12: Voos após uma data/hora
    List<Flight> findByDepartureTimeAfter(LocalDateTime dateTime);

    // Q14: Voos com capacidade mínima
    List<Flight> findByCapacityGreaterThanEqual(Integer minCapacity);

    // Q15: Voos de um passageiro por email
    @Query("SELECT f FROM Flight f JOIN f.passengers p WHERE p.email = :email")
    List<Flight> findFlightsByPassengerEmail(@Param("email") String email);

    // Q16: Contar passageiros de um voo
    @Query("SELECT COUNT(p) FROM Flight f JOIN f.passengers p WHERE f.flightNumber = :flightNumber")
    Long countPassengersByFlightNumber(@Param("flightNumber") String flightNumber);

    // Q18: Voos entre dois aeroportos
    @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: Preço médio dos voos por país de origem
    @Query("SELECT AVG(f.price) FROM Flight f WHERE f.departureAirport.country = :country")
    Double findAveragePriceByDepartureCountry(@Param("country") String country);
}

---

Parte 5: Testes e Validação

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 Teste

Crie uma classe de teste para validar suas consultas:

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("Voos 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("Viajantes frequentes: " + travelers.size());
    }
}

---

Critérios de Avaliação

Critério	Pontos
Entidades corretamente modeladas	15%
Relações JPA corretas (@ManyToOne, @ManyToMany)	15%
Questões 1-7 (Fácil)	21% (3% cada)
Questões 8-14 (Médio)	28% (4% cada)
Questões 15-20 (Difícil)	18% (3% cada)
Testes passam	3%
Total	100%

---

Dicas

Sobre as convenções de nomenclatura:

• findBy + PropertyName = busca simples
• _ permite navegar nas relações (ex: findByDepartureAirport_Code)
• Containing = LIKE %keyword%
• IgnoreCase = busca sem diferenciar maiúsculas/minúsculas

Sobre @Query:

• JPQL usa os nomes das classes Java, não os nomes das tabelas SQL
• JOIN para navegar nas relações
• SIZE() para contar elementos de uma coleção
• AVG(), COUNT(), SUM() para agregações

---

Recursos

• Spring Data JPA Reference
• JPQL Reference

---

Voltar ao Módulo 6