File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes how to write the jpql to query against the one to many table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Reply locked New topic
Author

how to write the jpql to query against the one to many table

Peter Saw
Greenhorn

Joined: Jun 20, 2007
Posts: 10
I asked the question in my last post but not sure why no reply.
So I post it again as an standalone question.

I have 2 tables Customer and Contact. One customer can have multiple contacts.
So the relationship is one to many.

Part of Customer Entity:
@OneToMany(cascade = CascadeType.REFRESH, mappedBy="customer", fetch = FetchType.EAGER)
private Set<Contact> contacts;

Part of Contact Entity:
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "customer_ID")
private Customer customer;

@Column(name = "FIELD_NAME")
@NotNull
private String fieldName;

@Column(name = "FIELD_VALUE")
@Length(max = 100, message = "Maximum length allowed for user defined field is 100")
private String fieldValue;

Now I want to search out the customers based on the contact criterias.
For example, the criteria is fieldName='home number' and fieldValue='1234'

How to write such JPQL?
It could looks like,
select c from Customer c where c.contacts.fieldName='home number' and c.contacts.fieldValue='1234'
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61761
    
  67

Please do not post the same question more than once.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to write the jpql to query against the one to many table