sorting - Using a prepared statement and variable bind Order By in Java with JDBC driver

Question

I'm using

1. jdbcTemplate to make JDBC connections to a mySQL DB
2. prepared statements to protect myself as much as possible from SQL injection attacks
3. in need to accept requests from the user to sort the data on any of a dozen different columns

4. the following statement

   jdbcTemplate.query("SELECT * FROM TABLE1 ORDER BY ? ?", colName, sortOrder);
   

Of course this doesn't work, because the variable bindings aren't supposed to specify column names just parameter values for expressions in the query.

So...how are people solving this issue? Just doing the sort in Java code seems like an easy solution, but since I'm getting a variable string for the column to sort on, and a variable telling me the sort order....that's an ugly number of comparator-conditions to cover. This seems like it should be a common problem with a common pattern to solve it...

Answer 1

Placeholders ? can only be used for parameter values but not with column and sort order directions. So the standard way to do this as is pointed e.g. here is to use String#format() or something similar to append your column name and order value to your query.

Another option is to use Spring Data JPA where you can give to your method as an argument an instance of type Sort which can contain all needed info for database to sort.