Using SQLite in your Android applications

Hi there!

Currently i'm developing some android apps which needs to access a SQLite database and use basic CRUD (create, read, update, delete) methods.  

I had some difficulties at the beginning and because I think that out there are a lot of other developers that would be happy to have a practical, reusable, well UML-documented solution, I decided to share my experience and solution with you. (this is just one solution of millions) I was looking for a design where the responsibilities were cleary separeted, making it easy to understand it without beeing to complex. If you have never worked with SQLite, you'll learn here how to instanciate and insert data into SQLite as well how to retrieve data from it and so on... We will end up with something like this at the end of this post:

Explaning the details of the UML-diagram first

I'm a convinced clean coder, so can't develop without UML-digrams. It's like the old saying: A picture says more then a thousand words. And in my point of view it is also a better documentation then tousend of lines of comments inside your code. (But i'm not saying that you don't should comment, when needed and helpful)

This tiny framework is showing to you how the design could be. The main classes here are DAO (Data Access Object), DMO (Data Manipulation Object), Table and the enum DbConfig. 

DbConfig defines all database's configuration central in one location, which makes it simple to change something in case you need. The inner enums defines all the needed things associated with the tables you may need. It is responsible for configuration tasks.

Table is abstract. All tables you may need extends from it making the implementation very simple. It is responsible for defining and creating tables.

DMO is also abstract and contais all manipulation methods you may need. It is up to you to extend it in the way you want.

Entity is a abstract class you may define to encapsulate common properties of the tables you may have in your application. It is responsible to hold the data readed from the database.

DAO is that class which accesses the database and populates the entities you've defined. Its responsibility is to access, read and return the entity you require.

ClientTest here is just a JunitTest representing your Activity when it uses the DAO.

Looking inside the code of DMO and Table

I think the code is a better teller then words. For this reason lets take a look inside of it to understand the details. We will beginn step by step and the logical way we would start, when developing. Lets assume we know what we want. What's the first step? We define the tables. So lets start with the DbConfig and its inner classes.

Enum DbConfig and its inner classes

/**
 * Defines all database associated information in a central, reusable, extensible way. (database, tables, columns, configuration etc.).<br/>
 * 
 * @author Ricardo Ferreira <a href="http://www.treslines.com">www.treslines.com</a>
 * 
 */
public enum DbConfig {

    DATABASE_TEST, TABLE_VIDEO, TABLE_FOTO, TABLE_AUDIO, TABLE_TEXTO;

    public static final int dbDefaultVersion = 1;
    public static final CursorFactory dbDefaultCursorFactory = null;

    /** defines in a central way the table column names from the table {@link TableFotoConfig} */
    public enum TableFotoConfig {

        ID, NOME, FOTO, TIMESTAMP;

        public static String generateCreateTableStatement() {
            final String c0 = NOME.name() + TEXT + COMMA;
            final String c1 = FOTO.name() + BLOB + COMMA;
            final String c2 = TIMESTAMP.name() + TEXT;
            final String creteStatement = c0 + c1 + c2;
            return creteStatement;
        }
    }

    /** defines in a central way the table column names from the table {@link TableVideoConfig} */
    public enum TableVideoConfig {

        ID, NOME, VIDEO, TIMESTAMP;
        public static String generateCreateTableStatement() {
            final String c0 = NOME.name() + TEXT + COMMA;
            final String c1 = VIDEO.name() + BLOB + COMMA;
            final String c2 = TIMESTAMP.name() + TEXT;
            final String creteStatement = c0 + c1 + c2;
            return creteStatement;
        }
    }

    /** defines in a central way the table column names from the table {@link TableAudioConfig} */
    public enum TableAudioConfig {

        ID, NOME, AUDIO, TIMESTAMP;

        public static String generateCreateTableStatement() {
            final String c0 = NOME.name() + BLOB + COMMA;
            final String c1 = AUDIO.name() + BLOB + COMMA;
            final String c2 = TIMESTAMP.name() + TEXT;
            final String creteStatement = c0 + c1 + c2;
            return creteStatement;
        }
    }

    /** defines in a central way the table column names from the table {@link TableTextoConfig} */
    public enum TableTextoConfig {

        ID, TEXTO, TIMESTAMP;

        public static String generateCreateTableStatement() {
            final String c0 = TEXTO.name() + TEXT + COMMA;
            final String c1 = TIMESTAMP.name() + TEXT;
            final String creteStatement = c0 + c1;
            return creteStatement;
        }
    }

    /** use it to separate values while creating SQL statements */
    public static final String COMMA = ",";
    /** use it to save it as a null value */
    public static final String NULL = " NULL ";
    /** use it to save integers, primary keys */
    public static final String INTEGER = " INTEGER ";
    /** use it to save doubles, floats */
    public static final String REAL = " REAL ";
    /** use it to save text, varchar, char */
    public static final String TEXT = " TEXT ";
    /** use it to save fotos, videos, audio, data etc. */
    public static final String BLOB = " BLOB ";

} 

Now lets take a look inside of the abstract class Table. All tables we need will extend from it.

Abstract Class Table

/** @author Ricardo Ferreira <a href="http://www.treslines.com">www.treslines.com</a> */
public abstract class Table extends SQLiteOpenHelper {

    public Table(Context appContext) {
        super(appContext, DbConfig.DATABASE_TEST.name(), DbConfig.dbDefaultCursorFactory, DbConfig.dbDefaultVersion);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        final String sqlCmdStart = "CREATE TABLE ";
        final String primaryKeyCmd = " (ID integer primary key autoincrement, ";
        final String sqlCmdEnd = ");";
        final String createQuery = sqlCmdStart + defineTableNameToCreate() + primaryKeyCmd + defineTableColumnsToCreate() + sqlCmdEnd;
        db.execSQL(createQuery);
    }

    public abstract String defineTableColumnsToCreate();

    public abstract String defineTableNameToCreate();

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // NOP
    }
} 

In my case i designed 4 classes(tables) for a proof of concept i had. Text, Audio, Video and Audio. Lets see how they look like:

Class TextTable

/** @author Ricardo Ferreira <a href="http://www.treslines.com">www.treslines.com</a> */
public class TextoTable extends Table {

    public TextoTable(Context appContext) {
        super(appContext);
    }

    @Override
    public String defineTableColumnsToCreate() {
        return TableTextoConfig.generateCreateTableStatement();

    }

    @Override
    public String defineTableNameToCreate() {
        return DbConfig.TABLE_TEXTO.name();
    }
} 

Class VideoTable

 /** @author Ricardo Ferreira <a href="http://www.treslines.com">www.treslines.com</a> */
public class VideoTable extends Table {

    public VideoTable(Context appContext) {
        super(appContext);
    }

    @Override
    public String defineTableColumnsToCreate() {
        return TableVideoConfig.generateCreateTableStatement();

    }

    @Override
    public String defineTableNameToCreate() {
        return DbConfig.TABLE_VIDEO.name();
    }
}

Class AudioTable

/** @author Ricardo Ferreira <a href="http://www.treslines.com">www.treslines.com</a> */
public class AudioTable extends Table {

    public AudioTable(Context appContext) {
        super(appContext);
    }

    @Override
    public String defineTableColumnsToCreate() {
        return TableAudioConfig.generateCreateTableStatement();

    }

    @Override
    public String defineTableNameToCreate() {
        return DbConfig.TABLE_AUDIO.name();
    }
} 

Class FotoTable

/** @author Ricardo Ferreira <a href="http://www.treslines.com">www.treslines.com</a> */
public class FotoTable extends Table {

    public FotoTable(Context appContext) {
        super(appContext);
    }

    @Override
    public String defineTableColumnsToCreate() {
        return TableFotoConfig.generateCreateTableStatement();
    }

    @Override
    public String defineTableNameToCreate() {
        return DbConfig.TABLE_FOTO.name();
    }
} 

OK done! we have our tables now. Lets see what DMO offers to us. This is the class which contains more logic inside. It is a very important class in this approach. It encapsulates all methods we may need. It is best to understand how SQLite works and what we are able to do with.

Abstract Class DMO

**
 * This Data Manipulation Object (DMO) offers CRUD operations (create, read, update, delete) and whatever you may need.
 * 
 * @author Ricardo Ferreira <a href="http://www.treslines.com">www.treslines.com</a>
 */
public abstract class DMO {

    private SQLiteDatabase db;

    public DMO(Context appContext, Table tableToOpen) throws NullPointerException {
        garantConnection(appContext, tableToOpen);
        openTable(tableToOpen);
    }

    public void openTable(Table tableToOpen) {
        if (this.db == null || !this.db.isOpen()) {
            this.db = tableToOpen.getWritableDatabase();
        }
    }

    public void close() {
        if (this.db != null) {
            this.db.close();
        }
    }

    /** Defines the name of this database in the concrete implementation of this class */
    public abstract String defineDatabaseNameToCreate();

    public int insert(String tableName, ContentValues rowToCreate) {

        db.insert(tableName, null, rowToCreate);
        String[] columnsToShow = null;
        String selection = null;
        String[] selectionArgs = null;
        String groupBy = null;
        String having = null;
        String orderBy = "ID DESC LIMIT 1";
        Cursor query = db.query(tableName, columnsToShow, selection, selectionArgs, groupBy, having, orderBy);
        query.moveToFirst();
        int indexId = query.getColumnIndex("ID");
        int id = query.getInt(indexId);
        close();
        return id;
    }

    public void update(String tableName, int rowIdToUpdate, ContentValues rowToUpdate) {

        final String whereClause = "ID=" + rowIdToUpdate;
        db.update(tableName, rowToUpdate, whereClause, null);
        close();
    }

    public void dropTable(String tableName) {

        String sql = "DROP TABLE IF EXISTS " + tableName;
        db.execSQL(sql);
        close();
    }

    public boolean dropDatabase(Context appContext, String databaseName) {
        return appContext.deleteDatabase(databaseName);
    }

    public void deleteById(String tableName, int id) {

        String whereClause = "ID=" + id;
        String[] whereArgs = null;
        db.delete(tableName, whereClause, whereArgs);
        close();
    }

    public Cursor selectAll(String tableName, String[] columnNamesToShow) {

        String[] columnsToShow = columnNamesToShow;
        String selection = null;
        String[] selectionArgs = null;
        String groupBy = null;
        String having = null;
        String orderBy = null;
        return db.query(tableName, columnsToShow, selection, selectionArgs, groupBy, having, orderBy);
    }

    public Cursor selectAllOrderBy(String tableName, String[] columnNamesToShow, String columnNameToOrderBy) {

        String[] columnsToShow = columnNamesToShow;
        String selection = null;
        String[] selectionArgs = null;
        String groupBy = null;
        String having = null;
        String orderBy = columnNameToOrderBy;
        return db.query(tableName, columnsToShow, selection, selectionArgs, groupBy, having, orderBy);
    }

    public Cursor selectAllDistinct(String tableName, String[] columnNamesToShow, String columnNameToDistinct) {

        String[] columnsToShow = columnNamesToShow;
        String selection = null;
        String[] selectionArgs = null;
        String groupBy = columnNameToDistinct;
        String having = null;
        String orderBy = columnNameToDistinct;
        return db.query(tableName, columnsToShow, selection, selectionArgs, groupBy, having, orderBy);
    }

    public Cursor selectRowById(String tableName, int id) {

        String[] columnsToShow = null;
        String selection = "ID=" + id;
        String[] selectionArgs = null;
        String groupBy = null;
        String having = null;
        String orderBy = null;
        return db.query(tableName, columnsToShow, selection, selectionArgs, groupBy, having, orderBy);
    }

    public Cursor getDifferences(String tableName, String timestamp) {

        String[] columnsToShow = null;
        String selection = "TIMESTAMP = '" + timestamp + "'";
        String[] selectionArgs = null;
        String groupBy = null;
        String having = null;
        String orderBy = null;
        return db.query(tableName, columnsToShow, selection, selectionArgs, groupBy, having, orderBy);
    }

    private void garantConnection(Context appContext, Table tableToOpen) {
        if (appContext == null || tableToOpen == null) {
            throw new NullPointerException("appContext and tableToOpen can't be set to null");
        }
    }

} 

 

The next class is the implementation of DMO. In my case i called it Database. In there i define some specific settings acc. to my needs like database name.

Class Database

/** @author Ricardo Ferreira <a href="http://www.treslines.com">www.treslines.com</a> */
public class Database extends DMO {

    public Database(Context appContext, Table tableToOpen) {
        super(appContext, tableToOpen);
    }

    @Override
    public String defineDatabaseNameToCreate() {
        return DbConfig.DATABASE_TEST.name();
    }
} 

Ok greate we are done for this part. Now lets take a look into the DAO. It will use the DMO to access the database and manipulate it. In this class you'll learn how to retrieve data from SQLlite. It is a good example if you are new on it.

Looking inside the code of the DAO and Entities

So we are almost done. Before we can look inside of the DAO, we need to create our entities to represent the tables we have. The UML-diagram from it is very simple and looks like that:

As I sad in the introduction, we don't want to repeat ourself right? Remember "clean code" ;-) So in order to reuse the common properties of my tables, i decided to create the abstract class Entity. Here is the code of it. (it is a simple bean)

Abstract Class Entity

/** @author Ricardo Ferreira <a href="http://www.treslines.com">www.treslines.com</a> */
public abstract class Entity {

    private String name;
    private byte[] stream;
    private String timeStamp;

    public String getName() {
        return this.name;
    }

    public byte[] getStream() {
        return this.stream;
    }

    public String getTimeStamp() {
        return this.timeStamp;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setStream(byte[] stream) {
        this.stream = stream;
    }

    public void setTimeStamp(String timeStamp) {
        this.timeStamp = timeStamp;
    }
} 

Good! In this simple example my concrete classes of entity are very simple. I has an empty body. For this reason i will show only one of it.

Class VideoEntity

/** @author Ricardo Ferreira <a href="http://www.treslines.com">www.treslines.com</a> */
public class VideoEntity extends Entity {
    // concrete entity
} 

Finally le's take a look inside of the DAO. That's also a very interesting class you should take a look inside of it.

Class DAO.

/**
 * This data access object (DAO) is responsible to read and setup database entities<br/>
 * 
 * @author Ricardo Ferreira <a href="http://www.treslines.com">www.treslines.com</a>
 */
public class DAO {

    private DMO connection;

    public FotoEntity readFotoEntity(Activity activity) {
        FotoEntity fotoEntity = new FotoEntity();
        connection = new Database(activity, new FotoTable(activity));
        final Cursor result = connection.selectAll(DbConfig.TABLE_FOTO.name(), null);
        fotoEntity.setName(result.getString(result.getColumnIndex(DbConfig.TableFotoConfig.NOME.name())));
        fotoEntity.setStream(result.getBlob(result.getColumnIndex(DbConfig.TableFotoConfig.FOTO.name())));
        fotoEntity.setTimeStamp(result.getString(result.getColumnIndex(DbConfig.TableFotoConfig.TIMESTAMP.name())));
        connection.close();
        return fotoEntity;
    }

    public AudioEntity readAudioEntity(Activity activity) {
        AudioEntity audioEntity = new AudioEntity();
        connection = new Database(activity, new AudioTable(activity));
        final Cursor result = connection.selectAll(DbConfig.TABLE_FOTO.name(), null);
        audioEntity.setName(result.getString(result.getColumnIndex(DbConfig.TableAudioConfig.NOME.name())));
        audioEntity.setStream(result.getBlob(result.getColumnIndex(DbConfig.TableAudioConfig.AUDIO.name())));
        audioEntity.setTimeStamp(result.getString(result.getColumnIndex(DbConfig.TableAudioConfig.TIMESTAMP.name())));
        connection.close();
        return audioEntity;
    }

    public VideoEntity readVideoEntity(Activity activity) {
        VideoEntity videoEntity = new VideoEntity();
        connection = new Database(activity, new VideoTable(activity));
        final Cursor result = connection.selectAll(DbConfig.TABLE_FOTO.name(), null);
        videoEntity.setName(result.getString(result.getColumnIndex(DbConfig.TableVideoConfig.NOME.name())));
        videoEntity.setStream(result.getBlob(result.getColumnIndex(DbConfig.TableVideoConfig.VIDEO.name())));
        videoEntity.setTimeStamp(result.getString(result.getColumnIndex(DbConfig.TableVideoConfig.TIMESTAMP.name())));
        connection.close();
        return videoEntity;
    }

    public TextoEntity readTextoEntity(Activity activity) {
        TextoEntity textoEntity = new TextoEntity();
        connection = new Database(activity, new TextoTable(activity));
        final Cursor result = connection.selectAll(DbConfig.TABLE_FOTO.name(), null);
        textoEntity.setName(result.getString(result.getColumnIndex(DbConfig.TableTextoConfig.TEXTO.name())));
        textoEntity.setTimeStamp(result.getString(result.getColumnIndex(DbConfig.TableTextoConfig.TIMESTAMP.name())));
        connection.close();
        return textoEntity;
    }
} 

And last but not least, the test of it. The class which explains how all objects interacts together. (That's a Junit test using the library Roboelectric that i've explained in a post here called: Testing Android Apps with Junit (no more slow emulator) )

Class ClientTest

@RunWith(RobolectricTestRunner.class)
public class ClientTest {

    private Activity activity = new Activity();
    private DMO connection;
    private DAO dao = new DAO();

    @Test
    public void databaseConnection() {
        ContentValues fotoRowToCreate = new ContentValues();
        fotoRowToCreate.put(DbConfig.TableFotoConfig.NOME.name(), "foto nome");
        fotoRowToCreate.put(DbConfig.TableFotoConfig.FOTO.name(), new byte[] { 1, 2, 3 });
        fotoRowToCreate.put(DbConfig.TableFotoConfig.TIMESTAMP.name(), "foto nome");
        connection = new Database(activity, new FotoTable(activity));
        final int newRowIndex = connection.insert(DbConfig.TABLE_FOTO.name(), fotoRowToCreate);
        // assert the new inserted row index or query everything if you want...

        ContentValues textoRowToCreate = new ContentValues();
        fotoRowToCreate.put(DbConfig.TableTextoConfig.TEXTO.name(), "foto nome");
        fotoRowToCreate.put(DbConfig.TableTextoConfig.TIMESTAMP.name(), "foto nome");
        connection = new Database(activity, new TextoTable(activity));
        connection.insert(DbConfig.TABLE_TEXTO.name(), textoRowToCreate);

        ContentValues audioRowToCreate = new ContentValues();
        fotoRowToCreate.put(DbConfig.TableAudioConfig.NOME.name(), "foto nome");
        fotoRowToCreate.put(DbConfig.TableAudioConfig.AUDIO.name(), new byte[] { 1, 2, 3 });
        fotoRowToCreate.put(DbConfig.TableAudioConfig.TIMESTAMP.name(), "foto nome");
        connection = new Database(activity, new AudioTable(activity));
        connection.insert(DbConfig.TABLE_AUDIO.name(), audioRowToCreate);

        ContentValues videoRowToCreate = new ContentValues();
        fotoRowToCreate.put(DbConfig.TableVideoConfig.NOME.name(), "foto nome");
        fotoRowToCreate.put(DbConfig.TableVideoConfig.VIDEO.name(), new byte[] { 1, 2, 3 });
        fotoRowToCreate.put(DbConfig.TableVideoConfig.TIMESTAMP.name(), "foto nome");
        connection = new Database(activity, new VideoTable(activity));
        connection.insert(DbConfig.TABLE_VIDEO.name(), videoRowToCreate);

        // cleanUp db, because this is a test and we don't want to keep tests in the db
        connection.dropDatabase(activity, DbConfig.DATABASE_TEST.name());
    }

    @Test
    public void dao() {
        FotoEntity fotoEntity = dao.readFotoEntity(activity);
        // assert whatever you want...
        AudioEntity audioEntity = dao.readAudioEntity(activity);
        VideoEntity videoEntity = dao.readVideoEntity(activity);
        TextoEntity textoEntity = dao.readTextoEntity(activity);
    }
} 

So that was all. You have now an example from A-Z. Hope you like it. Happy coding! ;-)

😱👇 PROMOTIONAL DISCOUNT: BOOKS AND IPODS PRO 😱👇

Be sure to read, it will change your life!

Show your work by Austin Kleon: https://amzn.to/34NVmwx

This book is a must read - it will put you in another level! (Expert)

Agile Software Development, Principles, Patterns, and Practices: https://amzn.to/30WQSm2

Write cleaner code and stand out!

Clean Code - A Handbook of Agile Software Craftsmanship: https://amzn.to/33RvaSv

This book is very practical, straightforward and to the point! Worth every penny!

Kotlin for Android App Development (Developer's Library): https://amzn.to/33VZ6gp

Needless to say, these are top right?

Apple AirPods Pro: https://amzn.to/2GOICxy

😱👆 PROMOTIONAL DISCOUNT: BOOKS AND IPODS PRO 😱👆