Pages

▼

Android SQLite + Starter code

You can save your repeating data in a database in android. It's a lightweight database which will only be accessible to your app. Below you will find starter code for handling databases in android.

Step 1: Declare database schema and contract

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
public final class TestContract {
    // this prevents users from initializing this class
    private TestContract () {}

    // create an abstact class like this for each table
    // BaseColumns adds _id variable which is used by cursors
    public static abstract class TestTable implements BaseColumns {
        public static final String TABLE_NAME = "test";
        public static final String COLUMN_NAME_TITLE = "title";
    }
}

Step 2: Declare database helper class. This automatically takes care of creating database and tables if they are not present.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
public class TestHelper extends SQLiteOpenHelper {
    // if you change the database schema, you must increase the database version
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "TestReader.db";

    private static final String TEXT_TYPE = " TEXT";
    private static final String COMMA = ",";
    private static final String SQL_CREATE_ENTRIES =
            "CREATE TABLE " + TestContract.TestTable.TABLE_NAME + " (" +
                    TestContract.TestTable._ID + " INTEGER PRIMARY KEY, " +
                    TestContract.TestTable.COLUMN_NAME_TITLE + TEXT_TYPE +
                    " )";

    private  static final String SQL_DELETE_ENTRIES =
            "DROP TABLE IF EXISTS " + TestContract.TestTable.TABLE_NAME;

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_ENTRIES);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(SQL_DELETE_ENTRIES);
        onCreate(db);
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }
}

Step 3: initialize TestHelper. Do this in activity's onCreate() method.

1
TestHelper th = new TestHelper(this);

Step 4: you can now perform database operations

INSERT

1
2
3
4
5
6
SQLiteDatabase db = th.getWritableDatabase();

ContentValues values = new ContentValues();
values.put(TestTable.COLUMN_NAME_TITLE, "sam");

long newRowId = db.insert(TestTable.TABLE_NAME, null, values);

READ

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SQLiteDatabase db = th.getReadableDatabase();

String[] projection = {
        TestTable._ID,
        TestTable.COLUMN_NAME_TITLE
};

Cursor c = db.query(TestTable.TABLE_NAME, projection, null, null, null, null, null);

c.moveToFirst();
do {
    Log.d(TAG, "onCreate database values "+c.getLong(c.getColumnIndex(TestTable._ID))+" "+c.getString(c.getColumnIndex(TestTable.COLUMN_NAME_TITLE)));
} while (c.moveToNext());

DELETE

1
2
3
4
5
6
SQLiteDatabase db = th.getReadableDatabase();

String selection = TestTable._ID + " LIKE ?";
String[] selectionArgs = { String.valueOf(1) };

int count = db.delete(TestTable.TABLE_NAME, selection, selectionArgs);

UPDATE

1
2
3
4
5
6
7
8
9
SQLiteDatabase db = th.getReadableDatabase();

ContentValues values = new ContentValues();
values.put(TestTable.COLUMN_NAME_TITLE, "sam updated");

String selection = TestTable._ID + " LIKE ?";
String[] selectionArgs = { String.valueOf(1) };

int count = db.update(TestTable.TABLE_NAME, values, selection, selectionArgs);

More info at
https://developer.android.com/training/basics/data-storage/databases.html