SQLite Databases Presentation

SQLite Databases Presentation


  • Know how to create a SQLite database with tables of data
  • Fetch rows from SQLite database tables
  • Add, update and delete rows in SQLite database tables

SQLite databases

  • For storing larger amounts of data, a database is your best option
    • Not all data has to be in memory at any one point in time
    • Easy to sort and filter the data
  • Knowledge needed
    • Basic understanding of tables, columns, rows, relations/joins
    • You should know some about the SQL language (SQL92 to be specific)
    • SELECT * FROM Contacts WHERE City='London' ORDER BY LastName
  • Compatability
    • SQLite database files / file format is the same on many platforms
    • You can create a SQLite database on desktop and read it inside of an Android app
    • You can export your Android SQLite database to desktop and read/modify it

About SQLite

  • Open source library for single file database
    • Is threadsafe, meaning multiple applications can access the database file simultaneously
  • All column values are stored as text
    • SQLite will convert when you specify the type
    • No size of column string values either
    • Integer, Text
  • Identifier columns

Use SQLite on Android

  • Create your own class extended from SQLiteOpenHelper
  • Override onCreate to create the tables of the database
  • Create object of this class in your Activity
  • Get database object by calling getWritableDatabase
  • Now you can
    • Read data using rawQuery or query that returns a Cursor
    • Add data using insert and a collection of values in a ContentValues object
    • Update data using update and a ContentValues object
    • Delete data using delete
    • Execute other SQLite statements using execute


  • All tables should have a column named "_id" as a unique numeric identifier
    • There are a few places in the Android platform where this is important, or at least helpful.
  • Create your own class extended from SQLiteOpenHelper
    public class MyDatabase extends SQLiteOpenHelper {
      public MyDatabase(Context context) {
        super(context, "mydb", null, 1);
      public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE Items (_id integer primary key autoincrement,
          name text, description text, level integer)");
  • Use your database helper class in the Activity
    MyDatabase _mydata;
    _mydata = new MyDatabase(this);
    SQLiteDatabase db = _mydata.getWritableDatabase();

When you have the database object

  • Read all rows from a table
    Cursor table = db.rawQuery("SELECT * FROM Items ORDER BY name");
    while (table.moveNext()) {
      String name = table.getString("name");
  • Read a single row from a table
    Cursor row = db.rawQuery("SELECT * FROM Items WHERE name='Apple'");
    // Must always call moveNext to enter the row, even if just one
    if (row.moveNext()) {
      int columnIndex = row.getColumnIndex("price");
      int price = row.getInt(columnIndex);
      Toast.makeText(this, "Price of Apple is "+Integer.toString(price), Toast.LENGTH_SHORT).show();
    else {
      Toast.makeText(this, "Sorry, no Apples here!", Toast.LENGTH_SHORT).show();

Update and delete in a database table

  • Add a row to the table
    ContentValues row = new ContentValues();
    row.put("name", "Adam");
    db.insert("Items", null, row);
  • Update a row in the table
    int id = 5; // Assume we know the id of the row to update
    ContentValues row = new ContentValues();
    row.put("name", "Alice");
    db.update("Items", row, String.format("_id=%d", id), null);
  • Delete a row in the table
    int id = 5; // Assume we know the id of the row to delete
    db.delete("Items", String.format("_id=%d", id), null);

What if you need to add a column to a table?

  • Adding a new column to a SQLite database table without destroying existing data
    • Increase the version number in your SQLiteOpenHelper extended class
    • Implement the onUpdate method in your SQLiteOpenHelper extended class
    • Use the ALTER TABLE ADD COLUMN statement to add the column inside the onUpdate method
  • Example
      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion<=1)
          db.execSQL("ALTER TABLE Items ADD COLUMN level integer");

Show SQLite data in ListView

  • Retrieve a Cursor with the data from the SQLite database
  • Use a SimpleCursorAdapter for the ListView
    • Specify the open Cursor from a rawQuery or query call on the database
    • Specify an xml layout to use for the individual items
    • Specify an array of column names to match to the int indentifiers
    • Specify an array of int identifiers inside the xml layout for the column names
  • Example
    SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, 
      cursor, android.R.layout.simple_list_item_2,
      new String[] { "name", "desc" }, 
      new int[] { android.R.id.text1, android.R.id.text2 });

Updating the cursor

  • Create a new cursor
  • Replace the existing cursor with the new one
    ListView list = (ListView) findViewById(R.id.list);
  • Note that these API calls are marked deprecated
    • Ignored in course for simplicity reasons
    • Reason is that in a performant app, you should not do lengthy database operations (like query/requiery) on the main UI thread

Show customized ListView item

  • Create your own class that extends SimpleCursorAdapter
  • Override the bindView method
    • Called by framework when an item view should be filled with data
    • The view at hand might have been used previously with other data
    • Don't assume empty values, reset/empty each time
  • Example
    public void bindView(View view, Context context, Cursor cursor) {
      super.bindView(view, context, cursor);
      TextView text = (TextView)view.findViewById(android.R.id.text2);
      int columnIndex = cursor.getColumnIndex("price");
      text.setText(String.format("%d SEK", cursor.getInt(columnIndex)));