Skip to main content

A simple JPA query made difficult with MAX ?

3 replies [Last post]
johnnydh
Offline
Joined: 2008-09-18

Dear All,

Having spent all my life utilising Google to answer my questions, I have come across a problem for which I can find no answer and yet it seems so obvious that I can not understand why! If anyone can help, I would appreciate it enormously...

I have an entity Record with a composite primary key consisting of Id and VersionNumber. I need to create a query in JPA which will return the latest Record i.e. highest Id and VersionNumber, in the most elegant way possible.

If this were SQL, I would select max(Id) AS ID, max(versionNumber) AS versionNumber and then group the rest of the fields. I would like to recreate something similar in JPA which returns a simple clean instance (or List) of Record(s).

Can anyone help?

Thanks, John

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
chris_delahunt
Offline
Joined: 2005-07-06

Hello John,

One way might be to use a seperate query to return the max values and then find the Records matching those values. This can be done in a subquery such as:
"SELECT record FROM Record record WHERE Id = (SELECT max(Id) FROM Record )"
Of course, this should return records with only the max ID. You could order them by versionNumber and then only take the first Record returned:
query = em.createQuery("SELECT record FROM Record record WHERE Id = (SELECT max(Id) FROM Record ) ORDER BY record.versionNumber"
query.setMaxRows(1);

There are probably many ways to do the same thing though.

Best Regards,
Chris

johnnydh
Offline
Joined: 2008-09-18

Thank you very much Chris. I had taken to creating a native sql query and mapping the result, however your method is much more elegant!

Regards,
John

Witold Szczerba

Maybe something like this:

@SuppressWarnings("unchecked")
List resultList = em.createQuery(
"SELECT r " +
"FROM Record r " +
"ORDER BY r.id DESC")
.setMaxResults(1)
.getResultList();

Record result = result.isEmty()
? null
: resultList.get(0);

Regards,
Witold Szczerba

2008/9/23 :
> Thank you very much Chris. I had taken to creating a native sql query and mapping the result, however your method is much more elegant!
>
> Regards,
> John
> [Message sent by forum member 'johnnydh' (johnnydh)]
>
> http://forums.java.net/jive/thread.jspa?messageID=300979
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
> For additional commands, e-mail: users-help@glassfish.dev.java.net
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
For additional commands, e-mail: users-help@glassfish.dev.java.net