HQL投影查询时assignedVehicle为null的记录无法返回的问题

现有VehicleRequest表共146条记录，使用带构造函数投影的HQL查询时，仅返回36条存在assignedVehicle的记录，所有assignedVehicle为null的记录被过滤；若直接查询实体SELECT vr FROM VehicleRequest AS vr，能返回全部记录，但JPA会触发大量关联查询，性能极差。

原因分析 #

生成的SQL中，assignedVehicle和assignedVehicleDriver对应的关联使用了内连接（通过cross join+等值条件实现，等价于内连接），当关联字段为null时，内连接会自动过滤掉这些不满足关联条件的记录。

解决方案 #

方法1：显式使用左连接（LEFT JOIN）处理可空关联 #

修改HQL查询，对可能为null的关联（assignedVehicle、assignedVehicleDriver）显式声明左连接，同时调整投影和过滤条件中的字段引用，避免直接链式调用，确保JPA正确生成左连接逻辑。

修改后的HQL示例：

@Query("SELECT new com.api.projection.GarageVehicleRequestLookupProjection" +
        "(vr.id, vr.appUser.fullName, vr.appUser.unit.id, vr.appUser.unit.name, vr.createdAt, " +
        "vr.startAt, vr.endAt, vr.destination,vr.reason, av.id, " +
        "av.plate, vr.latestAction.vehicleRequestStatus,vr.appUser.unit.director.id, vr.appUser.unit.director.fullName,vr.driverRequested," +
        "vr.garage.name, vr.vehicleTypeRequested, vr.requestType, vr.description, avd.id, avd.fullName)" +
        " FROM VehicleRequest AS vr " +
        "LEFT JOIN vr.assignedVehicle av " + 
        "LEFT JOIN vr.assignedVehicleDriver avd " +
        "WHERE " +
        "vr.appUser.fullName like %:fullName% " +
        "AND (coalesce(:unitIds, null) is null or vr.appUser.unit.id in :unitIds) " +
        "AND (coalesce(:garageIds, null) is null or vr.garage.id in :garageIds) " +
        "AND (coalesce(:vehicleIds, null) is null or av.id in :vehicleIds) " +
        "AND (coalesce(:statuses, null) is null or vr.latestAction.vehicleRequestStatus in :statuses) " +
        "AND (coalesce(:createdAt, null) is null or cast(:createdAt as date) = cast(vr.createdAt as date)) " +
        "AND vr.startAt >= :startAt and vr.startAt <= :startAtEnd ")
Page<GarageVehicleRequestLookupProjection> getGarageVehicleRequestsWithFilter(
        @Param("fullName") String fullName,
        @Param("unitIds") List<Long> unitIds,
        @Param("garageIds") List<Long> garageIds,
        @Param("vehicleIds") List<Long> vehicleIds,
        @Param("statuses") List<VehicleRequestStatus> status,
        @Param("createdAt") Date createdAt,
        @Param("startAt") Date startAt, @Param("startAtEnd") Date startAtEnd,
        Pageable pageable);

关键调整点：

• 对vr.assignedVehicle和vr.assignedVehicleDriver添加LEFT JOIN并指定别名，让JPA明确生成左连接逻辑
• 投影和过滤条件中用别名引用关联字段（如av.id代替vr.assignedVehicle.id），避免JPA默认生成内连接
• 当vehicleIds为null时，过滤条件自动跳过，不影响左连接结果；若有值，仅过滤出关联车辆匹配的记录，同时保留无关联的记录

方法2：使用LEFT JOIN FETCH（可选，适合需部分实体场景） #

如果投影中需要的关联字段较多，也可以用LEFT JOIN FETCH预先加载可空关联，但需添加DISTINCT避免分页时出现重复记录：

@Query("SELECT DISTINCT new com.api.projection.GarageVehicleRequestLookupProjection" +
        "(vr.id, vr.appUser.fullName, vr.appUser.unit.id, vr.appUser.unit.name, vr.createdAt, " +
        "vr.startAt, vr.endAt, vr.destination,vr.reason, vr.assignedVehicle.id, " +
        "vr.assignedVehicle.plate, vr.latestAction.vehicleRequestStatus,vr.appUser.unit.director.id, vr.appUser.unit.director.fullName,vr.driverRequested," +
        "vr.garage.name, vr.vehicleTypeRequested, vr.requestType, vr.description, vr.assignedVehicleDriver.id, vr.assignedVehicleDriver.fullName)" +
        " FROM VehicleRequest AS vr " +
        "LEFT JOIN FETCH vr.assignedVehicle " +
        "LEFT JOIN FETCH vr.assignedVehicleDriver " +
        "WHERE ...")

效果验证 #

修改后生成的SQL会将对应关联改为LEFT JOIN，例如：

left join vehicle vehicle5_ on vehiclereq0_.assigned_vehicle_id=vehicle5_.id
left join app_user appuser13_ on vehiclereq0_.assigned_vehicle_driver_id=appuser13_.id

此时即使assignedVehicleId或assignedVehicleDriverId为null，对应的VehicleRequest记录仍会被保留，投影中对应字段填充为null，同时保持投影查询的性能优势（仅查询需要的字段，避免N+1查询）。

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