How to map dynamic query to projection interface. not an entity class

Question

@Entity
@Table(name = "delivery_status_summary", schema = "dsd")
@Data
public class DeliveryStatusSummaryV2 {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="delivery_status_summary_id_seq")
    @SequenceGenerator(name="delivery_status_summary_id_seq", sequenceName="delivery_status_summary_id_seq", allocationSize=1)
    private Integer id;

    @Column(name = "expected_delivery_date")
    private LocalDateTime expectedDeliveryDate;

    private Integer total;

    @Column(name = "on_time")
    private Integer onTime;

    private Integer late;

    private Integer pending;

    @Column(name = "not_received")
    private Integer notReceived;

}

Repository:

public interface DeliveryStatusSummaryRepository extends JpaRepository<DeliveryStatusSummary, Integer>, DeliveryStatusSummaryCustomRepository {
    //language=SQL
    String MANAGER_DELIVERY = "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 >= CAST(:range_start AS TIMESTAMP) AND" +
        "          dss.expected_delivery_date <= 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 t.manager_id, dss.expected_delivery_date" +
        ")" +
        "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";

    @Query(value = MANAGER_DELIVERY, nativeQuery = true)
    CompletableFuture<List<DeliveryStatusSummaryByManagerAndDate>> getDailyDeliveryStatusSummaryByManagers(
            @Param("manager_ids") final Set<String> employeeIds,
            @Param("range_start") ZonedDateTime rangeStart,
            @Param("range_end") ZonedDateTime rangeEnd
    );
}

Projection.

public interface DeliveryStatusSummaryByManagerAndDate {

    String getManagerId();

    LocalDate getExpectedDeliveryDate();

    int getTotalCount();

    int getOnTimeCount();

    int getLateCount();

    int getPendingCount();

    int getNotReceivedCount();
}

getDailyDeliveryStatusSummaryByManagers works as expected.

But I need to have an opportunity to change group by section of this query on-the-fly, depending on user's input. So I decided to play with query as a plain string. The idea is to put a query to string, and then depending on user's input, make query.replace('group placeholder', group by <needed list of fields>).

In order to archive this, I created a custom DeliveryStatusSummaryCustomRepository. I decided not to make replace on a string so far, but to try to execute a simple query MANAGER_DELIVERY_QUERY.

public interface DeliveryStatusSummaryCustomRepository {

    List<DeliveryStatusSummaryByManagerAndDate> getDailyDeliveryStatusSummaryByManagersV2();
}

@Repository
public class DeliveryStatusSummaryCustomRepositoryImpl implements DeliveryStatusSummaryCustomRepository {

    private String MANAGER_DELIVERY_QUERY_TRY = "SELECT '1' AS managerId, " +
            "CAST(dss.expected_delivery_date AS DATE) AS expectedDeliveryDate, " +
            "0 AS totalCount, " +
            "0 AS onTimeCount, " +
            "0 AS lateCount, " +
            "0 AS pendingCount, " +
            "0 AS notReceivedCount " +
            "FROM dsd.delivery_status_summary AS dss " +
            "WHERE dss.expected_delivery_date >= CAST('01-01-2022' AS TIMESTAMP) AND dss.expected_delivery_date <= CAST('10-01-2022' AS TIMESTAMP)";


    private final PrimaryDbConfig primaryDbConfig;

    public DeliveryStatusSummaryCustomRepositoryImpl(PrimaryDbConfig primaryDbConfig) {
        this.primaryDbConfig = primaryDbConfig;
    }

    @Override
    public List<DeliveryStatusSummaryByManagerAndDate> getDailyDeliveryStatusSummaryByManagersV2() {
        final LocalContainerEntityManagerFactoryBean em = primaryDbConfig.primaryEntityManager();
        final EntityManager nativeEntityManager = em.createNativeEntityManager(new HashMap<>());
        final Query query = nativeEntityManager.createQuery(MANAGER_DELIVERY_QUERY_TRY);
        return query.getResultList();
    }
}

But I got an error:

org.hibernate.hql.internal.ast.QuerySyntaxException: delivery_status_summary is not mapped
    at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:169)
    at org.hibernate.hql.internal.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:91)
    at org.hibernate.hql.internal.ast.tree.FromClause.addFromElement(FromClause.java:77)
    at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:333)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3765)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3654)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:737)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:593)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:330)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:278)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:276)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:613)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:725)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23)
    at amp.ae.dataset.status.dashboard.library.repository.primary.DeliveryStatusSummaryCustomRepositoryImpl.getDailyDeliveryStatusSummaryByManagersV2(DeliveryStatusSummaryCustomRepositoryImpl.java:115)
    at amp.ae.dataset.status.dashboard.library.repository.primary.DeliveryStatusSummaryCustomRepositoryImpl$$FastClassBySpringCGLIB$$f0397

I believe that the issue is related to DeliveryStatusSummaryByManagerAndDate is not entity, this is just projection.

If so, what approach to use for writing dynamic query taking into account that MANAGER_DELIVERY query is quite complicated to be re-written with Hibernate joins? I heard about https://www.jooq.org. Any advice appreciated.

Answer 1

You need to use EntityManager.createNativeQuery() method. Also you can just autoware EntityManager

@Repository
public class DeliveryStatusSummaryCustomRepositoryImpl {

    @PersistenceContext
    private EntityManager entityManager;

}

To map query results to DTO

1. Replace the interface DeliveryStatusSummaryByManagerAndDate with POJO with getters and setters.
2. Use a result transformer from Hibernate

import org.hibernate.query.Query;

Query<DeliveryStatusSummaryByManagerAndDate> query = entityManager
        .createNativeQuery(sql)
        .unwrap(Query.class)
        .setResultTransformer(
                Transformers.aliasToBean(DeliveryStatusSummaryByManagerAndDate.class)
        );

return query.getResultList();

3. Also check this mapping Hibernate query results to custom class?