0

I have a table like below

    ID      creationDate        startDate       status      
    1       10-08-2019 08:00    10-08-2019 08:00    1   
    2       10-08-2019 07:00    10-08-2019 08:00    2
    3       10-08-2019 09:00    10-08-2019 08:00    2
    4       10-08-2019 08:00    10-08-2019 10:00    1
    5       10-08-2019 11:00    10-08-2019 08:00    2

I have written a code like below to fetch data by considering both dates and status. Finally I want to sort the result by both creationDate and startDate .

from = 09-08-2019 08:00

to = 11-08-2019 08:00

Disjunction disjunction = Restrictions.disjunction();
Conjunction creationDateConjunction = Restrictions.conjunction(); 
creationDateConjunction.add(Restrictions.in("status", 2));
creationDateConjunction.add(between("creationDate", from, to));

Conjunction startDateConjunction = Restrictions.conjunction();
startDateConjunction.add(Restrictions.in("status",1));
startDateConjunction.add(between("startDate", from, to));

disjunction.add(creationDateConjunction);
disjunction.add(startDateConjunction);
criteria.add(disjunction);
criteria.addOrder(Order.asc("creationDate"));
criteria.addOrder(Order.asc("startDate"));

I am getting results (IDs) as 2,3,5,1,4 (First order by createDate then startDate)

But I want to get 2,1,3,4,5 (As time getting increase)

Can some one help me to write a query to get correct order

Inoshika Atapattu
  • 3
  • 1
  • 4
  • Can you show the SQL statement being generated by this code? – Laughing Vergil Sep 17 '19 at 16:53
  • Your code does not work as you want to pick the sorting key depending on another column. I would try to select a new, computed value with a case statement, give it an alias and order by this column. – Lesiak Sep 17 '19 at 19:27

1 Answers1

0

Try changing your query to predicates and add required sort order.

https://www.javacodegeeks.com/2013/04/jpa-2-0-criteria-query-with-hibernate.html

How to order result of hibernate based on a specific order

Ast
  • 143
  • 7