mysql - Spring Boot Data JPA - 如何获取特定id的数据

Question

我有两个表Ticket 和Flight。一个航类可以有多张机票。

我想显示表 Flight 中的字段 departure_date、destination_date 和 name、surname 来自表 Ticket。并且仅显示特定flight_id的数据。我使用findBy方法。

实体飞行

@Entity
@Table(name = "flight")
public class Flight {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer flight_id;

@Column(name = "departureDate")
private Date departureDate;

@Column(name = "destinationDate")
private Date destinationDate;

@OneToMany(mappedBy = "flight")
@JsonManagedReference("flight")
private List<Ticket> tickets;

实体票

@Entity
@Table(name = "ticket")
public class Ticket {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int ticket_id;

@Column(name = "name")
private String name;

@Column(name = "surname")
private String surname;

@ManyToOne(targetEntity = Flight.class)
@JoinColumn(name = "flight_id")
@JsonBackReference("flight")
@Fetch(FetchMode.JOIN)
private Flight flight;

我创建了包含某些字段的文件 FlightsTicketDto:

public class FlightTicketDto {

private Integer flight_id;
private Date departureDate;
private Date destinationDate;
private String name;
private String surname;

public FlightTicketDto() {
}

public FlightTicketDto(Integer flight_id, Date departureDate, Date destinationDate, String name, String surname) {
    this.flight_id = flight_id;
    this.departureDate = departureDate;
    this.destinationDate = destinationDate;
    this.name = name;
    this.surname = surname;
}

带有我的查询的 FlightTicketRepository

 public interface FlightTicketRepository extends JpaRepository<Ticket, Integer> {

 @Query("SELECT new pl.edu.wat.dto.FlightTicketDto(f.flight_id, f.departureDate, f.destinationDate, t.name, t.surname) "
        + "FROM Flight f INNER JOIN f.tickets t")
 List<FlightTicketDto> findByFlightId(Integer flight_id);
 }

FlightTicketController

@CrossOrigin(origins = "http://localhost:4200")
@RestController
@RequestMapping("/api")
public class FlightTicketController {

@Autowired
FlightTicketRepository flightTicketRepository;

@GetMapping("/mytickets/{flight_id}")
public List fetchEmpDeptDataInnerJoin(@PathVariable Integer flight_id) {
    return flightTicketRepository.findByFlightId(flight_id);
}

实际上，无论我写什么flight_id(甚至不是flight_id，而只是另一个数字)，我都有我的所有航类

例如，我只想获取flight_id = 431的结果，您可以在图片上看到结果。怎么了？

Answer 1

替换

@Query("SELECT new pl.edu.wat.dto.FlightTicketDto(f.flight_id, f.departureDate, f.destinationDate, t.name, t.surname) "
        + "FROM Flight f INNER JOIN f.tickets t")
 List<FlightTicketDto> findByFlightId(Integer flight_id);

与

@Query("SELECT new pl.edu.wat.dto.FlightTicketDto(f.flight_id, f.departureDate, f.destinationDate, t.name, t.surname) "
            + "FROM Flight f INNER JOIN f.tickets t where f.flight_id = :flight_id")
     List<FlightTicketDto> findByFlightId(@Param("flight_id") Integer flight_id);