7

I have several NamedQuery's defined, and I'd like to be able to sort on a field for an entity dynamically, without having to create unique NamedQuery's for each field I want to sort on. For example:

I have an entity called MyObject, with fields 'a', 'b', and 'c'. My base query is "SELECT DISTINCT o FROM MyObject o", but I'd like to be able to add an ORDER BY clause to my query. Ideally, I'd be able to do something like named parameters, where my query would look like:

SELECT DISTINCT o FROM MyObject o ORDER BY :order

I would then specify the field (a, b, c) that I want to sort on. Is there any way to accomplish this using Seam/Hibernate/JPA? Is there a better strategy for tackling this?

Shadowman
  • 11,150
  • 19
  • 100
  • 198
  • 1
    This question is related to [Hibernate named query order by parameter](http://stackoverflow.com/questions/4120388/hibernate-named-query-order-by-partameter). Maybe the corresponding answer help you. – kraftan Nov 23 '10 at 21:58

2 Answers2

4

Named queries cannot be changed at run-time.

//----- Edited-part

public void getOrders(String orderByElement){

    String query = "SELECT DISTINCT o FROM MyObject o ORDER BY " + orderByElement;

    entityManager.createQuery(query).getResultList();
}

Its JPA specific.

Salvatorelab
  • 11,614
  • 6
  • 53
  • 80
Nayan Wadekar
  • 11,444
  • 4
  • 50
  • 73
  • That's exactly what I was looking for. But, would the query you provided work if I specified it as a JPA @NamedQuery? Or would it only work when created with entityManager.createQuery? I can't think of any reason why it wouldn't, but just want to make sure. – Shadowman Nov 23 '10 at 19:43
  • I don't think this solution works. According to the [JPQL Language reference](http://download.oracle.com/docs/cd/E11035_01/kodo41/full/html/ejb3_langref.html#ejb3_langref_input_params) input parameters are only allowed in `WHERE` and `HAVING` clauses. Have you tried that query? – kraftan Nov 23 '10 at 21:53
  • Edited my answer accordingly. – Nayan Wadekar Nov 24 '10 at 07:41
  • Okay, that said, does this have an impact on performance? I believe I recall reading something that said that NamedQuery's have better performance than creating a Query on the fly. Is this correct? – Shadowman Nov 24 '10 at 20:22
  • 1
    Yes, it gives better performance. Named queries are validated during deployment time & query definition is parsed only once and re-used afterwards. Deployment can fail if query is syntactically incorrect. They are scoped to the persistence unit. – Nayan Wadekar Nov 25 '10 at 03:27
  • 2
    On a side-note: if you use this solution in a website it'll be easy to hack using a simple SQL-injection. There's no other solution for dynamical Hibernate queries afaik so this is the correct solution, but add a method to check whether it's a valid argument and reject strings containing a 'select' or other suspicious SQL statements... – T_D Jun 07 '13 at 08:38
  • This is prone to SQL injection. It should not be done this way. – Rudi Kershaw May 21 '20 at 07:26
  • @RudiKershaw Can you revert correct way to accomplish it; would appreciate criticism with corrections. – Nayan Wadekar May 22 '20 at 09:46
  • @NayanWadekar there's no way to do it by altering a string query. Would either have to create your query programatically using the Criteria API, or build options into you query using case statements as described here https://stackoverflow.com/a/54448635/2182928. – Rudi Kershaw May 22 '20 at 20:46
3

See my solution in hibernate-named-query-order-by-partameter

Basic idea is to store the query without the 'order by clause and edit it on at run-time.

Community
  • 1
  • 1
SaSConsul
  • 298
  • 2
  • 9