CODEWITHSUNDEEP
hibernatehibernate-criteria
gene b.
gene b.

Reputation: 12026

Hibernate Criteria Query with Join - Avoid Full Selection from 2nd Table

I'm using Hibernate Criteria to create a Join. My problem is the resulting SQL selects the full set of columns from both tables (1 & 2). But I actually only need the 1st table, without the 2nd table.

        Criteria cr = sessionFactory.getCurrentSession().createCriteria(Publication.class,"p");
        // Join on REQUESTS_T, 2nd Table
        Criteria requestCriteria = cr.createCriteria("request");

        // Restrict by Table 1 column
        cr.add(Restrictions.isNotNull("p.someCol"));
        // Also restrict by Table 2 columns
        ArrayList<Integer> eligibleStatusIds = new ArrayList<Integer>();
        eligibleStatusIds.add(19);
        eligibleStatusIds.add(20);
        requestCriteria.add(Restrictions.in("status.id", eligibleStatusIds));

        List<Publication> results = cr.list();

Note the SQL:

select
    this_.ID as ID1_9_1_,
    this_.CREATED_BY_ID as CREATED_2_9_1_,
    this_.LAST_CHANGED_BY_ID as LAST_CHA3_9_1_,
    // ...etc.
    // ...FULL Table 1

    request1_.ID as ID1_16_0_,
    request1_.TITLE as TITLE3_16_0_,
    // ...etc.
    // FULL Table 2
from
    PUBLICATIONS_T this_ 
inner join
    REQUESTS_T request1_ 
        on this_.REQUEST_ID=request1_.ID 
where
    this_.SOME_COL is not null 
    and request1_.STATUS_ID in (
        19, 20
    )

What I need, with the Criteria syntax, is this:

select p.*   // P Only
from Publications p, Request r 
where p.request.id = r.id and ... // restrictions

Upvotes: 1

Views: 154

Answers (1)

wi2ard
wi2ard

Reputation: 1555

I suggest you use a (named) entity graph: jpa entity graphs

jpa named entity graphs

tutorial with examples

This JPA approach lets you define exactly what fields to fetch from the entities per query, ignoring the fetching strategy defined in the entity class. You can then keep your Criteria as it is and just pass the graph with the fields you want to fetch as a hint

Upvotes: 1

Related Questions