How To Write Reusable Code For Android SQLite Database

This example will tell you how to encapsulate android SQLite database operations in java class, then you can reuse them to operate different SQLite database and tables.

1. Android SQLite Database Operations Reusable classes.

There are three java classes in this example as below.

android sqlite database operations reusable java class

  1. SQLiteDatabaseHelper : This class is subclass of android.database.sqlite.SQLiteOpenHelper. It overwrite it’s onCreate() and onUpgrade() methods. This class main purpose is to create and upgrade SQLite database and tables.
  2. DatabaseManager : This class includes methods to implement basic SQLite database and table operations, such as open database, close database, insert data, update data, delete data and query data.
  3. TableColumn : This class represent a column in table, it stores column name and value. It is used when insert and update table. One table row is a list of TableColumn instances.

2.  SQLite Database Create And Upgrade Java Class.

SQLiteDatabaseHelper.java

package com.dev2qa.example.storage.sqlite.util;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.widget.Toast;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Jerry on 1/17/2018.
 */

public class SQLiteDatabaseHelper extends SQLiteOpenHelper {

    // Contains all table name string list.
    private List<String> tableNameList = null;

    // Contains all create table sql command string list.
    private List<String> createTableSqlList = null;

    // This is the log tag in android monitor console.
    public static final String LOG_TAG_SQLITE_DB = "LOG_TAG_SQLITE_DB";

    // This is the android activity context.
    private Context ctx = null;

    /* Constructor with all input parameter*/
    public SQLiteDatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        ctx = context;
    }

    /* Generally run all create table sql in this method. */
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        if(createTableSqlList!=null)
        {
            int size = createTableSqlList.size();
            for(int i = 0;i<size;i++)
            {
                // Loop all the create table sql command string in the list.
                // each sql will create a table in sqlite database.
                String createTableSql = createTableSqlList.get(i);
                sqLiteDatabase.execSQL(createTableSql);

                Toast.makeText(ctx, "Run sql successfully, " + createTableSql, Toast.LENGTH_SHORT).show();
            }
        }
    }

    /* When the new db version is bigger than current exist db version, this method will be invoked.
    * It always drop all tables and then call onCreate() method to create all table again.*/
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        if(tableNameList!=null)
        {
            // Loop and drop all exist sqlite table.
            int size = tableNameList.size();
            for(int i = 0;i<size;i++)
            {
                String tableName = tableNameList.get(i);
                if(!TextUtils.isEmpty(tableName)) {
                    sqLiteDatabase.execSQL("drop table if exists " + tableName);
                }
            }
        }

        // After drop all exist tables, create all tables again.
        onCreate(sqLiteDatabase);
    }

    public List<String> getTableNameList() {
        if(tableNameList==null)
        {
            tableNameList = new ArrayList<String>();
        }
        return tableNameList;
    }

    public void setTableNameList(List<String> tableNameList) {
        this.tableNameList = tableNameList;
    }

    public List<String> getCreateTableSqlList() {
        if(createTableSqlList==null)
        {
            createTableSqlList = new ArrayList<String>();
        }
        return createTableSqlList;
    }

    public void setCreateTableSqlList(List<String> createTableSqlList) {
        this.createTableSqlList = createTableSqlList;
    }
}

3. SQLite Database Table Basic Operations Java Class.

DatabaseManager.java

package com.dev2qa.example.storage.sqlite.util;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Jerry on 1/17/2018.
 */

public class DatabaseManager {

    // This is the activity context.
    private Context ctx = null;

    // Database helper class to manipulate sqlite database.
    private SQLiteDatabaseHelper dbHelper = null;

    // Represent connection with sqlite database.
    private SQLiteDatabase database = null;

    // Database name.
    private String dbName = "";

    // Database version.
    private int dbVersion = 0;

    // All tables that this database contains.
    private List<String> tableNameList = null;

    // All table create sql that this db need.
    private List<String> createTableSqlList = null;

    // Create database manager instance.
    public DatabaseManager(Context ctx, String dbName, int dbVersion, List<String> tableNameList, List<String> createTableSqlList) {
        this.ctx = ctx;
        this.dbName = dbName;
        this.dbVersion = dbVersion;
        this.tableNameList = tableNameList;
        this.createTableSqlList = createTableSqlList;
    }

    // Open database connection.
    public DatabaseManager openDB()
    {
        // Create database helper instance.
        dbHelper = new SQLiteDatabaseHelper(ctx, this.dbName, null, this.dbVersion);
        dbHelper.setTableNameList(this.tableNameList);
        dbHelper.setCreateTableSqlList(this.createTableSqlList);

        // Get sqlite database connection. If not exist then create onw else return a database object.
        this.database = dbHelper.getWritableDatabase();
        return this;
    }


    // close database connection.
    public void closeDB()
    {
        this.database.close();
        this.dbHelper.close();
        this.database = null;
        this.dbHelper = null;
    }

    /* Insert data into table.
    *  tableName : The table name.
    *  columnList : A list of table column name value pair.
    * */
    public void insert(String tableName, List<TableColumn> columnList)
    {
        if(!TextUtils.isEmpty(tableName) && columnList!=null)
        {
            int size = columnList.size();

            if(size > 0)
            {
                // Create a content values object.
                ContentValues contentValues = new ContentValues();

                // Loop in the table column list
                for(int i=0;i<size;i++)
                {
                    TableColumn tableColumn = columnList.get(i);

                    // Put column name and value in content values.
                    if(!TextUtils.isEmpty(tableColumn.getColumnName())) {
                        contentValues.put(tableColumn.getColumnName(), tableColumn.getColumnValue());
                    }
                }

                // Insert data.
                this.database.insert(tableName, null, contentValues);
            }
        }
    }

    /*
    *  Update data in table.
    *  tableName : The table name.
    *  columnList : Need update column name and value.
    *  whereClause : The update rows meet condition.
    * */
    public int update(String tableName,  List<TableColumn> columnList, String whereClause)
    {
        int ret = 0;
        if(!TextUtils.isEmpty(tableName) && columnList!=null)
        {
            int size = columnList.size();

            if(size > 0)
            {
                // Create a content values object.
                ContentValues contentValues = new ContentValues();

                // Loop in the table column list
                for(int i=0;i<size;i++)
                {
                    TableColumn tableColumn = columnList.get(i);

                    // Put column name and value in content values.
                    if(!TextUtils.isEmpty(tableColumn.getColumnName())) {
                        contentValues.put(tableColumn.getColumnName(), tableColumn.getColumnValue());
                    }
                }

                // Update data.Return update row count.
                ret = this.database.update(tableName, contentValues, whereClause, null);
            }
        }
        return ret;
    }

    /* Delete rows in table.
    *  tableName : Delete data table name.
    *  whereClause : Deleted rows meet condition. */
    public void delete(String tableName, String whereClause)
    {
        if(!TextUtils.isEmpty(tableName)) {
            this.database.delete(tableName, whereClause, null);
        }
    }

    /*
    *  Query all rows in sqlite database table.
    *  tableName : The table name.
    *  Return a list a Map object. Each map object represent a row of data in table.
    * */
    public List<Map<String, String>> queryAllReturnListMap(String tableName)
    {
        List<Map<String, String>> ret = new ArrayList<Map<String, String>>();

        // Query all rows in table.
        Cursor cursor = this.database.query(tableName, null, null, null, null, null, null);
        if(cursor!=null)
        {
            // Get all column name in an array.
            String columnNamesArr[] = cursor.getColumnNames();

            // Move to first cursor.
            cursor.moveToFirst();
            do {
                // Create a map object represent a table row data.
                Map<String, String> rowMap = new HashMap<String, String>();

                // Get column count.
                int columnCount = columnNamesArr.length;
                for(int i=0;i<columnCount;i++)
                {
                    // Get each column name.
                    String columnName = columnNamesArr[i];

                    // This is the column value.
                    String columnValue = "";

                    // Get column index value.
                    int columnIndex = cursor.getColumnIndex(columnName);

                    // Get current column data type.
                    int columnType = cursor.getType(columnIndex);

                    if(Cursor.FIELD_TYPE_STRING == columnType)
                    {
                        columnValue = cursor.getString(columnIndex);
                    }else if(Cursor.FIELD_TYPE_INTEGER == columnType)
                    {
                        columnValue = String.valueOf(cursor.getInt(columnIndex));
                    }else if(Cursor.FIELD_TYPE_FLOAT == columnType)
                    {
                        columnValue = String.valueOf(cursor.getFloat(columnIndex));
                    }else if(Cursor.FIELD_TYPE_BLOB == columnType)
                    {
                        columnValue = String.valueOf(cursor.getBlob(columnIndex));
                    }else if(Cursor.FIELD_TYPE_NULL == columnType)
                    {
                        columnValue = "null";
                    }

                    rowMap.put(columnName, columnValue);

                    ret.add(rowMap);
                }
            }while(cursor.moveToNext());

            cursor.close();
        }
        return ret;
    }

    /*
    *  Query all rows in sqlite database table.
    *  tableName : The table name.
    *  Return a Cursor object.
    * */
    public Cursor queryAllReturnCursor(String tableName)
    {
        // Query all rows in table.
        Cursor cursor = this.database.query(tableName, null, null, null, null, null, null);
        if(cursor!=null)
        {
            // Move to first cursor.
            cursor.moveToFirst();
        }
        return cursor;
    }
}

4. SQLite Database Table Column Java Class.

TableColumn.java

package com.dev2qa.example.storage.sqlite.util;

/**
 * Created by Jerry on 1/17/2018.
 */

/* This class represent one table column*/
public class TableColumn {

    // This is the table column name.
    private String columnName;

    // This is the table column value.
    private String columnValue;

    public String getColumnName() {
        return columnName;
    }

    public void setColumnName(String columnName) {
        this.columnName = columnName;
    }

    public String getColumnValue() {
        return columnValue;
    }

    public void setColumnValue(String columnValue) {
        this.columnValue = columnValue;
    }
}
(Visited 1,141 times, 3 visits today)
READ :   Android SQLite CRUD Operations Examples

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.