Spring Data Specifications for Dynamic Query Generation

Background

One of our critical API is responsible for all the real-time request processing in our system apart from advertisement clicks. We found it difficult to scale and extend our codebase for new feature implementation and maintain it because of its legacy nature and spaghetti code. Thereby, we went for a complete revamp.

The API also communicates with our PostgreSQL database for all the transactions that happens in our database (i.e., CRUD). For this we used Spring Data JPA which uses Hibernate underneath for all the query executions. But then we encountered a scenario where different users were trying to fetch data based on different filters that resulted in the functions growing too large in the JPA repositories and soon the codebase could no longer be maintained.

For this, we came up with a solution to use Specifications with atomic and reusable predicates that binds to the query as and when required (discussed later). This provided users the flexibility to pass filters for the data based on their needs and also helped developers maintain code with ease.

What is Spring Data JPA Specification?

Spring Data JPA Specifications is yet another tool at our disposal to perform database queries with Spring or Spring Boot. It is built on top of the Criteria API.

When building a criteria query, we’re required to build a managed Root, CriteriaQuery and CriteriaBuilder objects by ourselves.

Subquery subQuery = criteriaBuilder.createQuery(OfferEntity.class).subquery(AffiliateOfferEntity.class);
Root subRoot = subQuery.correlate(root);

Specification is built on top of the Criteria API to simplify the developer experience. We simply need to implement the Specification interface.

interface Specification{
 
  Predicate toPredicate(Root root, 
            CriteriaQuery query, 
            CriteriaBuilder criteriaBuilder);

}

Using Specifications, we can build atomic predicates and combine those predicates to build complex dynamic queries.

Specifications are inspired by the Domain-Driven Design “Specification” pattern.

The Problem Statement

We had the following requirements:

• Instead of implementing a single, custom solution specific to a given use case, we needed a generic yet configurable solution to be used across different type of requests for different roles (i.e., advertisers, affiliates, admins, etc.) in the complete system.

• We have different relationships between tables (both ToMany and ToOne). We needed ultra low latency, so building a separate query for each of the relations wouldn't be sufficient.

Additionally, considerable tradeoffs with code readability, testability, and the ability to troubleshoot were all considered non-starters.

Solution Explained

We wanted to serve each incoming request without any failure and with minimum possible delay. We implemented several atomic Specifications that are used for the comparisons in the query as shown in the following class diagram:

where BaseFilter looks like:

package com.inmobi.affiliate.platform.v2.util.filter;

import lombok.extern.slf4j.Slf4j;

import javax.persistence.criteria.*;

@Slf4j
public abstract class BaseFilter {
    protected static Join getRoot(String[] fields, Root root) {
        Join field = null;
        for(int i = 0; i < fields.length; ++i) {
            if(i == 0) {
                field = root.join(fields[i], JoinType.LEFT);
            } else if(i < fields.length - 1) {
                field = field.join(fields[i], JoinType.LEFT);
            }
        }
        return field;
    }
    
}

while one of the filters looks like:

package com.inmobi.affiliate.platform.v2.util.filter;

import lombok.extern.slf4j.Slf4j;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.JoinType;
import java.util.Arrays;
import java.util.List;

@Slf4j
public class EqualsFilter extends BaseFilter {
    public static Specification getFilter(String name, Object value) {
        if(name.contains(Constants.delimiter)) {
            String[] fields = name.split(Constants.delimiter);
            return ((root, criteriaQuery, criteriaBuilder) -> value == null ? null :
                    criteriaBuilder.equal(getRoot(fields, root).get(fields[fields.length - 1]), value));
        }
        return ((root, criteriaQuery, criteriaBuilder) -> value == null ? null :
                criteriaBuilder.equal(root.get(name), value));
    }
}

Now, let’s consider that we have an Entity named OfferEntity which represents the abstraction of offers and can be retrieved by affiliates, advertisers, admins, etc. and it needs to be configured based on the roles (i.e., filters applied). The OfferEntity now has one-to-one mapping with OsEntity (it represents the operating system tagged for the offer) and many-to-many relationship with affiliateOfferEntity (represents the abstractions of the affiliates assigned for that offer to execute an advertisement) and all these entities can be represented as follows:

OfferEntity.java

package com.inmobi.affiliate.platform.v2.entities;

import lombok.*;
import org.hibernate.Hibernate;
import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;
import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.*;
import java.time.LocalDateTime;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

@Entity
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "t_offer_info")
@EntityListeners(AuditingEntityListener.class)
@NamedEntityGraph(name = "offer_entity_graph",
        attributeNodes = {
                @NamedAttributeNode("offerFund"),
                @NamedAttributeNode("os"),
                @NamedAttributeNode("kpi"),
                @NamedAttributeNode("offerConfig")
        })
public class OfferEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "f_offer_id")
    private Integer offerId;
    @Column(name = "f_currency")
    private String currency;
    @Column(name = "f_default_payout")
    private Double defaultPayout;
    @Column(name = "f_default_revenue")
    private Double defaultRevenue;
    @OneToMany(mappedBy = "offerId",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            orphanRemoval = true)
    private Set offerDescEntities = new HashSet<>();
    @Column(name = "f_offer_description")
    private String descriptionString = "";
    @Column(name = "f_expiration_date")
    private String expirationDate;
    @Column(name = "f_adv_id")
    private Integer advertiserId;
    @Column(name = "f_is_expired")
    private Boolean isExpired;
    @Column(name = "f_daily_conversion_cap")
    private Integer dailyConversionCap;
    @Column(name = "f_monthly_conversion_cap")
    private Integer monthlyConversionCap;
    @Column(name = "f_monthly_payout_cap")
    private Double monthlyPayoutCap;
    @Column(name = "f_offer_name")
    private String offerName;
    @Column(name = "f_daily_payout_cap")
    private Double dailyPayoutCap;
    @Column(name = "f_require_approval")
    private Boolean requireApproval;
    @Column(name = "f_require_tnc")
    private Boolean requireTnc = false;
    @Column(name = "f_offer_status_id")
    private Integer offerStatusId;
    @Column(name = "f_tnc")
    private String tnc;
    @Column(name = "f_use_target_rules")
    private Boolean useTargetRules;
    @Column(name = "f_offer_category_id")
    private Integer offerCategoryId;
    @OneToOne(fetch = FetchType.LAZY,
            optional = false)
    @JoinColumn(name = "f_os_id")
    private OsEntity os;

    @CreatedDate
    @Column(name = "f_created_time")
    private String createdTime;

    @LastModifiedDate
    @Column(name = "f_modified_time")
    private String modifiedTime;

    @Column(name = "f_offer_url")
    private String offerUrl;
    @Column(name = "f_preview_url")
    private String previewUrl;
    @Column(name = "f_imro")
    private String imro;
    @Column(name = "f_app_name")
    private String appName;
    @Column(name = "f_is_private")
    private Boolean isPrivate;
    @Column(name = "f_redirect_url")
    private String redirectUrl;
    @Column(name = "f_country_id")
    private Integer countryId;
    @Column(name = "f_is_featured")
    private Boolean isFeatured;
    @Column(name = "f_user_email")
    private String userEmail;
    @Column(name = "f_click_lifespan")
    private Integer clickLifeSpan = 3600;
    @Column(name = "f_is_deviceid")
    private Boolean isDeviceId = true;
    @Column(name = "f_s2s_enabled")
    private Boolean s2sEnabled = false;
    @Column(name = "f_tpid")
    private Integer tpid;
    @Column(name = "f_s2s_params")
    private String s2sParams;
    @Column(name = "f_referrer")
    private String referer;
    @Column(name = "f_score")
    private Integer score = 0;
    @Column(name = "f_default_goal")
    private String defaultGoal = "download";
    @Column(name = "f_is_cap_reached")
    private Boolean isCapsReached = false;
    @Column(name = "f_offer_tag")
    private String offerTag = "default";
    @Column(name = "f_is_fraud_disabled")
    private Boolean isFraudDisabled = false;
    @Column(name = "f_is_whitelist_enabled")
    private Boolean isWhitelistEnabled = false;
    @Column(name = "f_is_advanced_targeting_enabled")
    private Boolean isAdvancedTargetingEnabled = false;
    @Column(name = "f_is_burn_velocity_enabled")
    private Boolean isBurnVelocityEnabled = false;
    @Column(name = "f_is_burn_velocity_blocked")
    private Boolean isBurnVelocityBlocked = false;
    @Column(name = "f_redirect_offer_id")
    private Integer redirectOfferId = 0;
    @Column(name = "f_is_clt_enabled")
    private Boolean isCltEnabled = false;
    @Column(name = "f_is_unique_goal")
    private Boolean isUniqueGoal = false;
    @Column(name = "f_is_encoded_id_supported")
    private Boolean isEncodedIdSupported = false;
    @Column(name = "f_impression_url")
    private String impressionUrl;
    @Column(name = "f_kpi_event")
    private String kpiEvent = "download";
    @Column(name = "f_revenue_event")
    private String revenueEvent = "download";
    @Column(name = "f_net_revenue")
    private Double netRevenue = 0.0;
    @Column(name = "f_is_carrier_targeting_enabled")
    private Boolean isCarrierTargetingEnable = false;
    @Column(name = "f_daily_click_cap")
    private Integer dailyClickCap = -1;
    @Column(name = "f_mandatory_affsub")
    private String mandatoryAffSub = "";
    @Column(name = "f_campaign_name")
    private String campaignName;
    @Column(name = "f_is_glance_oci_enabled")
    private Boolean isGlanceOciEnabled = false;
    @Column(name = "f_bundle_id")
    private String bundleId;
    @Column(name = "category")
    private String category = "UNDEFINED";
    @Column(name = "f_tracker_name")
    private String trackerName = "default";
    @Column(name = "click_redirection_url")
    private String clickRedirectionUrl = "";
    @Column(name = "imro_tag")
    private String imroTag;
    @OneToMany(mappedBy = "offerId",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL)
    @Fetch(value = FetchMode.SUBSELECT)
    private Set affiliates = new HashSet<>();
    @OneToMany(mappedBy = "offerId",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL)
    private Set affiliateFunds = new HashSet<>();

    @ToString.Exclude
    @OneToMany(mappedBy = "offerId",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            orphanRemoval = true)
    private Set geoTarget = new HashSet<>();

    @ToString.Exclude
    @OneToOne(mappedBy = "offer",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            optional = false)
    private OsVersionTargetingEntity targetOsVersions;

    @ToString.Exclude
    @OneToOne(mappedBy = "offer",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            optional = false)
    private KpiEventsEntity kpi;

    @ToString.Exclude
    @OneToMany(mappedBy = "offerId",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            orphanRemoval = true)
    private Set targetCarriers = new HashSet<>();

    @ToString.Exclude
    @OneToMany(mappedBy = "offerId",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            orphanRemoval = true)
    private Set creatives = new HashSet<>();

    @ToString.Exclude
    @OneToMany(mappedBy = "offerId",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            orphanRemoval = true)
    private Set schedules = new HashSet<>();

    @ToString.Exclude
    @OneToOne(mappedBy = "offer",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            optional = false)
    private OfferFundEntity offerFund;

    @ToString.Exclude
    @OneToMany(mappedBy = "offerId",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            orphanRemoval = true)
    private Set offerGoals = new HashSet<>();

    @ToString.Exclude
    @OneToMany(mappedBy = "offerId",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            orphanRemoval = true)
    private Set offerSchedulers = new HashSet<>();

    @ToString.Exclude
    @OneToOne(mappedBy = "offer",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL)
    private OfferConfigEntity offerConfig;

    @ToString.Exclude
    @OneToMany(mappedBy = "offerId",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            orphanRemoval = true)
    private Set offerDisableLinks = new HashSet<>();

    @ToString.Exclude
    @OneToMany(mappedBy = "offerId",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            orphanRemoval = true)
    private Set affiliatePostBacks = new HashSet<>();

    @ToString.Exclude
    @OneToMany(mappedBy = "offerId",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            orphanRemoval = true)
    private Set offerRuleMapping = new HashSet<>();

    @ToString.Exclude
    @OneToMany(mappedBy = "parentOfferId", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private Set childOffers = new HashSet<>();

    @ToString.Exclude
    @OneToMany(mappedBy = "offerId",
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            orphanRemoval = true)
    private Set clickDayPartings = new HashSet<>();

    @LastModifiedDate
    @Column(name = "modified_on")
    private LocalDateTime modifiedOn;

//    @OneToOne
//    @JoinColumn(name = "f_offer_id", referencedColumnName = "offer_id")
    @OneToMany(mappedBy = "offerEntity")
    @ToString.Exclude
    private List offerClidMapping;
    
    public OsVersionTargetingEntity getTargetOsVersions(){
        if(!Hibernate.isInitialized(targetOsVersions)){
            try{
                Hibernate.initialize(targetOsVersions);
            }
            catch (EntityNotFoundException e) {
                targetOsVersions = null;
            }
        }
        return targetOsVersions;
    }

    public  KpiEventsEntity getKpi(){
        if(!Hibernate.isInitialized(kpi)){
            try{
                Hibernate.initialize(kpi);
            }
            catch (EntityNotFoundException e) {
                kpi = null;
            }
        }
        return kpi;
    }

    public OfferConfigEntity getOfferConfig() {
        if(!Hibernate.isInitialized(offerConfig)){
            try{
                Hibernate.initialize(offerConfig);
            }
            catch (EntityNotFoundException e) {
                offerConfig = null;
            }
        }
        return offerConfig;
    }
}

OSEntity.java

package com.inmobi.affiliate.platform.v2.entities;

import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Data
@NoArgsConstructor
@Table(name = "t_os_mapping")
public class OsEntity {
    @Id
    @Column(name = "f_os_id")
    private Integer id;
    @Column(name = "f_os_name")
    private String name;
}

AffiliateOfferEntity.java

package com.inmobi.affiliate.platform.v2.entities;

import lombok.*;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import javax.persistence.*;
import java.time.LocalDateTime;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity
@EntityListeners(AuditingEntityListener.class)
@Table(name = "t_affiliate_offer")
public class AffiliateOfferEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "f_aff_off_id")
    private Integer id;

    @Column(name = "f_offer_id")
    private Integer offerId;

    @Column(name = "f_aff_id")
    private Integer affiliateId;

    @OneToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "f_aff_id", updatable = false, insertable = false)
    private AffiliateEntity affiliate;

    @Column(name = "f_agreed_tnc")
    private Boolean agreedTnc = false;

    @ManyToOne(fetch = FetchType.EAGER, cascade = {})
    @JoinColumn(name = "f_aff_offer_status")
    private StatusEntity status;

    @Column(name = "f_click_url")
    private String clickUrl;

    @Column(name = "f_impression_url")
    private String impressionUrl;

    @Column(name = "f_aff_daily_conversion_cap")
    private Integer dailyConversionCap = 0;

    @Column(name = "f_aff_daily_payout_cap")
    private Double dailyPayoutCap;

    @Column(name = "f_aff_daily_revenue_cap")
    private Double dailyRevenueCap;

    @Column(name = "f_aff_monthly_conversion_cap")
    private Integer monthlyConversionCap;

    @Column(name = "f_aff_monthly_payout_cap")
    private Double monthlyPayoutCap;

    @Column(name = "f_aff_monthly_revenue_cap")
    private Double monthlyRevenueCap;

    @Column(name = "f_is_cap_reached")
    private Boolean isConversionCapReached = false;

    @Column(name = "f_payout")
    private Double payout = 0.0;

    @Column(name = "f_is_default_payout")
    private Boolean isDefaultPayout = true;

    @Column(name = "f_aff_offer_rank")
    private Integer rank = 0;

    @Column(name = "f_aff_off_daily_click_cap")
    private Integer dailyClickCap = 0;

    @Column(name = "f_aff_off_click_cap_blocked")
    private Boolean isCLickCapReached = false;

    @CreatedDate
    @Column(name = "created_on")
    private LocalDateTime createdOn;

    @LastModifiedDate
    @Column(name = "modified_on")
    private LocalDateTime modifiedOn;

    @LastModifiedBy
    @Column(name = "modified_by")
    private String modifiedBy;
}

Now, we can use the Atomic Specifications by invoking them dynamically as shown below:

private Specification getOfferFilter(OffersListRequest request, Class type) {
        return Specification
                .where(EqualsFilter.getFilter("advertiserId", request.getAdvertiserId()))
                .and(EqualsInFilter.getFilter("offerStatusId", request.getOfferStatusId()))
                .and(EqualsInFilter.getFilter("offerId", request.getOfferId()))
                .and(IlikeFilter.getFilter("offerName", request.getOfferName()))
                .and(EqualsFilter.getFilter("offerCategoryId", request.getCategoryId()))
                .and(EqualsFilter.getFilter("defaultRevenue", request.getRevenue()))
                .and(IlikeFilter.getFilter("defaultGoal", request.getDefaultGoal()))
                .and(IlikeFilter.getFilter("bundleId", request.getBundleId()))
                .and(EqualsInFilter.getFilter("offerTag", request.getOfferTag()))
                .and(EqualsInFilter.getFilter("revenueEvent", request.getRevenueEvent()))
                .and(IlikeFilter.getFilter("os" + Constants.delimiter + "name", request.getOsName()))
                .and(EqualsInFilter.getFilter("os" + Constants.delimiter + "id", request.getOsId()))
                .and(IlikeFilter.getFilter("previewUrl", request.getPreviewUrl()))
                .and(EqualsFilter.getFilter("isCapsReached", request.getIsCapsReached()))
                .and(EqualsFilter.getFilter("isBurnVelocityBlocked", request.getIsBurnVelocityBlocked()))
                .and(NotInFilter.getFilter("advertiserId", request.getExcludedAdvertisers()))
                .and(EqualsFilter.getFilter("isExpired", request.getIsOfferExpired()))
                .and(EqualsFilter.getFilter("isPrivate", request.getIsPrivate()))
                .and(OfferServiceUtil.affiliateOfferEntityCollectionFilter(request))
                .and(OfferServiceUtil.offerClickDayPartingEntityCollectionFilter(request));
    }

and to execute the Specification, we extend our JpaSpecificationExecutor in our OfferRepository

public interface OfferRepository extends JpaRepository, JpaSpecificationExecutor {
}

and it can be invoked as:

public Page getFilteredOfferEntity(OffersListRequest request, SortRequest pageParameterRequest) {
        return offerRepo.findAll(
                getOfferFilter(request, OfferEntity.class),
                pageParameterRequest.getPageRequest(new OfferListMapping()));
    }

Specifications helped us solve our first requirement of generic yet configurable solution.

But, for our second requirement we needed a configurable solution to reduce latency and that's caused by ToMany mappings as it creates separate a query to check for the condition in the Collection of entities In ToMany mapping, or there could be another solution to create a single query with a condition to check whether at least a single entity exists for a given condition.

The second approach looked more promising as it reduces the number of queries being executed and hence reduces the number of network calls involved for all the queries, and thus reduces the overall latency for processing a request. Here, for our rescue we used Predicates and using them helped us generate a subquery for the condition to get executed in exists condition over a collection of entities. Furthermore, using Predicates allowed us to make the solution configurable based on the request and generic enough to serve all the requests using the same solution.

Now comes the logic of roots. We had to play a lot with this to achieve this solution. We can visualize it as a directed graph where the root node corresponds to the base entity where we execute the query, and all the fields and associated relationships can be considered as child nodes. So, to apply condition on a field/attribute we need to traverse to that attribute and provide the path of traversal so that JPA can fetch the value in that field to apply the condition. But there's a twist – we can easily provide the path of traversal for ToOne relationships as there exists only one child node for this relationship, but for ToMany relationships there lies a collection of entities and a SubRoot at the attribute/field which represents the ToMany relationships. Hence, we cannot simply pass the path of traversal to the attribute of ToMany relation entity as JPA behaves randomly for this scenario and doesn't apply condition in this particular case.

To solve this problem, we pointed the SubRoot to affiliates and created a subquery onto the subRoot and we correlate the SubRoot created to the original Root so that subQuery can be created in the root of the original query. And once we created the subRoot, we can now pass the traversal path to the attribute from the SubRoot created and create Predicates based on the parameters passed in the request that leads to dynamic Predicates generation and hence helps us to achieve our target.

public static Specification affiliateOfferEntityCollectionFilter(OffersListRequest request) {
        return ((root, criteriaQuery, criteriaBuilder) -> {
            Subquery subQuery = criteriaBuilder.createQuery(OfferEntity.class).subquery(AffiliateOfferEntity.class);
            Root subRoot = subQuery.correlate(root);
            Join affiliates = subRoot.join("affiliates");

//            subQuery.select(subRoot.join("affiliates"));
            List predicates = new ArrayList<>();
            if(request.getAffiliateId() != null) {
                predicates.add(
                        criteriaBuilder.equal(
                                affiliates
                                        .join("affiliate")
                                        .get("affiliateId"),
                                request.getAffiliateId()
                        )
                );
            }
            if(request.getAffiliateMapDailyClickCap() != null) {
                predicates.add(
                        criteriaBuilder.equal(
                                affiliates
                                        .get("dailyClickCap"),
                                request.getAffiliateMapDailyClickCap()
                        )
                );
            }
            if(request.getAffiliateMapStatusId() != null) {
                predicates.add(
                        criteriaBuilder.equal(
                                affiliates
                                        .join("status")
                                        .get("statusId"),
                                request.getAffiliateMapStatusId()
                        )
                );
            }
            if(request.getAffiliateMapStatusValue() != null) {
                predicates.add(criteriaBuilder.like(
                        criteriaBuilder.lower(
                                affiliates
                                        .join("status")
                                        .get("statusValue")
                        ),
                        criteriaBuilder.lower(criteriaBuilder.literal("%" + request.getAffiliateMapStatusValue().toString() + "%"))
                ));
            }
            if(Objects.nonNull(request.getAffiliateMapIsCapsReached())) {
                predicates.add(
                        criteriaBuilder.equal(
                                affiliates
                                        .get("isConversionCapReached"),
                                request.getAffiliateMapIsCapsReached()
                        ));
            }
            if(Objects.nonNull(request.getAffiliateMapRank())) {
                predicates.add(
                        criteriaBuilder.greaterThan(
                                affiliates
                                        .get("rank"),
                                request.getAffiliateMapRank()
                        )
                );
            }
            if(Objects.nonNull(request.getAffiliateMapIsClickCapBlocked())) {
                predicates.add(
                        criteriaBuilder.equal(
                                affiliates
                                        .get("isCLickCapReached"),
                                request.getAffiliateMapIsClickCapBlocked()
                        )
                );
            }
            if(Objects.nonNull(request.getExcludedAffiliates())) {
                predicates.add(
                        criteriaBuilder.not(
                                affiliates
                                .join("affiliate")
                                .get("affiliateId")
                                .in(request.getExcludedAffiliates())
                        )
                );
            }

            if(predicates.isEmpty())
                return null;
            subQuery.where(predicates.toArray(new Predicate[] {}));

            if(Boolean.TRUE.equals(request.getAffiliateMapFindAll())) {
                if (Boolean.TRUE.equals(request.getNewAffiliateOffers()))
                    return criteriaBuilder.not(criteriaBuilder.exists(subQuery));
                else
                    return null;
            }
            return criteriaBuilder.exists(subQuery);
        });
    }

So, this is how we leveraged the Spring Data Specifications to achieve dynamic query generation while reusing the atomic specification. Hope you enjoyed reading and find this useful.