Hibernate/Spring/JPA中ORDER BY LIKE失效，如何用非原生SQL实现？

我之前也碰到过这个问题！JPQL和CriteriaQuery确实不允许在ORDER BY子句里直接使用LIKE表达式——这是因为JPQL的语法规范比原生SQL更严格，排序条件只能引用查询返回的字段、别名或者实体属性，不能直接放条件判断。不过有两种数据库无关的方案可以完美实现你要的逻辑，不用写原生SQL：

方案一：使用JPQL的CASE WHEN表达式模拟排序逻辑 #

JPQL支持CASE WHEN语法，我们可以用它把LOWER(i.name) LIKE 'abc%'这个条件转换成一个可排序的数值（匹配的返回0，不匹配的返回1），这样就能让以搜索词开头的条目优先展示。

带@Query的JpaRepository实现 #

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

public interface ItemRepository extends JpaRepository<Item, Long> {

    @Query("SELECT i.name, i.popularity FROM Item i " +
           "LEFT JOIN i.picture p ON p.disabled = false " +
           "LEFT JOIN i.backgroundPicture b ON b.disabled = false " +
           "WHERE i.disabled = false AND LOWER(i.name) LIKE %:searchTerm% " +
           "ORDER BY CASE WHEN LOWER(i.name) LIKE :prefixTerm THEN 0 ELSE 1 END, " +
           "i.popularity DESC")
    List<Object[]> searchItems(@Param("searchTerm") String searchTerm, @Param("prefixTerm") String prefixTerm);
}

服务层参数处理 #

因为我们需要两个参数（一个是包含搜索词的%abc%，一个是前缀匹配的abc%），可以在服务层统一处理：

@Service
public class ItemService {

    private final ItemRepository itemRepository;

    public ItemService(ItemRepository itemRepository) {
        this.itemRepository = itemRepository;
    }

    public List<Object[]> searchItems(String searchTerm) {
        String lowerCaseTerm = searchTerm.toLowerCase();
        // 构造两个参数：包含匹配和前缀匹配
        String containsTerm = "%" + lowerCaseTerm + "%";
        String prefixTerm = lowerCaseTerm + "%";
        return itemRepository.searchItems(containsTerm, prefixTerm);
    }
}

方案二：用CriteriaQuery实现相同逻辑 #

如果你更喜欢用CriteriaQuery（比如动态查询场景），同样可以用CriteriaBuilder的selectCase()方法实现：

import jakarta.persistence.EntityManager;
import jakarta.persistence.criteria.*;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public class ItemCustomRepositoryImpl implements ItemCustomRepository {

    private final EntityManager entityManager;

    public ItemCustomRepositoryImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    public List<Object[]> searchItems(String searchTerm) {
        String lowerCaseTerm = searchTerm.toLowerCase();
        String containsTerm = "%" + lowerCaseTerm + "%";
        String prefixTerm = lowerCaseTerm + "%";

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
        Root<Item> root = query.from(Item.class);

        // 左关联图片和背景图片，过滤未禁用的
        Join<Item, Picture> pictureJoin = root.join("picture", JoinType.LEFT);
        pictureJoin.on(cb.equal(pictureJoin.get("disabled"), false));

        Join<Item, Picture> backgroundPicJoin = root.join("backgroundPicture", JoinType.LEFT);
        backgroundPicJoin.on(cb.equal(backgroundPicJoin.get("disabled"), false));

        // WHERE条件：未禁用的Item，且名称包含搜索词
        Predicate itemDisabledPredicate = cb.equal(root.get("disabled"), false);
        Predicate nameContainsPredicate = cb.like(cb.lower(root.get("name")), containsTerm);
        query.where(cb.and(itemDisabledPredicate, nameContainsPredicate));

        // 指定返回字段
        query.multiselect(root.get("name"), root.get("popularity"));

        // 排序逻辑：CASE WHEN实现前缀匹配优先
        Expression<Integer> sortPriority = cb.selectCase()
                .when(cb.like(cb.lower(root.get("name")), prefixTerm), 0)
                .otherwise(1);
        query.orderBy(cb.asc(sortPriority), cb.desc(root.get("popularity")));

        // 设置分页限制
        TypedQuery<Object[]> typedQuery = entityManager.createQuery(query);
        typedQuery.setMaxResults(50);

        return typedQuery.getResultList();
    }
}

为什么原来的写法会报错？ #

原生SQL允许在ORDER BY里直接使用条件表达式，但JPQL的语法规范不支持——它要求排序条件必须是查询中明确返回的字段、别名，或者实体的持久化属性。直接写LOWER(i.name) LIKE 'abc%' DESC会被JPQL解析器判定为语法错误，所以抛出QuerySyntaxException。

这两种方案都是纯JPA兼容的，不需要依赖特定数据库，完美符合你的需求。

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