Hibernate joined table limit result

Question

I've got two tables A and B. I execute LEFT OUTER JOIN on it and works fine. I tried to limit rows number by table A, so for me when I ask for 5 rows I want 5 rows from table A joined with no matter how many rows from table B.

Just like:

select * from (select * from A where rownum < ? ) a left outer join B b on a.id=b.id;

I've tried to use hibernate criteria API, and methods setMaxResult on joined criteria, but what hibernate does is:

select * from( select * from A a left outer join B b on a.id=b.id) where rownum < ?;

JPA/hibernate doesn't support subqueries in from clauses, any idea how to reach this result?

Michal

actually your query wont work in sql either, your subquery suposes to return a table name, what does not seem to be true — Jordi Castilla, Sep 09 '15 at 09:34
I don't... but you can make me a believer and improve your question to easier get an answer with a [MCVE](http://stackoverflow.com/help/mcve) :) — Jordi Castilla, Sep 09 '15 at 12:50

score 0 · Answer 1 · answered Sep 09 '15 at 10:03

0

One possible option, I can think of is using Hibernate native SQL:

 hibernateSession.createSQLQuery("select * from( select * from A a left outer join B b on a.id=b.id) where rownum < ?");
 //Rest of the code

answered Sep 09 '15 at 10:03

Vasu

21,832
11
51
67

Hibernate joined table limit result

1 Answers1