如何用Spring Data Specification实现单航班对应唯一FlightSeat实体

我需要查询**不共享同一航班（flight.id）**的FlightSeat实体，也就是每个航班只对应一条FlightSeat记录，但当前查询结果里同一个航班对应了多条FlightSeat。

我尝试添加以下代码，但没有效果：

public class FlightSearchSpecification {
    // getSeatSpecification()

    public static Specification<Object> distinct() {
        return (root, query, cb) -> {
            query.distinct(true);
            return null;
        };
    }
}

flightSeatRepository.findAll(Specification
    .where(FlightSearchSpecification.distinct().and(FlightSearchSpecification.getSeatSpecification(model))) , pageable);

考虑过用groupBy()实现，但不清楚具体操作方式。

相关类代码 #

Specification类 #

public class FlightSearchSpecification {
  
    public static Specification<FlightSeat> getSeatSpecification(TicketFilterModel model) {
        return (root, query, cb) -> {

            Join<FlightSeat, Flight> join = root.join("flight");
            Join<Flight, AviaCompany> join2 = join.join("company");

            Predicate whereSeatClass = cb.equal(root.get("seatClass"), model.getSeatClass());
            Predicate whereSource = cb.equal(join.get("source"), model.getSource());
            Predicate whereDestination = cb.equal(join.get("destination"), model.getDestination());
            Predicate whereCompanyName = join2.get("name").in(model.getCompanies());
            Predicate WhereCustomerNull = cb.isNull(root.get("customer"));

            return cb.and(
                    whereSeatClass, whereSource,
                    whereDestination, whereCompanyName, WhereCustomerNull);
        };
    }
}

JPA Repository #

public interface FlightSeatRepository extends JpaRepository<FlightSeat, Long>, JpaSpecificationExecutor<FlightSeat> {

    List<FlightSeat> findByFlightId(Long flightId);

    @EntityGraph(attributePaths = {"flight", "flight.company"})
    Page<FlightSeat> findAll( Specification<FlightSeat> spec,  Pageable pageable);

}

Specification在findAll()中的使用 #

flightSeatRepository.findAll(FlightSearchSpecification.getSeatSpecification(model), pageable);

FlightSeat实体类 #

@Entity
@Table(name = "flight_seats")
public class FlightSeat {

    @Column(name = "flight_seat_id")
    @Id
    @GeneratedValue
    public Long id;

    @Column(nullable = false)
    private int position;

    @Column(nullable = false)
    private int price;

    @Column(name = "class", nullable = false)
    @Enumerated(EnumType.STRING)
    @JsonProperty("seat_class")
    private FlightSeatClass seatClass;

    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    @JoinColumn(name = "flight_id", nullable = false)
    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    private Flight flight;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id")
    @JsonIgnore
    private Customer customer;

    // constructor, getters, setters
}

Flight实体类 #

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

    @Column(name = "flight_id")
    @Id
    @GeneratedValue
    private Long id;

    @Column(name = "airplane_model", nullable = false)
    @JsonProperty(value = "airplane_model")
    private String airplaneModel;

    @Column(name = "takeoff_timestamp", nullable = false)
    @JsonProperty(value = "takeoff_timestamp")
    private ZonedDateTime takeoffTimestamp;

    @Column(name = "landing_timestamp", nullable = false)
    @JsonProperty(value = "landing_timestamp")
    private ZonedDateTime landingTimestamp;

    @Column(nullable = false)
    @Enumerated(EnumType.STRING)
    private City source;

    @Column(nullable = false)
    @Enumerated(EnumType.STRING)
    private City destination;

    @Column(name = "seat_count")
    @JsonProperty(value = "seat_count")
    private int seatCount;

    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    @JoinColumn(name = "avia_company_id", nullable = false)
    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    private AviaCompany company;

    @OneToMany(mappedBy = "flight")
    @JsonIgnore
    private final Set<FlightSeat> seats = new HashSet<>();

    // constructor, getters, setters
}

你的distinct()方法无效原因：

1. 泛型用了Object而非FlightSeat，和getSeatSpecification类型不匹配，无法正确组合逻辑；
2. distinct(true)仅对完全相同的实体去重，但同一航班的不同座位是不同实体，因此不起作用。

以下两种方式可实现需求：

方式1：修改Specification添加groupBy逻辑 #

直接在原有查询逻辑中添加分组规则，确保每个航班仅返回一条座位记录：

public static Specification<FlightSeat> getSeatWithSinglePerFlight(TicketFilterModel model) {
    return (root, query, cb) -> {
        // 原有查询条件
        Join<FlightSeat, Flight> join = root.join("flight");
        Join<Flight, AviaCompany> join2 = join.join("company");

        Predicate whereSeatClass = cb.equal(root.get("seatClass"), model.getSeatClass());
        Predicate whereSource = cb.equal(join.get("source"), model.getSource());
        Predicate whereDestination = cb.equal(join.get("destination"), model.getDestination());
        Predicate whereCompanyName = join2.get("name").in(model.getCompanies());
        Predicate whereCustomerNull = cb.isNull(root.get("customer"));

        Predicate whereClause = cb.and(whereSeatClass, whereSource, whereDestination, whereCompanyName, whereCustomerNull);

        // 按航班ID分组
        query.groupBy(join.get("id"));
        // 可选：指定排序规则，确保取每个航班的特定座位（比如价格最低的）
        query.orderBy(cb.asc(root.get("price")));

        return whereClause;
    };
}

调用方式：

flightSeatRepository.findAll(FlightSearchSpecification.getSeatWithSinglePerFlight(model), pageable);

方式2：自定义JPQL查询 #

如果Specification不够灵活，可在Repository中编写自定义查询：

@EntityGraph(attributePaths = {"flight", "flight.company"})
@Query("SELECT fs FROM FlightSeat fs " +
       "JOIN fs.flight f " +
       "JOIN f.company c " +
       "WHERE fs.seatClass = :seatClass " +
       "AND f.source = :source " +
       "AND f.destination = :destination " +
       "AND c.name IN :companies " +
       "AND fs.customer IS NULL " +
       "GROUP BY f.id " +
       "ORDER BY fs.price ASC")
Page<FlightSeat> findSingleSeatPerFlight(@Param("seatClass") FlightSeatClass seatClass,
                                         @Param("source") City source,
                                         @Param("destination") City destination,
                                         @Param("companies") List<String> companies,
                                         Pageable pageable);

注意事项 #

• 使用groupBy时，JPA要求查询字段要么是分组字段，要么是聚合函数结果；Hibernate在部分场景下允许返回实体，但需确保分组逻辑与返回字段兼容；
• 若需指定每个航班返回的具体座位（如价格最低、位置最靠前），可添加对应排序规则，groupBy后会取排序后的第一条记录。

内容的提问来源于stack exchange，提问作者Denis_54213213