This set of examples shows how to use Native Queries to perform different calculations on the objects in the database. Store Pilots function is used to fill in the database.
Calculate the sum of the points of all the pilots in the database.
01private static void sumPilotPoints() { 02
ObjectContainer container = database(); 03
04
if (container != null) { 05
try { 06
SumPredicate sumPredicate = new SumPredicate(); 07
List<Pilot> result = container.query(sumPredicate); 08
listResult(result); 09
System.out.println("Sum of pilots points: " + sumPredicate.sum); 10
} catch (Exception ex) { 11
System.out.println("System Exception: " + ex.getMessage()); 12
} finally { 13
closeDatabase(); 14
} 15
} 16
}
1private static class SumPredicate extends Predicate<Pilot> { 2
private int sum = 0; 3
4
public boolean match(Pilot pilot) { 5
// return all pilots 6
sum += pilot.getPoints(); 7
return true; 8
} 9
}
Find a pilot having the minimum points.
01private static void selectMinPointsPilot() { 02
ObjectContainer container = database(); 03
if (container != null) { 04
try { 05
List<Pilot> result = container.query(new Predicate<Pilot>() { 06
public boolean match(Pilot pilot) { 07
// return all pilots 08
return true; 09
} 10
}, new QueryComparator<Pilot>() { 11
// sort by points then by name 12
public int compare(Pilot p1, Pilot p2) { 13
return p1.getPoints() - p2.getPoints(); 14
} 15
}); 16
if (result.size() > 0) { 17
System.out.println("The min points result is: " 18
+ result.get(0)); 19
} 20
} catch (Exception ex) { 21
System.out.println("System Exception: " + ex.getMessage()); 22
} finally { 23
closeDatabase(); 24
} 25
} 26
}
Calculate what is the average amount of points for all the pilots in the database.
01private static void averagePilotPoints() { 02
ObjectContainer container = database(); 03
04
if (container != null) { 05
try { 06
AveragePredicate averagePredicate = new AveragePredicate(); 07
List<Pilot> result = container.query(averagePredicate); 08
if (averagePredicate.count > 0) { 09
System.out 10
.println("Average points for professional pilots: " 11
+ averagePredicate.sum 12
/ averagePredicate.count); 13
} else { 14
System.out.println("No results"); 15
} 16
} catch (Exception ex) { 17
System.out.println("System Exception: " + ex.getMessage()); 18
} finally { 19
closeDatabase(); 20
} 21
} 22
}
01private static class AveragePredicate extends Predicate<Pilot> { 02
private int sum = 0; 03
04
private int count = 0; 05
06
public boolean match(Pilot pilot) { 07
// return professional pilots 08
if (pilot.getName().startsWith("Professional")) { 09
sum += pilot.getPoints(); 10
count++; 11
return true; 12
} 13
return false; 14
} 15
}
Calculate how many pilots are in each group ("Test", "Professional").
01private static void countSubGroups() { 02
ObjectContainer container = database(); 03
if (container != null) { 04
try { 05
CountPredicate predicate = new CountPredicate(); 06
List<Pilot> result = container.query(predicate); 07
listResult(result); 08
Iterator keyIterator = predicate.countMap.keySet().iterator(); 09
while (keyIterator.hasNext()) { 10
String key = keyIterator.next().toString(); 11
System.out 12
.println(key + ": " + predicate.countMap.get(key)); 13
} 14
} catch (Exception ex) { 15
System.out.println("System Exception: " + ex.getMessage()); 16
} finally { 17
closeDatabase(); 18
} 19
} 20
}
01private static class CountPredicate extends Predicate<Pilot> { 02
03
private HashMap countMap = new HashMap(); 04
05
public boolean match(Pilot pilot) { 06
// return all Professional and Test pilots and count in 07
// each category 08
String[] keywords = { "Professional", "Test" }; 09
for (int i = 0; i < keywords.length; i++) { 10
if (pilot.getName().startsWith(keywords[i])) { 11
if (countMap.containsKey(keywords[i])) { 12
countMap.put(keywords[i], ((Integer) countMap 13
.get(keywords[i])) + 1); 14
} else { 15
countMap.put(keywords[i], 1); 16
} 17
return true; 18
} 19
} 20
return false; 21
} 22
}