JPQL example queries

28 10 2008

Looking for JPQL queries, look no further. I use this as a reference page myself.

Search for Persons which all live in San Fransisco, New York or Frederikssund, traversal over single relation

SELECT p FROM Person WHERE p.city.id IN ("SF","NY", "FS")

Search for Persons with some of these tags(tags is a list of tags), traversal over list relations: Married, Divorced, OverTheHill

SELECT p FROM Person P JOIN p.tags t WHERE t.name IN ("married","divorced", "overthehill")

Above could be slow..
Search with enumerations:

SELECT p FROM Person WHERE p.enum = com.mypackage.enum.value
</code

Copy paste stuff:

public List searchForEvents(City city,
List eventTypeList,
List eventAdmittance,
List categoryTypeList)
{
List events = new ArrayList();
List categories = new ArrayList();
List admittanceTypes = new ArrayList();
if (eventTypeList.size() > 0 || categoryTypeList.size() > 0
|| eventAdmittance.size() > 0) {
for (EventType event : eventTypeList) {
events.add(EventType.class.getCanonicalName() + "."
+ event.name());
}

for (CategoryType categoryType : categoryTypeList) {
categories.add(CategoryType.class.getCanonicalName() + "."
+ categoryType.name());

}
for (EventAdmittanceType admittanceType : eventAdmittance) {
admittanceTypes.add(EventAdmittanceType.class
.getCanonicalName()
+ "." + admittanceType.name());

}

}
String jpql = "select e from Event e WHERE e.city.id='" + city.getId()
+ "'";
// types
List typesList = new ArrayList();

if (events.size() > 0) {
typesList.add("e.eventType IN ( " + buildJPQL(events, ",") + " ) ");
}
if (categories.size() > 0) {
typesList.add(" e.categoryType IN ( " + buildJPQL(categories, ",")
+ " )");
}
if (admittanceTypes.size() > 0) {
typesList.add(" e.eventAdmittanceType IN ( "
+ buildJPQL(admittanceTypes, ",") + " )");
}
if (typesList.size() > 0) {
jpql += " and " + buildJPQL(typesList, "and");
}

return getJpaTemplate().find(jpql);
}

private String buildJPQL(List sqlList, String delimiter) {
String constructedSQL = "";

for (String subSQL : sqlList) {
constructedSQL += subSQL + " " + delimiter;
}

if(sqlList.size()!=0){
constructedSQL = constructedSQL.substring(0, constructedSQL.length()
- delimiter.length());

}

return constructedSQL;
}

Advertisements

Actions

Information

One response

16 04 2009
Ann

I found good examples at http://www.searchjava.org with the keywords “jpql examples”
Hope this helps.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s