How to Dynamically Modify Pre-defined JPA Queries Before Sending to EntityManager?

Question

I already have JPA query methods in several repositories in a project. I want to modify these queries before they are sent to the Entity Manager. The modification needs to be dynamic based on the incoming request.

Here are the approaches I've tried so far:

1. JPA Specification: This requires creating specifications in advance. However, I already have several JPA queries which I don’t want to alter. I need to apply dynamic filters to these existing queries at runtime. Using 'findByIdAndSpecification(String id, Specification)' doesn’t directly support adding runtime-created specifications to existing queries.

2. Hibernate Filters: Filters can be applied to multiple attributes, but the conditions need to be predefined. My condition need to be built dynamically. Example: I have 4-5 fields on which conditions can be applied. Conditions could be dynamic like it could OR / AND with any combination of those fields. In below example, I have to already specify that there is OR between status and category.

@FilterDef(name = "productFilter", 
       parameters = {
               @ParamDef(name = "statusParam", type = "string"),
               @ParamDef(name = "categoryParam", type = "string") } )
@Filter(name = "productFilter", condition = "status = :statusParam OR category = :categoryParam")

3. QueryRewriter: This works for JPQL and native queries. However, for named queries, it requires overriding the query by its name, not by its text string. For example, defining many named queries in the repository with no query value and then defining all the queries in a 'QueryRewriter' class.

It works in this way:

public interface MyRepository extends JpaRepository<User, Long>, QueryRewriter {
@Query(value = "select original_user_alias.* from SD_USER original_user_alias",
       nativeQuery = true, queryRewriter = MyRepository.class)
List<User> findByNativeQuery(String param);
@Query(value = "select original_user_alias from User original_user_alias",
       queryRewriter = MyRepository.class) 
List<User> findByNonNativeQuery(String param);
@Override
default String rewrite(String query, Sort sort) { 
    return query.replaceAll("original_user_alias", "rewritten_user_alias");
}}

However, I don't want to pass the query in a string for each method. There are so many JPA query methods already written, and it would be a tedious task to rewrite each query as a string. Instead, it should be able to read and modify the existing JPA query methods directly, like this:

public interface MyRepository extends JpaRepository<User, Long>, QueryRewriter {
@Query(queryRewriter = MyQueryRewriter.class)
List<User> findByUserName(String userName);
}}

Given these constraints, how can I dynamically apply preconditions to my predefined JPA queries?

Answer 1

If you want dynamics queries based on user input, take a look at my library, internally use Specifications. It might be right for you. Through this library your controller will be able to receive requests like this:

curl -X GET \
  'https://myexampledomain.com/persons?
  firstName=Biagio
  &lastName_startsWith=Toz
  &birthDate_gte=19910101
  &country_in=IT,FR,DE
  &address_eq=Via Roma 1,Via Milano/,1,20 West/,34th Street
  &company.name_in=Microsoft,Apple,Google
  &company.employees_between=500,5000'

or:

curl -X POST -H "Content-type: application/json" -d '{   "filter" : {
      "operator": "and", // the first filter must contain a root operator: AND, OR or NOT
      "filters" : [
        {
          "operator": "eq",
          "key": "firstName",
          "value": "Biagio"
        },
        {
          "operator": "or",
          "filters": [
            {
              "operator": "startsWith",
              "key": "lastName",
              "value": "Toz",
              "options": {
                "ignoreCase": true
              }
            },
            {
              "operator": "endsWith",
              "key": "lastName",
              "value": "ZZI",
              "options": {
                "ignoreCase": true,
                "trim" : true
              }
            }
          ]
        },
        {
          "operator": "in",
          "key": "company.name",
          "values": ["Microsoft", "Apple", "Google"]
        },
        {
          "operator": "or",
          "filters": [
            {
              "operator": "gte",
              "key": "birthDate",
              "value": "19910101"
            },
            {
              "operator": "lte",
              "key": "birthDate",
              "value": "20010101"
            }
          ]
        },
        {
          "operator": "between",
          "key" : "company.employees",
          "values": [500, 5000],
          "options": {
            "negate": true
          }
        }
      ]   },   "options": {
    "pageSize": 10,
    "pageOffset": 0,
    "sortKey": "birthDate",
    "sortDesc": false   }    }' 'https://myexampledomain.com/persons'

Answer 2

You want dynamic queries based on user input like if user provide filter1 you want to add it in query if provide filter5 then add it in query so you can do it by spring data specifications for generating advanced queries

this is the documentation https://docs.spring.io/spring-data/jpa/reference/jpa/specifications.html

findAll method in JpaRepository has one impl that receive Specification as argument

public class UserEventSpecification {

    public static Specification<UserEvent> withinTimeRange(LocalDateTime start, LocalDateTime end) {
        return (root, query, criteriaBuilder) -> criteriaBuilder.between(root.get("timestamp"), start, end);
    }

    public static Specification<UserEvent> hasUserId(String userId) {
        return (root, query, criteriaBuilder) -> criteriaBuilder.equal(root.get("userId"), userId);
    }

    // Add more static methods for other filters as needed
}

public class DynamicSpecificationBuilder {

    public static Specification<UserEvent> build(LocalDateTime start, LocalDateTime end, String userId) {
        List<Specification<UserEvent>> specs = new ArrayList<>();

        if (start != null && end != null) {
            specs.add(UserEventSpecification.withinTimeRange(start, end));
        }

        if (userId != null && !userId.isEmpty()) {
            specs.add(UserEventSpecification.hasUserId(userId));
        }

        // Add more conditions here as needed

        Specification<UserEvent> result = specs.get(0);
        for (int i = 1; i < specs.size(); i++) {
            result = result.and(specs.get(i));
        }

        return result;
    }
}

@Service
public class UserEventService {

    @Autowired
    private UserEventRepository userEventRepository;

    public List<UserEvent> getUserEvents(LocalDateTime start, LocalDateTime end, String userId) {
        Specification<UserEvent> spec = DynamicSpecificationBuilder.build(start, end, userId);
        return userEventRepository.findAll(spec);
    }
}