Changing Data

For this test we will select and update a car with a new pilot, where existing pilot has 15 points:

SQLite:

SqlExample.java: updateCar
01public static void updateCar(){ 02 SQLiteDatabase db = database(); 03 if (db != null){ 04 long startTime = System.currentTimeMillis(); 05 // insert a new pilot 06 ContentValues updateValues = new ContentValues(); 07 08 updateValues.put("id", 101); 09 updateValues.put("name", "Tester1"); 10 updateValues.put("points", 25); 11 db.insert(DB_TABLE_PILOT, null, updateValues); 12 13 updateValues = new ContentValues(); 14 15 // update pilot in the car 16 updateValues.put("pilot", 101); 17 int count = db.update(DB_TABLE_CAR, updateValues, "pilot in (select id from pilot where points = 15)", null); 18 if (count == 0){ 19 logToConsole(0, "Car not found, refill the database to continue.", false); 20 } else { 21 logToConsole(startTime, "Updated selected object: ", false); 22 } 23 } 24 }

db4o:

(Select Car using Native Query)

Db4oExample.java: updateCar
01public static void updateCar(){ 02 ObjectContainer container=database(); 03 if (container != null){ 04 try { 05 long startTime = System.currentTimeMillis(); 06 ObjectSet result = container.query(new Predicate(){ 07 public boolean match(Object object){ 08 if (object instanceof Car){ 09 return ((Car)object).getPilot().getPoints() == 15; 10 } 11 return false; 12 } 13 }); 14 Car car = (Car)result.next(); 15 car.setPilot(new Pilot("Tester1", 25)); 16 container.set(car); 17 logToConsole(startTime, "Updated selected object: ", false); 18 } catch (Exception e){ 19 logToConsole(0, "Car not found, refill the database to continue.", false); 20 } 21 } 22 }

 In this example db4o and SQLite actually behave quite differently. For SQLite in order to update a pilot in an existing car in the database the following actions are needed:

  1. A new pilot should be created and saved to the database.
  2. New pilot's primary key (101) should be retrieved (not shown in this example, but is necessary for a real database application).
  3. An update statement should be issued to replace pilot field in the car table.

For db4o database the sequence will be the following:

  1. Retrieve the car from the database
  2. Update the car with a new pilot object

As you can see the only benefit of SQLite API is that the car can be selected and updated in one statement. But in the same time there are serious disadvantages:

In db4o we avoid these disadvantages as creating new pilot and updating the car value are actually combined in one atomic operation.