Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPA query that returns LAST (youngest) entity (pk index) for ANY table
JPA query that returns LAST (youngest) entity (pk index) for ANY table [message #533206] Wed, 12 May 2010 14:43 Go to next message
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 #533357 is a reply to message #533206] Thu, 13 May 2010 13:55 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You could try,

"Select o from " + entityName + " o order by o DESC"

not sure if that will work, but might

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.

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).

Since you are assuming a generated Id already, maybe just assume it is named "id" as well.

Otherwise you may try a native SQL query using a database function or ROWNUM.



James : Wiki : Book : Blog : Twitter
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 23:19 Go to previous message
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.
Previous Topic:MappedSuperclass doesn't work with dynamic weaving
Next Topic:entity.getAlist() reopen de emFactory and entityManager that was previously closed!!
Goto Forum:
  


Current Time: Fri Oct 31 12:26:54 GMT 2014

Powered by FUDForum. Page generated in 0.08056 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software