aspose file tools*
The moose likes Object Relational Mapping and the fly likes Problem when execute the JPQL with Map Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Problem when execute the JPQL with Map" Watch "Problem when execute the JPQL with Map" New topic
Author

Problem when execute the JPQL with Map

Peter Saw
Greenhorn

Joined: Jun 20, 2007
Posts: 10
I am new to JPA and JPQL. If can't explain the problem clear I will try it again.

I have 2 tables Customer and Contact
It is one to many relationship. That means one customer has multiple contacts.

Part of Customer Entity:
@OneToMany(cascade = CascadeType.REFRESH, mappedBy="customer", fetch = FetchType.EAGER)
@MapKey(name="fieldName")
private Map<String, 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;



The JPQL is,
select c from Customer c where c.contacts['home'].fieldValue='1234'


But I got the error message,

Hibernate:
select
....
from
Customer customer0_,
Contact contact1_
where
customer0_.id=contact1_.CUSTOMER_ID
and contact1_.null = 'home'
and contact1_.FIELD_VALUE='1234'
ERROR [main][] org.hibernate.util.JDBCExceptionReporter - Incorrect syntax near the keyword 'null'.



I find the mapkey is not translated to column name
contact1_.null = 'home'.
It should be contact1_.field_name = 'home'

Did I missed something here?
The database is MS SQL.


Since some problem for this MapKey, I am thinking to change the Map to Set.
Part of Customer Entity:
@OneToMany(cascade = CascadeType.REFRESH, mappedBy="customer", fetch = FetchType.EAGER)

private Set<Contact> contacts;


If use Set instead of Map, what's this JPQL should be?
select c from Customer c where c.contacts['home'].fieldValue='1234'
Arun Kumarr
Ranch Hand

Joined: May 16, 2005
Posts: 513

Peter, You might want to read this.
You can change your name here. Thank you.

If you are not laughing at yourself, then you just didn't get the joke.
Waswani Naresh
Ranch Hand

Joined: May 01, 2008
Posts: 66
Hi Peter,

There are 2 options to solve this issue.

Option 1) Replace the javax.persistence.MapKey with org.hibernate.annotations.MapKey. Please note that Hibernate MapKey is depricated.

Option 2) Instead of javax.persistence.MapKey use javax.persistence.MapKeyColumn.

Let me know if you face any issues while applying above solutions.

Regards,
Naresh Waswani


Naresh Waswani
Peter Saw
Greenhorn

Joined: Jun 20, 2007
Posts: 10
Hi Naresh Waswani,
Thank you for your help. I tried the option 1, it works.
For option 2, I can't find this class javax.persistence.MapKeyColumn in my Eclipse. Maybe the hibernate library I am using is not updated.

Then I can use org.hibernate.annotations.MapKey at this moment.
But any reason the problem with javax.persistence.MapKey?
 
 
subject: Problem when execute the JPQL with Map