CODEWITHSUNDEEP
javaspringjpaspring-bootspring-data-jpa
hemu
hemu

Reputation: 3261

Filtering using EntityGraph in Spring Data JPA Repository

I have a Listing table wich have 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?

Upvotes: 3

Views: 5928

Answers (1)

andih
andih

Reputation: 5603

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")

Upvotes: 7

Related Questions