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??

score 1 · Accepted Answer · edited May 23 '17 at 12:28

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

edited May 23 '17 at 12:28

Community

1
1

answered Oct 22 '13 at 08:08

kostja

60,521
48
179
224

I list the 3 results (msg.targetGroup, msg.customer, msg.template), will I get the paramters from the SentMessage table too? Or should I write it like: `SELECT msg, msg.targetGroup, msg.customer, msg.template FROM SentMessage msg ` – victorio Oct 22 '13 at 08:11
and how to search in the WHERE place if I want to check a parameter from the Customer table? Maybe I want to search for the Customer's name, how should I write it in the WHERE? – victorio Oct 22 '13 at 08:16
@victorio I hope I understood you correctly. Please take a look at the updated answer. – kostja Oct 22 '13 at 08:17
@victorio - please dont just pile up follow up questions in comments. Check if your original request has been resolved and return with some feedback. Once the initial issue is solved, open a new thread for further questions. – kostja Oct 22 '13 at 08:26
Almost! But what if I want to search for the customer's name in the WHERE, and search for the name of the TargetGroup (also in the WHERE)?? – victorio Oct 22 '13 at 08:27
@victorio I hope the new edit will clarify ht eissue – kostja Oct 22 '13 at 10:59
perfect, thank you! :) maybe you could answer my new question too: http://stackoverflow.com/questions/19517502/why-isnt-my-entity-table-mapped-in-jpa-hibernate – victorio Oct 22 '13 at 12:10

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

1 Answers1