Fetch Records from @Entity as well as relation table using Specification as Query

Question

I am query data from Entity which has ManytoOne relation on other entities. Using Specification able to fetch the Record of only Root. But I need to fetch the column from relational entities as well.

I am new to Spring boot and trying to build API.

Query root always references entities, then how to use the custom query in the specification.

return new Specification<CallMessage>() {

private static final long serialVersionUID = 1L;

@Override
public Predicate toPredicate(Root<CallMessage> root, 
               CriteriaQuery<?> query, CriteriaBuilder cb) 
     {

    List<Predicate> predicates = new ArrayList<>();
            In<Long> inClause = cb.in(root.get("bed_address"));
            inClause.value("XYZ");
            predicates.add(inClause);
            return cb.and(predicates.toArray(new Predicate[predicates.size()]));
     }

}


@Entity(name = "calls")

public class CallMessage implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;


    @OneToMany(targetEntity = PresetModel.class, mappedBy = "id", orphanRemoval = false)
    private Set<PresetModel> presetModels;
}


@Entity(name = "reports_preset_filter")
public class PresetModel extends AuditModel{

   private static final long serialVersionUID = 1L;

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name = "id")
   private int id;
   private String preset_name;



    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name="call_id" ,nullable=false)
    @OnDelete(action = OnDeleteAction.CASCADE)
    @JsonIgnore
    private CallMessage callMessage ;
}

While using JpaSpecificationExecutor can't use another query to join these two tables. It there any way to fetch records like- call_message { id: 1, reports_preset_filter : [ ] }

I am trying to build filter API with multiple Predicates, to put here I have trimmed down the List of Predicates as well as other columns of Entities.

wi2ard · Accepted Answer · 2019-08-26T13:41:20.787

What happens is that JPA @OneToMany fetching strategy is by default lazy, meaning the reference fields are just "empty" proxies that are populated only when you explicitly read from them, using the getter. You probably want to leave this strategy in place and fetch the field only for this query.

An elegant solution is to create a fetch plan with entity graphs:

Entity graphs are templates for a particular Persistence query or operation. They are used when creating fetch plans, or groups of persistent fields that are retrieved at the same time[..] By default, entity fields or properties are fetched lazily. Developers specify fields or properties as part of a fetch plan, and the persistence provider will fetch them eagerly.

https://docs.oracle.com/javaee/7/tutorial/persistence-entitygraphs.htm

Here's a code sample of a master entity with some detail fields:

@NamedEntityGraph(
  name = "post-entity-graph-with-comment-users",
  attributeNodes = {
    @NamedAttributeNode("subject"),
    @NamedAttributeNode(value = "comments", subgraph = "comments-subgraph"),
  },
  subgraphs = {
    @NamedSubgraph(
      name = "comments-subgraph",
      attributeNodes = {
        @NamedAttributeNode("user")
      }
    )
  }
)
@Entity
public class Post {
    @Id
    private Long id;
    private String subject;
    @OneToMany(mappedBy = "post")
    private List<Comment> comments;
}

@Entity
public class Comment {
    @Id
    private Long id;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn
    private User user;
    //...
}

Then you can have a SimpleJpaRepository subclass overriding

@Override
    public List<T> findAll(Specification<T> spec, Sort sort, EntityGraph.EntityGraphType entityGraphType, String entityGraphName) {
        TypedQuery<T> query = getQuery(spec, sort);
        query.setHint(entityGraphType.getKey(), em.getEntityGraph(entityGraphName));
        return query.getResultList();
    }

So to run a Specification that also loads the Users related to the Comments:

List<Post> results = myRepository.findAll(mySpecification, EntityGraph.EntityGraphType.LOAD, "post-entity-graph-with-comment-users");

See https://www.baeldung.com/jpa-entity-graph

Fetch Records from @Entity as well as relation table using Specification as Query

1 Answers1