0

I have a MySql query like this:

select AK.*, max(AA.activityDate) 
from AssessmentKey AK 
join AssessmentActivity AA on AA.ASSESSMENTKEY_ID = AK.id 
group by AK.id having max(AA.activityDate) <=  '2012-10-02 17:30:55'

Is there a way to convert into in JPA NamedQuery. I am using OpenJPA.

If I put it directly into:

@NamedQuery(name = "AssessmentActivity.findByDate", 
  query = "select AK.*, max(AA.activityDate) from AssessmentKey AK 
           join AssessmentActivity AA on AA.ASSESSMENTKEY_ID = AK.id 
           group by AK.id having max(AA.activityDate) <=  '2012-10-02 17:30:55'")

The error is showed here: select AK.* that identifier expected, got "*" and also it does not like on, here it says:

enter image description here

How can I resolve this problem?

Hash
  • 4,647
  • 5
  • 21
  • 39
Paulius Matulionis
  • 23,085
  • 22
  • 103
  • 143

1 Answers1

2

First problem: you should replace AK.* with AK you just need the entity alias here.

Second problem: join syntax is not like that. You should write: join and navigate through the object references,eg: AK.assesmentActivity and use the where keyword instead of on

Here's a tip on join: JPQL join

Remember: you are in the ORM, dealing with Entities and their properties, not DB foreign keys and columns.

(ps: Maybe you wanted to write a NativeQuery? There you can use native SQL syntax)

EDIT: (on your comment) So you must start your query from AA:

select AK from AssesmentActivity AA join AssesmentKey AK where AA.assesmentKey = AK ...

This way you can join them.

Community
  • 1
  • 1
szegedi
  • 863
  • 8
  • 20
  • Thanks, I'll try to use NativeQuery and let you know if it worked. – Paulius Matulionis Oct 08 '12 at 08:54
  • NativeNamedQuery only works for the current table, it does not loads me other objects related to that table. So I am trying to make a NamedQuery, but there is a problem with join statement. The problem is that I can not do like this: AK.assesmentActivity because my assessment key does not have any mapping to assessment activity only the assessment activity have a many to one mapping on assessment key object. Any ideas how to make it work without changing the database, just how to convert this mysql query to jpql? – Paulius Matulionis Oct 08 '12 at 10:25