CODEWITHSUNDEEP
javaspring-boothibernatespring-data-jpahibernate-envers
Dario Vogogna
Dario Vogogna

Reputation: 678

Load many-to-many relationship and map to entity

I'm fairly new to Spring Boot/Hibernate development, this was my first big project.

I have an application using Spring Boot, Hibernate and Hibernate Envers to audit some entities. Hibernate Envers is set to use the ValidityAuditStrategy. Since Hibernate Envers doesn't support yet eager loading to-Many relationships out of the box, I'm trying to find a way to perform a single query and retrieve all the data I need, to avoid the N+1 queries problem that, at the moment, is killing my performance: it's taking almost 2 minutes in our development environment to completely load the entity we need with the relationships required.

Since I need to retrieve an audited version, I cannot leverage on EntityGraph like I used in other part of the application (at least to my understanding).

One of the situation I need to tackle has 4 entities Parameter, Formula, Value and Variable, that are related like this (showing only the relevant parts, Value and Variable are entities and not listed here)

@Entity
public class Parameter {
  @OneToOne(fetch = FetchType.LAZY, optional = false)
  @JoinColumn(name = "formula_id", referencedColumnName = "id", nullable = false)
  private Formula formula;
}

@Entity
public class Formula {
  @ManyToOne(optional = false)
  @JoinColumn(name = "tag_id")
  private Value tag;

  @ManyToMany
  @JoinTable(
      name = "Formulas_Variables",
      joinColumns = @JoinColumn(name = "formula_id"),
      inverseJoinColumns = @JoinColumn(name = "variable_id"))
  private Set<Variable> variables = new HashSet<>();
}

What I tried to do is create a custom query and mapping the result using @NamedNativeQuery and @SqlResultSetMapping but, even though Hibernate is creating the relationship between Formula and Value correctly, it's not creating the array on variables field. The query and the mapping I used are the following:

@SqlResultSetMapping(name = "Parameter.findAllByRevisionMapping", entities = {
    @EntityResult(entityClass = Parameter.class, fields = {
        @FieldResult(name = "id", column = "id"),
        @FieldResult(name = "formula", column = "formula_id")
    }),
    @EntityResult(entityClass = Formula.class, fields = {
        @FieldResult(name = "id", column = "formulaId"),
        @FieldResult(name = "tag", column = "tag_id"),
        @FieldResult(name = "variables", column = "variable_id")
    }),
    @EntityResult(entityClass = DomainValue.class, fields = {
        @FieldResult(name = "id", column = "tag_id")
    }),
    @EntityResult(entityClass = Variable.class, fields = {
        @FieldResult(name = "id", column = "variableId")
    })
})
@NamedNativeQuery(name = "Parameter.findAllByRevision", query = "SELECT om_c_p_aud.id,\n"
    + "       f_aud.id AS formulaId,\n"
    + "       dv_aud.id AS tag_id,\n"
    + "       fv_aud.formula_id AS formula_id,\n"
    + "       v_aud.id AS variable_id,\n"
    + "       v_aud.id AS variableId\n"
    + "FROM Parameters_AUD om_c_p_aud\n"
    + "         LEFT OUTER JOIN Formulas_AUD f_aud\n"
    + "                    ON f_aud.id = om_c_p_aud.formula_id AND f_aud.REV <= ?1 AND\n"
    + "                       f_aud.REVTYPE <> 2 AND (f_aud.REVEND > ?1 OR\n"
    + "                                               f_aud.REVEND IS NULL)\n"
    + "         LEFT OUTER JOIN Values_AUD dv_aud\n"
    + "                    ON dv_aud.id = f_aud.tag_id AND dv_aud.REV <= ?1 AND\n"
    + "                       dv_aud.REVTYPE <> 2 AND (dv_aud.REVEND > ?1 OR\n"
    + "                                                dv_aud.REVEND IS NULL)\n"
    + "         LEFT OUTER JOIN Formulas_Variables_AUD fv_aud\n"
    + "                    ON fv_aud.formula_id = f_aud.id AND fv_aud.REV <= ?1 AND\n"
    + "                       fv_aud.REVTYPE <> 2 AND (fv_aud.REVEND > ?1 OR\n"
    + "                                                fv_aud.REVEND IS NULL)\n"
    + "         LEFT OUTER JOIN Variables_AUD v_aud\n"
    + "                    ON v_aud.id = fv_aud.variable_id AND v_aud.REV <= ?1 AND\n"
    + "                       v_aud.REVTYPE <> 2 AND (v_aud.REVEND > ?1 OR\n"
    + "                                               v_aud.REVEND IS NULL)\n"
    + "WHERE om_c_p_aud.REV <= ?1\n"
    + "  AND om_c_p_aud.REVTYPE <> 2\n"
    + "  AND (om_c_p_aud.REVEND > ?1 OR\n"
    + "       om_c_p_aud.REVEND IS NULL)", resultSetMapping = "Parameter.findAllByRevisionMapping")

An example of result from the query performed directly on the database is the following: Query example result

The resulting array of json objects I'm receiving when calling findAllByRevision query is something like this

[
   {
      "id":1,
      "formula":{
         "id":52,
         "tag":{
            "id":20
         },
         "variables":null
      }
   },
   {
      "id":2,
      "formula":{
         "id":88,
         "tag":{
            "id":24
         },
         "variables":null
      }
   },
   {
      "id":2,
      "formula":{
         "id":88,
         "tag":{
            "id":24
         },
         "variables":null
      }
   },
   {
      "id":2,
      "formula":{
         "id":88,
         "tag":{
            "id":24
         },
         "variables":null
      }
   },
   {
      "id":2,
      "formula":{
         "id":88,
         "tag":{
            "id":24
         },
         "variables":null
      }
   },
   {
      "id":2,
      "formula":{
         "id":88,
         "tag":{
            "id":24
         },
         "variables":null
      }
   },
   {
      "id":2,
      "formula":{
         "id":88,
         "tag":{
            "id":24
         },
         "variables":null
      }
   }
]

whereas the one I'd expect was

[
   {
      "id":1,
      "formula":{
         "id":52,
         "tag":{
            "id":20
         },
         "variables":[
            {
               "id":4
            }
         ]
      }
   },
   {
      "id":2,
      "formula":{
         "id":88,
         "tag":{
            "id":24
         },
         "variables":[
            {
               "id":3
            },
            {
               "id":23
            },
            {
               "id":33
            },
            {
               "id":34
            },
            {
               "id":35
            },
            {
               "id":52
            }
         ]
      }
   }
]

Does anyone have any idea why it isn't creating the formulas <-> variables relationship? I tried to check the query created by Hibernate when using an EntityGraph for a similar situation and it seemed to me the same as the one showed above. I have no way to check the mapping used in that case though (to my understanding again).

Upvotes: 0

Views: 1023

Answers (1)

Christian Beikov
Christian Beikov

Reputation: 16420

You should be able to use HQL to specify join fetches for the audited associations. An audited entity is just like a normal entity. The entity name is usually suffixed with "_AUD" so if you want to query the audit info of Parameters you could query Parameters_AUD like e.g.:

SELECT p
FROM Parameters_AUD p
LEFT JOIN FETCH p.values
LEFT JOIN FETCH p.variables

Upvotes: 1

Related Questions