如何通过分页从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();

}

我的Jpa存储库:

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.

在

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屋！