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;
}
