无法在 JPA+spring 数据中设置简单的 LIKE+忽略案例查询

Question

我一直在尝试返回以给定名称开头的城市的基本列表，但没有成功，我已经尝试了以下所有组合：

1. 在 Spring Data 中使用纯 JPA 关键字：

List<City> findByNameENStartingWithIgnoreCaseAndAdminDivisionAndCountry( String cityName, AdministrativeDivision administrativeDivision, Country country);

2. 使用 LIKE 和 CONCAT：

@Query("SELECT c from City c WHERE UPPER(c.nameEN) LIKE CONCAT(UPPER(:cityName), '%') AND c.adminDivision = :adminDivision AND c.country = :country") List<City> findByNameENStartingWithIgnoreCaseAndAdminDivisionAndCountry(@Param("cityName") String cityName, @Param("adminDivision") AdministrativeDivision administrativeDivision, @Param("country") Country country);

我正在使用 Spring Boot 2.03、Spring 数据 2.08 及以下的城市实体定义：

@Entity
@Data
@Table(name="city",
uniqueConstraints=@UniqueConstraint(name = "uniqueCityConstraint", columnNames={"name_en", "country_iso3166_numeric3", "admin_division_id"}))
public class City  extends AbstractBaseEntityId {
@Column(name="name_en")
private String nameEN;
@ManyToOne
@JoinColumn(name="admin_division_id")
private AdministrativeDivision adminDivision;
@ManyToOne
@JoinColumn(name="country_iso3166_numeric3", nullable=false)
private Country country;
}

和我的应用程序属性：

spring.datasource.url = jdbc:hsqldb:mem:spring
spring.datasource.username = sa
# Keep the connection alive if idle for a long time (needed in production)
spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1
# Show or not log for each sql query
spring.jpa.show-sql = true
# Hibernate ddl auto (create, create-drop, update, none)
spring.jpa.hibernate.ddl-auto = create
# Naming strategy
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.HSQLDialect

和我的城市存储库：

package com.billpayments.repository;
import com.billpayments.domain.AdministrativeDivision;
import com.billpayments.domain.City;
import com.billpayments.domain.Country;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import java.util.List;
public interface CityRepository extends CrudRepository<City, Long> {
@Cacheable(value="cityByNameAdminDivisionIdAndCountryCode", key="#p0 + #p1.id + #p2.iso3166Alpha2")
City findByNameENIgnoreCaseAndAdminDivisionAndCountry(String name, AdministrativeDivision administrativeDivision, Country country);
List<City> findByNameENIgnoreCaseAndCountry(String name, Country country);
@Query("SELECT c from City c WHERE UPPER(c.nameEN) LIKE CONCAT(UPPER(:cityName), '%') AND c.adminDivision = :adminDivision AND c.country = :country")
List<City> findByNameENStartingWithIgnoreCaseAndAdminDivisionAndCountry(@Param("cityName") String cityName, @Param("adminDivision") AdministrativeDivision administrativeDivision, @Param("country") Country country);

}

Answer 1

这是我在自己的测试用例中的错误，我没有通过正确的省份，所以我只是想确认实际上我发布的任一查询方法都可以正常工作。 我个人更喜欢 Option#1，因为它涉及更少的代码维护。