Oracle & java dynamic 'Order by' clause

Question

Hi guys,

I am trying to build a dynamic sql query in java (shown below)

    sqlStr = "Select * " +
        "from " + tableName

    if(tableName!=null){
        if(tableName.equals("Table1"){
            sqlStr = sqlStr.concat("order by city desc");
        }else if(tableName.equals("Table2"){
            sqlStr = sqlStr.concat("order by country desc");
        }else if(tableName.equals("Table3"){
            sqlStr = sqlStr.concat("order by price desc");
        }
    }

Now what i would like to do is to add a final 'else' statement which would order the query based on whether the table contains a column named 'custID'. There will be several tables with that column so i want to sort the ones that have that column by custID. (Rather than having hundreds of additional if statements for each table that does have that column name.) Is this possible? i have seen people using the 'decode' function but i cant work out how to use it here.

Answer 1

Use DatabaseMetaData to get the table information.

You can use the getTablexxx() and getColumnxx() methods to get the table information.

Connection conn = DriverManager.getConnection(.....);
DatabaseMetaData dbmd = conn.getMetaData();
dbmd.getxxxx(); 

Note: you forgot space in your code before ORDER BY clause.

Answer 2

The most straight-forward way to do it is to read the column definitions from USER_TAB_COLUMNS or ALL_TAB_COLUMNS and check for the existence of a custID column. Without crazy PL/SQL tricks, you won't be able to solve this in SQL alone.

BTW, there is a " " missing between tableName and the order by clauses.

Answer 3

I understand that you're looking for a solution that can do this in one query, i.e. without running a separate metadata query beforehand.

Unfortunately, this won't be possible. The decode function can do some dynamic things with column values, but not with column name. And you're looking for a solution dynamically derive the column name.

An alternative might be to just add ORDER BY 1, 2. This is an old syntax that means order by the first and than by the second column. It might be a good solution if the custID column is the first column. Otherwise it at least gives you some sorting.

Answer 4

If you are happy with hardcoding things, a way to avoid multiple conditionals would be to store a list of all the tables that include custID.

private final static String tablesWithCustID = "/TableX/TableY/TableZ/";

...

if (tablesWithCustID.contains( tableName )) {
  sqlStr = sqlStr.concat("order by custID")
}

You could use a List instead of a simple delimited string if you like.

Perhaps better, you could store a map with table names as the key, and the sort string as the value. Load it up once, then you don't need any conditional at all.

Answer 5

How about ArrayList.contains()?
You can create a list of tables which have that column, and just check for tables.contains(tablename) in the final if condition.