JPA: Selective fetching of related entity

Question

First of all, thank anyone who is trying to help.

I am developing a simple game back-end with MYSQL DB and hibernate ORM, and trying to eagerly load a relation selectively.

This is how the entities are defined in java, only revelant fields were included:

@Entity
@Table(name = "CHARACTERS", uniqueConstraints = { @UniqueConstraint(columnNames = { "CHARACTER_UID" }) })
public class Character {

    @OneToMany(mappedBy = "character", targetEntity = CharacterItem.class, cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<CharacterItem> characterItems = new ArrayList<>();

    @Column(name = "IS_ACTIVE", nullable = false, columnDefinition = "TINYINT(1)")
    @Type(type = "org.hibernate.type.NumericBooleanType")
    private boolean isActive;
}

@Entity
@Table(name = "CHARACTER_ITEMS", uniqueConstraints = { @UniqueConstraint(columnNames = { "ITEM_UID" }) })
public class CharacterItem {

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "CHARACTER_UID")
    private Character character;

    @Column(name = "ITEM_STATUS", length = 1)
    private String itemStatus;
}

Table definitions:

CREATE TABLE IF NOT EXISTS CHARACTERS 
(IS_ACTIVE      BOOLEAN NOT NULL DEFAULT FALSE);

CREATE TABLE IF NOT EXISTS CHARACTER_ITEMS
(CHARACTER_UID          VARCHAR(80) NOT NULL,
ITEM_STATUS             VARCHAR(1),
FOREIGN KEY (CHARACTER_UID) REFERENCES CHARACTERS(CHARACTER_UID));

I have a the following DAO class method:

@Override
public List<Character> getActiveCharacters() {
    List<Character> res = em
            .createQuery("SELECT c FROM Character c JOIN c.characterItems ci WHERE c.isActive = 1 and ci.itemStatus = 'A'", Character.class)
            .getResultList();

    return res;
}

What i want is to return a list of active Character objects whose characterItems are eagerly initialized, and i want those character items to only be active items (itemStatus = 'A') however, i am getting a list with duplications of Character records, and once i call Character.getCharacterItems(), another sql is generated and all items are loaded regardless of their status(Lazy loading).

Is there a way to accomplish this using JPA? I want this to be performed in one query due to performance reasons as many characterItems are expected to have an item status value that is not 'A'.

I have also tried join fetch, but according to JPA specification this cannot be aliased, hence no filtering can be done on the fetched relation, unless i am missing something.

EntityGraphs to the rescue (https://thoughts-on-java.org/jpa-21-entity-graph-part-1-named-entity/) — Nikolai Shevchenko, Jul 10 '19 at 20:25
@Nikolay Shevchenko I am aware that entity graphs can be defined to fetch some relations eagerly, but how can we use them to filter the fetched entities? — monkeyStix, Jul 10 '19 at 20:51
Then try to use `fetch join` instead of regular `join` in your HQL query — Nikolai Shevchenko, Jul 10 '19 at 21:02
If you want the associated entities to always be filtered, see [this question](https://stackoverflow.com/questions/7700071/how-to-use-where-in-hibernate) for a Hibernate-specific solution — crizzis, Jul 11 '19 at 09:07
@crizzis Thank you, that actually did the trick, And although hibernate specific, i think it is by far the most popular ORM solution, and also applies to my case — monkeyStix, Jul 13 '19 at 20:13

JPA: Selective fetching of related entity

0 Answers0