Apply MySQL's ORDER BY FIELD In Hibernate

Rashidi · **Joined:** Tue Sep 20, 2011 4:03 am **Posts:** 1

Hello.

Currently I have a table with following schema:

Code:

+--------+------------------------------------+------+-----+---------+-------+
| Field  | Type                               | Null | Key | Default | Extra |
+--------+------------------------------------+------+-----+---------+-------+
| id     | int(11)                            | YES  |     | NULL    |       |
| name   | varchar(256)                       | NO   |     | NULL    |       |
| status | enum('ACTIVE','DELETED','PENDING') | YES  |     | NULL    |       |
+--------+------------------------------------+------+-----+---------+-------+

I want to SELECT user and sort them by status with particular order. My desired order is 'PENDING', 'ACTIVE', 'DELETED'. If I do it without any condition it will return me:

Code:

mysql> select * from user;
+------+-------+---------+
| id   | name  | status  |
+------+-------+---------+
|    1 | Smith | PENDING |
|    2 | Jason | ACTIVE  |
|    3 | Brad  | DELETED |
+------+-------+---------+
3 rows in set (0.02 sec)

With MySQL I can use ORDER BY FIELD. The result will be

Code:

mysql> select * from user ORDER BY FIELD('PENDING', 'ACTIVE', 'DELETED');
+------+-------+---------+
| id   | name  | status  |
+------+-------+---------+
|    1 | Smith | PENDING |
|    2 | Jason | ACTIVE  |
|    3 | Brad  | DELETED |
+------+-------+---------+
3 rows in set (0.05 sec)

Other than using HQL, is there any other way I can make Hibernate to execute same query?

Thanks in advanced for your helps.