Hibernate Sorting Problem

[Siva Prasad Reddy Katamreddy]

Hi,

I have a situation where in i need to sort the records based on the status. The status value could be ACTIVE/PROCESSING/COMPLETED/TERMINATED.

Now i want to get the records like first i want to get all the ACTIVE records, then PROCESSING, then COMPLETED and then TERMINATED.

If i directly sort on status column i wont get the desired result.

So how can i implement it in Hibernate?

Thanks,
Siva

[David Newton]

Use the "sort" attribute and implement a comparator class.

[Siva Prasad Reddy Katamreddy]

Hi,

Once we got all the records we can sort it by using a Comparator.
But my question is how to implement it in Database side only?

Thanks,
Siva

[David Newton]

Put something with a natural order in the database instead of a string. You could probably do it with an enum as well. Or use SQL instead of HQL--I'm not even sure if this is doable with HQL, you'd have to check. If you're trying to save a lot of time, I'd wait until you know there's actually a performance issue.

[Siva Prasad Reddy Katamreddy]

Hi,

One solution which comes to my mind is as follows:

In my HQL i will write it as:

SELECT
CASE
WHEN emp.status='ACTIVE' THEN '1'
WHEN emp.status='PROCESSING' THEN '2'
WHEN emp.status='COMPLETED ' THEN '3'
WHEN emp.status='TERMINATED' THEN '4'
END status
FROM employee emp
ORDER BY status ASC

I hope it should work.

Thanks,
Siva

[Siva Prasad Reddy Katamreddy]

Hi,

I got your point. You mean to say instead of storing 'ACTIVE', 'PROCESSING' etc it is better to store 1,2 etc where 1 mean 'ACTIVE', 2 mean 'PROCESSING' etc...

Nice solution if the DB design is in our hands. But if we have to interact with legacy DB design where in it is storing STRING status values the above mentioned CASE-WHEN approach might be the way to go...

Thanks,
Siva