Hi Guys,
In short:
I am moving from plain JDBC to JPA on my web app (now JPA will work
over hibernate ORM but in future I plan to move to GAE hosting).
Question: How to create an effective query on JPA with calculated
field?
The details are as follows:
There is a user profiles table and there is a table that represents
"friend" relationship between them:
create table PROFILE (
ID integer,
NAME varchar(64) not null
);
alter table PROFILE add constraint IDX_PROFILE_ID primary key (ID);
create table FRIEND (
FROM_ID integer not null,
TO_ID integer not null
);
alter table FRIEND add constraint IDX_FRIENDS_IDS primary key
(FROM_ID, TO_ID);
alter table FRIEND add constraint FK_FRIENDS_FROM_ID foreign key
(FROM_ID) references PROFILE(ID);
alter table FRIEND add constraint FK_FRIENDS_TO_ID foreign key (TO_ID)
references PROFILE(ID);
One of the most frequently used queries is to select the users for a
given user A from the PROFILE table with the added calculated boolean
field that represents whether the friendship relationship is
established from user A to certain user B in the query output list.
The "social" query that illustrates the required scenario in
parameterized SQL notation may look as follows (:ID is a parameter for
user A) -
select P.NAME, exists(select * from FRIEND where FRIEND.FROM_ID = :ID
and FRIEND.TO_ID = P.ID) as IS_FRIEND
from PROFILE as P
where P.ID != :ID;
my code breaks the query output to the list of objects
SocialProfileData:
public class SocialProfileData {
private String name;
private boolean isFriend;
//+ getters/setters
}
The question is how to effectively implement the desired query in JPA?
It seems that the domain class shall look as follows:
public class ProfileData {
private int id;
private String username;
private Set<ProfileData> friends;
//+ getters/setters
}
but what about "social" query that shall return a collection of
SocialProfileData objects?
I guess that iterating over the ProfileData.friends and assigning
SocialProfileData.isFriend field by hand is too ineffective approach
because it results in multiple queries.
--
You received this message because you are subscribed to the Google Groups
"Google App Engine" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/google-appengine?hl=en.
