You are here: Platform Specific Issues > Android > Comparison With SQLite > Changing Data

Changing Data

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

SQLite

SQLiteDatabase db = database();
db.execSQL("INSERT INTO REG_RECORDS (id,year) VALUES ('A1', DATETIME('NOW'))");

ContentValues updateValues = new ContentValues();
updateValues.put("reg_record", "A1");
int count = db.update(DB_TABLE_CAR, updateValues,
		"pilot IN (SELECT id FROM " + DB_TABLE_PILOT
				+ " WHERE points = 15)", null);
if (count == 0) {
	logToConsole(0, "Car not found, refill the database to continue.",
			false);
} else {
	Cursor c = db.rawQuery("SELECT c.model, r.id, r.year from car c, "
			+ "REG_RECORDS r, pilot p where c.reg_record = r.id "
			+ "AND c.pilot = p.id AND p.points = 15;", null);
	if (c.getCount() == 0) {
		logToConsole(0,
				"Car not found, refill the database to continue.",
				false);
		return;
	}
	c.moveToFirst();
	String date = c.getString(2);
	
	Date dt = parseDate(date);
	RegistrationRecord record = new RegistrationRecord(c.getString(1),dt);
	
	Car car = new Car();
	car.setModel(c.getString(0));
	car.setRegistration(record);
	logToConsole(startTime, "Updated Car (" + car + "): ", true);
}
SqlExample.java: update a car with SQLite

db4o

ObjectContainer container = database();
if (container != null){
	ObjectSet<Car>  result = container.query(new Predicate<Car>(){
		@Override
		public boolean match(Car car) {
			return car.getPilot().getPoints()==15;
		}
		
	});
	if (!result.hasNext()){
		logToConsole(0, "Car not found, refill the database to continue.", false);
	} else {
		Car car = result.next();
		logToConsole(startTime, "Selected Car (" + car + "): ", false);
		startTime = System.currentTimeMillis();
		car.setRegistration(new RegistrationRecord("A1", new Date()));
		logToConsole(startTime, "Updated Car (" + car + "): ", true);
	}			
}
Db4oExample.java: update a car with db4o

Conclusion

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.