Spring Data Repository Query Precedence Tricks

Use Case

Given the following type:

@Entity
public class Book {
    @Id
    private Long id;
    private String title;
    private String edition;

    // Getters and Setters ...
}

1. check if there is any book whose title or edition matches the specified.

This one is easy to come up with. We could simply use the keyword Or to compose the query:

boolean existsByTitleOrEdition(String title, String edition);

2. check if there is any book whose title or edition matches the specified and is not itself.

This one looks just as easy as the first one, we could have just added another condition and compose with the keyword And:

boolean existsByTitleOrEditionAndIdIsNot(
    String title, String edition, Long id);

Is that so?

When this method name is translated to SQL, it reads surprisingly as

test if any book whose title matches the specified or (edition matches the specified and is not itself)

What is the problem?

The problem is Spring data repository methods doesn't support precedence with parenthesis. That is why it only honors its default precedence when building the query.

Solution 1

We could just not use the repository method to build the query, but instead using the @Query or @NamedQuery to build the query with JPQL:

    @Query("SELECT CASE WHEN (COUNT(b) > 0) then true else false end FROM Book a"
        + " WHERE b.id <> :id AND (b.title = :title OR b.edition = :edition)")
    boolean existsByTitleOrEditionWithIdIsNot(
        @Param("id") Long id, 
        @Param("title") String title, 
        @Param("edition") String edition);

Solution 2

We know that (A or B) and C <==> (A and C) or (B and C) is true. We could compose the repository method with an extra duplicated or condition to make it work.

boolean existsByTitleAndIdIsNotOrEditionAndIdIsNot(
    String title, Long id1, String edition, Long id2);

Caveats

This trick can build queries with precedence without writing any JPQL, but it comes at the cost of verbose -- not just in long and hard to read method name, but also in the method arguments -- notice that the same id is expressed twice with two different method local variable names.

Source Code

@Entity
public class Book {
    @Id
    private Long id;
    private String title;
    private String edition;

    // Getters and Setters ...
}

@Repository
public interface BookRepository extends JpaRepository<Book, Long> {
    // A or B
    // test if any book whose title or edition matches the specified
    boolean existsByTitleOrEdition(String title, String edition);
    
    // A or (B and C)
    // test if any book whose 
    //    title matches the specified or 
    //    (edition matches the specified and is not itself)
    boolean existsByTitleOrEditionAndIdIsNot(
        String title, String edition, Long id);

    // Solution 1
    @Query("SELECT CASE WHEN (COUNT(b) > 0) then true else false end FROM Book a"
        + " WHERE b.id <> :id AND (b.title = :title OR b.edition = :edition)")
    boolean existsByTitleOrEditionWithIdIsNot(
        @Param("id") Long id, 
        @Param("title") String title, 
        @Param("edition") String edition);

    // Solution 2
    // (A or B) and C <==> (A and C) or (B and C)
    // test if any book whose 
    //     (title matches the specified and is not itself) or 
    //     (edition matches the specified and is not itself)
    boolean existsByTitleAndIdIsNotOrEditionAndIdIsNot(
        String title, Long id1, String edition, Long id2);
}

Zhenyang Hua