How to write an SQL SELECT in JPA with 3 JOINs?

Question

I have entities:

@Entity(name = "sent_message")
public class SentMessage extends AbstractEntity {
    @ManyToOne(fetch = FetchType.LAZY)
    private TargetGroup targetGroup;
    @ManyToOne(fetch = FetchType.LAZY)
    private Customer customer;
    @ManyToOne(fetch = FetchType.LAZY)
    private MessageTemplate template;
    private Date sentDate;
    getter.setter...
}

@Entity(name = "target_group")
public class TargetGroup extends AbstractEntity {
    private String name;
    private String description;
    @ManyToMany(fetch = FetchType.LAZY)
    private List<Customer> customers = new ArrayList<>();
    getter.setter...
}

@Entity(name = "customer")
public class Customer extends AbstractEntity {
    private String name;
    private String email;
    private String mobile;
    @ManyToMany(mappedBy="customers", fetch = FetchType.LAZY)
    private List<TargetGroup> targetGroups = new ArrayList<>();
    @OneToMany(mappedBy = "customer", fetch = FetchType.LAZY)
    private List<SentMessage> sentMessages = new ArrayList<>();
    getter.setter...
}

@Entity(name = "message_template")
public class MessageTemplate extends AbstractEntity {
    private String subject;
    private String text;
    @OneToMany(mappedBy = "template", fetch = FetchType.LAZY)
    private List<SentMessage> sentMessages;
    getter.setter...
}

I want the TargetGroup, Customer, MessageTemplate to be the result of the query. My SELECT:

SELECT msg FROM SentMessage msg JOIN msg.targetGroup tg, msg.customer cust, msg.template temp WHERE ....

Is that correct, or how should I write it? I want all the parameters from all entities. Thank you!

EDIT: I want to search by the name of the TargetGroup, the name of the Customer, and others, how should I write it in the WHERE??

Answer 1

You have to list the results you want in the select clause of the query:

SELECT msg.targetGroup, msg.customer, msg.template FROM SentMessage msg 

You will get an Object[] containing the data.

If you are experiencing problems due to lazy fetching, you can add fetch joins to retrieve the relating entities:

SELECT msg.targetGroup, msg.customer, msg.template FROM SentMessage msg join fetch msg.targetGroup tg join fetch  msg.customer cust join fetch msg.template temp

EDIT Your comment suggests that you also need the 'parent' message in your results. In this case, it does not make sense to create a projection query (listing all the attributes in the select clause). Just fetch the parent message and call the regular getters to access the Customer, TargetGroup and other associations. Use fetch joins if problems with lazy fetching arise.

SELECT msg FROM SentMessage msg 

or

SELECT msg FROM SentMessage msg join fetch msg.targetGroup tg join fetch  msg.customer cust join fetch msg.template temp

EDIT 2 For adding constraints to your query, you can add regular where clauses referencing any id variable:

... join fetch  msg.customer cust where cust.name = :cust_name... 

However the behaviour of the fetch join together with where clauses is a bit unexpected - the fetch is restricted as well by the where clause. See this thread for reference