Hibernate is not initialising nested entities when using NamedNativeQuery

Question

The challenge:

I am trying to batch fetch a collection of nested entities from my database. The resulting dataset ranges in the thousands of entities so my approach is to fetch entities in a paged fashion based on this post. The data is fetched from a web-based frontend and the pure un-paged query takes up to 10 seconds (unacceptable).

The problem:

The "parent" entities are fetched correctly, however the "child" entities are seemingly not fetched. In the resulting list of entities from TestRepository.getRankedTests(...), the list of "child" entities is not initialised and accessing them will result in a LazyInitializationException. This points in the direction of an issue with my SqlResultMapping, but I am not able to see the mistake. I've tried to inject errors into my SqlResultMapping for the child, and it causes hibernate to complain at runtime, so it seems to try to map my config to the properties of the child entity, though the uninitialised collection of child entities dumbfound me.

Parent entity (Test.kt):

@NamedNativeQuery(
    name = "Test.getRankedTests",
    query = """
        select *
        from (
            select
                *,
                DENSE_RANK() over (
                    order by "o.id"
                ) rank
            from (
                select
                    o.id as "o.id",
                    o.version as "o.version",
                    a.id as "a.id",
                    a.organisation_id as "a.organisation_id",
                    a.type as "a.type"
                from  organisation o
                left join address a on o.id = a.organisation_id
                order by o.organisation_number
            ) o_a_an_c
        ) o_a_an_c_r
        where o_a_an_c_r.rank > :min and o_a_an_c_r.rank <= :max
        """,
    resultSetMapping = "TestMapping"
)
@SqlResultSetMapping(
    name = "TestMapping",
    entities = [
        EntityResult(
            entityClass = Test::class,
            fields = [
                FieldResult(name = "id", column = "o.id"),
                FieldResult(name = "version", column = "o.version"),
            ]
        ),
        EntityResult(
            entityClass = TestChild::class,
            fields = [
                FieldResult(name = "id", column = "a.id"),
                FieldResult(name = "organisation", column = "a.organisation_id"),
            ]
        ),
    ]
)
@Entity
@Table(name = "organisation")
class Test(
    @Id
    val id: Long,
    val version: Long,
    @OneToMany(mappedBy = "organisation", cascade = [CascadeType.ALL], orphanRemoval = true)
    val addresses: List<TestChild>,
)

Child entity (TestChild.kt):

@Entity
@Table(name = "address")
@Suppress("LongParameterList")
class TestChild(
    @Id
    val id: Long,
    @ManyToOne(fetch = FetchType.LAZY)
    val organisation: Test,
)

Repository (TestRepository.kt):

@Repository
interface TestRepository : JpaRepository<Test, Long> {
    fun getRankedTests(
        min: Long,
        max: Long
    ): List<Test>
}

Answer 1

Cudos to Christian Beikov for a good proposal. The missing link here was the ResultTransformer. Since the native query will end up with both the parent and child on the same JDBC row, we will end up with an object array containing both. The ResultTransformer will have the responsibility of mapping that object array back to an entity hierarchy. Here's how I fixed it:

Added a DAO for fetching the results with an entityManager:

@Repository
class Dao(
    @PersistenceContext
    private val entityManager: EntityManager
) {

    fun getRankedTests(): List<Test> =
        entityManager.createNamedQuery("Test.getRankedTests")
            .setParameter("max", 5)
            .setHint(QueryHints.HINT_READONLY, true)
            .unwrap(NativeQuery::class.java)
            .setResultTransformer(TestResultTransformer(entityManager))
            .resultList.filterIsInstance(Test::class.java)
}

Created the following ResultTransformer:

class TestResultTransformer(private val entityManager: EntityManager) : BasicTransformerAdapter() {
    override fun transformList(
        list: List<*>
    ): List<Test> {
        val identifiableMap: MutableMap<Long, Test> = mutableMapOf()
        for (entityArray in list) {
            if (entityArray is Array<*>) {
                var test: Test? = null
                var testChild: TestChild? = null
                for (tuple in entityArray) {
                    entityManager.detach(tuple);
                    when (tuple) {
                        is Test -> test = tuple
                        is TestChild -> testChild = tuple
                        else -> {
                            throw UnsupportedOperationException(
                                "Tuple " + tuple?.javaClass + " is not supported!"
                            )
                        }
                    }
                }
                if (test != null) {
                    val key = test.id
                    if (!identifiableMap.containsKey(key)) {
                        identifiableMap[key] = test
                        test.addresses = mutableListOf()
                    }
                    if (testChild != null) {
                        test.addresses.add(testChild)
                    }
                }
            }
        }
        return identifiableMap.values.toList()
    }
}

Answer 2

AFAIK, fetching collections is not possible through the JPA result set mapping annotations. There are Hibernate specific APIs to do this though if you want, which would look similar to this:

SQLQuery q = session.createNativeQuery(...);
q.addRoot("o", Test.class)
 .addProperty("id", "o.id")
 .addProperty("version", "o.version");
q.addFetch("a", "o", "addresses")
 .addProperty("id", "a.id")
 .addProperty("organisation", "a.organisation_id");

But if you just want efficient pagination, I would recommend you look into Blaze-Persistence which comes with a specialized implementation and spring-data integration that just works:

@Repository
interface TestRepository : JpaRepository<Test, Long> {
    @EntityGraph("addresses")
    fun getRankedTests(
        pageable: Pageable
    ): Page<Test>
}