SODA Query API

There are occasions when we don't want to query for exact field values, but rather for value ranges, objects not containing given member values, etc. This functionality is provided by the Constraint API.

Not

First, let's negate a query to find all pilots who are not Michael Schumacher:

QueryExample.java: retrieveByNegation
1private static void retrieveByNegation(ObjectContainer container) { 2 Query query = container.query(); 3 query.constrain(Pilot.class); 4 query.descend("name").constrain("Michael Schumacher").not(); 5 ObjectSet result = query.execute(); 6 listResult(result); 7 }

And 

Where there is negation, the other boolean operators can't be too far.

QueryExample.java: retrieveByConjunction
01private static void retrieveByConjunction(ObjectContainer container) { 02 Query query = container.query(); 03 query.constrain(Pilot.class); 04 Constraint constr = query.descend("name").constrain( 05 "Michael Schumacher"); 06 query.descend("points").constrain(new Integer(99)) 07 .and(constr); 08 ObjectSet result = query.execute(); 09 listResult(result); 10 }

Or 

QueryExample.java: retrieveByDisjunction
1private static void retrieveByDisjunction(ObjectContainer container) { 2 Query query = container.query(); 3 query.constrain(Pilot.class); 4 Constraint constr = query.descend("name").constrain( 5 "Michael Schumacher"); 6 query.descend("points").constrain(new Integer(99)).or(constr); 7 ObjectSet result = query.execute(); 8 listResult(result); 9 }

Greater, Smaller, Equal <>=

We can also constrain to a comparison with a given value.

Return pilots with more than 99 points:

QueryExample.java: retrieveByComparison
1private static void retrieveByComparison(ObjectContainer container) { 2 Query query = container.query(); 3 query.constrain(Pilot.class); 4 query.descend("points").constrain(new Integer(99)).greater(); 5 ObjectSet result = query.execute(); 6 listResult(result); 7 }

Return pilots with 99 or more points:

QueryExample.java: retrieveByEqualComparison
1private static void retrieveByEqualComparison(ObjectContainer container) { 2 Query query = container.query(); 3 query.constrain(Pilot.class); 4 query.descend("points").constrain(new Integer(99)).greater().equal(); 5 ObjectSet result = query.execute(); 6 listResult(result); 7 }

Using Default Values 

The query API also allows to query for field default values.

QueryExample.java: retrieveByDefaultFieldValue
01private static void retrieveByDefaultFieldValue( 02 ObjectContainer container) { 03 Pilot somebody = new Pilot("Somebody else", 0); 04 container.set(somebody); 05 Query query = container.query(); 06 query.constrain(Pilot.class); 07 query.descend("points").constrain(new Integer(0)); 08 ObjectSet result = query.execute(); 09 listResult(result); 10 container.delete(somebody); 11 }

String Comparisons

Like 

This is an equivalent to SQL "like" operator:

SodaExample.java: testLike
01public static void testLike() { 02 new File(DB4O_FILE_NAME).delete(); 03 ObjectContainer container = database(); 04 if (container != null) { 05 try { 06 Pilot pilot = new Pilot("Test Pilot1", 100); 07 container.set(pilot); 08 pilot = new Pilot("Test Pilot2", 102); 09 container.set(pilot); 10 11 // Simple query 12 Query query1 = container.query(); 13 query1.constrain(Pilot.class); 14 query1.descend("name").constrain("est"); 15 ObjectSet result = query1.execute(); 16 listResult(result); 17 18 // Like query 19 Query query2 = container.query(); 20 query2.constrain(Pilot.class); 21 // All pilots with the name containing "est" will be retrieved 22 query2.descend("name").constrain("est").like(); 23 result = query2.execute(); 24 listResult(result); 25 } catch (Db4oException ex) { 26 System.out.println("Db4o Exception: " + ex.getMessage()); 27 } catch (Exception ex) { 28 System.out.println("System Exception: " + ex.getMessage()); 29 } finally { 30 closeDatabase(); 31 } 32 } 33 }

startsWith, endsWith 

Compares a beginning or ending of a string:

SodaExample.java: testStartsEnds
01public static void testStartsEnds() { 02 new File(DB4O_FILE_NAME).delete(); 03 ObjectContainer container = database(); 04 if (container != null) { 05 try { 06 Pilot pilot = new Pilot("Test Pilot0", 100); 07 container.set(pilot); 08 pilot = new Pilot("Test Pilot1", 101); 09 container.set(pilot); 10 pilot = new Pilot("Test Pilot2", 102); 11 container.set(pilot); 12 13 Query query = container.query(); 14 query.constrain(Pilot.class); 15 query.descend("name").constrain("T0").endsWith(false).not(); 16 // query.descend("name").constrain("Pil").startsWith(true); 17 ObjectSet result = query.execute(); 18 listResult(result); 19 } catch (Db4oException ex) { 20 System.out.println("Db4o Exception: " + ex.getMessage()); 21 } catch (Exception ex) { 22 System.out.println("System Exception: " + ex.getMessage()); 23 } finally { 24 closeDatabase(); 25 } 26 } 27 }

Case Insensitive Queries

By default all string querying functions use case-sensitive comparison. startsWith and endsWith allow to switch between comparison modes using a parameter. However, if you need a case-insensitive comparison for like , equals or contains queries, it is recommended to use Native Queries as SODA does not provide such an option.

Contains 

Allows to retrieve objects constraining by included value (collection). If applied to a string will behave as "Like".

SodaExample.java: testContains
01public static void testContains() { 02 new File(DB4O_FILE_NAME).delete(); 03 ObjectContainer container = database(); 04 if (container != null) { 05 try { 06 ArrayList list = new ArrayList(); 07 Pilot pilot1 = new Pilot("Test 1", 1); 08 list.add(pilot1); 09 Pilot pilot2 = new Pilot("Test 2", 2); 10 list.add(pilot2); 11 Team team = new Team("Ferrari", list); 12 container.set(team); 13 14 Query query = container.query(); 15 query.constrain(Team.class); 16 query.descend("pilots").constrain(pilot2).contains(); 17 ObjectSet result = query.execute(); 18 listResult(result); 19 } catch (Db4oException ex) { 20 System.out.println("Db4o Exception: " + ex.getMessage()); 21 } catch (Exception ex) { 22 System.out.println("System Exception: " + ex.getMessage()); 23 } finally { 24 closeDatabase(); 25 } 26 } 27 }

Identity Comparison 

db4o database identity can also be used as a constraint. In this case only objects with the same database instance will be retrieved:

SodaExample.java: testIdentity
01public static void testIdentity() { 02 new File(DB4O_FILE_NAME).delete(); 03 ObjectContainer container = database(); 04 if (container != null) { 05 try { 06 Pilot pilot = new Pilot("Test Pilot1", 100); 07 Car car = new Car("BMW", pilot); 08 container.set(car); 09 // Change the name, the pilot instance stays the same 10 pilot.setName("Test Pilot2"); 11 // create a new car 12 car = new Car("Ferrari", pilot); 13 container.set(car); 14 15 // Simple Query: 16 Query query1 = container.query(); 17 query1.constrain(Car.class); 18 query1.descend("_pilot").constrain(pilot); 19 ObjectSet result = query1.execute(); 20 listResult(result); 21 22 // identity query: 23 Query query2 = container.query(); 24 query2.constrain(Car.class); 25 // All cars having pilot with the same database identity 26 // will be retrieved. As we only created Pilot object once 27 // it should mean all car objects 28 query2.descend("_pilot").constrain(pilot).identity(); 29 result = query2.execute(); 30 listResult(result); 31 } catch (Db4oException ex) { 32 System.out.println("Db4o Exception: " + ex.getMessage()); 33 } catch (Exception ex) { 34 System.out.println("System Exception: " + ex.getMessage()); 35 } finally { 36 closeDatabase(); 37 } 38 } 39 }

Sorting Results 

It is also possible to have db4o sort the results.

QueryExample.java: retrieveSorted
01private static void retrieveSorted(ObjectContainer container) { 02 Query query = container.query(); 03 query.constrain(Pilot.class); 04 query.descend("name").orderAscending(); 05 ObjectSet result = query.execute(); 06 listResult(result); 07 query.descend("name").orderDescending(); 08 result = query.execute(); 09 listResult(result); 10 }

All these techniques can be combined arbitrarily, of course. Please try it out. There still may be cases left where the predefined query API constraints may not be sufficient - don't worry, you can always let db4o run any arbitrary code that you provide in an Evaluation. Evaluations will be discussed in a Evaluations chapter.