Deleting Data

The following methods will delete a car with a pilot having 5 points from each database:

SQLite:

SqlExample.java: deleteCar
01public static void deleteCar(){ 02 SQLiteDatabase db = database(); 03 if (db != null){ 04 long startTime = System.currentTimeMillis(); 05 int count = db.delete(DB_TABLE_CAR, "pilot in (select id from pilot where points = 5)", null); 06 if (count == 0){ 07 logToConsole(0, "Car not found, refill the database to continue.", false); 08 } else { 09 logToConsole(startTime, "Deleted selected object: ", false); 10 } 11 12 } 13 }

db4o:

(Select Car using Native Query)

Db4oExample.java: deleteCar
01public static void deleteCar(){ 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() == 5; 10 } 11 return false; 12 } 13 }); 14 Car car = (Car)result.next(); 15 container.delete(car); 16 logToConsole(startTime, "Deleted selected object: ", false); 17 } catch (Exception e){ 18 logToConsole(0, "Car not found, refill the database to continue.", false); 19 } 20 } 21 }

In this example db4o code looks much longer. But should we consider it a disadvantage? My opinion is - NO. Of course, SQLite seems to handle the whole operation in just one statement: db.delete(). But if you look attentively you will see that basically this statement just transfers all the difficult job to SQL: SQL statement should select a pilot with a given condition, then find a car. Using SQL can look shorter but it has a great disadvantage - it uses strings. So what will happen if the statement is wrong? You will never notice it till somebody in the running application will cause this statement to execute. Even then you might not see the reason immediately. The same applies to the schema changes - you may not even notice that you are using wrong tables and fields.

db4o helps to avoid all the above mentioned problems: query syntax is completely compile-checked and schema evolution will be spotted immediately by the compiler, so that you would not need to rely on code search and replace tools.