Returning result from 3 tables using JPA criteria

Question

I have the following situation:

@Entity
public class Period
{
    String Name;
}

@Entity
public class Bill
{
    Period period;

    @OneToMany(mappedBy = "bill", fetch = FetchType.LAZY)
    private List<Entry> entry = new ArrayList<Entry>(0);
}

@Entity
public class Entry
{
    @NotNull
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "BILL_ID", nullable = false)
    Bill bill;

    String text;

    BigDecimal amount;
}

So what I need is to fetch all the data in a single query, either with the root being the Bill or the Entry using JPA 2.0 criteria (with Hibernate behind). I've read few posts about this problem HERE and HERE and it seems that I can't use subqueries in the result or fetch data two levels deep.

EDIT: To make my problem more clear: When I use Entry as root, I can't fetch Period and when I use Bill as root I can't fetch all other tables in Entry. Also I can't use eager fetch because there are other use cases that need those tables.

Are there any other ways to do this?

Thanks!

It would help if you could also post the HQL or SQL for what you are trying to accomplish. — Tim Biegeleisen, Jun 24 '15 at 04:50
If you change fetch type to `eager`, it will fetch it, depending on your HQL — Naman Gala, Jun 24 '15 at 04:57
I have different use cases for these table, I can't use `earger` here. — danizmax, Jun 24 '15 at 05:10

JB Nizet · Accepted Answer · 2015-06-24T06:30:16.687

0

To fetch data from association, you use left join fetch clauses:

select distinct b from Bill b 
left join fetch b.period 
left join fetch b.entry
where b...

or

select distinct e from Entry e 
left join fetch e.bill b 
left join fetch b.period
where e...

Regarding Criteria, its fetch() method returns a Fetch, which itself has a method fetch() returning a Fetch(), which itself has a method fetch() returning a Fetch, etc. So yes, its supports as many levels you want.

edited Jun 24 '15 at 06:30

answered Jun 24 '15 at 05:05

JB Nizet

678,734
91
1,224
1,255

But how do I fetch `Period` from `Bill` when my root is `Entry`? – danizmax Jun 24 '15 at 05:13
Please read the whole question. Also I need type safe JPA criteria. – danizmax Jun 24 '15 at 05:29
Please try something on your own. Criteria also has fetch. You just need to translate the above queries. – JB Nizet Jun 24 '15 at 05:36
The point that I'm making above is, that JPA does not seem to have the ability to fetch any deeper than one level, in your example `e.b.period` the `period` attribute is second lvl. – danizmax Jun 24 '15 at 05:44
JPA is not hibernate and your example is not criteria, its HQL. – danizmax Jun 24 '15 at 05:49
It's JPQL. JPQL is part of JPA. Criteria's fetch() method returns a Fetch, which itself has a method [fetch()](http://docs.oracle.com/javaee/6/api/javax/persistence/criteria/FetchParent.html) returning a Fetch(), which itself has a method fetch() returning a Fetch, etc. So yes, its supports as many levels you want. – JB Nizet Jun 24 '15 at 05:57
That's a great find, thanks. Can you please add last comment info to the answer so I can accept it. regards – danizmax Jun 24 '15 at 06:20

Returning result from 3 tables using JPA criteria

1 Answers1