JPA Criteria API - Setup Joins and Fetch data from Joined Entity

Question

I have two entities Foo and FooDerivedData, described below.

Entity Foo:

@Entity
@Table(name="FOO")
public class Foo {

// skipping property declarations

@OneToOne
@PrimaryKeyJoinColumn(name = "FOO_ID")
@Fetch(FetchMode.JOIN)
public getFooDerivedData() {
    return fooDerivedData;
}

Entity FooDerivedData

@Entity
@Table(name = "FOO_DERIVED_VW")
@Immutable
public class FooDerivedData {

    @Id
    @Column(name = "FOO_ID")
    long fooId;

    @Column(name = "FOO_COUNT")
    private Integer fooCount;

    @Column(name = "FOO_SUM")
    private BigDecimal fooSum;


    // Getters left out here

I am working with the JPA Criteria API (Hibernate is the JPA implementation) and I need the instance of the FooDerivedData to be loaded in Foo. This is fetched eagerly by default, but I want it fetched using an inner join, not a separate select statement for every instance of FooDerivedData. I was successfully able to add the inner join against FooDerivedData.

CriteriaQuery<Foo> criteriaQuery = criteriaBuilder.createQuery(Foo.class);
Root<Foo> root = criteriaQuery.from(Foo.class);
root.join("fooDerivedData");
Predicate p = getPredicate(); // details omitted here
criteriaQuery.select(root).where(p);

Although I can see from the SQL in the logs that the inner join was added for FOO_DERIVED_VW, none of the columns are selected. For every instance of Foo, I still need to have a separate select statement to get the data I have readily available.

Currently the query is something like this:

select f.foo_id, f.foo_bar from from FOO f
inner join FOO_DERIVED_VW d on d.foo_id = f.foo_id;
select d.foo_id, d.foo_count, d.foo_sum from FOO_DERIVED_VW d where foo_id = ?;
select d.foo_id, d.foo_count, d.foo_sum from FOO_DERIVED_VW d where foo_id = ?;

Note that I am not selecting any data from the joined FOO_DERIVED_VW and this data is being fulfilled by additional database hits. I want the query to be like this:

select f.foo_id, f.foo_bar, d.foo_count, d.foo_sum from from FOO f
inner join FOO_DERIVED_VW d on d.foo_id = f.foo_id;

How do I select the properties from a joined entity in the Criteria API? Can I instruct javax.persistence.criteria.Root to select the attributes I am joining?

I have attempted changing from a join to a fetch, or a fetch where I specified the join type. In either case, it's failing with a Hibernate QueryException.

Root<Foo> root = criteriaQuery.from(Foo.class);
root.fetch("fooDerivedData");

Caused by: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list

Answer 1

I think you have a couple of options here. If your where conditions do not use any columns from the joined tables, then the fetch you tried should work as follows:

CriteriaQuery<Foo> criteriaQuery = criteriaBuilder.createQuery(Foo.class);
Root<Foo> root = criteriaQuery.from(Foo.class);
root.fetch("fooDerivedData");
Predicate p = getPredicate(); // details omitted here
criteriaQuery.select(root).where(p);

If your predicate does include columns from joined tables then you can join as you have done (fetch won't work for this currently) and then also specify a dynamic entity graph (starting with JPA 2.1) by adding it to what you had:

CriteriaQuery<Foo> criteriaQuery = criteriaBuilder.createQuery(Foo.class);
Root<Foo> root = criteriaQuery.from(Foo.class);
root.join("fooDerivedData");
Predicate p = getPredicate(); // details omitted here
criteriaQuery.select(root).where(p);
EntityGraph<Foo> fetchGraph = entityManager.createEntityGraph(Foo.class);
fetchGraph.addSubgraph("fooDerivedData");
EntityManager.createQuery(criteriaQuery).setHint("javax.persistence.loadgraph", fetchGraph);