Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Join on concatenated columns
Join on concatenated columns [message #759871] Wed, 30 November 2011 12:19 Go to next message
Matti Hansson is currently offline Matti Hansson
Messages: 68
Registered: July 2009
Member
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 16:24 Go to previous message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1034
Registered: July 2009
Senior Member
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 16:24 Go to previous message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1034
Registered: July 2009
Senior Member
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
Previous Topic:How to get a list of active CacheCoordination members?
Next Topic:Server startup FileNotFoundException
Goto Forum:
  


Current Time: Wed Oct 22 13:53:11 GMT 2014

Powered by FUDForum. Page generated in 0.01676 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software