Spring Data JPA - 是否可以对计算属性进行排序？ [英] Spring Data JPA - Is it possible to sort on a calculated property?

问题描述

假设您拥有以下实体：

@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 ，如何获取结果页面，按排序状态属性？

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 - 是否可以对计算属性进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！