| JPA query that returns LAST (youngest) entity (pk index) for ANY table [message #533206] |
Wed, 12 May 2010 10:43  |
Eclipse User |
|
|
|
Originally posted by: natgross.ATgmail.com
To get the pk of the LAST saved record of ANY entity/table, I currently have the following query via the entity manager:
---------
(In the following snippet, 'columnName' & 'entityName' are supplied at runtime.)
final String qry = "Select o." + columnName + " from " + entityName + " o " + "ORDER by o." + columnName + " DESC";
Query query = em.createQuery(qry);
query.setMaxResults(1);
---------
This worked ok in the past. But now I have situations that the column name of the pk field(s) is unknown even at runtime.
So I am trying to get a query that works for any table/entity (name is of course known).
Something like the following (that works!):
--------
final String qryNewTest = "Select o from " + entityName + " o DESC"; //-----> Since there is no 'order by', DESC is in error.
qryNewTest.setMaxResults(1); //-----> Can't I simply tell it here to get the LAST 1?
----------
If no other choice I might have to use reflection to get the fields annotated with @Id, but I'd rather not use reflection.
Also, with reflection, if the pk is composite, how do I determine the order?
It's neater and more correct to have a proper jpa/sql query to achieve this goal.
Thank you all.
nat
|
|
|
|
| Re: JPA query that returns LAST (youngest) entity (pk index) for ANY table [message #533451 is a reply to message #533357] |
Thu, 13 May 2010 19:19  |
| Eclipse User |
|
|
|
Originally posted by: natgross.ATgmail.com
On Thu, 13 May 2010 09:55:54 -0400, James wrote:
> You could try,
>
> "Select o from " + entityName + " o order by o DESC"
>
> not sure if that will work, but might
Nope. JPQL exception.
> Otherwise you can get the ClassDescriptor for your Entity from the
> EntityManager's Session (getDescriptor()), and get the Id mapping from
> the ClassDescriptor or its ObjectBuilder.
Will look into this, versus reflection. I didn't know of ClassDescriptor.
> Note that this order by only gives you the last inserted record assuming
> you are using an incrementing generated Id. If you used a natural Id,
> then there is no concept to ordering (i.e. ssn).
Understood. Also if a *Unique* Timestamp is the id. (I manually adjust the nanos to ensure uniqueness.
However some databases hiccup if the primary key is a Timestamp.)
> Since you are assuming a generated Id already, maybe just assume it is
> named "id" as well.
Some like to name it key, some idKey, and what not. (Even more so if it's a date or a long representation of a date.)
> Otherwise you may try a native SQL query using a database function or ROWNUM.
Interesting thought but it's likely that the same SQL would not work for all db's/primary key types.
Thank you much!
-nat
PS. Am having some nntp server problems and my follow up might be delayed.
|
|
|
Powered by
FUDForum. Page generated in 0.26388 seconds
Home