• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

how to select c.field as alias in JPA

 
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Everybody

how to write query in JPA-QL.

My query is

"select c.nameenglish as student, count(m.contact) as messagecount
from Message m , Contact c where m.contact=c.id " group by contact
order by messagecount desc";

but I am getting error in parsing [as].

Any solution or idea to solve this problem is appreciated
 
ranger
Posts: 17344
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why do you need the "as" clause at all, the query is going to return an Object[], so there is no naming indexes in an array.

A JPA Query is not the same syntax as SQL.

Mark
 
Syed Saifuddin
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi Mark Spritzler

sir but in case of aggregate function like count(field) how to get this function value.
 
Mark Spritzler
ranger
Posts: 17344
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Originally posted by Syed Saifuddin:
hi Mark Spritzler

sir but in case of aggregate function like count(field) how to get this function value.



Again it is an Object[][] that is being returned, the value of the count will be one of the indexes in the object array. Objects don't have names.

So in your query, the count is the second item, so when you get a row from the Object[][] it will be the second item it the array.

so for each Object[x][y], the y will equal "1"

Now you can do another trick where you create a new object of your special type

[CODE]
public class MyObject
{

private String student;
private int count;

public MyObject(String student, int count) {
this.student = student;
this.count = count;
}

//Getters and Setters here
}
[\CODE]

then your JPA QL could be

"select new MyObject(c.nameenglish, count(m.contact))
from Message m , Contact c where m.contact=c.id " group by m.contact";

So now you will get a Collection of MyObject, and now to get the values you would be able to call myObjectVar.getCount();

Then sort it later too.

Mark
 
Syed Saifuddin
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi Mark Spritzler

Thank You Mark Spritzler this really solve the big part of my problem.
but 1 more thing is left

in start of query I say count(contact) as smscount
and in last I write order by smscount desc.

If I dont put alias to the function how can I use order by
 
Mark Spritzler
ranger
Posts: 17344
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Originally posted by Syed Saifuddin:
hi Mark Spritzler

Thank You Mark Spritzler this really solve the big part of my problem.
but 1 more thing is left

in start of query I say count(contact) as smscount
and in last I write order by smscount desc.

If I dont put alias to the function how can I use order by



Exactly.

You just need to remember that JPA-QL is not SQL.

Try the alias again in the count and what happens. If you keep getting an error then you know you can't use that alias in the query and might just have to put the count() function call in the order by. Try them both out and see what happens.


Mark
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!