Android SQLite Test

Most of the secret of implementing SQLite Test with JUnit unit tests, is in the use of the assert methods in the class org.junit.Assert. In this text I will take a closer look at what assert methods are available in this class.

The most easy way is to use InstrumentationRegistry in android test package

mDataSource = new RateDataSource(InstrumentationRegistry.getTargetContext());

We can start with a normal SQLite class with CRUD (Create, Read, Update and Delete).

public class RateDataSource {
    // Database fields
    private SQLiteDatabase mDatabase;
    private MySQLiteHelper mDbHelper;
    private String[] mAllColumns = {MySQLiteHelper.COLUMN_ID,
            MySQLiteHelper.COLUMN_COIN, MySQLiteHelper.COLUMN_VALUE};

    public RateDataSource(Context context) {
        mDbHelper = new MySQLiteHelper(context);
    }

    public void open() throws SQLException {
        mDatabase = mDbHelper.getWritableDatabase();
    }

    public void close() {
        mDbHelper.close();
    }

    public Rate createRate(String coin, double value) {
        ContentValues values = new ContentValues();
        values.put(MySQLiteHelper.COLUMN_COIN, coin);
        values.put(MySQLiteHelper.COLUMN_VALUE, value);
        long insertId = mDatabase.insert(MySQLiteHelper.TABLE_RATE, null,
                values);
        Cursor cursor = mDatabase.query(MySQLiteHelper.TABLE_RATE,
                mAllColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null,
                null, null, null);
        cursor.moveToFirst();
        Rate newComment = cursorToRate(cursor);
        cursor.close();
        return newComment;
    }

    public void deleteRate(Rate comment) {
        long id = comment.getId();
        System.out.println("Rate deleted with id: " + id);
        mDatabase.delete(MySQLiteHelper.TABLE_RATE, MySQLiteHelper.COLUMN_ID
                + " = " + id, null);
    }

    public List<Rate> getAllRates() {
        List<Rate> rates = new ArrayList<Rate>();

        Cursor cursor = mDatabase.query(MySQLiteHelper.TABLE_RATE,
                mAllColumns, null, null, null, null, null);

        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            Rate rate = cursorToRate(cursor);
            rates.add(rate);
            cursor.moveToNext();
        }
        // make sure to close the cursor
        cursor.close();
        return rates;
    }

    private Rate cursorToRate(Cursor cursor) {
        Rate rate = new Rate();
        rate.setId(cursor.getLong(0));
        rate.setCoin(cursor.getString(1));
        rate.setValue(cursor.getDouble(2));
        return rate;
    }

    public void deleteAll() {

        mDatabase.delete(MySQLiteHelper.TABLE_RATE, null, null);
    }
}

After that you can start with your unit test, don’t forget to open and close database:

import android.support.test.InstrumentationRegistry;
import android.support.test.runner.AndroidJUnit4;
import android.test.suitebuilder.annotation.LargeTest;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;

import java.util.List;

import static junit.framework.Assert.assertNotNull;
import static junit.framework.Assert.assertTrue;
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.MatcherAssert.assertThat;

@RunWith(AndroidJUnit4.class)
@LargeTest
public class SQLiteTest {

    private RateDataSource mDataSource;

    @Before
    public void setUp(){
        mDataSource = new RateDataSource(InstrumentationRegistry.getTargetContext());
        mDataSource.open();
    }

    @After
    public void finish() {
        mDataSource.close();
    }

    @Test
    public void testPreConditions() {
        assertNotNull(mDataSource);
    }

    @Test
    public void testShouldAddExpenseType() throws Exception {
        mDataSource.createRate("AUD", 1.2);
        List<Rate> rate = mDataSource.getAllRates();

        assertThat(rate.size(), is(1));
        assertTrue(rate.get(0).toString().equals("AUD"));
        assertTrue(rate.get(0).getValue().equals(1.2));
    }

    @Test
    public void testDeleteAll() {
        mDataSource.deleteAll();
        List<Rate> rate = mDataSource.getAllRates();

        assertThat(rate.size(), is(0));
    }

    @Test
    public void testDeleteOnlyOne() {
        mDataSource.createRate("AUD", 1.2);
        List<Rate> rate = mDataSource.getAllRates();

        assertThat(rate.size(), is(1));

        mDataSource.deleteRate(rate.get(0));
        rate = mDataSource.getAllRates();

        assertThat(rate.size(), is(0));
    }

    @Test
    public void testAddAndDelete() {
        mDataSource.deleteAll();
        mDataSource.createRate("AUD", 1.2);
        mDataSource.createRate("JPY", 1.993);
        mDataSource.createRate("BGN", 1.66);

        List<Rate> rate = mDataSource.getAllRates();
        assertThat(rate.size(), is(3));

        mDataSource.deleteRate(rate.get(0));
        mDataSource.deleteRate(rate.get(1));

        rate = mDataSource.getAllRates();
        assertThat(rate.size(), is(1));
    }
}

you can see the complete code in GitHub