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
  2. 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);
    }
  3. Create a Data layer class. This class will be responsible for using our DbHelper to perform our desired CRUD functions.
  4. 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();
         }
    }
  5. Add CRUD functions to your Android application
  6. 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
  2. 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;
         }
    }
  3. Create a DB Helper Class
  4. 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();
         }
    }
  5. Create a Data layer class.
  6. 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();
              }
         }
    }
  7. Add CRUD functions to your Android application
  8. 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]

  1. Gert Rijs
    Hi Wiebe, Did you know Fire Fox uses SQLite for its (what is it called) url-bar and history to quickly show previous sites. I have been using SQLite for my commercial programs several years now - great product. Gertje
  2. Matthias
    Hi, I've build an OR-Mapper for SQLite on Android. Check it out: https://github.com/mattelacchiato/androidb Matthias
  3. software development
    Appreciate your efforts to provide such a great read and info. Just bookmarked this post in delicious for future comeback.

Leave a Reply

*

captcha *