Using SQLite in Android

androidsqlite.md

First, we define our schema, which means that we have name of the table(s), columns and their data types organized; on paper.

Then, in the code, we make Contract class, say BlankContract and make entry (nested, static) classes in them, say SomeEntry(which is, well, a table)

import android.provider.BaseColumns; //BaseColumns interface provides _id and _count

public final class BlankContract {

    public static final class SomeEntry implements BaseColumns {

        public final static String TABLE_NAME  = "<table_name>";

        public final static String _ID = BaseColumns._ID;

       /*and all other columns here*/


    }

}

Actually Making A SQLite Database

For that, we use SQLiteOpenHelper class. So we make a class which extends SQLiteOpenHelper and implement methods from super class, namely onCreate and onUpgrade. And constructor like this:

public SomeDBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

and in OnCreate(SQLiteDatabase sqLiteDatabase) we make a String query variable(column names with constants) and execute with sqLiteDatabase.execSQL(query).

Inserting data in DB ft. ContentValues

So, SQLiteDatabase class has long insert(String table, String nullColumnHack, ContentValues values) method.

Note: nullColumnHack is pretty interesting. Because (it seems) SQL doesn't allow fully empty rows to be inserted into database, we can put nullable column name as nullColumnHack and if, for some reason, values turns out to be empty, insert method will put NULL into that nullable column(i.e.nullColumnHack parameter) and empty values into others.

    SomeDBHelper dbHelper = new SomeDBHelper(this);
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(SOME_COLUMN, "some value");
    long row_id = db.insert(SomeEntry.TABLE_NAME, null, values);
    
    //and then we can log row_id, if we want

Reading data from DB

So for retrieving data from database, we should GetReadableDatabase() and then execute query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy), which returns a Cursor implementation object(Cursor essentialy contains a result set).

To explain query:

• table is SomeEntry.TABLE_NAME;
• columns or projection is a String array which just contains column names.
• selection is what comes after WHERE clause(passing null will return all rows).
• selectionArgs are values for selection
• orderBy - how to order rows.

So now we have a Cursor object. We can get indexes of all columns with getColumnIndex(String columnName). I think they use this method mainly for clarity and for being less error-prone while assigning indexes.

Then we can go through the loop: while(cursor.moveToNext()) and get values from columns with getString(int index), getInt(int index), etc. therein.

Important Note: all this is done in try-finally statement, with cursor closing(cursor.close()) in the finally block. If we don't close cursor, it can cause memory leaks.