How to combine multiple And and Or through method name

Question

I am trying to migrate the application. I am working on from Hibernate to Spring Data JPA.

Though Spring Data JPA offers simple methods for query building, I am stuck up in creating query method that uses both And and Or operator.

MethodName - findByPlan_PlanTypeInAndSetupStepIsNullOrStepupStepIs(...)

When it converts into the query, the first two expressions are combined and it executes as [(exp1 and exp2) or (exp3)], whereas required is ](exp1) and (exp2 or exp3)].

Can anyone please let me know if this is achievable through Spring Data JPA?

Mullens

Answer 1

I agree with Oliver on long and unreadable method names, but nevertheless and for the sake of argument, you can achieve desired result by using the equivalency

A /\ (B \/ C) <=> (A /\ B) \/ (A /\ C)
A and (B or C) <=> (A and B) or (A and C)

So in your case it should look something like this:

findByPlan_PlanTypeInAndSetupStepIsNullOrPlan_PlanTypeInAndStepupStepIs(...)

Locule

This worked basically - however: how to avoid duplicated variables with with approach? (if possible at all ....) – Libertylibia

yes, this works... It looks like you can't avoid duplicated parameters. I used a facade method to hide the awkward method signature – Cristobalcristobalite

Yes, that worked. Great use of principles. – Jolson

Answer 2

It's currently not possible and also won't be in the future. I'd argue that even if it was possible, with a more complex query you wouldn't want to artificially squeeze all query complexity into the method name. Not only because it becomes hard to digest what's actually going on in the query but also from a client code point of view: you want to use expressive method names, which — in case of a simple findByUsername(…) — the query derivation allows you to create.

For more complex stuff you' just elevate query complexity into the calling code and it's advisable to rather move to a readable method name that semantically expresses what the query does and keep the query complexity in a manually declared query either using @Query, named queries or the like.

Brockie

Answer 3

Use something like

findByFirstElementAndCriteriaOrSecondElementAndCriteria

is like (first & condition) OR ( second & condition) --> condition & ( first or second)

Spermatophyte

It will work, but the biggest disadvantage of it is that You have to pass as an argument the same criteria as many times as You use them in query. In this case You will have to pass two variables Criteria containing the same content: findByFirstElementAndCriteriaOrSecondElementAndCriteria(Element element1, Criteria criteria1, Element element2, Criteria criteria2) //criteria1.equals(criteria2) -> true – Bookmark

Answer 4

Option1: You could use named-queries (see Using JPA Named Queries):

@Entity
@NamedQuery(name = "User.findByEmailAddress",
  query = "select u from User u where u.emailAddress = ?1")
public class User {

}

public interface UserRepository extends JpaRepository<User, Long> {

   User findByEmailAddress(String emailAddress);
}

Option2: use @Query to write your custom queries (see Using @Query)

public interface UserRepository extends JpaRepository<User, Long> {

   @Query("select u from User u where u.emailAddress = ?1")
   User findByEmailAddress(String emailAddress);
}

Rib

Thanks for the reply! The only disadvantage of using NamedQuery or Query is its making the class look so messy when there are multiple join queries in the same repo class:( – Mullens

But then again, wouldn't your method-names start to look messy and become difficult to read? Query-deriving from the method-name is convenient for simple queries - at some point you should switch to @Queryor the like. – Rib

Answer 5

imagine you have this method in Spring Repository class:

Page<SystemEntity>  findByNameContainsAndFlagIsFalseOrFamilyContainsAndFlagIsFalseOrEmailContainsAndFlagIsFalse(String name, String family ,String email, Pageable pageable); 

one good practice is to break big query into its parts, then use them in this way:

Page<SystemEntity> findByNameContainsAndFlagIsFalse(String name, Pageable pageable); 

Page<SystemEntity> findByFamilyContainsAndFlagIsFalse(String family, Pageable pageable); 

Page<SystemEntity> findByEmailContainsAndFlagIsFals(String email, Pageable pageable); 


// Combine results from above methods
default Page<SystemEntity> findByPrimaryLogicalCondition(String name, String family ,String email, Pageable pageable){
    Page<SystemEntity> pageName = findByNameContainsAndFlagIsFalse(name, pageable);
    Page<SystemEntity> pageFamily = findByFamilyContainsAndFlagIsFalse(family, pageable);
    Page<SystemEntity> pageEmail = findByEmailContainsAndFlagIsFals(email, pageable);
    
    // Combine the results from both queries
    List<SystemEntity> combinedResults = new ArrayList<>(pageName.getContent());
    combinedResults.addAll(pageFamily.getContent());
    combinedResults.addAll(pageEmail.getContent());
    
    return new PageImpl<>(combinedResults, pageable, combinedResults.size());
}

This approach allows you to reuse the smaller methods for other queries if needed and keeps your code more modular and maintainable.

Danielson

Your proposal is very bad. You will sent multiple queries into db, instead of one. – Romulus