如何将动态原生SQL查询映射至Projection接口并解决执行异常

问题背景 #

现有Spring Data JPA项目中，通过@Query(nativeQuery = true)可正常将复杂原生SQL的查询结果映射到Projection接口DeliveryStatusSummaryByManagerAndDate。但因业务需求需动态修改SQL中的GROUP BY子句，尝试自定义Repository实现动态SQL生成时，抛出如下异常：

org.hibernate.hql.internal.ast.QuerySyntaxException: delivery_status_summary is not mapped

异常核心原因是误用EntityManager.createQuery()方法（该方法用于解析HQL，而非原生SQL），同时需解决动态生成的原生SQL结果到Projection接口的映射问题。

解决方案 #

方案1：修正原生SQL查询方式（最简方案） #

直接使用EntityManager.createNativeQuery()方法执行原生SQL，并指定Projection接口作为结果类型，同时优化EntityManager的获取方式：

代码实现

@Repository
public class DeliveryStatusSummaryCustomRepositoryImpl implements DeliveryStatusSummaryCustomRepository {

    private final EntityManager entityManager;

    // 直接通过@PersistenceContext注入EntityManager，避免手动创建
    public DeliveryStatusSummaryCustomRepositoryImpl(@PersistenceContext EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    public List<DeliveryStatusSummaryByManagerAndDate> getDailyDeliveryStatusSummaryByManagersV2() {
        // 动态生成SQL（此处示例固定GROUP BY，实际可根据业务逻辑动态拼接）
        String dynamicSql = generateDynamicSql();
        
        // 创建原生SQL查询，指定映射的Projection接口
        Query query = entityManager.createNativeQuery(dynamicSql, DeliveryStatusSummaryByManagerAndDate.class);
        
        // 若有动态参数，可通过setParameter设置
        // query.setParameter("range_start", ZonedDateTime.now().minusMonths(1));
        // query.setParameter("range_end", ZonedDateTime.now());
        
        return query.getResultList();
    }

    // 动态生成SQL的核心方法，根据需求修改GROUP BY子句
    private String generateDynamicSql() {
        // 示例：根据业务条件动态选择GROUP BY字段
        String groupByClause = "t.manager_id, dss.expected_delivery_date";
        
        // 拼接完整动态SQL（复用原有的复杂查询逻辑）
        return "WITH dates AS (" +
                "    SELECT GENERATE_SERIES(CAST(:range_start AS TIMESTAMP), CAST(:range_end AS TIMESTAMP), INTERVAL '1 day') AS day" +
                "), deliveries AS (" +
                "    SELECT * FROM dsd.delivery_status_summary AS dss WHERE dss.expected_delivery_date BETWEEN CAST(:range_start AS TIMESTAMP) AND CAST(:range_end AS TIMESTAMP)" +
                "), managers AS (" +
                "    SELECT * FROM dsd.teams as t WHERE t.manager_id IN (:manager_ids)" +
                "), summary AS (" +
                "    SELECT t.manager_id, dss.expected_delivery_date, CAST(dss.expected_delivery_date AS DATE) AS day," +
                "           SUM(dss.total) AS total, SUM(dss.on_time) AS on_time, SUM(dss.late) AS late," +
                "           SUM(dss.pending) AS pending, SUM(dss.not_received) AS not_received" +
                "    FROM deliveries AS dss" +
                "    INNER JOIN dsd.sla_datasets AS s ON dss.sla_id = s.sla_id" +
                "    INNER JOIN dsd.datasets AS ds ON ds.dataset_id = s.dataset_id" +
                "    INNER JOIN managers AS t on ds.team_id = t.ad_id" +
                "    INNER JOIN dsd.employee AS e on t.manager_id = e.id" +
                "    GROUP BY " + groupByClause + "" +
                ")" +
                "SELECT d.day AS expectedDeliveryDate, s.manager_id, " +
                "       COALESCE(s.total, 0) AS totalCount, COALESCE(s.on_time, 0) AS onTimeCount," +
                "       COALESCE(s.late, 0) AS lateCount, COALESCE(s.pending, 0) AS pendingCount," +
                "       COALESCE(s.not_received, 0) AS notReceivedCount " +
                "FROM dates AS d LEFT JOIN summary AS s ON d.day = s.day " +
                "ORDER BY s.manager_id, d.day";
    }
}

方案2：使用jOOQ处理复杂动态SQL（推荐方案） #

对于逻辑复杂的动态SQL场景，jOOQ提供类型安全的SQL构建能力，自动规避SQL注入风险，且支持直接映射到Projection接口：

1. 添加依赖

<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.18.4</version>
</dependency>
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-spring-boot-starter</artifactId>
    <version>3.18.4</version>
</dependency>

（需配合对应数据库驱动，并配置代码生成插件生成数据库表对应的jOOQ类）

2. 代码实现

@Repository
public class DeliveryStatusSummaryCustomRepositoryImpl implements DeliveryStatusSummaryCustomRepository {

    private final DSLContext dslContext;

    public DeliveryStatusSummaryCustomRepositoryImpl(DSLContext dslContext) {
        this.dslContext = dslContext;
    }

    @Override
    public List<DeliveryStatusSummaryByManagerAndDate> getDailyDeliveryStatusSummaryByManagersV2(
            Set<String> managerIds, ZonedDateTime rangeStart, ZonedDateTime rangeEnd) {
        
        // 动态构建GROUP BY字段数组
        Field<?>[] groupByFields = {DSD.TEAMS.MANAGER_ID, DSD.DELIVERY_STATUS_SUMMARY.EXPECTED_DELIVERY_DATE};

        // 类型安全构建SQL
        return dslContext.select(
                        DSL.date(DSD.DATES.DAY).as("expectedDeliveryDate"),
                        DSD.TEAMS.MANAGER_ID.as("managerId"),
                        DSL.coalesce(DSL.sum(DSD.DELIVERY_STATUS_SUMMARY.TOTAL), DSL.inline(0)).as("totalCount"),
                        DSL.coalesce(DSL.sum(DSD.DELIVERY_STATUS_SUMMARY.ON_TIME), DSL.inline(0)).as("onTimeCount"),
                        DSL.coalesce(DSL.sum(DSD.DELIVERY_STATUS_SUMMARY.LATE), DSL.inline(0)).as("lateCount"),
                        DSL.coalesce(DSL.sum(DSD.DELIVERY_STATUS_SUMMARY.PENDING), DSL.inline(0)).as("pendingCount"),
                        DSL.coalesce(DSL.sum(DSD.DELIVERY_STATUS_SUMMARY.NOT_RECEIVED), DSL.inline(0)).as("notReceivedCount")
                )
                .with("dates").as(
                        dslContext.select(DSL.generateSeries(
                                        DSL.timestamp(rangeStart.toLocalDateTime()),
                                        DSL.timestamp(rangeEnd.toLocalDateTime()),
                                        DSL.interval("1 day")
                                ).as("day")
                        )
                )
                .with("deliveries").as(
                        dslContext.selectFrom(DSD.DELIVERY_STATUS_SUMMARY)
                                .where(DSD.DELIVERY_STATUS_SUMMARY.EXPECTED_DELIVERY_DATE
                                        .between(rangeStart.toLocalDateTime(), rangeEnd.toLocalDateTime()))
                )
                .with("managers").as(
                        dslContext.selectFrom(DSD.TEAMS)
                                .where(DSD.TEAMS.MANAGER_ID.in(managerIds))
                )
                .with("summary").as(
                        dslContext.select(
                                        DSD.TEAMS.MANAGER_ID,
                                        DSD.DELIVERY_STATUS_SUMMARY.EXPECTED_DELIVERY_DATE,
                                        DSL.date(DSD.DELIVERY_STATUS_SUMMARY.EXPECTED_DELIVERY_DATE).as("day"),
                                        DSL.sum(DSD.DELIVERY_STATUS_SUMMARY.TOTAL),
                                        DSL.sum(DSD.DELIVERY_STATUS_SUMMARY.ON_TIME),
                                        DSL.sum(DSD.DELIVERY_STATUS_SUMMARY.LATE),
                                        DSL.sum(DSD.DELIVERY_STATUS_SUMMARY.PENDING),
                                        DSL.sum(DSD.DELIVERY_STATUS_SUMMARY.NOT_RECEIVED)
                                )
                                .from(DSD.DELIVERIES)
                                .join(DSD.SLA_DATASETS).on(DSD.DELIVERY_STATUS_SUMMARY.SLA_ID.eq(DSD.SLA_DATASETS.SLA_ID))
                                .join(DSD.DATASETS).on(DSD.SLA_DATASETS.DATASET_ID.eq(DSD.DATASETS.DATASET_ID))
                                .join(DSD.MANAGERS).on(DSD.DATASETS.TEAM_ID.eq(DSD.TEAMS.AD_ID))
                                .join(DSD.EMPLOYEE).on(DSD.TEAMS.MANAGER_ID.eq(DSD.EMPLOYEE.ID))
                                .groupBy(groupByFields)
                )
                .from(DSD.DATES)
                .leftJoin(DSD.SUMMARY).on(DSD.DATES.DAY.eq(DSD.SUMMARY.DAY))
                .orderBy(DSD.SUMMARY.MANAGER_ID, DSD.DATES.DAY)
                .fetchInto(DeliveryStatusSummaryByManagerAndDate.class);
    }
}

方案3：手动映射结果集（兜底方案） #

若上述方案无法适配，可手动处理ResultSet到Projection接口的映射：

代码实现

@Repository
public class DeliveryStatusSummaryCustomRepositoryImpl implements DeliveryStatusSummaryCustomRepository {

    private final EntityManager entityManager;
    private final ProjectionFactory projectionFactory;

    public DeliveryStatusSummaryCustomRepositoryImpl(
            @PersistenceContext EntityManager entityManager,
            ProjectionFactory projectionFactory) {
        this.entityManager = entityManager;
        this.projectionFactory = projectionFactory;
    }

    @Override
    public List<DeliveryStatusSummaryByManagerAndDate> getDailyDeliveryStatusSummaryByManagersV2() {
        String sql = MANAGER_DELIVERY_QUERY_TRY;
        Session session = entityManager.unwrap(Session.class);
        
        return session.doReturningWork(connection -> {
            try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                // 动态设置参数
                // stmt.setTimestamp(1, Timestamp.from(rangeStart.toInstant()));
                // stmt.setTimestamp(2, Timestamp.from(rangeEnd.toInstant()));
                
                try (ResultSet rs = stmt.executeQuery()) {
                    List<DeliveryStatusSummaryByManagerAndDate> result = new ArrayList<>();
                    while (rs.next()) {
                        // 创建Projection代理实例
                        DeliveryStatusSummaryByManagerAndDate projection = projectionFactory.createProjection(DeliveryStatusSummaryByManagerAndDate.class);
                        BeanWrapper wrapper = new BeanWrapperImpl(projection);
                        
                        // 手动映射字段
                        wrapper.setPropertyValue("managerId", rs.getString("managerId"));
                        wrapper.setPropertyValue("expectedDeliveryDate", rs.getDate("expectedDeliveryDate").toLocalDate());
                        wrapper.setPropertyValue("totalCount", rs.getInt("totalCount"));
                        wrapper.setPropertyValue("onTimeCount", rs.getInt("onTimeCount"));
                        wrapper.setPropertyValue("lateCount", rs.getInt("lateCount"));
                        wrapper.setPropertyValue("pendingCount", rs.getInt("pendingCount"));
                        wrapper.setPropertyValue("notReceivedCount", rs.getInt("notReceivedCount"));
                        
                        result.add(projection);
                    }
                    return result;
                }
            }
        });
    }
}

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