Spring Data JPA - 是否可以对计算的属性进行排序?

Spring Data JPA - Is it possible to sort on a calculated property?(Spring Data JPA - 是否可以对计算的属性进行排序?)

问题描述

假设您有以下实体:

@Entity
public class Game {

    @Id
    @GeneratedValue
    private Integer id;

    private String name;

    private Calendar startTime;
    private int durationInSeconds;

    public GameStatus getStatus() {
        if( startTime.after(Calendar.getInstance()))
        {
            return GameStatus.SCHEDULED;
        } else {
            Calendar endTime = Calendar.getInstance();
            endTime.setTime(startTime.getTime());
            endTime.roll(Calendar.SECOND, durationInSeconds);

            if( endTime.after(Calendar.getInstance())) {
                return GameStatus.OPEN_FOR_PLAY;
            }
            else {
                return GameStatus.FINISHED;
            }
        }
    }
}

如果我的 GameRepository 是 PagingAndSortingRepository，我怎样才能获得按 status 属性排序的结果页面?

If my GameRepository is a PagingAndSortingRepository, how can I get a page of results, sorted by the status property?

我目前得到:

java.lang.IllegalArgumentException: Unable to locate Attribute  with the the 
given name [status] on this ManagedType [org.test.model.Game]

我可以理解，因为 status 确实没有 JPA 属性.有没有办法解决这个问题?

Which I can understand since status is indeed no JPA attribute. Is there a way around this?

(我在下面使用 Hibernate，所以任何特定于 Hibernate 的东西也可以)

(I am using Hibernate underneath, so anything Hibernate specific is also ok)

推荐答案

问题是Spring Data的PageRequest排序是在数据库层通过形成ORDER BY子句来完成的.

The problem is that Spring Data's PageRequest sort is done on the database layer by forming the ORDER BY clause.

您可以创建一个@Formula 列，例如

You could create a @Formula column, e.g.

@Entity
public class Game {
...
     // rewrite your logic here in HQL
     @Formula("case when startTime >= endTime then 'FINISHED' ... end")
     private String status;

然后可以按排序顺序使用新列，因为您在公式中编写的所有内容都将传递给 ORDER BY 子句.

Then it will be possible to use the new column in sort order as everything you write in the formula will be passed to ORDER BY clause.

这篇关于Spring Data JPA - 是否可以对计算的属性进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！