[FIXED] Spring Data JPA load lazy associations using native query

Issue

I am querying an entity by using a Postgres specific JSON operator (like @>). To achieve that, the query needs to be decorated with @Query(nativeQuery = true). My plan is to fetch the abovementioned entity along with some of its lazy associations. If I were using JPQL I'd simply JOIN FETCH the entity associations, but in this case I can't.

Please share some suggestions.. thanks

Solution

With plain Spring Data, you are out of luck because it is simply so limited. You have to use Hibernate APIs to achieve what you want with a native query. There you can specify for a query that you want to materialize some columns for the association of an entity e.g.

List<Entity1> list = session.createNativeQuery("select {t1.*}, {t2.*} from tbl1 t1 join tbl2 t2 ...", Entity1.class)
    .addEntity("t1", Entity1.class)
    .addJoin("t2", "t1", "associationName")
    .getResultList();

Alternatively, you can introduce a custom SQLFunction to model this PostgreSQL operator and still continue to use JPQL/HQL. See a similar answer for supporting array functions in PostgreSQL: HQL - Check if an Array contains a value

Answered By - Christian Beikov
Answer Checked By - Cary Denson (JavaFixing Admin)