Hibernate subselect fetching of association, how to pass parameters?

Question

We have a class A that has two levels of nested associations as follows:

@Entity
@NamedQuery("select a from A JOIN FETCH b");
public class A {
     @OneToOne(cascade = CascadeType.ALL, optional = true)
     @JoinColumn(name = "XXX")
     private B b;
}

@Entity
public class B {
     @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
     @JoinColumn(name = "XXX")
     @Fetch(FetchMode.SUBSELECT)
     private List<B> details = new ArrayList<>(); 
}

@Entity
public class C {
  ...
}

Now for a report query we are fetching all A records, so I do a two-step query first I use pagination using setMaxResults and setFirstResult and then use the IDs of fetched objects to fire the second query where I want the entire object graph for the paginated object IDs. Now since Hibernate does not seem to allow to SUBSELECT for OneToOne, to fetch records of B, I resorted to JOIN FETCH (would prefer SUBSELECT here also if anyone knows how to do it, please share) but table C is much larger and I want to use SUBSELECT than join. Without the SUBSELECT, it fires many queries but uses the single IN parameter I specify and passes. But with SUBSELECT, it seems to expect the same IN parameter again,

as in real SQL queries are like

select a.id from a limit ?  (fetch IDs by pagination)
select a.*, b.* from a inner join b on a.b_id=b.id where a.id in (?, ?)
select c.* from c where c.id in (select c_id from b where b.id in (Select b_id from a where id in (?, ?)))  - (this is fired by the SUBSELECT,  how to resend same named parameter say :ids a second time for this scenario?)

My code is like this:

List<Long> ids = fetchIds(startPos, maxRecords);
TypedQuery<A> query =em.createNamedQuery(A.NAMED_QUERY, A.class);
query.setParameter("ids", ids);

Assume that the fetchIds fetches the IDs in first step by pagination as described (no issues there).

Thanks,
Paddy

Hibernate subselect fetching of association, how to pass parameters?

0 Answers0