[FIXED] Convert OrderBy list of String raw query to jpa query

Issue

Table:

ID  Code
1   Health
2   Freeze
3   Phone
4   Phone
5   Health
6   Hot

Order by list of String raw query:

select * from table 
order by FIELD(Code, 'Health', 'Phone', 'Freeze', 'Hot')

I want to convert this raw query to JPA query but can't found any way to do it. Please help me!

Note: The enviroment I'm using

• MySQL version: 8.0.25
• Spring boot version 2.5
• Java 11

Update 1: The condition FIELD(Code, 'Health', 'Phone', 'Freeze', 'Hot') should be a list. I also want to pass a list when convert this raw query to JPA query

Update 2: Thanks to @TimBiegeleisen answer and comment I realize that JPA is not suitable for my problem.

Solution

The FIELD expression you have, particular to MySQL, can be rewritten in ANSI SQL using a CASE expression:

ORDER BY CASE WHEN Code = 'Health' THEN 1
              WHEN Code = 'Phone'  THEN 2
              WHEN Code = 'Freeze' THEN 3
              WHEN Code = 'Hot'    THEN 4 END

We can use the following JPA query:

SELECT e, (CASE WHEN e.Code = 'Health' THEN 1
                WHEN e.Code = 'Phone'  THEN 2
                WHEN e.Code = 'Freeze' THEN 3
                WHEN e.Code = 'Hot'    THEN 4 END) AS ord
FROM Entity e
ORDER BY ord

As I mentioned in the comments above, you will need a fixed CASE expression here. You can't convert a Java list of strings into the CASE expression you'll need in your JPA query. A JPA query is backed by a prepared statement, and the structure of the query (but not the literal values) must be hard coded and fixed.

Answered By - Tim Biegeleisen
Answer Checked By - Mary Flores (JavaFixing Volunteer)