jeudi 16 juin 2016

Order Page by different Date Properties with Spring Data

I am using Spring Data to access a SQL Database with mapped objects. I have got an object which has multiple date properties which are relevant in different states of the object.

public class Foo {
 Date deadlineOne; // relevant in State.ONE
 Date deadlineTwo; // relevant in State.TWO
 Date deadlineThree; // relevant in State.THREE
 State state; // implemented via persisted Enum
}

Now, in the UI I want to display these objects with a property called something like 'Next Deadline' and also be able to sort by this aspect. This 'Next Deadline' should always be the earliest Date relevant by the related state. So for example, if I have the following objects:

O1{state:ONE, deadlineOne: '2016-06-14', deadlineTwo: '2016-06-16', deadlineThree: '2016-06-19'}
O2{state:TWO, deadlineOne: '2016-06-12', deadlineTwo: '2016-06-13', deadlineThree: '2016-06-20'}
O3{state:THREE, deadlineOne: '2016-06-15', deadlineTwo: '2016-06-18', deadlineThree: '2016-06-21'}

I want them to be displayed like this when sorted by 'Next Deadline'

Object | Next Deadline | State
O2     | 2016-06-13    | TWO
O1     | 2016-06-14    | ONE
O3     | 2016-06-21    | THREE

I just can not figure out how to implement this functionality with Spring Data. Up to this point I have been using a PageRequest and got basic sorting working by just passing a property of my object. But that way I can not mix the sorting-properties the way I need to.

What I've tried so far

Implementing this functionality via JPA Query Annotaion using SQL Case syntax like this in a JpaRepository interface:

@Query("SELECT f FROM Foo f ORDER BY CASE
WHEN f.state = State.ONE THEN f.deadlineOne
WHEN f.state = State.TWO THEN f.deadlineTwo
WHEN f.state = State.THREE THEN f.deadlineThree")
Page<Foo> findSortedByNextDeadline(Pageable pageable);

The interpreter just cant seem to accept this and gives no useful error information.

Question

Is there any way to make this work with Spring Data and the right Query/PageRequest or do I have to maintain a seperate nextDeadline property or something of that matter?

Aucun commentaire:

Enregistrer un commentaire