Import and Use External Database in Android

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.


Step 9:
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
Previous
Next Post »

238 comments

Write comments «Oldest   ‹Older   201 – 238 of 238   Newer›   Newest»
shraddha
AUTHOR
October 25, 2017 at 9:08 AM delete

How to insert data in external sqllite database table?

Reply
avatar
Mohammad Reza Sh
AUTHOR
November 8, 2017 at 2:16 AM delete

Hi
thanks a lot.
It was perfect and short to solve my problem.

Reply
avatar
sirvan alie
AUTHOR
November 12, 2017 at 10:45 PM delete

Hi. Thanks for this post. I want to show data inside recyclerview items, But data can't display it. When print list, adding funection was correct and printed inside Logcat. But when I want to display it, my list size is zero. I can post my code here ?

Reply
avatar
vinod kumar
AUTHOR
December 8, 2017 at 4:30 AM delete

How do i import database with multiple tables

Reply
avatar
Gobinath
AUTHOR
December 9, 2017 at 3:26 AM delete

Hi,
You can create any number of tables in step 1 and access them all in your Android code.

Reply
avatar
Uzain
AUTHOR
December 10, 2017 at 1:34 PM delete

after running the app it says unfortunately " External Database Demo has stopped " . can you help me with this please ?

Reply
avatar
uzain
AUTHOR
December 10, 2017 at 6:41 PM delete

whenever i run this code it says " Unfortunately External Database Demo " has stopped.

Reply
avatar
Jorge Américo Vargas Freitas
AUTHOR
December 11, 2017 at 5:46 PM delete

Awesome tutorial, but rather simple approach.

It'd be nice to have a version of this offline sqlite only-read table with a custom adapter and clickable items.

Although it's a great sample for to test simple SQLite databases, it's hard to modify that for most useful uses.

I've been searching hardly for a solution to develop a clickable list that jumps to an activity that displays more info about the item from the same SQLite table, but it's been hard to find something good enough.

It'd be nice to read a tutorial about it from you here on JavaHelps.

Reply
avatar
Gobinath
AUTHOR
December 15, 2017 at 5:32 PM delete

Hi,
Please share your Logcat error message to track the exact problem.

Reply
avatar
sharon perumala
AUTHOR
December 28, 2017 at 2:39 PM delete

Sir,
I need to use a database stored in the external storage of the phone.
How can I do it?

Reply
avatar
Gobinath
AUTHOR
December 28, 2017 at 5:50 PM delete

Hi,
Try this tutorial
I developed that library long time ago so it may not work as expected now.

Reply
avatar
ranveer oraon
AUTHOR
January 20, 2018 at 12:05 AM delete

except sqlite ,which type of dbs can be import in android studio..like I wnt CSV, XML, SQL..

Reply
avatar
Ani Ifeatu
AUTHOR
March 8, 2018 at 4:39 PM delete

I don't think step 9 is necessary or rather the getQuotes() function in 9 can be moved to 8, bypassing the need for a DatabaseAccess class. The main difference between creating your database on the app and using a preloaded database is that the former utilizes SQLiteOpenHelper while the latter utilizes SQLiteAssetHelper. When extending the OpenHelper you have to override the onCreate and onUpgrade methods since the database is yet to be created. But for the AssetHelper, you only need to include the its dependency in gradle then you can implement all CRUD functions within that class.
I was able to implement a working db helper for my preloaded database using only the DatabaseOpenhelper class in step 8. I was then able to implement all my crud functions within that class.

Reply
avatar
Claudine Christy
AUTHOR
March 13, 2018 at 4:32 PM delete

Every weekend i used to visit this site, as i want enjoyment, since this this website conations in fact nice funny information too.
https://createmobailapp.shutterfly.com/

Reply
avatar
Wendy Aikens
AUTHOR
March 14, 2018 at 11:49 AM delete

Hi, I do believe this is an excellent blog. I stumbledupon it ;) I will revisit yet again since I book-marked it. Money and freedom is the greatest way to change, may you be rich and continue to guide others.
https://www.bloglovin.com/@eugenerivera/how-to-create-an-app-without-coding-skills

Reply
avatar
Lisa S. Maxwell
AUTHOR
March 14, 2018 at 12:46 PM delete

Someone essentially lend a hand to make critically articles I would state. That is the first time I frequented your web page and so far? I surprised with the research you made to make this actual post extraordinary. Wonderful process!
https://goo.gl/XemCw1?How-To-Create-An-App-For-Free-And-Make-Money

Reply
avatar
Elisa Buttler
AUTHOR
March 14, 2018 at 1:27 PM delete

Oh my goodness! Awesome article dude! Thanks, However I am encountering difficulties with your RSS. I don't understand why I cannot subscribe to it. Is there anyone else having the same RSS problems? Anyone that knows the answer can you kindly respond? Thanks!!
https://goo.gl/cuz2D3

Reply
avatar
Jonathon Morales
AUTHOR
March 16, 2018 at 4:25 PM delete

I don't know whether it's just me or if everyone else experiencing issues with your site. It appears as though some of the text on your content are running off the screen. Can someone else please comment and let me know if this is happening to them too? This might be a issue with my internet browser because I've had this happen before. Many thanks
https://www.bloglovin.com/@eugenerivera/how-to-make-your-own-app-make-money

Reply
avatar
sruthi
AUTHOR
May 28, 2018 at 11:54 PM delete

sir i have created a bookstore app,i want to retrieve the data from the bookstore table in my database,i followed ur steps sir,but as u said in step 8,when i extend DatabaseHelper with SQLiteAssetHelper i get an error here https://uploads.disquscdn.com/images/bc029ff3b07d090080409db6f4f324ea285117ece11a7394769ea861267f7646.png

because before this i created a table in android and inserted it in database.sir can u help me??

Reply
avatar
ansar bilal
AUTHOR
June 5, 2018 at 1:03 PM delete

Sir,i have a database as external as excel sheet how do i import into android. when i want import then this problem occurs. https://uploads.disquscdn.com/images/a3857e78ca95553a1fd8567199848c38abac13e1445c85b36bb6c7583221cf73.png

Reply
avatar
jojo
AUTHOR
June 8, 2018 at 1:55 PM delete

how do I download your code

Reply
avatar
Gobinath
AUTHOR
June 13, 2018 at 8:37 PM delete

Hi,
I am unable to guess anything from the error message. Could you please try to uninstall the installed application manually using Settings --> Apps and install it again.

Reply
avatar
Gobinath
AUTHOR
June 13, 2018 at 8:39 PM delete

Hi,
Please check the code in the article. There is no overriding onCreate method.

Reply
avatar
wearXable
AUTHOR
October 15, 2018 at 6:38 AM delete

case R.id.btn_login:
List userList=dbHelper.readAllInfo();
int id=0;
for (int i=0;i0){
Toast.makeText(this,"Login Successful!!!",Toast.LENGTH_SHORT).show();
Intent intent = new Intent(this,EditProfile.class);
intent.putExtra("ID",id);
startActivity(intent);
this.finish();
break;
---------------------------
Intent intent=getIntent();
id=intent.getIntExtra("ID",-1);

Reply
avatar
Hope Suresh
AUTHOR
November 1, 2018 at 1:25 PM delete

I am feeling grateful to read this. you gave a nice info for us.please update more stories.
https://goo.gl/aBhrtq
https://goo.gl/EDHuds
https://goo.gl/Vg8RbV

Reply
avatar
erick jayson deguzman
AUTHOR
November 7, 2018 at 2:33 PM delete

your code it works but do you have create database from SDcard/MicroSDcard using SQLiteCipher? im facing from encrypted database. BTW thanks for sharing :)

Reply
avatar
Laurent
AUTHOR
December 12, 2018 at 7:19 PM delete

Hello,

Thank you for your code very useful. I'm noob with android studio and SQLite and I succeed in my first steps thanks to you !

I created a SQLite db on PC (using SB Browser for SQLite) and join it in my project in the assets/databases folder. It works perfectly :)

Now I have update my SQLite db on PC and join it again in my project. After installing the new apk, the app use the first release of the db. The only way to use the new release is to uninstall the app, or to delete datas of the app using android applications options.

The app only use the db for reading, so I would like to upgrade my SQLite db (replace the old by the new one) when the app start after an update. I found some samples with OnUpgrade event, but I can't make it work…
Read you.

Reply
avatar
Gobinath
AUTHOR
December 12, 2018 at 9:06 PM delete

Hi,
Though I didn't try, the official website provides a guide to do this: https://github.com/jgilfelt/android-sqlite-asset-helper#database-upgrades


I will give a try on this and if possible, I will write a new article.

Reply
avatar
Laurent
AUTHOR
December 12, 2018 at 11:02 PM delete

Thank's a lot Gobinath. I already had a quick read at this article, but thanks to you I found the chapter "Upgrades via overwrite". So the issue is to use "setForcedUpgrade()" method. Very simple to use if you only want to replace the database as I do !

Reply
avatar
Srinivasan Jayamohan
AUTHOR
February 4, 2019 at 3:54 PM delete

gopinath loganathan, sir thank you so much for this awesome tutorial . i developed my offline exams preparation app and everything is finished but since last 1 month i was getting this error : no such table (direct copy db by streaming data ) your code works 100% thank you so much..............

Reply
avatar
Chandler Perez Santos
AUTHOR
March 20, 2019 at 8:21 AM delete

hello i am using Eclipse IDE to write Android Programs. i want to ask you how to install or add SQLiteAssetHelper in my Eclipse? there is seem to be no SQLiteAssetHelper in my IDE. how can i add it?

Reply
avatar
Gobinath
AUTHOR
March 21, 2019 at 5:26 AM delete

Hi,
I stopped using Eclipse for Android long time ago so I am not sure how it looks these days. Anyway, Android project is basically a gradle project so that regardless of the IDE, there should be build.gradle file. Please follow the Step 4 to add the dependency. If it doesn't help, please comment below with a screenshot of your project.

Reply
avatar
MarTa
AUTHOR
August 29, 2019 at 3:02 PM delete

Hi sir, thanks a lot for your article! I would like to ask you, if i have more than one table in my database, how i will took the data from the second table?

Reply
avatar
Gobinath
AUTHOR
August 31, 2019 at 12:42 AM delete

Hi,
In Step 9, add more methods (like getQuotes) to read from the second database.

Reply
avatar
Soma Pill
AUTHOR
September 16, 2019 at 3:51 PM delete

Soma pill is very effective as a painkiller that helps us to get effective relief from pain. This cannot cure pain. Yet when it is taken with proper rest, it can offer you effective relief from pain.
This painkiller can offer you relief from any kind of pain. But Soma 350 mg is best in treating acute pain. Acute pain is a type of short-term pain which is sharp in nature. Buy Soma 350 mg online to get relief from your acute pain.

https://globalonlinepills.com/product/soma-350-mg/


Buy Soma 350 mg
Soma Pill
Buy Soma 350 mg online



Buy Soma 350 mg online
Soma Pill
Buy Soma 350 mg

Reply
avatar
ouafae kandoussi
AUTHOR
September 19, 2019 at 4:27 AM delete

Thank you sir for this tutorial, it was helpful.
I need your help so I can use an online database instead of local.
I want to make my app detect that it's connected to network and upgrade the databse.
Can you please help me? Thank you

Reply
avatar
dayan yenesew
AUTHOR
January 12, 2020 at 5:30 PM delete

hello sir
after running the app it says unfortunately " External Database Demo1 has stopped " . https://uploads.disquscdn.com/images/9415a9fd438508c19652f4b95f4b387b3fc1073263495e573665a95eb8d204b2.png

mylogcat message is ......

Reply
avatar
mohamed salah
AUTHOR
February 18, 2020 at 4:29 AM delete

the image is not found

Reply
avatar
«Oldest   ‹Older   201 – 238 of 238   Newer›   Newest»

Contact Form

Name

Email *

Message *