java - SQL result not shown as the same in Servlet

Question

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.

<entry key="ascUserList">
      SELECT 
            CLASS
           , ID
           , NAME
           , USER_NICK
           , EMAIL
           , PHONE
           , AREA_NAME
           , USER_GRADE
           , USER_POINT
           , GRADE_START
           , GRADE_END
        FROM 
            COM_INFO
        JOIN 
            USER_INFO ON (ID = USER_ID)
        JOIN 
            AREA USING(USER_AREA)
       WHERE 
             CLASS LIKE '%' || '??' || '%'
         AND  USER_SIGNUP LIKE '%' || 'Y' || '%'
     ORDER BY  ? ASC
   </entry>

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.

public ArrayList<UserInfo> ascUserList(Connection conn, String sort) {
      ArrayList<UserInfo> list = new ArrayList<>();
      PreparedStatement pstmt = null;
      ResultSet rset = null;
      String sql = prop.getProperty("ascUserList");
      System.out.println(sort.equals("??"));
      try {
         pstmt = conn.prepareStatement(sql);
         
         switch(sort) {
         case "????": pstmt.setString(1, "ID"); break;
         case "???": pstmt.setString(1, "ID"); break;
         case "??": pstmt.setString(1, "NAME"); break;
         case "???": pstmt.setString(1, "USER_NICK"); break;
         case "??": pstmt.setString(1, "AREA_NAME"); break;
         case "??": pstmt.setString(1, "USER_GRADE"); break;
         }
         
         rset = pstmt.executeQuery();

         while(rset.next()) {
            list.add(new UserInfo(rset.getString(2),
                    rset.getString(1),
                    rset.getString(5),
                    rset.getString(3),
                    rset.getString(6),
                    rset.getString(4),
                    rset.getString(7),
                    rset.getString(8),
                    rset.getDate(10),
                    rset.getDate(11),
                    Integer.parseInt(rset.getString(9))));
            System.out.println(list);
         }
         
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {
         close(rset);
         close(pstmt);
      }
      
      return list;
   }

I will be very appreciated your advice!

question from:https://stackoverflow.com/questions/65903759/sql-result-not-shown-as-the-same-in-servlet

Answer 1

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.

with a as (
  select 1 as s1, 10 as s2 from dual union all
  select 3, 20 from dual union all
  select 2, 30 from dual
)
select *
from a
order by 1, 2

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

with a as (
  select 1 as s1, 10 as s2 from dual union all
  select 3, 20 from dual union all
  select 2, 30 from dual
)
select *
from a
order by cast('1' as number), 2

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:

with a as (
  select
    'A' as COL1_VARCHAR,
    'B' as COL2_VARCHAR,
    sysdate as COL3_DATE,
    sysdate as COL4_DATE,
    1 as COL5_NUMBER,
    2 as COL6_NUMBER,
    3 as COL7_NUMBER
  from dual
)
select *
from a
order by
  /*varchar*/
  decode(?,
    1, COL1_VARCHAR,
    2, COL2_VARCHAR
  ) asc,
  /*date*/
  decode(?,
    3, COL3_DATE,
    4, COL4_DATE
  ) asc,
  /*number*/
  decode(?,
    5, COL5_NUMBER,
    6, COL6_NUMBER,
    7, COL7_NUMBER
  ) asc

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:

select count(1)
from all_tab_cols
where table_name = 'YOUR_TABLE_NAME_IN_FROM'
  and column_name = ? --user input with column name