Return ResultSet with intact joins in Hibernate / JPA

Question

I have 2 entities: EntityA and EntityB. They are related with a One To Many relation.

public class EntityA {

    @Identifier
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="ID", updatable = false, nullable = false)
    private long id;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name="ENTITY_A_ID", referencedColumnName="ID", nullable=true)
    private List<EntityB> entityBs;

   /* GETTERS SETTERS ... */
}

public class EntityB {

    @Identifier
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="ID", updatable = false, nullable = false)
    private long id;


    @Column(name="SOME_PROPERTY")
    private String someProperty;

    @ManyToOne
    @JoinColumn(name="ENTITY_A_ID")
    private EntityA entityA;
    /* GETTERS SETTERS ... */

}

I have a query that joins EntityA with a LEFT JOIN to Entity B. And a 'ON' clause. In normal SQL lingo this would be:

select * from EntityA eA left join EntityB eB
    on (eA.ID = eB.ENTITY_A_ID and eB.SOME_PROPERTY = "blabla" )
    where ...

So I'm having much needed information from my joined resultset. I only want records joined if they match certain properties. I need EntityA, allways, and an attached EntityB if EntityB matched the join clause.

The project is set up with Hibernate / JPA. I can't figure out how to retreive the information needed. At this moment I have:

public class EntityADAO {

    public List<EntityA> findMethod() {

        CriteriaBuilder builder = entityManager.getCriteriaBuilder();

        CriteriaQuery<EntityA> query = builder.createQuery(EntityA.class);

        Root<EntityA> entityARoot = query.from(EntityA.class);
        Join<EntityA, EntityB> entityBJoin = entityARoot.join("entityB", JoinType.INNER);
        entityBJoin.on(new Predicate [] {builder.equal(entityBJoin.get("someProperty"), "fixed_val_for_now"});

       /* where clause left out for readability */

        TypedQuery<EntityA> q = entityManager.createQuery(query);

        return q.getResultList();

        }

    }

So here I am.. Stuck with my List of EntityAs. whenever I call getEntityBs() on a EntityA, I'm getting all of them.. And this makes sense.. But How can I retrieve the joined set?

I'm stuck with JPA and Hibernate, as this choice is not made by me.

Thanks in advance!

score 1 · Accepted Answer · answered May 19 '20 at 14:28

What you need here is a custom projection or DTO. Filtering the entity collection might cause a delete because entities always reflect the current DBMS state and are synchronized at the end of the transaction.

You can write a JPQL query, just like the SQL one, that does what you want.

SELECT a.id, b.id 
FROM EntityA a 
LEFT JOIN EntityB b ON a.id = b.entityA.id AND b.someProperty = 'blabla'

But this won't help you with the materialization of the results into rich objects. If an Object[] i.e. the tuples are good enough for your use case, then use this kind of query and be done, but if you want to map to rich objects, I can recommend that you take a look at Blaze-Persistence Entity-Views.

Blaze-Persitence is a query builder on top of JPA which supports many of the advanced DBMS features on top of the JPA model. I created Entity Views on top of it to allow easy mapping between JPA models and custom interface defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure the way you like and map attributes(getters) via JPQL expressions to the entity model. Since the attribute name is used as default mapping, you mostly don't need explicit mappings as 80% of the use cases is to have DTOs that are a subset of the entity model.

A mapping for your model could look as simple as the following

@EntityView(EntityA.class)
public interface EntityAView {
    long getId();
    @Mapping("entityBs[someProperty = 'blabla']")
    List<EntityBView> getEntityBs();
}

@EntityView(EntityB.class)
public interface EntityBView {
    long getId();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

EntityAView dto = entityViewManager.find(entityManager, EntityAView.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Return ResultSet with intact joins in Hibernate / JPA

1 Answers1

Linked