能否使用Spring JPA/JPQL替代含LEFT JOIN子查询的PostgreSQL原生查询

结论 #

标准JPQL规范本身不支持在JOIN子句中嵌入子查询，也不支持PostgreSQL特有的LATERAL JOIN语法，因此无法用纯JPQL实现你当前的需求。但你不需要完全放弃Spring JPA生态，有两种成熟的落地方案可以选择：

方案1：使用Spring JPA原生查询（推荐，改造成本最低） #

Spring JPA的@Query原生查询完全支持动态参数绑定、分页、排序能力，不需要你手动处理JDBC逻辑，适配成本极低：

• 直接把你已经写好的SQL放入@Query注解，设置nativeQuery = true，把SQL中硬编码的公司ID、单位ID、货币ID、时间范围等参数换成命名参数，通过方法入参动态传入即可。
• 要支持分页只需给方法添加Pageable参数，同时声明对应countQuery用来统计总条数，Spring JPA会自动处理分页、排序逻辑，完全满足你数据库层面排序分页的需求。
• 可以用Spring JPA的Projection接口直接接收查询返回的自定义字段，不需要额外做结果映射。

示例代码：

// 定义结果接收Projection
public interface ForecastStatProjection {
    String getCompanyAddressId();
    BigDecimal getPlanningVolume();
    BigDecimal getPlanningSpend();
}

// Repository接口写法
public interface CompanyAddressRepository extends JpaRepository<CompanyAddress, Long> {
    @Query(
        value = """
            SELECT 
              CAST(ca.id as varchar) AS companyAddressId, 
              COALESCE(materialForecast.planningVolume, 0) + COALESCE(componentForecast.planningVolume, 0) AS planningVolume, 
              COALESCE(materialForecast.planningSpend, 0) + COALESCE(componentForecast.planningSpend, 0) AS planningSpend 
            FROM company_address ca 
            JOIN company c ON c.id = :companyId AND ca.company_id = c.id AND c.is_deleted = false 
            LEFT JOIN LATERAL (
                SELECT mf.company_address_id as mid, 
                  COALESCE(SUM(mf.volume * fn_get_unit_multiplier(mf.volume_unit_id, :unitId)), 0) as planningVolume, 
                  COALESCE(SUM(mf.price * fn_get_currency_exchange(mf.currency_unit_id, :currencyId, 'FORECAST', CAST(mf.volume_date || '-01' as date))), 0) as planningSpend 
                FROM material_forecast mf 
                WHERE TO_DATE(mf.volume_date, 'YYYY-MM-DD') BETWEEN :startDate AND :endDate
                  AND mf.is_deleted = false AND mf.company_address_id = ca.id 
                GROUP BY mf.company_address_id
            ) materialForecast ON TRUE 
            LEFT JOIN LATERAL (
                SELECT cf.company_address_id as cid, 
                  COALESCE(SUM(cf.volume * cs.net_weight_value * fn_get_unit_multiplier(cs.net_weight_unit_id, :unitId)), 0) as planningVolume, 
                  COALESCE(SUM(cf.price * fn_get_currency_exchange(cf.currency_unit_id, :currencyId, 'FORECAST', CAST(cf.volume_date || '-01' as date))), 0) as planningSpend 
                FROM component_forecast cf 
                JOIN component_specification cs ON cs.id = cf.component_specification_id AND cs.is_deleted = false 
                WHERE TO_DATE(cf.volume_date, 'YYYY-MM-DD') BETWEEN :startDate AND :endDate
                  AND cf.is_deleted = false AND cf.company_address_id = ca.id 
                GROUP BY cf.company_address_id
            ) componentForecast ON TRUE 
            WHERE ca.is_deleted = false 
            ORDER BY planningVolume DESC
            """,
        countQuery = """
            SELECT count(*) 
            FROM company_address ca 
            JOIN company c ON c.id = :companyId AND ca.company_id = c.id AND c.is_deleted = false 
            WHERE ca.is_deleted = false
            """,
        nativeQuery = true
    )
    Page<ForecastStatProjection> getForecastStats(
        @Param("companyId") String companyId,
        @Param("unitId") String unitId,
        @Param("currencyId") String currencyId,
        @Param("startDate") LocalDate startDate,
        @Param("endDate") LocalDate endDate,
        Pageable pageable
    );
}

方案2：使用Blaze-Persistence扩展JPQL能力 #

如果你的项目中存在大量类似的复杂查询，希望用JPQL的面向对象语法编写、避免直接写原生SQL，可以引入Blaze-Persistence扩展：

• 它是JPA标准的功能增强组件，原生支持LATERAL JOIN、JOIN子句嵌入子查询等高级SQL特性，完全兼容Spring Data JPA，不需要修改现有JPA配置。
• 编写的查询语法和JPQL一致，基于实体对象定义，自动适配不同数据库方言，后续如果更换数据库不需要修改查询逻辑，可维护性比零散的原生SQL更高。

不建议的方案 #

• 不要将查询拆分为多次执行后在内存中做分页排序，数据量较大时会出现严重的性能问题，且排序分页准确率无法保障。
• 不要强行把聚合逻辑写到SELECT子句的子查询中实现JPQL适配，这种写法会对每一行数据执行两次子查询，性能远低于LATERAL JOIN。

内容的提问来源于stack exchange，提问作者séan35