This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
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


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: 61315
    
  66

Please do not post the same question more than once.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: how to write the jpql to query against the one to many table