Spring Data JPA组合AND、OR查询不符合预期，如何实现指定SQL逻辑？

问题根因 #

Spring Data JPA 方法名解析规则默认AND优先级高于OR，你的两种写法都会被解析为where name like ? or (detail like ? and age = ?)，和你需要的(name like ? or detail like ?) and age = ?逻辑不符，第一种写法还漏了name字段的Containing关键字，不会生成模糊匹配逻辑，所以都达不到预期。

实现方案 #

方案1：使用@Query注解（固定条件场景首选，无歧义）

直接在Repository方法上声明JPQL，手动控制条件优先级：

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

// 替换实体字段为你实际的命名
@Query("SELECT p FROM Post p WHERE (p.name LIKE %:query% OR p.detail LIKE %:query%) AND p.age = :age")
Page<Post> findByNameOrDetailContainingAndAge(@Param("query") String query, @Param("age") int age, Pageable pageable);

如果需要使用原生SQL，补充nativeQuery和分页所需的count查询即可：

@Query(
  value = "SELECT * FROM post WHERE (name LIKE %?1% OR detail LIKE %?1%) AND age = ?2",
  countQuery = "SELECT count(*) FROM post WHERE (name LIKE %?1% OR detail LIKE %?1%) AND age = ?2",
  nativeQuery = true
)
Page<Post> findByNameOrDetailContainingAndAgeNative(String query, int age, Pageable pageable);

方案2：使用JPA Specification（动态条件场景首选）

如果查询条件后续可能动态增减，用Specification更灵活：

1. 让Repository继承JpaSpecificationExecutor接口：

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface PostRepository extends JpaRepository<Post, Long>, JpaSpecificationExecutor<Post> {
}

2. 构造查询条件：

import org.springframework.data.jpa.domain.Specification;
import jakarta.persistence.criteria.Predicate;

public Specification<Post> buildQuery(String query, int age) {
    return (root, criteriaQuery, criteriaBuilder) -> {
        Predicate nameLike = criteriaBuilder.like(root.get("name"), "%" + query + "%");
        Predicate detailLike = criteriaBuilder.like(root.get("detail"), "%" + query + "%");
        Predicate ageEqual = criteriaBuilder.equal(root.get("age"), age);
        return criteriaBuilder.and(criteriaBuilder.or(nameLike, detailLike), ageEqual);
    };
}

3. 执行查询：

Page<Post> result = postRepository.findAll(buildQuery(query, age), pageable);

内容的提问来源于stack exchange，提问作者Sam Su