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?
[Updated on: Wed, 30 November 2011 12:19]
Report message to a moderator
] 
Search
Help
Register
Login
Home
