How-To: Android and Databases

Applications usually uses databases to store application data. Inside this article I will be explaining two implementation types:

• SQLite
• DB4o

I will use the Android application which was developed inside my previous post.

SQLite

SQLite is a lightweight relational database engine. SQLite is fast and has a small footprint, making it perfect for Android devices. Instead of the heavyweight server-based databases like Oracle and Microsoft SQL Server, each SQLite database is stored within a single file on disk. So lets set-up an implementation.

1. Create a DB Helper Class
public DbHelper(Context context) {
     super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase sqliteDb) {
     String sql = "CREATE TABLE " + TABLE_NAME + " (" + COLOMN_NAME+ " LONGTEXT" + ");";
     sqliteDb.execSQL(sql);
}

@Override
public void onUpgrade(SQLiteDatabase sqliteDb, int oldVersion,
          int newVersion) {
     Log.i(DbHelper.class.getName(), "Upgrading database from version " + oldVersion + " to "
               + newVersion + ", which will destroy all old data");
     sqliteDb.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
     onCreate(sqliteDb);
}
2. Create a Data layer class. This class will be responsible for using our DbHelper to perform our desired CRUD functions.
public void addSearchResult(String text) {
     SQLiteDatabase db = _dbHelper.getWritableDatabase();
     try {
          ContentValues values = new ContentValues();
          values.put(_dbHelper.COLOMN_NAME, text);
          db.insert(_dbHelper.TABLE_NAME, "", values);
     } finally {
          if (db != null)
               db.close();
     }
}

public void clearHistory() {
     SQLiteDatabase db = _dbHelper.getWritableDatabase();
     try {
          db.delete(_dbHelper.TABLE_NAME, "", null);
     } finally {
          if (db != null)
               db.close();
     }
}

public List<String> getHistory() {
     SQLiteDatabase db = _dbHelper.getWritableDatabase();
     try {
          List<String> results = new ArrayList<String>();
          Cursor c = db.rawQuery("select * from " + _dbHelper.TABLE_NAME, null);
          if (c.getCount() > 0) {
               c.moveToFirst();
               do {
                    results.add(c.getString(c.getColumnIndex(_dbHelper.COLOMN_NAME)));
               } while (c.moveToNext());
               }
               return results;
     } finally {
          if (db != null)
               db.close();
     }
}
3. Add CRUD functions to your Android application
Db dB = new Db(getBaseContext());
dB.addSearchResult(result);

private void clearHistory() {
     Db dB = new Db(getBaseContext());
     dB.clearHistory();
}

private void displayHistory() {
     Db dB = new Db(getBaseContext());
     List<String> history = dB.getHistory();
     
     ArrayAdapter<String> mHistory = new ArrayAdapter<String>(this, R.layout.row, R.id.ENTRY_CELL, history);
     
     historyList.setAdapter(mHistory);

}

Download source code AndroidSQLLite.zip [20kB]

DB4o

db4o is an object database, so no mapping of tables in a relational model. You can persist complex objects with nested collections or other complex objects, any level of complexity in your hierarchy. You’re not limited to flat objects with primitive types to get a reasonable performance. So lets set-up an implementation.

1. Create a persistent Object
public class HistoryItem {
     
     private String item;
     
     public HistoryItem() {
     }
     
     public HistoryItem(String item) {
          this.item= item;
     }

     public void setItem(String item) {
          this.item = item;
     }

     public String getItem() {
          return item;
     }
}
2. Create a DB Helper Class
public DbHelper(Context context) {
     try {
          if (database == null || database.ext().isClosed()) {
               database = Db4oEmbedded.openFile(configure(), db4oDBFullPath(context));
          }
     } catch (Exception ie) {
          Log.e(DbHelper.class.getName(), ie.toString());
     }
}

private static EmbeddedConfiguration configure() {
     EmbeddedConfiguration configuration = Db4oEmbedded.newConfiguration();
     configuration.common().objectClass(HistoryItem.class).objectField(
               "item").indexed(true);
     configuration.common().objectClass(HistoryItem.class).cascadeOnUpdate(true);
     configuration.common().objectClass(HistoryItem.class).cascadeOnDelete(true);
     return configuration;
}

private String db4oDBFullPath(Context ctx) {
     return ctx.getDir("data", DATABASE_MODE) + "/" + DATABASE_NAME;
}

public void close() {
     if (this.database != null) {
          this.database.close();
     }
}
3. Create a Data layer class.
public Db(Context c) {
     if(_dbHelper==null) {
          _dbHelper = new DbHelper(c);
     }
}

public void addSearchResult(String text) {
     ObjectContainer db = _dbHelper.getDatabase();
     try {
          db.store(new HistoryItem(text));
          db.commit();
     } finally {
          if (db != null) {
               db.close();
          }
     }
}

public void clearHistory() {
     ObjectContainer db = _dbHelper.getDatabase();
     try {
          ObjectSet<HistoryItem> items = db.queryByExample(new HistoryItem());
          for (HistoryItem historyItem : items) {
               db.delete(historyItem);
          }
          db.commit();
     } catch (Exception e) {
          Log.e(Db.class.getName(), e.toString());
     } finally {
          if (db != null) {
               db.close();
          }
     }
}

public List<String> getHistory() {
     ObjectContainer db = _dbHelper.getDatabase();
     try {
          List<String> results = new ArrayList<String>();

          ObjectSet<HistoryItem> items = db.queryByExample(new HistoryItem());
          for (HistoryItem historyItem : items) {
               results.add(historyItem.getItem());
          }
          return results;
     } finally {
          if (db != null) {
               db.close();
          }
     }
}
4. Add CRUD functions to your Android application
Db dB = new Db(getBaseContext());
dB.addSearchResult(result);

private void clearHistory() {
     Db dB = new Db(getBaseContext());
     dB.clearHistory();
}

private void displayHistory() {
     Db dB = new Db(getBaseContext());
     List<String> history = dB.getHistory();
     
     ArrayAdapter<String> mHistory = new ArrayAdapter<String>(this, R.layout.row, R.id.ENTRY_CELL, history);
     
     historyList.setAdapter(mHistory);

}

Download source code AndroidDb4o [2131kB]