如何按带有分页的 Spring Data JPA 中的 SELECT 子句的别名对投影进行排序? [英] How to sort projection by alias from SELECT clause in Spring Data JPA with pagination?

问题描述

我创建了这两个实体来演示我的问题:

I created this two entites to demonstrate my problem:

OwnerEntity.java:

OwnerEntity.java:

@Entity
public class OwnerEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Size(min = 1)
    @OneToMany(mappedBy = "ownerEntity", cascade = CascadeType.ALL)
    private Set<ChildEntity> childEntities = new HashSet<>();
}

ChildEntity.java:

ChildEntity.java:

@Entity
public class ChildEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @NotNull
    @ManyToOne(optional = false)
    private OwnerEntity ownerEntity;

    public ChildEntity() {
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public OwnerEntity getOwnerEntity() {
        return ownerEntity;
    }

    public void setOwnerEntity(OwnerEntity ownerEntity) {
        this.ownerEntity = ownerEntity;
    }
}

我想编写一个查询来显示来自 OwnerEntity 和一个加入的 ChildEntity 的信息.我创建了一个投影:

I want to write a query that will show information from OwnerEntity and one joined ChildEntity. I created a projection:

OwnerEntityProjection.java:

OwnerEntityProjection.java:

public interface OwnerEntityProjection {

    Long getId();

    String getName();

}

我的 JpaRepository:

My JpaRepository:

public interface OwnerEntityRepository extends JpaRepository<OwnerEntity, Long> {

    @Query(" SELECT                                        " +
           "       ownerEntity.id AS id,                   " +
           "       childEntities.name AS name              " +
           " FROM OwnerEntity ownerEntity                  " +
           " JOIN ownerEntity.childEntities childEntities  ")
    // There must be also WHERE clause, but for demonstration it is omitted
    Slice<OwnerEntityProjection> findAllPaginated(Pageable pageRequest);

}

现在当我运行这个简单的测试时:

Now when i run this simple test:

@Test
public void findAllPaginatedTest() {
    Pageable pageRequest = new PageRequest(0, 3, Sort.Direction.ASC, "name");
    Slice<OwnerEntityProjection> OwnerEntityProjectionsPaginated =
            ownerEntityRepository.findAllPaginated(pageRequest);
}

我收到以下错误:

org.hibernate.QueryException: could not resolve property: name of: com.example.domain.OwnerEntity

我还在日志中检查了生成的 JQPL:

I also checked generated JQPL in logs:

... order by ownerEntity.name asc

如您所见，Spring Data Jpa 附加了来自 FROM 子句的第一个实体别名.我发现如果我将 PageRequest 更改为:

As you can see Spring Data Jpa appended first entity alias from FROM clause. I found that if i change PageRequest to this:

new PageRequest(0, 3, Sort.Direction.ASC, "childEntities.name");

它可以正常工作，但我不想将排序属性传递给具有 JPQL 查询中某处别名的存储库.我想传递直接存在于存储库方法返回的投影中的属性.如果我在 JPQL 查询中手动编写 ORDER BY，如下所示:

It works without errors, but i don't want to pass ordering property to repository with aliases that are somewhere in JPQL query. I want to pass property that is directly present in projection that repository method returns. If i write ORDER BY manually in JPQL query like this:

... ORDER BY name ASC

然后这个查询也没有任何错误地运行，因为我可以从 ORDER BY 子句中引用 SELECT 子句中的别名.有没有办法告诉 Spring Data Jpa 在不附加来自 FROM 或 JOIN 子句的别名的情况下执行排序?像这样:

Then this query also run without any errors because i can reference aliases in SELECT clause from ORDER BY clause. Is there any way to tell Spring Data Jpa to perform ordering without appending aliases from FROM or JOIN clauses? Something like this:

new PageRequest(0, 3, Sort.Direction.ASC, "name") ===> ORDER BY name asc

推荐答案

这是 Spring Data 中的一个错误:未正确检测别名.在此处也有报道.

This is a bug in Spring Data: the aliases are not detected correctly. It has also been reported here.

在 QueryUtils 在 applySorting 方法中，只检测(外部)连接别名和正好有一对括号的函数别名.属性的简单别名当前不起作用.

In QueryUtils in the applySorting method, only (outer) join aliases and function aliases with exactly one pair of parenthesis are detected. A simple alias for a property doesn't work currently.

一种解决方法是在构建 PageRequest 时使用带有括号中的别名的 JpaSort.unsafe，例如

One workaround for this is to use JpaSort.unsafe with the aliases in parenthesis when building the PageRequest, e.g.

PageRequest.of(0, 3, JpaSort.unsafe(Sort.Direction.ASC, "(name)"))

顾名思义，这是不安全的，当根据用户输入动态排序时，它应该只用于硬编码排序.

This is unsafe as the name implies, when sorting dynamically based on user input, so it should only be used for hard-coded sorting.

这篇关于如何按带有分页的 Spring Data JPA 中的 SELECT 子句的别名对投影进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！