Hibernate: Oracle join with (+) on independent tables

Question

Basically here's the prob:

I can't change existing queries with "(+)" joins
I need to use Hibernate.
I can't put an association between these independent tables.

What I got so far is to use

entityMgr.createNativeQuery("my join (+) query here");

I then store it in a generic

List<Object> list = query.getResultList()

And get column values via

    for(Object obj: list) {
       firstColVal = obj[1]
       ...
    }

Question: Is this the best option I've got? Are there better solutions?

Edit: You guys can suggest a more fitting ORM framework than Hibernate :)

The query will work this way, but in such situation I don't get the point of using Hibernate... — Adam Dyga, Jul 30 '13 at 11:55
@AdamDyga, good point but we need Hibernate for proper transaction handling, persisting etc. We just can't use hibernate for joins (as of now). — Rey Libutan, Jul 30 '13 at 12:10

score 0 · Accepted Answer · answered Jul 30 '13 at 11:52

0

The oracle join operation that you can not remove from your query is deprecated as it was introduced before ANSI standard.

That operator is different from typical join, but the main difference is that you can do less with it then with ANSI notation.

A solution for you might be to create a view on database that will be mapped in code. But your solution is still valid. You can additionally add a result transformer, that will produce valid tuple for result.

answered Jul 30 '13 at 11:52

Damian Leszczyński - Vash

30,365
9
60
95

It's not deprecated - still available. It's not _that_ different; it works in the same way see http://stackoverflow.com/questions/1193654/difference-between-oracles-plus-notation-and-ansi-join-notation – Ben Jul 30 '13 at 11:54
@Bem, Oracle recommendation is to not use it in new code, and for sure woks different. As it is applied only in where clause and it do not operate on the block. There fore it has its limitation, you can create a ANSI query that works as (+) but not vice versa. – Damian Leszczyński - Vash Jul 30 '13 at 11:59
@Ben, see outer join section http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm – Damian Leszczyński - Vash Jul 30 '13 at 12:00
@Ben: it **is** "deprecated". From the [manual](http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm#i2054062): "*Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator*" – Jul 30 '13 at 12:43
I don't know how to integrate "result transformer" because I have a javax.persistence.Query and .addResultTransformer is only for org.hibernate.Query – Rey Libutan Jul 31 '13 at 06:48

Hibernate: Oracle join with (+) on independent tables

1 Answers1