0

Well keep it simple I want to build a query to make it equivalent to:

select * from user where (name like ? or detail like ?) and age = ?

What I have tried so far:

Page<Post> findByNameOrDetailContainingAndAgeEquals(String query, int age, Pageable pageable);

Page<Post> findByNameContainingOrDetailContainingAndAgeEquals(String query, String query, int age, Pageable pageable);

All of these approaches failed. It seems that Spring split the query very wrong.

I have checked all the examples in https://github.com/spring-projects/spring-data-jpa/blob/main/src/test/java/org/springframework/data/jpa/repository/sample/UserRepository.java but all of those examples are pretty simple (most queries even with just one simple condition).

All I have read this answer How to combine multiple And and Or through method name and I agree with it. BUT, I don't think my scenario is complicated as the OP's, as you can see. Just "AND" and "OR" query condition combined which is quite easy in a SQL way but I just want make it by a JPA way.

Sam Su
  • 6,532
  • 8
  • 39
  • 80

1 Answers1

1

I don't think JPA can handle these complex conditions with brackets using out-of-the-box named queries. In addition, you're using the like clause, which named queries don't handle (those are for equality only).

Your best shot is probably use something like

@Query("SELECT u FROM User u WHERE (u.name like : name or u.detail like : detail) and u.age = :age"
Collection<User> findUsersByNameDetailAndAge(String name, String detail, int age);

Worst case if that doesn't work you'll have to use a native query here.

Check out here https://www.baeldung.com/spring-data-jpa-query for more examples/explanation

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nestor Milyaev
  • 5,845
  • 2
  • 35
  • 51
  • 2
    Actually in my case, the query condition for `name` and `detail` accept the same parameter. And in native SQL the brackets can even be removed which won't make it ambiguous. But it seems that JPA can not deal with it if you put both 'AND' and 'OR' in the clause. – Sam Su Oct 15 '21 at 10:57
  • 1
    For the `name` and `detail` to have the same value, you just pass the same value in the calling client, e.g, `findUsersByNameDetailAndAge("peter", "peter", 36);`, but you use the same query signature as I suggested above. Or, you can modify your query as `@Query("SELECT u FROM User u WHERE (u.name like :search or u.detail like : search) and u.age = :age"` and have the following signature: `Collection findUsersByNameDetailAndAge(String search, int age);` – Nestor Milyaev Oct 15 '21 at 14:54