sqlResultSetMapping problem with self join

hsiung · **Posted:** Sun Sep 11, 2005 4:20 am

I have a problem with @SqlResultSetMapping using a native SQL self join:
I get 2 same objects!

ClassA snippet:

Code:

SqlResultSetMapping(name="PairResults", entities={
  @EntityResult(name="com.ideartis.jee.A"),
  @EntityResult(name="com.ideartis.jee.A")})
public class A implements Serializable {
...

SQL snippet:

Code:

select a1.*, a2.* from
  (select * from a ..) as a1,
  (select * from a ..) as a2
where ..

This SQL is a self join with from-clause subqueries. The result is a list of pairs. The elements of a pair are of the same type (class A).
From the semantic of the search I expect the 2 elements to be always different instances. The SQL query from a DB client-GUI shows that this is true (they are 2 different rows).

But when I run the EJB 3.0 SQL-result-to-object mapping the elements are the same (the first element is also at the second position!).

What have I done wrong with the mapping?

Regards
Alain Hsiung

emmanuel · **Posted:** Tue Sep 13, 2005 6:49 pm

You have to define the field mapping explicitly since the second on does not respect the default values.