Android Ship SQLite Database with APK
September 24, 2015
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) );
Table: dbVersion
CREATE TABLE `dbVersion` ( `version_id` int NOT NULL, PRIMARY KEY(version_id) );
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;
}
}
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>
<?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
