Mapping NativeQuery results when using inner join

Question

I have the follow situation:

@Entity
@XmlRootElement
@Table(name = "TB_A")
public class A implements Serializable {

    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "CD_B")
    private B b;

}

@Entity
@XmlRootElement
@Table(name = "TB_B")
public class B implements Serializable {

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "CD_B")
    @JsonIgnore
    private Set<C> c = new HashSet<>();

}

@Entity
@XmlRootElement
@Table(name = "TB_C")
public class C implements Serializable {

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "CD_B")
    private B b;

}

I need to run the follow code:

String sql = "SELECT * FROM TB_A a "
            + "INNER JOIN TB_B b ON ... "
            + "LEFT JOIN TB_C c ON ... ";

Query query = em.createNativeQuery(sql, A.class);

List<A> AList = query.getResultList();

for(A a : AList) {
    List<c> CList = a.getB().getC();
}

Analising the executed queries, I notice that JPS is running a SELECT each time I access elements B and C.

How can I correctly map this nativeQuery to use the lazyLoad?

• Obs: I MUST use NativeQuery, because in my WHERE clause I need to use an especific function from Oracle. So JPQL is not an option for me (If I could use it, my life would be much more easy!).

Answer 1

Short answer: you can't.

What this line will do:

Query query = em.createNativeQuery(sql, A.class);

Is execute your SQL, then throw away anything not related to A.class.

Then your for loop:

for(A a : AList) {
    List<c> CList = a.getB().getC();
}

Fetches everything again.
Here you use JPA again, which doesn't know anything about the query you executed before.

In some cases, you can try to map to C.class instead.
Then you'll be able to avoid the loop entirely.
If in your case you need both A.class and C.class, you can look into ResultSetMapping instead: http://www.java2s.com/Tutorial/Java/0355__JPA/SqlResultSetMappingForHierarchicalEntity.htm