I am having a native query which joins two entities and returns instances of both of them using SQLResultSetMapping. Now I need to have pagination on it. For that I am using setFirstResult() and setMaxResult() over it.
Now problem is that both the tables have few columns which have exactly same name. So in entities also i have named the attributes matching column names . For example id , sourceType , sourceCode. The query returned is something like
select * from ( select row_.*, rownum rownum_ from ( Select e1.id, e1.sourceCode, e1.sourceType, ..... , e2.id, e2.sourceCode, e2.sourceType ..... from EntityClass1 e1 left outer join EntityClass e2 on ... ) row_ where rownum <= ?) where rownum_ > ?
Now when this query is executed I get the exception "Caused by: java.sql.SQLException: ORA-00918: column ambiguously defined".
Can any one help me how I can resolve this? or the only solution will be to name the attributes differently in the two entity classes?
posted 9 years ago
I was finally able to solve this problem. All i did was follow the rules for creating native query.
There are couple of things I did. As my entity classes were a combination of all possible (complex) cases which can be there in native query it took my almost a day to figure out solution. It had composite embedded id keys, same name of columns , two entities returned in one query etc etc.
I did following
1) Used SQLResultSetMapping for having more than one entity from query.
2) Used alias for duplicate column names in one of the entities.
3) Defined the Composite key mapping in SQLResultSetMapping for one of the entities.