Wednesday, July 23, 2014

How to import / export or backup sqlite db dump in your Android App

Hi there!

Today i'm gonna share with you how to import and export your database date using sqlite in your android application.

We will see how to create a sqlite database using SQLiteOpenHelper, how to populate it once while initializing and how to import and export it.

First Step: Defining an Entity

For this example we will implement a LanguageEntitty like shown bellow.
 
public class LanguageEntity {
    
    private int id;
    private String language;
    
    public int getId() {
        return this.id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getLanguage() {
        return this.language;
    }
    public void setLanguage(String language) {
        this.language = language;
    }
} 

Defining our DbFactory

The DbFactory will be responsible for defining all database and table data in a central place. It is also responsible for creating the first database instance and tables if it not exits already. It has the methods to import or export the database as you need.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.channels.FileChannel;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;


/**
 * Class responsible for defining and creating, updating or deleting DB, tables and its columns.
 * 
 * @author Ricardo Ferreira
 * @version 1.0
 * @since 17/07/2014
 */
public class DbFactory extends SQLiteOpenHelper {

    // Database & Name Version
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "yourdatabasename.db";

    // your table name. In this example language
    public static final String LANGUAGE = "language";
    public static final String LANGUAGE_ID = "language_id";
    public static final String LANGUAGE_NAME = "language_name";
    
    // used SQL statements
    private static final String UNIQUE = " UNIQUE ";
    private static final String INTEGER = " INTEGER ";
    private static final String TEXT = " TEXT ";
    private static final String INTEGERC = " INTEGER, ";
    private static final String TEXTC = " TEXT, ";
    private static final String PARENTHSE_LEFT = " ( ";
    private static final String PARENTHSE_RIGHT = " ) ";
    private static final String CREATE_TABLE_IF_NOT_EXISTS = "CREATE TABLE IF NOT EXISTS ";
    private static final String INTEGER_PRIMARY_KEY = " INTEGER PRIMARY KEY, ";
    private static final String DROP_TABLE_IF_EXISTS = "DROP TABLE IF EXISTS ";

    private final String DB_FILEPATH;

    public DbFactory(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        final String packageName = context.getPackageName();
        DB_FILEPATH = "/data/data/" + packageName + "/databases/yourdatabasename.db";
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        createLanguageTable(db);
        populateLanguageOnce(db);
    }

    private void populateLanguageOnce(SQLiteDatabase db) {
        String [ ] languages = new String [ ] { "C", "C++", "C#", "Java" };
        for (String language : languages) {
            insertLanguages(db, language);
        }
    }

    private void insertLanguages(SQLiteDatabase db, String value) {
        ContentValues columnValuePair = new ContentValues();
        columnValuePair.put(LANGUAGE_NAME, value);
        db.insert(LANGUAGE, null, columnValuePair);
    }

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

    private void dropTables(SQLiteDatabase db) {
        db.execSQL(DROP_TABLE_IF_EXISTS + LANGUAGE);
    }

    private void recreateTables(SQLiteDatabase db) {
        onCreate(db);
    }

    private void createLanguageTable(SQLiteDatabase db) {
        final StringBuilder sql = new StringBuilder();
        sql.append(CREATE_TABLE_IF_NOT_EXISTS);
        sql.append(LANGUAGE);
        sql.append(PARENTHSE_LEFT);
        sql.append(LANGUAGE_ID);
        sql.append(INTEGER_PRIMARY_KEY);
        sql.append(LANGUAGE_NAME);
        sql.append(TEXTC);
        sql.append(UNIQUE); // ensures uniqueness for languages
        sql.append(PARENTHSE_LEFT);
        sql.append(LANGUAGE_NAME);
        sql.append(PARENTHSE_RIGHT);
        sql.append(PARENTHSE_RIGHT);
        Log.d("CREATE_TABLE_LANGUAGE", sql.toString());
        db.execSQL(sql.toString());
    }

    /**
     * Copies the database file at the specified location 
     * over the current internal application database.
     * */
    public boolean importDatabase(String dbPath) throws IOException {

        // Close the SQLiteOpenHelper so it will
        // commit the created empty database to internal storage.
        close();
        File newDb = new File(dbPath);
        File oldDb = new File(DB_FILEPATH);
        if (newDb.exists()) {
            copyFile(new FileInputStream(newDb), new FileOutputStream(oldDb));
            // Access the copied database so SQLiteHelper
            // will cache it and mark it as created.
            getWritableDatabase().close();
            return true;
        }
        return false;
    }

    private void copyFile(FileInputStream fromFile, FileOutputStream toFile) throws IOException {
        FileChannel fromChannel = null;
        FileChannel toChannel = null;
        try {
            fromChannel = fromFile.getChannel();
            toChannel = toFile.getChannel();
            fromChannel.transferTo(0, fromChannel.size(), toChannel);
        } finally {
            try {
                if (fromChannel != null) {
                    fromChannel.close();
                }
            } finally {
                if (toChannel != null) {
                    toChannel.close();
                }
            }
        }
    }

    public void backupDatabase() throws IOException {

        if (isSDCardWriteable()) {
            // Open your local db as the input stream
            String inFileName = DB_FILEPATH;
            File dbFile = new File(inFileName);
            FileInputStream fis = new FileInputStream(dbFile);

            String outFileName = Environment.getExternalStorageDirectory() + "/syntaxionary";
            // Open the empty db as the output stream
            OutputStream output = new FileOutputStream(outFileName);
            // transfer bytes from the inputfile to the outputfile
            byte[] buffer = new byte[1024];
            int length;
            while ((length = fis.read(buffer)) > 0) {
                output.write(buffer, 0, length);
            }
            // Close the streams
            output.flush();
            output.close();
            fis.close();
        }
    }

    private boolean isSDCardWriteable() {
        boolean rc = false;
        String state = Environment.getExternalStorageState();
        if (Environment.MEDIA_MOUNTED.equals(state)) {
            rc = true;
        }
        return rc;
    }
}

Creating a DAO (Data Access Object)

Before you can use the DbFactory, we must create a DAO who knows how to open and close the database properly. We will see how to query, insert and delete rows in the database. The DAO is designed as a singleton, because it can be used anywhere in your application and because we don't need many instances of it. Let's do it in the next section:

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

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.treslines.syntaxionary.entity.LanguageEntity;


/**
 * Use this class to manipulate the DB created over DbFactory.
 * Usage example: Db.open(...).insert(...);
 * After execution the Db will always be closed automatically. 
 * @author Ricardo Ferreira
 * @version 1.0
 * @since 17/07/2014
 */
public class DAO {

    private static final String TAG = "TAG";
    private static final long INSERT_ERROR = -1;
    private static final int UPDATE_ERROR = 0;
    private static final String DB_QUERY_SIGN = "= ?";
    private static DAO instance;

    private DbFactory dbFactory;
    private SQLiteDatabase db;//NOPMD

    // singleton
    private DAO(Context context) {
        super();
        this.dbFactory = new DbFactory(context);
        this.db = this.dbFactory.getWritableDatabase();
    }

    /** always start a new db transaction over DAO.open(...) */
    public synchronized static DAO open(Context context) {
        if (instance == null) {
            instance = new DAO(context);
        } 
        return instance;
    }
    
    public long insertLanguage(LanguageEntity language) {
        final String columnName = DbFactory.LANGUAGE_NAME;
        ContentValues values = new ContentValues();
        values.put(columnName, language.getLanguage());
        long newRowId = db.insert(DbFactory.LANGUAGE, null, values);
        close();
        return newRowId;
    }
    
    public int getLanguageCounter() {
        final String tableName = DbFactory.LANGUAGE;
        final Cursor result = db.query(tableName, null, null,null,null,null,null);
        int counter = result.getCount();
        close();
        return counter;
    }
    
    public boolean hasLanguage(String newLanguage) {
        final String tableName = DbFactory.LANGUAGE;
        String where = DbFactory.LANGUAGE_NAME+DB_QUERY_SIGN;
        String [ ] languages = new String [ ] {newLanguage};
        final Cursor result = db.query(tableName, null, where,languages,null,null,null);
        boolean canInsert = result.moveToNext();
        close();
        return canInsert;
    }
    
    public int queryLanguageIdByName(String language) {
        final String tableName = DbFactory.LANGUAGE;
        String where = DbFactory.LANGUAGE_NAME+ DB_QUERY_SIGN;
        Cursor result = db.query(tableName, null,where,new String [ ] {language},null,null,null,null);
        while(result.moveToNext()){
            int columnIndex = result.getColumnIndex(DbFactory.LANGUAGE_ID);
            return result.getInt(columnIndex);
        }
        return 0;
    }
    
    public boolean insertLanguage(String newLanguage) {
        final String tableName = DbFactory.LANGUAGE;
        ContentValues tableValues = new ContentValues();
        tableValues.put(DbFactory.LANGUAGE_NAME, newLanguage);
        long newRowId = db.insert(tableName, null, tableValues);
        close();
        return newRowId != INSERT_ERROR;
    }
    
    public List < String > queryLanguages() {
     Cursor result = db.query(DbFactory.LANGUAGE, new String [ ]{DbFactory.LANGUAGE_NAME}, null, null, null, null, null);
     List< String > languages = new ArrayList< String >();
     while(result.moveToNext()){
      int nameIndex = result.getColumnIndex(DbFactory.LANGUAGE_NAME);
      languages.add(result.getString(nameIndex));
     }
     return languages;
    }

    public int deleteLanguage(String language) {
        final String tableName = DbFactory.LANGUAGE;
        String where = DbFactory.LANGUAGE_NAME + DB_QUERY_SIGN;
        String [ ] languages = new String [ ] { language };
        int result = db.delete(tableName,  where, languages);
        close();
        return result;
    }

    /** Use Db.open(...) to open a new connection */
    public void close() {
        if (db != null && db.isOpen()) {
            db.close();
            db = null;
        }
        if (dbFactory != null) {
            dbFactory.close();
            dbFactory = null;
        }
        instance =null;
    }

}

Usage of the DbFactory and DAO together

Here we can see how we could use it in the practise. In the DbFactory we have methods to import or export the database as you may need.

//... more code omitted ...
DAO.open(getContext()).queryLanguageIdByName("Java"); 
//... more code omitted ...
 
Thats all. Hope you like it!

😱👇 PROMOTIONAL DISCOUNT: BOOKS AND IPODS PRO ðŸ˜±ðŸ‘‡

Be sure to read, it will change your life!
Show your work by Austin Kleonhttps://amzn.to/34NVmwx

This book is a must read - it will put you in another level! (Expert)
Agile Software Development, Principles, Patterns, and Practiceshttps://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 ðŸ˜±ðŸ‘†