如何将带多个LIKE和AND条件的SQL转换为Spring Boot JPA查询语句

首先确保实体类和数据库表映射正确，参考如下实体写法，字段和你给出的Customers表属性一一对应：

import jakarta.persistence.*;

@Entity
@Table(name = "Customers")
public class Customer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "CustomerID")
    private Long customerId;

    @Column(name = "CustomerName")
    private String customerName;

    @Column(name = "ContactName")
    private String contactName;

    @Column(name = "Address")
    private String address;

    @Column(name = "City")
    private String city;

    @Column(name = "PostalCode")
    private String postalCode;

    @Column(name = "Country")
    private String country;

    // 必须补充无参构造、所有字段的getter/setter，JPA实体要求必须存在无参构造
}

推荐写法：@Query注解写JPQL（逻辑完全匹配原SQL，无优先级坑） #

JPQL是JPA定义的面向对象查询语句，操作实体属性而非数据库表字段，能完美复现原SQL的括号优先级逻辑，是这类带复杂逻辑查询的首选方案。
首先定义Repository接口继承JpaRepository：

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;

public interface CustomerRepository extends JpaRepository<Customer, Long> {
    @Query("SELECT c FROM Customer c WHERE (c.customerName LIKE %:nameKey% OR c.address LIKE %:addrKey%) AND c.country = :country AND c.city = :city")
    List<Customer> queryMatchCustomers(
            @Param("nameKey") String customerNameKeyword,
            @Param("addrKey") String addressKeyword,
            @Param("country") String targetCountry,
            @Param("city") String targetCity
    );
}

调用时直接传入对应参数即可，要实现你给出的SQL查询效果，调用方式如下：

// 传入参数对应原SQL的Chitta、Permanent、UK、London
List<Customer> result = customerRepository.queryMatchCustomers("Chitta", "Permanent", "UK", "London");

可选写法：原生SQL查询 #

如果你更习惯直接写原生SQL，可以开启nativeQuery属性，注意表名、列名必须和数据库中完全一致：

@Query(
    value = "SELECT * FROM Customers WHERE (CustomerName LIKE %:nameKey% OR Address LIKE %:addrKey%) AND Country = :country AND City = :city",
    nativeQuery = true
)
List<Customer> queryMatchCustomersNative(
        @Param("nameKey") String customerNameKeyword,
        @Param("addrKey") String addressKeyword,
        @Param("country") String targetCountry,
        @Param("city") String targetCity
);

注意避坑 #

不要尝试直接用方法名派生查询实现这个需求：Spring Data JPA方法名解析时AND优先级高于OR，如果直接写类似findByCustomerNameContainingOrAddressContainingAndCountryAndCity的方法，最终生成的SQL逻辑会变成CustomerName LIKE ? OR (Address LIKE ? AND Country = ? AND City = ?)，和你需要的(OR条件) AND 两个等于条件逻辑不一致，会查出不符合Country、City要求的脏数据。

内容的提问来源于stack exchange，提问作者CR Sardar