Filtering using EntityGraph in Spring Data JPA Repository

Question

I have a Listing table which has a one to many relationship with ListingAttachment. Now in whole application, every table/entity has deleteFlag and each repository should fetch data only with deleteFlag 0. So basically, we are not deleting any data just marking deleteFlag to 1.

Following is my Entity structure:

Listing.java

@Entity
public class Listing {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    
    String title;
    
    String description;
    
    @OneToMany(mappedBy = "listing", cascade={CascadeType.ALL})
    private Set<ListingAttachment> listingAttachments;
    
    private int deleteFlag; 
    
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public int getDeleteFlag() {
        return deleteFlag;
    }

    public void setDeleteFlag(int deleteFlag) {
        this.deleteFlag = deleteFlag;
    }

    public Set<ListingAttachment> getListingAttachments() {
        return listingAttachments;
    }

    public void setListingAttachments(Set<ListingAttachment> listingAttachments) {
        this.listingAttachments = listingAttachments;
    }
    
    public ListingAttachment addListingAttachment(ListingAttachment listingAttachment) {
        getListingAttachments().add(listingAttachment);
        listingAttachment.setListing(this);
        return listingAttachment;
    }

    public ListingAttachment removeListingAttachment(ListingAttachment listingAttachment) {
        getListingAttachments().remove(listingAttachment);
        listingAttachment.setListing(null);
        return listingAttachment;
    }
}

ListingAttachment.java

@Entity
public class ListingAttachment {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    
    String fileName;
    
    @ManyToOne
    @JoinColumn(name = "LISTING_ID")    
    private Listing listing;

    private int deleteFlag;
    
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public Listing getListing() {
        return listing;
    }

    public void setListing(Listing listing) {
        this.listing = listing;
    }

    public int getDeleteFlag() {
        return deleteFlag;
    }

    public void setDeleteFlag(int deleteFlag) {
        this.deleteFlag = deleteFlag;
    }
    
    
}

ListingRepository.java

public interface ListingRepository extends JpaRepository<Listing, Long> {

        @EntityGraph(attributePaths = { "listingAttachments" }) 
        @Query("SELECT l FROM Listing l WHERE l.id = (:id) and deleteFlag = 0")
        public ListingfindOneWithImagesAndAttachments(@Param("id") Long id);
        
}

Using EntityGraph we can easily fetch OneToMany entities. But the problem is how to filter or apply condition on Many related entity.

For e.g., in my case I should fetch Listing with deleteFlag 0 and its all attachments (ListingAttachments), which in turn also must have deleteFlag 0. Using EntityGraph as shown in above repository, it fetches all the attachments irrespective of deleteFlag. Is there any way I can filter attachments based on deleteFlag?

andih · Accepted Answer · 2016-07-17 15:04:20Z

7

+50

EntityGraph defines which attributes or (sub-)graphs of an entity should be fetched (eagerly or lazy) without having to define them on entity itself.

In JPA 2.0 (without EntityGraph) You had to define at the entity if you want to use FetchType.LAZY (default) or FetchType.EAGER to load the relation and this mode is always used.

With EntityGraph you can define the attributes and (sub-)graphs per query.

EntityGraph is not used to filter elements.

If you want to find the Listings which are not marked as deleted (delete flag = 0) and which have at least one ListingAttachment not marked as deleted, you can do this by using a FETCH JOIN

public interface ListingRepository extends JpaRepository<Listing, Long> {

    @EntityGraph(attributePaths = { "listingAttachments" }) 
    @Query("SELECT l FROM Listing l JOIN l.listingAttachments a 
        WHERE l.id = (:id) and l.deleteFlag = 0 and a.deleteFlag = 0")
    public Listing findOneWithImagesAndAttachments(@Param("id") Long id);

}

You need to join the ListingAttachments because you can't direct dereference the deleteFlag in the JPA Query using the listingAttachments Collection.

The above example returns you all your listings that are not marked as deleted and have at least one ListingAttachment which is not marked as deleted.

If you want to return Listings that are not marked as deleted but may have no ListingAttachments you have to change it to a LEFT OUTER JOIN

@Query("SELECT l FROM Listing l 
   LEFT OUTER JOIN  l.listingAttachments a 
   WHERE l.id = (:id) and l.deleteFlag = 0 and a.deleteFlag = 0")

answered Jul 17, 2016 at 15:04

andih

5,6233 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hemu Over a year ago

One correction...for listing not marked as delete and have no ListingAttachments, we have to add a is null in where clause. So the query will be

@Query("SELECT l FROM Listing l     LEFT OUTER JOIN  l.listingAttachments a     WHERE l.id = (:id) and l.deleteFlag = 0 and (a is null OR a.deleteFlag = 0)")

mangusta Over a year ago

If @Query defines its own join, then @EntityGraph would add an extra unnecessary join which is not even used anywhere in the query. On the other hand, without @EntityGraph, the entities will be fetched one by one. Breaking my head how to solve this situation

Collectives™ on Stack Overflow

Filtering using EntityGraph in Spring Data JPA Repository

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related