Skip to content
Advertisement

SQL result not shown as the same in Servlet

All.

I got SQL query that sorts the given data inside query.xml file.
As I test on SQLD(Oracle), it gives me back the sorted data as expected.

JavaScript

Then this Servlet takes the sorted data from the above code.
I expected the sorted data to be added in ArrayList, however, the result I got back is unsorted data.

JavaScript

I will be very appreciated your advice!

Advertisement

Answer

This is because ? designates a placeholder value, so you always sort by constant.

Oracle distinguishes between constant int (that you pass via ?) and integer position of the column, that you put when you do select * from ... order by 1. For the latter 1 is a part of syntax and acts like an identifier. You can check this with below code.

As you see, the first query treats 1 as column position and sorts by the first column, when the second actually sorts by the second column.

JavaScript
S1 | S2
-: | -:
 1 | 10
 2 | 30
 3 | 20
JavaScript
S1 | S2
-: | -:
 1 | 10
 3 | 20
 2 | 30

db<>fiddle here

UPD: If you have predefined number of columns and it is not so much of them, you may use CASE (or DECODE in Oracle) to choose the column for sorting, but you’ll need to take care of datatypes: the same datatypes should be in the same CASE statement. And it will be hard to maintain such a code. For example:

JavaScript

Or more flexible way – compose dynamic SQL query at the app side and pass it to the DB. But take care of SQL injection, take the metadata to sort with the application code, not from some user input (like input field). Or check the input against DBMS dictionary for example:

JavaScript
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement