SpringData JPA "AND" and "OR" query not working as expected-CodePudding

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.

CodePudding user response：

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 quieries 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

CodePudding user response：

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

What do you mean by 'split'? Spring just generates this query by following simple rules:

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

Which is exactly what your method name states. But not the same as your native query.

a or b and c is the same as a or (b and c) but not the same as (a or b) and c

Add the following to your application.yml and you will see the native query used by spring:

logging:
  level:
    org.hibernate.SQL: debug