Creating queries using JPQL -- Query Syntax Exception

Question

When I run the method: dao.query("SELECT p FROM Profile p WHERE p.group = :id ORDER BY p.datestamp :key", map); I get the following error:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: : near line 1, column 93 [SELECT p FROM Profile p WHERE p.group = :id ORDER BY p.datestamp :key]

Following is the query method implemenation; anyone see what is wrong?

public List<?> query(String criteria, HashMap<String, ?> args) {
        Query sqlQuery = this.em.createQuery(criteria);
        Set<String> keys = args.keySet();
        Iterator<String> iter = keys.iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            sqlQuery.setParameter(key, args.get(key));
        }
        return sqlQuery.getResultList();
    }

WHat are you trying to achieve? This code looks completely meaningless now. — axtavt
@axtavt: I'm trying to select profiles which have a certain group id and I want to sort the profiles by their datestamp, in DESC or ASC depending on the :key parameter. — AMS12

axtavt axtavt · Accepted Answer · 2010-11-18T18:36:24

You cannot use parameters to specify sorting direction, because parameter cannot be used in arbitrary places of the query. From JPA spec:

Input parameters can only be used in the WHERE clause or HAVING clause of a query.

So, in JPA 1.0 you have to build query string with appropriate ORDER clause manually.

In JPA 2.0 you can use Criteria API to construct dynamic queries.

Creating queries using JPQL -- Query Syntax Exception

2 Answers