时间戳

首页 > 解决方案 > JPQL 构造函数忽略具有 NULL 值的行

java - JPQL 构造函数忽略具有 NULL 值的行

问题描述

我有以下声明：

@Query("SELECT "
        + "new com.app.model.RestaurantOrderPartial( "
            + "o.orderId, o.orderedAt, o.orderType, o.orderState, o.orderValue, o.deliveryPrice, o.deliveredBy, o.driver) "
        + "FROM Order o "
        + "WHERE o.restaurant.restaurantId = ?1 "
        + "AND o.orderedAt BETWEEN ?2 and ?3 "
        + "ORDER BY o.orderedAt DESC ")
List<RestaurantOrderPartial> getRestaurantOrdersCompressed(long restaurantId, LocalDateTime dateBeforePeriod, LocalDateTime now); //12 Month

表 where o.driveris中的行NULL，构造函数不构造对象的条目，也不将其包含在结果列表中。

为什么会有这种行为？以及如何让构造函数包含 Driver 为空的条目，在自定义对象RestaurantOrderPartial中，Driver 对象将根据 Column 中的值为空。

这是我的 POJO：

public class RestaurantOrderPartial {
    
    private long orderId;
    private LocalDateTime orderedAt;
    private OrderType orderType;
    private OrderState orderState;
    private Long orderValue;
    
    
    private int deliveryPrice;
    private DeliveredBy deliveredBy;
    private Driver driver;

}

public class Driver {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long driverId;

@Column
private String email;

@Column
private String password;

@Column
private String fullName;

@Column
private String phoneNumber;

@Column
private String secondaryPhoneNumber;

@Column
private String fullAddress;

@Column
private boolean isActive;

@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "cityId")    
private City city;

@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "locationId")
private Location location;

@JsonIgnore
@OneToMany(mappedBy="driver")
private List<Order> orders;

样本数据： 样本数据

谢谢你的帮助。

标签： javasqlspringconstructorjpql

解决方案

如果你想在 o.driver 为 null 的情况下获取它，你必须指定一个左外连接；o.driver 始终是一个内部连接，因此会从结果中过滤掉空值。

尝试更多类似的东西

"SELECT "
        + "new com.app.model.RestaurantOrderPartial( "
            + "o.orderId, o.orderedAt, o.orderType, o.orderState, o.orderValue, o.deliveryPrice, o.deliveredBy, driver) "
        + "FROM Order o LEFT OUTER JOIN o.driver driver"
        + "WHERE o.restaurant.restaurantId = ?1 "
        + "AND o.orderedAt BETWEEN ?2 and ?3 "
        + "ORDER BY o.orderedAt DESC ")

推荐阅读