时间戳

首页 > 解决方案 > 为什么“ORDER BY field_name LIKE 'start%'”在 Hibernate/Spring/JPA 中不起作用，而在普通 SQL 中起作用？

hibernate - 为什么“ORDER BY field_name LIKE 'start%'”在 Hibernate/Spring/JPA 中不起作用，而在普通 SQL 中起作用？

问题描述

问题：

我有一个在 ORDER BY 语句中需要 LIKE 的查询。虽然这在普通 SQL 中有效，但我无法让它在JPQLor中工作CriteriaQuery；所以问题总是出在order by LOWER(i.name) like "abc%" desc！

此查询有效并列出名称包含搜索词的项目。它首先返回名称以搜索词开头的项目，然后返回所有其他项目。这是纯 SQL 中的查询：

SELECT i.name, i.popularity
FROM item i
    left join picture p on p.id=i.picture_id and p.disabled=false
    left join picture b on p.id=i.background_picture_id and b.disabled=false
WHERE i.disabled=false and LOWER(i.name) like "%abc%"
order by LOWER(i.name) like "abc%" desc, i.popularity desc
limit 50;

虽然这是一个开始，但我更喜欢使用JpaRepositorywith@Query("..")或保持数据库独立。JPQLCriteriaQuery

@Query("SELECT i FROM ItemEntity 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 %:partialName% " +
    "order by (LOWER(i.name) like :partialNameOrderBy%) desc, i.popularity desc")
Page<ItemListView> findActiveItemsWhereNameContains(@Param("partialName") String partialNameLowerCase, @Param("partialNameOrderBy") String partialNameLowerCaseOrderByStartsWith, Pageable pageable);

此 JPQL 查询导致：

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: like near line 1, column

所以我尝试了这个CriteriaQuery：

@Override
public Page<ItemListView> findActiveItemsWhereNameContains(String partialName, int maxResults) {
    String partialNameLowerCase = StringUtils.lowerCase(StringUtils.trimToEmpty(partialName));
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<ItemListView> cq = cb.createQuery(ItemListView.class);
    Root<ItemEntity> item = cq.from(ItemEntity.class);
    Join<ItemEntity, Ct2PictureEntity> picture = item.join(ItemEntity_.picture, JoinType.LEFT);
    picture.on(cb.and(picture.getOn()), cb.isFalse(picture.get(Ct2PictureEntity_.disabled)));
    Join<ItemEntity, Ct2PictureEntity> backgroundPicture = item.join(ItemEntity_.backgroundPicture, JoinType.LEFT);
    picture.on(cb.and(backgroundPicture.getOn()), cb.isFalse(backgroundPicture.get(Ct2PictureEntity_.disabled)));

    Predicate itemIsActive = cb.isFalse(item.get(ItemEntity_.disabled));
    Predicate itemNameContainsSearch = cb.like(item.get(ItemEntity_.name), "%" + partialNameLowerCase + "%");
    Predicate itemNameStartsWithSearch = cb.like(item.get(ItemEntity_.name), partialNameLowerCase + "%");

    cq.select(cb.construct(
        ItemListViewDto.class,
        item.get(ItemEntity_.id),
        item.get(ItemEntity_.name),
        item.get(ItemEntity_.popularity),
        CriteriaBuilderUtils.getMediumAndThumbnailPicture(cb, picture),
        CriteriaBuilderUtils.getMediumPicture(cb, backgroundPicture)))
    .where(cb.and(itemIsActive, itemNameContainsSearch)).orderBy(cb.desc(itemNameStartsWithSearch), cb.desc(item.get(ItemEntity_.popularity)));

    TypedQuery<ItemListView> query = entityManager.createQuery(cq);

    Pageable pageable = PageRequest.of(0, maxResults);
    int totalRows = query.getResultList().size();
    query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
    query.setMaxResults(pageable.getPageSize());

    return new PageImpl<>(query.getResultList(), pageable, totalRows);
}

此查询导致：

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: like near line 1, column

问题：

如何使用 JPA 或 Hibernate 编写此查询并避免使用本机 SQL？

标签： hibernatespring-data-jpajpqlsql-likecriteria

解决方案

这里的问题是 Hibernate 不会将谓词强制为布尔表达式。您将不得不使用 egCASE WHEN (LOWER(i.name) like :partialNameOrderBy%) THEN 1 ELSE 0 END代替，您可以将其放入 order by 子句中。

推荐阅读