0

I am trying to use few postgres inbuild functions, sub query and join in a single query in JPA where the result set (List<CampaignDTO>) is different from the actual entity (Campaign). I could achieve this by using native query and result transformation by "alias to bean".

Due to the deprecation I wanted to avoid the approach. Is there any way to achieve it using the criteria builder?

Here is the query:

select
    "table1"."attemptedDate" "attemptedDate",
    coalesce("table2"."messageAttempted", 0) "messageAttempted",
    coalesce("table2"."messageSuccess", 0) "messageSuccess",
    coalesce("table2"."messageFailure", 0) "messageFailure"
from
    (
    select
        distinct generate_series(date '2020-11-10', date '2020-11-17', '1 day') "attemptedDate"
    from
        campaign) as "table1"
full join (
    select
        campaign_date "attemptedDate",
        sum(coalesce(attempted_count, 0)) "messageAttempted",
        sum(coalesce(delivered_count, 0)) "messageSuccess",
        sum(coalesce(failed_count, 0)) "messageFailure"
    from
        campaign
    where
        channel = 'abc'
        and campaign_date between '2020-11-10' and '2020-11-17'
    group by
        1) as "table2" on
    "table1"."attemptedDate" = "table2"."attemptedDate"
order by
    "attemptedDate"

Entity Class:

@Entity
@Table(name = "campaign")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Campaign{

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "channel", nullable = false)
    private String channel;

    @Column(name = "attempted_count", nullable = false)
    private Integer attemptedCount;

    @Column(name = "delivered_count", nullable = false)
    private Integer deliveredCount;

    @Column(name = "failed_count", nullable = false)
    private Integer failedCount;

    @Column(name = "campaign_date", nullable = false)
    private Date campaignDate;
}

DTO Class:

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class CampaignDTO{

    private Date attemptedDate;

    @Builder.Default
    private Integer messageAttempted = 0;

    @Builder.Default
    private Integer messageSuccess = 0;

    @Builder.Default
    private Integer messageFailure = 0;
}

Or is there any way to avoid deprecation while using transformation?

Sanjib Pramanick
  • 1
  • 2

1 Answers1

0

You will have to restructure the query or focus it in another way to implement it with criteriabuilder, since among other things it does not allow a query in the From clause.

Regarding the functions you can use them using the criteriabuilder function method (CriteriaBuilder function method)

JLazar0
  • 1,257
  • 1
  • 11
  • 22
  • could you please elaborate the approach with any example – Sanjib Pramanick Nov 25 '20 at 06:20
  • I don't think it is the best option for me to restructure the query, I can tell you what cannot be done with JPA and once you have it we will see how to implement it with CriteriaBuilder, from the query that you indicate the only thing that cannot be used is a query in the from and join clauses. It would not have the most optimal performance, but to give you an idea, without knowing the model or what you want to do, I would make a query on table1, and in its select clause I would add 3 subqueries to count the messages. – JLazar0 Nov 25 '20 at 08:46
  • In that case could you please provide alternate approach to run the native query and get data using result transformation without using deprecated method? – Sanjib Pramanick Nov 25 '20 at 09:59
  • What deprecated method? – JLazar0 Nov 25 '20 at 10:59
  • https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection – JLazar0 Nov 25 '20 at 13:48