Spring data jpa - How to combine multiple And and

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?

Answer 1

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.

Answer 2

Use something like

findByFirstElementAndCriteriaOrSecondElementAndCriteria

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

Answer 3

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(...)

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);
}