This article explains the usage of an Android library which is helpful to import and use external SQLite database into your Android application. It has been a problem for Android developers to release an application with some existing rows of data in the database. For example if you are developing a static application which is used to display some tourist spots in your country, probably you need to release the database with predefined details about the tourist places inside it. However, there are no native ways to import external database easily into your Android application.
The Android SQLiteAssetHelper library allows you to build your SQLite database in your desktop computer, and to import and use it in your Android application. Let's create a simple application to demonstrate the application of this library.
Update: If you want to import database either from assets folder as described here or from an external location like SD card, a new approach is shared in Deploy and Upgrade Android Database From External Directory.
Step 1:
Create a database quotes.db using your favorite SQLite database application (DB Browser for SQLite is a portable cross platform freeware, which can be used to create and edit SQLite databases). Create a table 'quotes' with a single column 'quote'. Insert some random quotes into the table 'quotes'.
Step 2:
The database can be imported into project either directly as it is, or as a compressed file. The compressed file is recommended, if your database is too large in size. You can create either a ZIP compression or a GZ compression.
The file name of the compressed db file must be quotes.db.zip, if you are using ZIP compression or quotes.db.gz, if you are using GZ compression.
Step 3:
Create a new application “External Database Demo” with a package name “com.javahelps.com.javahelps.externaldatabasedemo”.
Step 4:
Open the build.gradle (Module: app) file and add the following dependency.
dependencies {
compile 'com.readystatesoftware.sqliteasset:sqliteassethelper:+'
}
Once you have saved the build.gradle file click on the 'Sync Now' link to update the project. You can synchronize the build.gradle, by right clicking on the build.gradle file and selecting "Synchronize build.gradle' option as well.
Step 5:
Right click on the app folder and create new assets folder.
Step 6:
Create a new folder 'databases' inside the assets folder.
Step 7:
Copy and paste the quotes.db.zip file inside the assets/databases folder.
Step 8:
Create a new class 'DatabaseOpenHelper'
package com.javahelps.externaldatabasedemo;
import android.content.Context;
import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;
public class DatabaseOpenHelper extends SQLiteAssetHelper {
private static final String DATABASE_NAME = "quotes.db";
private static final int DATABASE_VERSION = 1;
public DatabaseOpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
}
Notice that rather than extending SQLiteOpenHelper, the DatabaseOpenHelper extends SQLiteAssetHelper class.
Create a new class DatabaseAccess and enter the code as shown below. More details about this class is available at Advanced Android Database tutorial.
package com.javahelps.externaldatabasedemo;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.List;
public class DatabaseAccess {
private SQLiteOpenHelper openHelper;
private SQLiteDatabase database;
private static DatabaseAccess instance;
/**
* Private constructor to aboid object creation from outside classes.
*
* @param context
*/
private DatabaseAccess(Context context) {
this.openHelper = new DatabaseOpenHelper(context);
}
/**
* Return a singleton instance of DatabaseAccess.
*
* @param context the Context
* @return the instance of DabaseAccess
*/
public static DatabaseAccess getInstance(Context context) {
if (instance == null) {
instance = new DatabaseAccess(context);
}
return instance;
}
/**
* Open the database connection.
*/
public void open() {
this.database = openHelper.getWritableDatabase();
}
/**
* Close the database connection.
*/
public void close() {
if (database != null) {
this.database.close();
}
}
/**
* Read all quotes from the database.
*
* @return a List of quotes
*/
public List<String> getQuotes() {
List<String> list = new ArrayList<>();
Cursor cursor = database.rawQuery("SELECT * FROM quotes", null);
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
list.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
return list;
}
}
In this class only the getQuotes method is implemented to read the data from the database. You have the full freedom to insert, update and delete any rows in the database as usual. For more details, follow this link Advanced Android Database.
All the database related setups are completed and now we need to create a ListView to display the quotes.
Step 10:
Add a ListView in your activity_main.xml.
<FrameLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context=".MainActivity">
<ListView
android:id="@+id/listView"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_gravity="center" />
</FrameLayout>
Step 11:
Find the object of ListView in the onCreate method of MainActivity and feed the quotes which are read form the database.
package com.javahelps.externaldatabasedemo;
import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import java.util.List;
public class MainActivity extends ActionBarActivity {
private ListView listView;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
this.listView = (ListView) findViewById(R.id.listView);
DatabaseAccess databaseAccess = DatabaseAccess.getInstance(this);
databaseAccess.open();
List<String> quotes = databaseAccess.getQuotes();
databaseAccess.close();
ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, quotes);
this.listView.setAdapter(adapter);
}
}
Step 12:
Save all the changes and run the application.
This library also allows you to upgrade the database using an external SQL script. For more details about this library, visit to its official page.
Find the project at Git Hub.
Update: To store images in this database, read Import Database with Images in Android
Update: To store images in this database, read Import Database with Images in Android