PreparedStatement not returning ordered ResultSet

Question

I am having some problems and I'm sure it's something stupid.

So I have a query like

SELECT name, id, xyz FROM table ORDER BY ?

then later down the road setting the ? doing a

ps.setString(1, "xyz");

I am outputting the query and the value of xyz in the console. When I loop through the ResultSet returned from the PreparedStatement the values are not in the correct order. They are in the returned order as if I had left the ORDER BY clause off. When I copy/paste the query and the value into TOAD it runs and comes back correctly.

Any ideas to why the ResultSet is not coming back in the correct order?

Answer 1

The database will see the query as

SELECT name, id, xyz FROM table ORDER BY 'xyz'

That is to say, order by a constant expression (the string 'xyz' in this case). Any order will satisfy that.

Answer 2

? is for parameters, you can't use it to insert column names. The generated statements will look something like

SELECT name, id, xyz FROM table ORDER BY 'xyz'

so that your entries are sorted by the string 'xyz', not by the content of column xyz.

Answer 3

Hi,

Why not run:

ps.setInteger(1, 3);

Regards.

EDIT: AFAIK Oracle 10g supports it.

Answer 4

PreparedStatement placeholders are not intend for tablenames nor columnnames. They are only intented for actual column values.

You can however use String#format() for this, that's also the way I often do. For example:

private static final String SQL_SELECT_ORDER = "SELECT name, id, xyz FROM table ORDER BY %s";

...

public List<Data> list(boolean ascending) {
    String order = ascending ? "ASC" : "DESC";
    String sql = String.format(SQL_SELECT_ORDER, order);
    ...

Another example:

private static final String SQL_SELECT_IN = "SELECT name, id, xyz FROM table WHERE id IN (%s)";

...

public List<Data> list(Set<Long> ids) {
    String placeHolders = generatePlaceHolders(ids.size()); // Should return "?,?,?..."
    String sql = String.format(SQL_SELECT_IN, placeHolders);
    ...
    DAOUtil.setValues(preparedStatement, ids.toArray());
    ...