JPA inheritance @EntityGraph include optional associations of subclasses

Question

Given the following domain model, I want to load all Answers including their Values and their respective sub-children and put it in an AnswerDTO to then convert to JSON. I have a working solution but it suffers from the N+1 problem that I want to get rid of by using an ad-hoc @EntityGraph. All associations are configured LAZY.

@Query("SELECT a FROM Answer a")
@EntityGraph(attributePaths = {"value"})
public List<Answer> findAll();

Using an ad-hoc @EntityGraph on the Repository method I can ensure that the values are pre-fetched to prevent N+1 on the Answer->Value association. While my result is fine there is another N+1 problem, because of lazy loading the selected association of the MCValues.

Using this

@EntityGraph(attributePaths = {"value.selected"})

fails, because the selected field is of course only part of some of the Value entities:

Unable to locate Attribute  with the the given name [selected] on this ManagedType [x.model.Value];

How can I tell JPA only try fetching the selected association in case the value is a MCValue? I need something like optionalAttributePaths.

Voltz

Answer 1

You can only use an EntityGraph if the association attribute is part of the superclass and by that also part of all subclasses. Otherwise, the EntityGraph will always fail with the Exception that you currently get.

The best way to avoid your N+1 select issue is to split your query into 2 queries:

The 1st query fetches the MCValue entities using an EntityGraph to fetch the association mapped by the selected attribute. After that query, these entities are then stored in Hibernate's 1st level cache / the persistence context. Hibernate will use them when it processes the result of the 2nd query.

@Query("SELECT m FROM MCValue m") // add WHERE clause as needed ...
@EntityGraph(attributePaths = {"selected"})
public List<MCValue> findAll();

The 2nd query then fetches the Answer entity and uses an EntityGraph to also fetch the associated Value entities. For each Value entity, Hibernate will instantiate the specific subclass and check if the 1st level cache already contains an object for that class and primary key combination. If that's the case, Hibernate uses the object from the 1st level cache instead of the data returned by the query.

@Query("SELECT a FROM Answer a")
@EntityGraph(attributePaths = {"value"})
public List<Answer> findAll();

Because we already fetched all MCValue entities with the associated selected entities, we now get Answer entities with an initialized value association. And if the association contains an MCValue entity, its selected association will also be initialized.

Felony

I thought about having two queries, the 1st for fetching answers+value and a 2nd one to fetch selected for those answers that have a MCValue . I disliked that this would require an additional loop and I would need to manage the mapping between the data sets. I like your idea to exploit the Hibernate cache for this. Can you elaborate on how safe (in terms of consistency) it is to rely on the cache to contain the results? Does this work when the queries are made in a transaction? I am afraid of hard to spot and sporadic lazy initialization errors. – Voltz

You need to perform both queries within the same transaction. As long as you do that, and don't clear your persistence context, it's absolutely safe. Your 1st level cache will always contain the MCValue entities. And you don't need an additional loop. You should fetch all MCValue entities with 1 query that joins to the Answer and uses the same WHERE clause as your current query. I also talked about this in today's live stream: youtu.be/70B9znTmi00?t=238 It started at 3:58 but I took a few other questions in between ... – Felony

Great, thanks for the followup! Also I want to add, that this solution requires 1 query per Subclass. So maintainability is ok-ish for us but this solution might not be suitable for all cases. – Voltz

I need to correct my last comment a little: Of course you only need a query per subclass that suffers from the problem. Also it is worth noting, that for attributes of the subclasses this seems to not beeing an issue, because of using SINGLE_TABLE_INHERITANCE. – Voltz

Answer 2

I don't know what Spring-Data is doing there, but to do that, you usually have to use the TREAT operator to be able to access the sub-association but the implementation for that Operator is quite buggy. Hibernate supports implicit subtype property access which is what you would need here, but apparently Spring-Data can't handle this properly. I can recommend that you take a look at Blaze-Persistence Entity-Views, a library that works on top of JPA which allows you map arbitrary structures against your entity model. You can map your DTO model in a type safe way, also the inheritance structure. Entity views for your use case could look like this

@EntityView(Answer.class)
interface AnswerDTO {
  @IdMapping
  Long getId();
  ValueDTO getValue();
}
@EntityView(Value.class)
@EntityViewInheritance
interface ValueDTO {
  @IdMapping
  Long getId();
}
@EntityView(TextValue.class)
interface TextValueDTO extends ValueDTO {
  String getText();
}
@EntityView(RatingValue.class)
interface RatingValueDTO extends ValueDTO {
  int getRating();
}
@EntityView(MCValue.class)
interface TextValueDTO extends ValueDTO {
  @Mapping("selected.id")
  Set<Long> getOption();
}

With the spring data integration provided by Blaze-Persistence you can define a repository like this and directly use the result

@Transactional(readOnly = true)
interface AnswerRepository extends Repository<Answer, Long> {
  List<AnswerDTO> findAll();
}

It will generate a HQL query that selects just what you mapped in the AnswerDTO which is something like the following.

SELECT
  a.id, 
  v.id,
  TYPE(v), 
  CASE WHEN TYPE(v) = TextValue THEN v.text END,
  CASE WHEN TYPE(v) = RatingValue THEN v.rating END,
  CASE WHEN TYPE(v) = MCValue THEN s.id END
FROM Answer a
LEFT JOIN a.value v
LEFT JOIN v.selected s

Broadway

Hmm thanks for the hint to your library that I already found, but we would not use it for 2 main reasons: 1) we can not rely on the lib to be supported over the lifetime of our project (your company blazebit is rather small and in its beginnings). 2) We would not commit to a more complex tech-stack to optimize a single query. (I know that your lib can do more, but we prefer a common tech stack and rather would just implement a custom query / transformation if there is no JPA solution). – Voltz

Blaze-Persistence is open source and Entity-Views is more or less implemented on top of JPQL/HQL which is standard. The features it implements are stable and will still work with future versions of Hibernate, because it works on top of the standard. I understand that you don't want to introduce something because of a single use case, but I doubt that's the only use case for which you could use Entity Views. Introducing Entity Views usually leads to significantly reducing the amount of boilerplate code and also increases query performance. If you don't want to use tools that help you, so be it. – Broadway

At least you undestood the problem and you provide a solution. So you get the bounty even though the answers does not explain what exately is going on in the original problem and how JPA could solve it. From my perception it is just not supported by JPA and it should become a feature request. I will offer another bounty for a more elaborated answer targeting JPA only. – Voltz

It's simply not possible with JPA. You need the TREAT operator which is neither fully supported in any JPA provider, nor is it supported in the EntityGraph annotations. So the only way you can model this is through the Hibernate implicit subtype property resolving feature, which requires you to use explicit joins. – Broadway

In your answer the view definition should be interface MCValueDTO extends ValueDTO { @Mapping("selected.id") Set<Long> getOption(); } – Voltz

Answer 3

My latest project used GraphQL (a first for me) and we had a big issue with N+1 queries and trying to optimize the queries to only join for tables when they are required. I have found Cosium / spring-data-jpa-entity-graph irreplaceable. It extends JpaRepository and adds methods to pass in an entity graph to the query. You can then build dynamic entity graphs at runtime to add in left joins for only the data you need.

Our data flow looks something like this:

1. Receive GraphQL request
2. Parse GraphQL request and convert to list of entity graph nodes in the query
3. Create entity graph from the discovered nodes and pass into the repository for execution

To solve the problem of not including invalid nodes into the entity graph (for example __typename from graphql), I created a utility class which handles the entity graph generation. The calling class passes in the class name it is generating the graph for, which then validates each node in the graph against the metamodel maintained by the ORM. If the node is not in the model, it removes it from the list of graph nodes. (This check needs to be recursive and check each child as well)

Before finding this I had tried projections and every other alternative recommended in the Spring JPA / Hibernate docs, but nothing seemed to solve the problem elegantly or at least with a ton of extra code

Malloch

how does it solve the problem of loading associations that are not known from the super type? Also, as said to the other answer, we want to know if there is a pure JPA solution, but I also think that the lib suffers from the same problem that the selected association is not available for all sub types of value. – Voltz

If you are interested in GraphQL, we also have an integration of Blaze-Persistence Entity Views with graphql-java: persistence.blazebit.com/documentation/1.5/entity-view/manual/… – Broadway

@ChristianBeikov thanks but we are using SQPR to generate our schema programmatically from our models / methods – Malloch

If you like the code-first approach, you will love the GraphQL integration. It handles fetching only the actually used columns/expressions reducing joins etc. automatically. – Broadway

Answer 4

Edited after your comment:

My apologize, I haven't undersood you issue in the first round, your issue occurs on startup of spring-data, not only when you try to call the findAll().

So, you can now navigate the full example can be pull from my github: https://github.com/bdzzaid/stackoverflow-java/blob/master/jpa-hibernate/

You can easlily reproduce and fix your issue inside this project.

Effectivly, Spring data and hibernate are not capable to determinate the "selected" graph by default and you need to specify the way to collect the selected option.

So first, you have to declare the NamedEntityGraphs of the class Answer

As you can see, there is two NamedEntityGraph for the attribute value of the class Answer

• The first for all Value without specific relationship to load

• The second for the specific Multichoice value. If you remove this one, you reproduce the exception.

Second, you need to be in a transactional context answerRepository.findAll() if you want to fetch data in type LAZY

@Entity
@Table(name = "answer")
@NamedEntityGraphs({
    @NamedEntityGraph(
            name = "graph.Answer", 
            attributeNodes = @NamedAttributeNode(value = "value")
    ),
    @NamedEntityGraph(
            name = "graph.AnswerMultichoice",
            attributeNodes = @NamedAttributeNode(value = "value"),
            subgraphs = {
                    @NamedSubgraph(
                            name = "graph.AnswerMultichoice.selected",
                            attributeNodes = {
                                    @NamedAttributeNode("selected")
                            }
                    )
            }
    )
}
)
public class Answer
{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(updatable = false, nullable = false)
    private int id;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "value_id", referencedColumnName = "id")
    private Value value;
// ..
}

Boarish

The problem is not fetching the value-association of Answer but getting the selected association in case the value is a MCValue. Your answer does not include any information regarding that. – Voltz

@Voltz Thanks for your answer, can you please share with me the class MCValue, I'll try to reproduce you issue locally. – Boarish

Your example only works because you defined the association OneToMany as FetchType.EAGER but as stated in the question: all associations are LAZY. – Voltz

@Voltz I updated my answer since your last update, hope know my answer will help you to resolve your issue, and help you to understand the way to load en entity graph including optional relationships. – Boarish

Your "solution" still suffers from the original N+1 problem that this question is about: put insert and find methods in different transactions of your test and you see that jpa will issue a DB query to selected for every answer instead of loading them upfront. – Voltz

TransactionTemplate template = new TransactionTemplate(transactionManager); template.executeWithoutResult(s -> insert()); template.executeWithoutResult(s -> System.out.println(answerRepository.findAllByGraph())); – Voltz

@Voltz Thanks for both your comment, your analyze and this amazing question. I agree with you. I come back to you if I'm able to answer. I'm investigating because the solution of this issue will increase the performance. – Boarish