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'