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, Commented Nov 18, 2010 at 13:37
@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
– AMS12, Commented Nov 18, 2010 at 18:24

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

4

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.

answered Nov 18, 2010 at 18:36

axtavt

243k42 gold badges518 silver badges486 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jim Garrison · Accepted Answer · 2010-11-17 23:47:24Z

0

I think you need a comma after ORDER BY p.datestamp and before :key

answered Nov 17, 2010 at 23:47

Jim Garrison

87k20 gold badges161 silver badges196 bronze badges

1 Comment

AMS12 Over a year ago

Thanks for the suggestion, but it doesn't quite work. Essentially, I'm trying to achieve this: SELECT p FROM Profile p WHERE p.group = :id ORDER BY p.datestamp [ASC|DESC] where the order is determined by the :key parameter.

Collectives™ on Stack Overflow

Creating queries using JPQL -- Query Syntax Exception

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related