Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Join on concatenated columns
Join on concatenated columns [message #759870] Wed, 30 November 2011 07:19
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?

[Updated on: Wed, 30 November 2011 07:19]

Report message to a moderator

Previous Topic:has no primary key specified. It should define either an @Id, @EmbeddedId or an @IdClass
Next Topic:Logging JPQL
Goto Forum:
  


Current Time: Thu Apr 17 00:10:35 EDT 2014

Powered by FUDForum. Page generated in 0.02967 seconds