@NamedQuery and setting orderBy as a parameter?

[rocken7]

Lets say you have a ton of records in a content table, and a lot of criteria to determine appropriate content.

How would you specify the orderBy and sort on a @NamedQuery, say as a parameter or by overriding the default orderby etc. ?

I know i can annotate a bunch of @NamedQuery's but do I have to go and do the same huge query for every different orderBY and sort ? This will end up with at least 8-columns x 2 (asc|desc) = 16 named-queries, then if we alter the criteria, add another 16 named-queries. Its cr8zy.

It just seems to be begging for this simplicity:

Query q = em.createNamedQuery("findHottestContent");
q.setParameter("orderBy", "viewsWeekly desc");

Too many records to just load into a collection and post-sort like most documentation suggest doing, thats why there is an "order by" clause in SQL, thats what databases are good at.

I know I can do a native query but then I end up with cache issues.

Any ideas or coming patches to solve this?

[fhh]

Just from my database experience I would say that it is not possible. Any sane JPA-Provider will execute the query using a PreparedStatement and any sane JDBC driver will excute a prepared statement using bind variables.

AFAIK bind variables can only represent SQL literals like strings, numbers and dates but they can not be replaced by a database object like a column.

So your named query would result in something

SELECT * FROM TBL_HOT_CONTENT ORDER BY 'viewsWeekly desc'

and this will not work for obvious reasons. Please correct me if I am wrong.

Regards

Felix

[rocken7]

EJB3 is an ORM, therefore it shouldn't be so difficult to accept attributes (simple accessor method) for orderBy and then swap them out as it handles the query.

Suppose I haven't written much in the realm of JPA, but some kind of mapping or framework hint should be accounted for this kind of use.

Basically, this is a huge use-case for ejb3 and I'm suprised its not addressed, nor even discussed. Yet its the first thing anyone mentions when they get into implementing some ejb3 app.

Even if I handled the mapping on my end, viewsWeekly == 'c.views_weekly' etc.

[fhh]

Basically, this is a huge use-case for ejb3 and I'm suprised its not addressed, nor even discussed. Yet its the first thing anyone mentions when they get into implementing some ejb3 app.

Why is this a huge use case? Simply do something like this:

public final String findHottestQuery = "select c FROM Content c"

Query q = em.createNamedQuery(findHottestContent + " order by viewsWeekly desc");

If your queries access different database objects (and not just different data) you will not profit from named queries or parametrized statements anyway.

Regards

Felix

[mazz]

> Query q = em.createNamedQuery(findHottestContent + " order by viewsWeekly desc");

that doesn't work!! That's not a named query.

This is a very big thing missing in the spec, this much is clear.

[fhh]

that doesn't work!! That's not a named query.

It is not a named query but it does work. Why are you so keen on using a named query of you are not going to profit from it.

The restriction of not using a bind variable is - as far as I know - a restriction of databases not of the JPA provider. There is a work around for that if you are using Oracle (http://www.oracle.com/webapps/online-help/reports/10.1.2/state/content/navId.3/navSetId._/vtTopicFile.htmlhelp_rwbuild_hs%7Crwwhthow%7Cwhatare%7Cdmobj%7Csq_a_bind_references~htm/)
but to use it you will have to use a native query.

Regards

Felix