| Join on concatenated columns [message #759871] |
Wed, 30 November 2011 07:19  |
Eclipse User |
|
|
|
I have two tables in an Oracle database that I need to join, but the ID column in the one table equals two columns in the other table (FILE and NUMBER).
So table A have values FILE = "123-ABC/100" and NUMBER = 8 which would be joined by table B where ID = "123-ABC/100.8".
This SQL query works fine:
SELECT * FROM A
JOIN B ON B.ID = A.FILE || '.' || A.NUMBER
But I can't figure out how to do it with JPA. This is what I got at the moment:
@Entity
public class A {
@Id
private String id;
@OneToMany(mappedBy = "a")
private List<B> bs;
}
@Entity
public class B {
@Id
private String id;
@ManyToOne
@JoinColumn(name = "ID", referencedColumnName = "FILE || '.' || NUMBER", insertable = false, updatable = false)
private A a;
But this gives me a QueryException:
Exception [EclipseLink-6094] (Eclipse Persistence Services - 1.1.4.v20100812-r7860): org.eclipse.persistence.exceptions.QueryException
Exception Description: The parameter name [FILE || '.' || NUMBER] in the query's selection criteria does not match any parameter name defined in the query.
Query: ReadAllQuery(name="bs" referenceClass=B sql="SELECT B FROM B WHERE (ID = ?)")
at org.eclipse.persistence.exceptions.QueryException.parameterNameMismatch(QueryException.java:1012)
...
Is there any way to solve this?
|
|
|
| Re: Join on concatenated columns [message #760231 is a reply to message #759871] |
Thu, 01 December 2011 11:24  |
| Eclipse User |
|
|
|
Hello,
From what you are asking, it looks like A.FILE || '.' || A.NUMBER is the foreign key to B.ID, which means you have the mappings backwards. A would have a OneToOne to B, not the other way around. I assume that you would also have basic mappings for A.FILE and A.NUMBER.
Unfortunately what you are after is not possible in pure JPA - I am surprised you are not getting an exception or warning from Eclipselink elsewhere, as it should expect the "FILE || '.' || NUMBER" as a single field within the A table ie: "A.FILE || '.' || NUMBER".
It might be possible in EclipseLink to modify the mapping's selection criteria using customizers and make it read only. But I would recommend you not map the A<->B relationships and instead use a query when you want to access the associations:
"Select a, b from A a, B b where concat(a.file, concat('.', a.number)) = :bid"
which will return all A objects with its B object or
"Select a from A a where concat(a.file, concat('.', a.number)) = :bid"
and pass in the bid as a parameter for a particular B's As.
If you decide to go the mapping modification route, see http://wiki.eclipse.org/EclipseLink/Examples/JPA/MappingSelectionCriteria
for information on setting selection criteria on a mapping, and http://wiki.eclipse.org/Introduction_to_EclipseLink_Expressions_(ELUG)
on building the expressions needed by the mapping.
Best Regards,
Chris
|
|
|
| Re: Join on concatenated columns [message #760233 is a reply to message #759871] |
Thu, 01 December 2011 11:24 |
| Eclipse User |
|
|
|
Hello,
From what you are asking, it looks like A.FILE || '.' || A.NUMBER is the foreign key to B.ID, which means you have the mappings backwards. A would have a OneToOne to B, not the other way around. I assume that you would also have basic mappings for A.FILE and A.NUMBER.
Unfortunately what you are after is not possible in pure JPA - I am surprised you are not getting an exception or warning from Eclipselink elsewhere, as it should expect the "FILE || '.' || NUMBER" as a single field within the A table ie: "A.FILE || '.' || NUMBER".
It might be possible in EclipseLink to modify the mapping's selection criteria using customizers and make it read only. But I would recommend you not map the A<->B relationships and instead use a query when you want to access the associations:
"Select a, b from A a, B b where concat(a.file, concat('.', a.number)) = :bid"
which will return all A objects with its B object or
"Select a from A a where concat(a.file, concat('.', a.number)) = :bid"
and pass in the bid as a parameter for a particular B's As.
If you decide to go the mapping modification route, see http://wiki.eclipse.org/EclipseLink/Examples/JPA/MappingSelectionCriteria
for information on setting selection criteria on a mapping, and http://wiki.eclipse.org/Introduction_to_EclipseLink_Expressions_(ELUG)
on building the expressions needed by the mapping.
Best Regards,
Chris
|
|
|
Powered by
FUDForum. Page generated in 0.04889 seconds
Home