Sorting problem

Hi,

I was rewriting our DAO to support pagging in large dataset and I think I have found a problem with sorting. I send you a test case - sorry for using the same project as previous, but it was faster to write this test case. As usual, you can load data with DataLoader and then check problem with QueryData.

There is a simple query with "order by" sentence:

    List<Branch> prodList = null;
    TypedQuery<Branch> icQuery = null;
    String q = null;
    q = "select br from Branch br ORDER BY ?1 DESC";
    icQuery = em.createQuery(q, Branch.class);
    icQuery.setParameter(1, "br.id");

This query should return all the branches sorted descending, but I get this result:

Branch: 100
Branch: 101
Branch: 102
Branch: 103
Branch: 104
Branch: 105
Branch: 106
Branch: 107
Branch: 108
Branch: 109
Branch: 110
Branch: 111
Branch: 112
Branch: 113

The same if I use setFirstResult() and setMaxResult();

Your query works well with a small change:

   String q = "select br from Branch br ORDER BY br.id DESC";
   icQuery = em.createQuery(q, Branch.class);

The problem with your original query is that parameters are limited to values (numbers, strings, dates and other objects). Using field names and other expressions as arguments is not supported.

For example, the following query is invalid:

    Query query = em.createQuery(":query");
    query.setParameter("query", "SELECT b FROM Branch b");

This is because parameters are not parsed as part of the query when the query is compiled. It is essential for better performance (same query can be used with different arguments without recompilation) and also for security sake (to avoid SQL injection when the parameter value is obtained from an untrusted user).

By the way, in your original query the results were sorted but by using a constant expression ("br.id" is considered as a string literal value rather than as a navigation expression), and because this constant expression is the same for all the result objects - the sort didn't have any effect.

Thanks,

So if I use this kind of method:

public List<Product> list(
    int start, int count, String sortColumn, boolean isAscending)

I have to prepare correct query as a String using values from sortColumn and isAscending and after that parse query with em.createQuery(stringQuery). Then it should work.

Yes, and ObjectDB manages a query program cache, so if you use the same sortColumn and isAscending values more than once (i.e. you repeat a query string) - ObjectDB doesn't have to recompile the same query every time again.