How to tell oracle to sort by a specific sort order passed from java?

Question

Here is what I need to do.

I have a list in java that I can convert to a separate line of identifiers, such as "3,4,5,6,1,2"

I wonder if there is a way to pass this string to oracle and have the sql code sort based on this row sort order?

So this query:

select t.id from t_test t 

The result of this order will be

 ID 3 4 5 6 1 2 

Answer 1

If you can change the request in java, you can do something like this:

 SELECT t.id FROM t_test t ORDER BY DECODE(t.id, 3, 'A', 'B') ASC, DECODE(t.id, 4, 'A', 'B') ASC, DECODE(t.id, 5, 'A', 'B') ASC, DECODE(t.id, 6, 'A', 'B') ASC, DECODE(t.id, 1, 'A', 'B') ASC, DECODE(t.id, 2, 'A', 'B') ASC; 

You must put the decoding in the order by clause for each item in the list. The second parameter in each decode is one of the elements in the list.

Answer 2

Something like that:

 with ordered_ids as ( select to_number(regexp_substr ('3,4,5,6,1,2','[^,]+',1,level)) as id, level as sort_order from dual connect by regexp_substr ('3,4,5,6,1,2','[^,]+',1,level) is not null ) select t.id from t_test t join ordered_ids oi on oi.id = t.id order by oi.sort_order; 

Perhaps you can make the literal '3,4,5,6,1,2' parameter for PreparedStatement, but I have not tested it.

Answer 3

I do not think that's possible. You can use only ascending or descending order in queries. But you can use a prepared statement like select * from t_test t where t.id = ? and run it for each identifier in your list and add the result to the list of results.

You can also try to do this using a stored procedure whose parameter is a list of identifiers

EDIT

Another idea is to use the IN operator on small fragments of your ID list (maybe 10 in each). Since this will return the results in an unspecified order, you can write a custom comparator or another class that will bring this list into the order you specify. Then you can combine all the sub-lists into one list of results. For a list with 100 entries and a lot size of 10, you only need 10 DB queries + some time to reorder

Answer 4

 select t1 from (select t.id t1 from t_test t order by t.id asc); 

Answer 5

In sleep mode you can do -

 public String getResult(String sortOrder){ SQLQuery query = getSession().createSQLQuery("select t from ( select t.id t from t_test t order by t.id=:sortOrder").addScalar("name", Hibernate.STRING); query.setString("sortOrder", sortOrder); return (String)query.uniqueResult(); }