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

问题描述

我有两个表Ticket和Flight.一次航班可能有很多票.

I have two tables Ticket and Flight. One flight could have many tickets.

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

I want to show fields departure_date, destination_date from the table Flight and name, surname from the table Ticket. And show data only for the certain flight_id. I use findBy method.

实体飞行

@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:

I've created file FlightsTicketDto with certain fields:

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

FlightTicketRepository with my Query

 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

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，也只是另一个数字)，我都有我所有的flights

Actually whatever flight_id (even not flight_id, but just another number) I write, I've got all my flights

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

For example I want to get result only for flight_id = 431, result you can see on the picture. What's wrong?

推荐答案

替换

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

这篇关于Spring Boot Data JPA-如何获取特定ID的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！