Home  >  Android

Android Ship SQLite Database with APK | Copy SQLite Database from assets folder to data Example

By Arvind Rai, September 24, 2015
This page will provide how to ship SQLite database file with APK. We will show the demo to copy database file from assets folder to data directory of android OS. Find the below points for quick understanding.

1. Create SQLite database and tables and inserts data. Copy this file in assets folder of your android project.
2. While writing code we will get a directory location getFilesDir().getAbsolutePath() in android OS.
3. Before copying SQLite database file in data, first we will check if it is already there.
4. If yes, we will check database version, if it is older or newer. Here we will not check SQLiteOpenHelper provided database version, because once we delete our database to copy new database, then android provided database version will always be 0. So we will maintain a table to keep database version.
5. If database version is same, we will not copy database from assets folder.
6. If older, then first we will delete it and then copy fresh database file from assets folder.

Create Tables in SQLite Database

Create SQLite DB and create your tables in DB and insert the data. In our example, we are creating following tables.
Table: Employee
CREATE TABLE `Employee` (
	`id`	INTEGER NOT NULL,
	`name`	TEXT NOT NULL,
	`age`	INTEGER,
	PRIMARY KEY(id)
); 
This table will contain information of employees.
Android Ship SQLite Database with APK | Copy SQLite Database from assets folder to data Example
Now find one more table.
Table: dbVersion
CREATE TABLE `dbVersion` (
	`version_id`	int NOT NULL,
	PRIMARY KEY(version_id)
); 
We will keep DB version in this table.

Place SQLite Database in asset folder

Once our SQLite database is ready, we will place it in assets directory. In our example our SQLite database file name is database.db and we are placing in assets folder as
/assets/sqlite/database.db

Create Class for Database Handling

DatabaseHelper.java
package com.concretepage;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class DatabaseHelper extends SQLiteOpenHelper {
      private final static String TAG = "DatabaseHelper"; 
      private final Context myContext;
      private static final String DATABASE_NAME = "database.db";
      private static final int DATABASE_VERSION = 1;
      private String pathToSaveDBFile; 
      public DatabaseHelper(Context context, String filePath) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            this.myContext = context;
            pathToSaveDBFile = new StringBuffer(filePath).append("/").append(DATABASE_NAME).toString();
      }
      public void prepareDatabase() throws IOException {
            boolean dbExist = checkDataBase();
            if(dbExist) {
                  Log.d(TAG, "Database exists.");
                  int currentDBVersion = getVersionId();
                  if (DATABASE_VERSION > currentDBVersion) {
                      Log.d(TAG, "Database version is higher than old.");
                      deleteDb();
                      try {
                    	        copyDataBase();
        		  } catch (IOException e) {
    				Log.e(TAG, e.getMessage());
    			  }
                  }
            } else {
                  try {
        	 	copyDataBase();
  	          } catch (IOException e) {
			Log.e(TAG, e.getMessage());
		  }            	
            }
      }
      private boolean checkDataBase() {
            boolean checkDB = false;
            try {
                File file = new File(pathToSaveDBFile);
                checkDB = file.exists();
            } catch(SQLiteException e) {
            	Log.d(TAG, e.getMessage());
            }
            return checkDB;
      }
      private void copyDataBase() throws IOException {
            OutputStream os = new FileOutputStream(pathToSaveDBFile);
            InputStream is = myContext.getAssets().open("sqlite/"+DATABASE_NAME);
            byte[] buffer = new byte[1024];
            int length;
            while ((length = is.read(buffer)) > 0) {
                  os.write(buffer, 0, length);
            }
            is.close();
            os.flush();
            os.close();
      }
      public void deleteDb() {
            File file = new File(pathToSaveDBFile);
            if(file.exists()) {
                  file.delete();
                  Log.d(TAG, "Database deleted.");
            }
      }
      @Override
      public void onCreate(SQLiteDatabase db) {
          Log.d(TAG, "onCreate");
      }
      @Override
      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {    
      }
      public List<Employee> getEmployees() {
  		SQLiteDatabase db = SQLiteDatabase.openDatabase(pathToSaveDBFile, null, SQLiteDatabase.OPEN_READONLY);
 		String query = "SELECT id, name, age FROM Employee";
  		Cursor cursor = db.rawQuery(query, null);
  		List<Employee> list = new ArrayList<Employee>();
  		while(cursor.moveToNext()) {
	  		Employee employee = new Employee();
	  		employee.setId(cursor.getInt(0));
	  		employee.setName(cursor.getString(1));
	  		employee.setAge(cursor.getInt(2));	  		
	  		list.add(employee);
  		}
  		db.close();
  		return list;
      }
      private int getVersionId() {
  		SQLiteDatabase db = SQLiteDatabase.openDatabase(pathToSaveDBFile, null, SQLiteDatabase.OPEN_READONLY);
 		String query = "SELECT version_id FROM dbVersion";
  		Cursor cursor = db.rawQuery(query, null);
  		cursor.moveToFirst();
  		int v =  cursor.getInt(0);
  		db.close();
  		return v; 
     }
} 
Employee.java
package com.concretepage;
public class Employee {
	private int id;
	private String name;
	private int age;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
} 

Create Activity Class

MainActivity.java
package com.concretepage;
import java.io.IOException;
import java.util.List;
import android.app.Activity;
import android.os.Bundle;
import android.text.Html;
import android.util.Log;
import android.view.Menu;
import android.widget.TextView;
public class MainActivity extends Activity {
     private final static String TAG = "MainActivity"; 
     DatabaseHelper dbHelper= null;
     @Override    
     protected void onCreate(Bundle savedInstanceState) {
	super.onCreate(savedInstanceState);
	setContentView(R.layout.activity_main);
	dbHelper = new DatabaseHelper(this, getFilesDir().getAbsolutePath());
        try {
        	 dbHelper.prepareDatabase();
	} catch (IOException e) { 
			Log.e(TAG, e.getMessage());  
	}
        showData();
    }
    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
           getMenuInflater().inflate(R.menu.menu_main, menu);
           return true;
    }
    private void showData() {
    	List<Employee> list = dbHelper.getEmployees();
    	StringBuffer data = new StringBuffer();
    	for (int i =0; i< list.size(); i++) {
    		Employee emp = list.get(i);
    		data.append(emp.getId()).append(",").append(emp.getName())
    		.append(",").append(emp.getAge()).append("<br/>");
    	}
    	TextView textView = (TextView)findViewById(R.id.bodytext);
    	textView.setText(Html.fromHtml(data.toString()));
    }
} 

XML files

AndroidManifest.xml
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.concretepage"
    android:versionCode="1"
    android:versionName="1.0" >
    <uses-sdk android:minSdkVersion="14"/>
    <application 
        android:allowBackup ="false"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name" >
        <activity  android:name="com.concretepage.MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />
                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>
</manifest> 
layout/activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
   <ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
        xmlns:tools="http://schemas.android.com/tools"
        xmlns:ads="http://schemas.android.com/apk/res-auto" 
        android:layout_width="fill_parent"
        android:layout_height="fill_parent"
        android:fillViewport="true">  	  
	<LinearLayout
	    android:id="@+id/myLayout"
	    android:orientation="vertical"
	    android:layout_width="match_parent"
	    android:layout_height="wrap_content"
	    android:background="#FFFFFF"
	    android:fillViewport="true">
	    <TextView
	        android:id="@+id/bodytext"
	        android:layout_width="wrap_content"
	        android:layout_height="wrap_content"
	        android:textColor="#000000"
	        android:gravity="center"
	        android:textSize="25sp"/>
	</LinearLayout>
</ScrollView> 

Output

Android Ship SQLite Database with APK | Copy SQLite Database from assets folder to data Example

Drawbacks

The drawback of this approach is that after copying SQLite database from assets folder to android OS, we cannot delete database file from assets folder of our APK. Hence the database file in assets folder is occupying memory with no use. We cannot delete files from assets folder because APK file is read-only.

Download Source Code

POSTED BY
ARVIND RAI
ARVIND RAI
FIND MORE TUTORILAS






©2019 concretepage.com | Privacy Policy | Contact Us