JPA OneToMany Collection with inheritance limit to specific subclass

Question

I am playing around with hibernate + JPA and I am trying to get a query done to limit the results in a collection. The goal is to limit the query to an inheritance level and therefore not join against all tables.

My code:

    @Entity
    public static class Holder {

        @Id
        String holderId = UUID.randomUUID().toString();

        @OneToMany(cascade=CascadeType.ALL)
        @JoinColumn(name="subclassID")
        List elements;
    }

    @Entity
    @Inheritance(strategy=InheritanceType.JOINED)
    public static class A {

        @Id
        String pkey = UUID.randomUUID().toString();

        String type = "a";

        String subclassID;
        @Override
        public String toString() {
            return "Class A, pkey: " + pkey + " Subclass " + subclassID;
        }
    }

    @Entity
    public static class B extends A {

        String test;

        String type = "b";

        @Override
        public String toString() {
            return "Class B, pkey: " + pkey + " Subclass " + subclassID + " Test: " + test;
        }
    }

    @Entity
    public static class C extends A {
        String ello;

        String type = "c";

        @Override
        public String toString() {
            return "Class C, pkey: " + pkey + " Subclass " + subclassID + " ello: " + ello;
        }
    }

now, with this code:

        EntityManager em = createEntityManagerFactory.createEntityManager();

        em.getTransaction().begin();
        Holder h = new Holder();
        h.holderId = "hid";
        h.elements = new ArrayList<>();
        A e = new A();
        e.subclassID = h.holderId;
        h.elements.add(e);

        B b = new B();
        b.subclassID = h.holderId;
        b.test = "test";
        h.elements.add(b);

        C c = new C();
        c.subclassID = h.holderId;
        c.ello = "Ello";
        h.elements.add(c);

        em.persist(h);
        em.getTransaction().commit();

        em.getTransaction().begin();
        Holder find = em.find(Holder.class, "hid");
        em.getTransaction().commit();


        find.elements.forEach(x -> System.err.println(x));

it will print all subclasses and fetch them correctly. So i get this:

Class A, pkey: 5074ec45-5917-47aa-9ce7-b904fbb78a54 Subclass hid
Class B, pkey: a8afc689-1314-4253-8d20-4751526c0d00 Subclass hid Test: test
Class C, pkey: f57137cc-32fd-473c-8310-0f83c7dad2ed Subclass hid ello: Ello

This, since I have a joined strategy, has to join all subclass tables which could be quite a lot at some point.

Now most of the times I know what kind of subclass I am querying for, e.g. I want only A, or A and B, but not C.

I am struggling to figure out how to create a query that will take that into consideration and not join the subclasses that are not required. The subclass has a "type" property that identifies the different subclasses (See A) if that helps.

Let me know if you need more info,

Thanks!

EDIT:

I tried doing the approach suggested and here are the results:

Query by type.

This does not work. The lazy fetching still queries all the objects in my list regardless of what fetch strategy (lazy or eager) I choose. Code:

Query xx = em.createQuery("SELECT h FROM TestApp$Holder h LEFT JOIN h.elements e where TYPE(e) = TestApp$B", Holder.class);

    System.out.println(" -- -- -- -- -");
    List resultList = xx.getResultList();
    resultList.stream().forEach(x -> {
        x.elements.forEach(x2 -> System.err.println(x2));
    });

Output:

14:17:44.466 [main] DEBUG org.hibernate.SQL - select testapp_ho0_.holderId as holderId1_0_ from holder testapp_ho0_ left outer join TestApp$A elements1_ on testapp_ho0_.holderId=elements1_.subclassID left outer join TestApp$B elements1_1_ on elements1_.pkey=elements1_1_.pkey left outer join TestApp$C elements1_2_ on elements1_.pkey=elements1_2_.pkey where case when elements1_1_.pkey is not null then 1 when elements1_2_.pkey is not null then 2 when elements1_.pkey is not null then 0 end=1
Hibernate: select testapp_ho0_.holderId as holderId1_0_ from holder testapp_ho0_ left outer join TestApp$A elements1_ on testapp_ho0_.holderId=elements1_.subclassID left outer join TestApp$B elements1_1_ on elements1_.pkey=elements1_1_.pkey left outer join TestApp$C elements1_2_ on elements1_.pkey=elements1_2_.pkey where case when elements1_1_.pkey is not null then 1 when elements1_2_.pkey is not null then 2 when elements1_.pkey is not null then 0 end=1
14:17:44.468 [main] DEBUG org.hibernate.loader.Loader - Result set row: 0
14:17:44.469 [main] DEBUG org.hibernate.loader.Loader - Result row: EntityKey[io.test.TestApp$Holder#hid]
14:17:44.471 [main] DEBUG o.h.engine.internal.TwoPhaseLoad - Resolving associations for [io.test.TestApp$Holder#hid]
14:17:44.472 [main] DEBUG o.h.engine.internal.TwoPhaseLoad - Done materializing entity [io.test.TestApp$Holder#hid]
14:17:44.473 [main] DEBUG o.h.l.c.p.AbstractLoadPlanBasedCollectionInitializer - Loading collection: [io.test.TestApp$Holder.elements#hid]
14:17:44.473 [main] DEBUG org.hibernate.SQL - select elements0_.subclassID as subclass2_1_0_, elements0_.pkey as pkey1_1_0_, elements0_.pkey as pkey1_1_1_, elements0_.subclassID as subclass2_1_1_, elements0_.type as type3_1_1_, elements0_1_.test as test1_2_1_, elements0_1_.type as type2_2_1_, elements0_2_.ello as ello1_3_1_, elements0_2_.type as type2_3_1_, case when elements0_1_.pkey is not null then 1 when elements0_2_.pkey is not null then 2 when elements0_.pkey is not null then 0 end as clazz_1_ from TestApp$A elements0_ left outer join TestApp$B elements0_1_ on elements0_.pkey=elements0_1_.pkey left outer join TestApp$C elements0_2_ on elements0_.pkey=elements0_2_.pkey where elements0_.subclassID=?
Hibernate: select elements0_.subclassID as subclass2_1_0_, elements0_.pkey as pkey1_1_0_, elements0_.pkey as pkey1_1_1_, elements0_.subclassID as subclass2_1_1_, elements0_.type as type3_1_1_, elements0_1_.test as test1_2_1_, elements0_1_.type as type2_2_1_, elements0_2_.ello as ello1_3_1_, elements0_2_.type as type2_3_1_, case when elements0_1_.pkey is not null then 1 when elements0_2_.pkey is not null then 2 when elements0_.pkey is not null then 0 end as clazz_1_ from TestApp$A elements0_ left outer join TestApp$B elements0_1_ on elements0_.pkey=elements0_1_.pkey left outer join TestApp$C elements0_2_ on elements0_.pkey=elements0_2_.pkey where elements0_.subclassID=?
14:17:44.475 [main] DEBUG o.h.l.p.e.p.i.ResultSetProcessorImpl - Preparing collection intializer : [io.test.TestApp$Holder.elements#hid]
14:17:44.477 [main] DEBUG o.h.l.p.e.p.i.ResultSetProcessorImpl - Starting ResultSet row #0
14:17:44.478 [main] DEBUG o.h.l.p.e.p.i.CollectionReferenceInitializerImpl - Found row of collection: [io.test.TestApp$Holder.elements#hid]
14:17:44.479 [main] DEBUG o.h.l.p.e.p.i.ResultSetProcessorImpl - Starting ResultSet row #1
14:17:44.479 [main] DEBUG o.h.l.p.e.p.i.CollectionReferenceInitializerImpl - Found row of collection: [io.test.TestApp$Holder.elements#hid]
14:17:44.480 [main] DEBUG o.h.l.p.e.p.i.ResultSetProcessorImpl - Starting ResultSet row #2
14:17:44.480 [main] DEBUG o.h.l.p.e.p.i.CollectionReferenceInitializerImpl - Found row of collection: [io.test.TestApp$Holder.elements#hid]
14:17:44.480 [main] DEBUG o.h.engine.internal.TwoPhaseLoad - Resolving associations for [io.test.TestApp$A#c9b6a82a-eed7-48f5-824e-c591537a6be6]
14:17:44.480 [main] DEBUG o.h.engine.internal.TwoPhaseLoad - Done materializing entity [io.test.TestApp$A#c9b6a82a-eed7-48f5-824e-c591537a6be6]
14:17:44.480 [main] DEBUG o.h.engine.internal.TwoPhaseLoad - Resolving associations for [io.test.TestApp$B#c73dd9ae-9e4e-483e-96cc-4ab802f5dc59]
14:17:44.480 [main] DEBUG o.h.engine.internal.TwoPhaseLoad - Done materializing entity [io.test.TestApp$B#c73dd9ae-9e4e-483e-96cc-4ab802f5dc59]
14:17:44.480 [main] DEBUG o.h.engine.internal.TwoPhaseLoad - Resolving associations for [io.test.TestApp$C#729f2528-18f5-44da-9dca-2b399b66e183]
14:17:44.480 [main] DEBUG o.h.engine.internal.TwoPhaseLoad - Done materializing entity [io.test.TestApp$C#729f2528-18f5-44da-9dca-2b399b66e183]
14:17:44.480 [main] DEBUG o.h.e.l.i.CollectionLoadContext - 1 collections were found in result set for role: io.test.TestApp$Holder.elements
14:17:44.481 [main] DEBUG o.h.e.l.i.CollectionLoadContext - Collection fully initialized: [io.test.TestApp$Holder.elements#hid]
14:17:44.481 [main] DEBUG o.h.e.l.i.CollectionLoadContext - 1 collections initialized for role: io.test.TestApp$Holder.elements
14:17:44.481 [main] DEBUG o.h.r.j.i.ResourceRegistryStandardImpl - HHH000387: ResultSet's statement was not registered
14:17:44.481 [main] DEBUG o.h.l.c.p.AbstractLoadPlanBasedCollectionInitializer - Done loading collection
Class A, pkey: c9b6a82a-eed7-48f5-824e-c591537a6be6 Subclass hid
Class B, pkey: c73dd9ae-9e4e-483e-96cc-4ab802f5dc59 Subclass hid Test: test
Class C, pkey: 729f2528-18f5-44da-9dca-2b399b66e183 Subclass hid ello: Ello

2. Fetch in query

What did work is to have the query fetch automatically at which point the service works, however it is still joining all tables although the type clearly eliminates any result of the unnecessary join. This looks like this:

Query xx = em.createQuery("SELECT h FROM TestApp$Holder h LEFT JOIN fetch h.elements e where TYPE(e) = TestApp$B", Holder.class);

        System.out.println(" -- -- -- -- -");
        List resultList = xx.getResultList();
        resultList.stream().forEach(x -> {
            x.elements.forEach(x2 -> System.err.println(x2));
        });

Output:

14:20:25.063 [main] DEBUG org.hibernate.SQL - select testapp_ho0_.holderId as holderId1_0_0_, elements1_.pkey as pkey1_1_1_, elements1_.subclassID as subclass2_1_1_, elements1_.type as type3_1_1_, elements1_1_.test as test1_2_1_, elements1_1_.type as type2_2_1_, elements1_2_.ello as ello1_3_1_, elements1_2_.type as type2_3_1_, case when elements1_1_.pkey is not null then 1 when elements1_2_.pkey is not null then 2 when elements1_.pkey is not null then 0 end as clazz_1_, elements1_.subclassID as subclass2_1_0__, elements1_.pkey as pkey1_1_0__ from holder testapp_ho0_ left outer join TestApp$A elements1_ on testapp_ho0_.holderId=elements1_.subclassID left outer join TestApp$B elements1_1_ on elements1_.pkey=elements1_1_.pkey left outer join TestApp$C elements1_2_ on elements1_.pkey=elements1_2_.pkey where case when elements1_1_.pkey is not null then 1 when elements1_2_.pkey is not null then 2 when elements1_.pkey is not null then 0 end=1
Hibernate: select testapp_ho0_.holderId as holderId1_0_0_, elements1_.pkey as pkey1_1_1_, elements1_.subclassID as subclass2_1_1_, elements1_.type as type3_1_1_, elements1_1_.test as test1_2_1_, elements1_1_.type as type2_2_1_, elements1_2_.ello as ello1_3_1_, elements1_2_.type as type2_3_1_, case when elements1_1_.pkey is not null then 1 when elements1_2_.pkey is not null then 2 when elements1_.pkey is not null then 0 end as clazz_1_, elements1_.subclassID as subclass2_1_0__, elements1_.pkey as pkey1_1_0__ from holder testapp_ho0_ left outer join TestApp$A elements1_ on testapp_ho0_.holderId=elements1_.subclassID left outer join TestApp$B elements1_1_ on elements1_.pkey=elements1_1_.pkey left outer join TestApp$C elements1_2_ on elements1_.pkey=elements1_2_.pkey where case when elements1_1_.pkey is not null then 1 when elements1_2_.pkey is not null then 2 when elements1_.pkey is not null then 0 end=1
14:20:25.066 [main] DEBUG org.hibernate.loader.Loader - Result set row: 0
14:20:25.067 [main] DEBUG org.hibernate.loader.Loader - Result row: EntityKey[io.test.TestApp$Holder#hid], EntityKey[io.test.TestApp$A#3c5b7de6-1658-49ab-b90b-eacde31276c3]
14:20:25.071 [main] DEBUG org.hibernate.loader.Loader - Found row of collection: [io.test.TestApp$Holder.elements#hid]
14:20:25.077 [main] DEBUG o.h.engine.internal.TwoPhaseLoad - Resolving associations for [io.test.TestApp$Holder#hid]
14:20:25.077 [main] DEBUG o.h.engine.internal.TwoPhaseLoad - Done materializing entity [io.test.TestApp$Holder#hid]
14:20:25.077 [main] DEBUG o.h.engine.internal.TwoPhaseLoad - Resolving associations for [io.test.TestApp$B#3c5b7de6-1658-49ab-b90b-eacde31276c3]
14:20:25.077 [main] DEBUG o.h.engine.internal.TwoPhaseLoad - Done materializing entity [io.test.TestApp$B#3c5b7de6-1658-49ab-b90b-eacde31276c3]
14:20:25.077 [main] DEBUG o.h.e.l.i.CollectionLoadContext - 1 collections were found in result set for role: io.test.TestApp$Holder.elements
14:20:25.077 [main] DEBUG o.h.e.l.i.CollectionLoadContext - Collection fully initialized: [io.test.TestApp$Holder.elements#hid]
14:20:25.078 [main] DEBUG o.h.e.l.i.CollectionLoadContext - 1 collections initialized for role: io.test.TestApp$Holder.elements
Class B, pkey: 3c5b7de6-1658-49ab-b90b-eacde31276c3 Subclass hid Test: test

JPA OneToMany Collection with inheritance limit to specific subclass

Answers (1)

Related Questions