PreparedStatement not returning ordered ResultSet

问题:

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?

回答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.

回答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.

回答3:

Why not run:

ps.setInteger(1, 3);

Regards.

EDIT: AFAIK Oracle 10g supports it.

回答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());
    ...

回答5:

The database will see the query like this

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

I think you should add more variable like order_field and order_direction

I assume you have a method like below and I give you an example to solve it

pulbic List<Object> getAllTableWithOrder(String order_field, String order_direction) {
    String sql = "select * from table order by ? ?";
    //add connection here

    PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
    ps.setString(1,order_field);
    ps.setString(2,order_direction);

    logger.info(String.valueOf(ps)); //returns something like: com.mysql.jdbc.JDBC4PreparedStatement@a0ff86: select * from table order by 'id' 'desc'

    String sqlb = String.valueOf(ps);
    String sqlc = sqlb.replace("'"+order_field+"'", order_field);
    String sqld = sqlc.replace("'"+order_direction+"'", order_direction);

    String[] normQuery = sqld.split(":");

    ResultSet result = conn.createStatement().executeQuery(normQuery[1]);

    while(result.next()) { 
        //iteration
    }

}