JPQL HQL query operating on top of class hierarchy with joined inheritance strategy and applying DTO projection

Question

Given I have following class hierarchy with joined inheritance strategy:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class Notification {

    protected Long id;
    protected Long code;

    protected Notification() {
    }
}

@Entity
@PrimaryKeyJoinColumn(name = "NOTIFICATION_ID")
public class Sms extends Notification {

    private String phoneNumber;
    private String smsText;

    public Sms() {
    }
}

@Entity
@PrimaryKeyJoinColumn(name = "NOTIFICATION_ID")
public class Push extends Notification {

    private String application;
    private String pushText;
    
    public Push() {
    }
}

How can I write JPQL / HQL query which will return List<NotificationDetails> where NotificationDetails is:

public class NotificationDetails {

    private final String contact;
    private final String content;

    public NotificationDetails(String contact, String content) {
        this.contact = contact;
        this.content = content;
    }
}

where mapping should be as follows:

contact - phoneNumber / application
content - smsText / pushText

score 2 · Accepted Answer · answered Nov 05 '20 at 08:44

2

Hibernate has support for something called implicit subtype property resolving, so you can use a query like the following:

List<NotificationDetails> results = em.createQuery(
   "select new com.your.entities.NotificationDetails(coalesce(n.phoneNumber, n.application),  coalesce(n.smsText, n.pushText)) from Notification n ",
   NotificationDetails.class
).getResultList();

answered Nov 05 '20 at 08:44

Christian Beikov

15,141
2
32
58

Did you test it? The entity `Notification` does not have the phoneNumber, application, smsText, pushText properties and without explicit casting to the `Sms` or `Push` they are not available. – SternK Nov 05 '20 at 12:17
I did this multiple times in the past already, so yes, this works. – Christian Beikov Nov 05 '20 at 16:02

SternK · Answer 2 · 2020-11-03T20:26:54.080

You can try to use the following jpql:

List<NotificationDetails> results = em.createQuery(
   "select new com.your.entities.NotificationDetails( "
 + "  coalesce(s.phoneNumber, p.application), "
 + "  coalesce(s.smsText, p.pushText) "
 + ") from Notification n "
 + "left join Sms s on s.id = n.id "
 + "left join Push p on p.id = n.id ",
 NotificationDetails.class).getResultList();

It works, but it generate quite inefficient sql:

select
  coalesce(sms1_.sms_phone, push2_.push_app) as col_0_0_,
  coalesce(sms1_.sms_text, push2_.push_text) as col_1_0_ 
from TEST_SCHEMA.TST_NOTIFICATION notificati0_ 
left outer join (
   TEST_SCHEMA.TST_SMS sms1_ 
   inner join TEST_SCHEMA.TST_NOTIFICATION sms1_1_
      on sms1_.sms_not_id=sms1_1_.not_id
) on (sms1_.sms_not_id=notificati0_.not_id) 
left outer join (
   TEST_SCHEMA.TST_PUSH push2_ 
   inner join TEST_SCHEMA.TST_NOTIFICATION push2_1_ 
      on push2_.push_not_id=push2_1_.not_id
) on (push2_.push_not_id=notificati0_.not_id)

If performance is important here I guess it would be better to use a native query and map it (for example via @SqlResultSetMapping) to the NotificationDetails dto.

JPQL HQL query operating on top of class hierarchy with joined inheritance strategy and applying DTO projection

2 Answers2