Android: How to use own sqlite database

Android has built-in support for using Sqlite database to keep persistent data.

You can also use your own database in your application copying from external file that are saved in assets directory of that application. You may go through following steps:

  1. Modify your database schema/structure to make it compatible with Android OS. Change primary key fields of all tables to “_id“.
  2. Create a table “android_metadata(…)” and insert one row. Create a meta-data table  - CREATE TABLE  “android_metadata” (“locale” TEXT DEFAULT ‘en_US’)  and INSERT INTO “android_metadata” VALUES (‘en_US’)
  3. Copy your database file to … yourapp/assets/
  4. Read this file from MyDatabaseHelper and write it to application’s database directory
  5. Open and Read your database from your application using MyDatabaseHelper extends SQLiteOpenHelper
  6. Query on that database
  7. That’s all

For Example, application name is SearchSqlite:

  1. You may open your database file using SQLite Database Browser and then modify it
  2. Add new table by SQLite Database Browser
  3. Let your sqlite database file name is zaman_sqlite. Copy it into C:\Users\zaman\workspace\SearchSqlite\assets\zaman_sqlite (change it as your apps directory)
  4. Read zaman_sqlite and write into databases directory.
  5. Sample partial code as following

 public class MyDataBaseHelper extends SQLiteOpenHelper
{

    //singleton/ single instance reference of database instance
    private static MyDataBaseHelper _dbHelper;

    //The Android's default system path of your application database.
    //private static String DB_PATH = "/data/data/YOUR_PACKAGE/databases/";
    private static String DB_PATH = "/data/data/com.searchsqlite.droidclient/databases/";
    //database name
    private static String DB_NAME = "zaman_sqlite";
    //reference of database
    private SQLiteDatabase _zamanSqliteDb;
    //
    private String _searchToken;

    //
    private final Context _context;
    //Contains search result when user search with any text
    private Vector<Object> _searchResultVec;

    private MyDataBaseHelper(Context context)
    {
        super(context, DB_NAME, null, 1);
        this._context = context;
        _searchResultVec = new Vector<Object>();
        _searchToken = "";
    }
    public static MyDataBaseHelper getInstance(Context context)
    {
        if(_dbHelper == null)
        {
            _dbHelper = new MyDataBaseHelper(context);
        }
        return _dbHelper;
    }
    public String getLastSearchToken()
    {
        return _searchToken;
    }

    public void createDataBase() throws IOException
    {
        boolean dbExist = checkDataBase();

        if(dbExist)
        {
            //do nothing - database already exist
        }else{

            this.getReadableDatabase();

            try {

                copyDataBase();

            } catch (IOException e) {

                throw new Error("Error copying database");

            }
        }

    }

    private boolean checkDataBase()
    {
        SQLiteDatabase checkDB = null;

        try
        {
            String myPath = DB_PATH + DB_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

        }catch(SQLiteException e)
        {
            //database does't exist yet.
        }
        if(checkDB != null)
        {
            checkDB.close();
        }

        return checkDB != null ? true : false;
    }
    private void copyDataBase() throws IOException
    {
        //Open your local db as the input stream
        InputStream myInput = _context.getAssets().open(DB_NAME);

        // Path to the just created empty db
        String outFileName = DB_PATH + DB_NAME;

        //Open the empty db as the output stream
        OutputStream myOutput = new FileOutputStream(outFileName);

        //transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer))>0)
        {
            myOutput.write(buffer, 0, length);
        }
        //Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();
    }

    public Vector<Object> search(String text)
    {
        //list of Versioni, search result with query text
        _searchResultVec = new Vector<Object>();
        _searchToken = text;

        try
        {
            //open database to query
            openDataBase();

            Cursor cursor = _zamanSqliteDb.query("TextTable",
                    new String[] { "Column1"},
                    "Column1" + " like '%"+text+"%'",
                    null ,
                    null,
                    null,
                    null);

            //mapped all rows to data object
            if (cursor.moveToFirst())
            {
                do
                {
                    //create objects
                } while (cursor.moveToNext());
            }
            //close cursor
            cursor.close();
        }
        catch(Exception ex)
        {
            System.out.println("DatabaseHelper.search()- : ex " + ex.getClass() +", "+ ex.getMessage());
        }
        //
        return _searchResultVec;
    }
}

References:

  1. http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/
public class MyDataBaseHelper extends SQLiteOpenHelper
{

//singleton/ single instance reference of database instance
private static MyDataBaseHelper _dbHelper;

//The Android's default system path of your application database.
//private static String DB_PATH = "/data/data/YOUR_PACKAGE/databases/";
private static String DB_PATH = "/data/data/com.searchsqlite.droidclient/databases/";
//database name
private static String DB_NAME = "zaman_sqlite";
//reference of database
private SQLiteDatabase _zamanSqliteDb;
//
private String _searchToken;

//
private final Context _context;
//Contains search result when user search with any text
private Vector<Object> _searchResultVec;

private MyDataBaseHelper(Context context)
{
super(context, DB_NAME, null, 1);
this._context = context;
_searchResultVec = new Vector<Object>();
_searchToken = "";
}
public static MyDataBaseHelper getInstance(Context context)
{
if(_dbHelper == null)
{
_dbHelper = new MyDataBaseHelper(context);
}
return _dbHelper;
}
public String getLastSearchToken()
{
return _searchToken;
}

public void createDataBase() throws IOException
{
boolean dbExist = checkDataBase();

if(dbExist)
{
//do nothing - database already exist
}else{

this.getReadableDatabase();

try {

copyDataBase();

} catch (IOException e) {

throw new Error("Error copying database");

}
}

}

private boolean checkDataBase()
{
SQLiteDatabase checkDB = null;

try
{
String myPath = DB_PATH + DB_NAME;
checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

}catch(SQLiteException e)
{
//database does't exist yet.
}
if(checkDB != null)
{
checkDB.close();
}

return checkDB != null ? true : false;
}
private void copyDataBase() throws IOException
{
//Open your local db as the input stream
InputStream myInput = _context.getAssets().open(DB_NAME);

// Path to the just created empty db
String outFileName = DB_PATH + DB_NAME;

//Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);

//transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer))>0)
{
myOutput.write(buffer, 0, length);
}
//Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}

public Vector<Object> search(String text)
{
//list of Versioni, search result with query text
_searchResultVec = new Vector<Object>();
_searchToken = text;

try
{
//open database to query
openDataBase();

Cursor cursor = _zamanSqliteDb.query("TextTable",
new String[] { "Column1"},
"Column1" + " like '%"+text+"%'",
null ,
null,
null,
null);

//mapped all rows to data object
if (cursor.moveToFirst())
{
do
{
//create objects
} while (cursor.moveToNext());
}
//close cursor
cursor.close();
}
catch(Exception ex)
{
System.out.println("DatabaseHelper.search()- : ex " + ex.getClass() +", "+ ex.getMessage());
}
//
return _searchResultVec;
}
}

About these ads

About M Moniruzzaman
A passionate software engineer, have been developing applications on various platforms such as Android, iPhone, .Net (C#) technologies and web based ASP.NET, PHP, JavaScript, jQuery technologies for more than 7 years. Especially I have expertise on developing applications for Android and iPhone, as well as service oriented, client-server based applications where clients will be reside on Android/iPhone that communicate with WCF(.NET) service hosted on server. I have completed certification in Microsoft Certified Professional Developer (MCPD) on .Net 4 . I have completed my graduation in -- B.Sc in Computer Science and Engineering, ShahJalal University of Science and Technology, Bangladesh. Thanks, M. Moniruzzaman (Zaman)

41 Responses to Android: How to use own sqlite database

  1. Kumar says:

    I created database in the sqlite database browser.

    How do i integrate in to my android application and how do compare it to my editview textbox. if user enters some value in the textbox, it wants to check in the database. If it is correct i want to go next page.

    suggest me any ideas and give some sample code

    thanks in advance
    kumar

  2. Zaman says:

    Hi Kumar,

    1. you need to open your own database using MyDatabaseHelper extends SQLiteOpenHelper
    2. when user enters value in textbox , query into database and then compare these.
    3. you may use method search()

    The post is updated with some code samples, you may find it whether it is helpful.

  3. munirasu says:

    Hi, This procedure is very much useful for our applications…

    Munirasu.V

  4. munirasu says:

    Hi Zaman,
    Good morning.. I need some suggestion from u newly i am learning android and developing my mca final year project in android. so can u give me some URLs which will be useful to learn Android..?

    Basically am from mathematical background, i know java somehow better but don’t have that much experience..so based on this plz suggest me..

  5. Bhushan says:

    Hello there

    I already create database and put it into assets folder under path
    data/data/my_package_name/database_name..and in my DB there is only one table ,i want to fetch table field on text view with click event.. in my application…

    but it gives error like force to close application…if possible please
    explain with small example….

    Thanks

    • Zaman says:

      You may call any method of reference variable without instantiating the object.

      For example,

      MyDbHelper db;
      db.search(…) //call method before create new instance

      this is one reason to cause that exception.

      thanks,
      Zaman

  6. ared says:

    Hi,

    Firstly, this is a great tutorial. However, I am facing some issues. My database file in my android device only contains the android_metadata table I know this because I pulled it from eclipse after it was transfered to the device. I don’t understand why it is not copying my other table named words into the database. Any help would be appreciated. Thanks in advance.

    • Hi ared,

      I think you need to be sure about some concerns as following:

      1. Make sure your Sqlite db file is okay. First of all, browse it using Sqlite Db browser (link is in article) and add some tables there and data also.
      2. Check columns and data types of android_metadata are correct.
      3. Then be sure whether raw file is created in android correctly
      4. Then do other tasks on that Db

    • Faaiz Hadaina says:

      This happened to me too and here is how i fixed it, delete the pointer to the database file from your asset in the Eclipse package explorer and as well as in the DDMS, now re-copy the database file to your physical asset folder by navigating to the folder using windows explorer and pasting it there, do not use the new file function in eclipse. After copying the file using windows explorer, refresh your asset folder in Eclipse and run your project, it should work now.

  7. Ankur Parashar says:

    i’m not able to retrieve data from tables application gives exception while retrieving data from DB tables , application forcefully closed.While quering from a table it show no. of rows and well as coloums in result but i am not able to retrieve the data.

    • Hi Ankur,

      “application gives exception” according this pointer I think this is the same problem your are facing like Bhushan [April - 12-2011], you may review that previous answers. please make sure that all the objects are instantiated before call its any methods.

      Thanks,
      Moniruzzaman

  8. rika says:

    hi thx for the tutorial…
    how if, i push the file onto the device, should i import file to asset and write the path too?
    as u said we have to change the primary key name to “_id”, if that primary key become foreign key how we identify it, it will have 2 “_id” in 1 table

  9. vinnie says:

    hi M Moniruzzaman,
    I am having the same problem of fetching the data from the database,like i created a xml file having 2 buttons(next and previous button) and a textview.by clicking on next button …the textview will be changed to the next row which is in DB..i reviewed this problem as mentioned by other people above but cant able to find the solution..

  10. uis says:

    how to link SQLite database browser in android eclipse?on click of a button i must invoke database to open a file from the database?

  11. kirti says:

    Hi!!!

    its really good tutorial,

    i wanna some ready source code for my mca project, like

    – Personal Inventory Management
    – Personal Expense Manager
    – user’s PhoneBook fatcher

    with android – or phonegap.

    if any one have such source code then please share with me at my email : info@kmwebsoft.in

    Thanks in advance.

  12. krishh says:

    its good tutorial,,,But its missing opendatabase method code,,,,please add that method also

    Thanks in advance

  13. AndrewCesario says:

    Yes, I also noticed the missing opendatabase method. Can anyone post that please?

  14. shakil says:

    public void openDataBase() throws SQLException{

    //Open the database
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }

  15. pavithra says:

    how to retieve the single column value in external database and display it in list view

  16. satish says:

    Hello
    I already create database and put it into assets folder under path and its size is more than 10 mb. There are multiple tables in database. I am having the particular table fetching data problem.
    plz help me.

  17. chetan says:

    hi zaman,
    i need some help i hv inserted data base in asset folder and all operation are performing well but after then when i change my database structure and add one column then it gives null pointer exception.after modification i again performed metadata query on database.any suggestion y it happens.

  18. rubyroid2k says:

    I haven’t found that if databases directory is missing, I have to put my DB-copy slice of code inside this block:
    String outputFile = this.getDatabasePath(“base.sqlite”).getAbsolutePath();
    File f = new File(outputFile);
    if (f.getParentFile().mkdirs()) {
    // copy database to databases dir
    }

  19. SPDM says:

    This is exactly what I’m looking for! I’m trying to use the code, but when I put it into eclipse, the one method it can’t find is openDataBase();

    I’m importing the following:

    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.util.Vector;

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

  20. ramesh says:

    Thanks Zaman , it is quiet useful to my project development

  21. Jayant Shinde says:

    Hi zaman,
    I have created a bar chart how to connect my app to the database so that the graph is plotted using the values from the database and also how to refresh my app so that it plots a new graph if there is a change in the database. I am using achartengine to plot the graph. My graph displays top 10 travel destinations and i have created the database using sqlite databrowser.

  22. Pingback: Using External database in android : Android Community - For Application Development

  23. vids says:

    Hi. This post is very informative. Thanks . But i am getting an error. I am trying to execute a query in the same class. But i’m getting an error saying that no such table “XYZ” found. My code is

    {
    String query=”SELECT _id,Name,PhoneNo FROM ContactDtls “;
    database=SQLiteDatabase.openDatabase(DB_PATH+DB_NAME,null,SQLiteDatabase.OPEN_READWRITE);
    if(database!=null)
    database.rawQuery(query, null);
    if(result!=null)
    {
    if(result.moveToFirst())
    {
    do
    {
    System.out.println(“The Name is”+result.getString(result.getColumnIndex(“Name”)));
    }while(result.moveToNext());
    }
    }

    }

  24. I made my database in this way and it works without problem.. EXCEPT, when I have DB update and I want to deliever new DB version with new APK..

    Help / ideas / suggestions?

  25. Dave says:

    I have used your search method and I keep on getting [] as the output on the new activity

  26. Pingback: how to use sqlite file with android : Android Community - For Application Development

  27. Just an addition:

    in createDataBase() there is the following check;

    this.getReadableDatabase();

    This checks if there is already a database with the provided name and if not creates an empty database such that it can be overwritten with the one in the assets folder. On newer devices this works flawlessly but there are some devices on which this doesn’t work. Mainly older devices. I do not know exactly why, but it seems like the getReadableDatabase() function not only gets the database but also opens it. If you then copy the database from the assets folder over it, it still has the pointer to an empty database and you will get table does not exist errors.

    So in order to make it work on all devices you should modify it to the following lines:

    SQLiteDatabase db = this.getReadableDatabase();
    if (db.isOpen()){
    db.close();
    }

    Even if the database is opened in the check, it is closed thereafter and it will not give you any more trouble.

  28. btw, how to connect it with own sqlite? my data on my own server and i want to sync data with database sqlite in android handheld :)

    • Hi CreatorB doodler,

      If you want to sync your local sqlite database with an already existing online database, you will have to write a couple of functions to get the data from your online database to your local database and vice versa.

      In Android you will have to do that on a different thread and you will have to do some version management to make sure you only sync the updated items, and not the entire database every time you sync. Below I placed some example code.

      private void downloadlatest(){
      //new thread creation to get the database in the background
      Thread updatethread = new Thread(null,new Runnable(){
      public void run(){
      try{
      String searchUrl = “your php script with JSON encoded database entrys”;
      String version = //get version of local database
      //data that you want to send to the script to get the newest values
      ArrayList nameValuePairs = new ArrayList();
      nameValuePairs.add(new BasicNameValuePair(“version”, version));
      nameValuePairs.add(new BasicNameValuePair(“other var name”, other var value));

      HttpClient httpclient = new DefaultHttpClient();
      HttpPost httppost = new HttpPost(searchUrl);
      httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
      HttpResponse response = httpclient.execute(httppost);
      HttpEntity entity = response.getEntity();
      is = entity.getContent();
      String result = null;
      BufferedReader reader = new BufferedReader(new InputStreamReader(is,”iso-8859-1″),8);
      StringBuilder sb = new StringBuilder();
      String line = null;
      while ((line = reader.readLine()) != null) {
      sb.append(line + “\n”);
      }
      is.close();
      result=sb.toString();
      JSONArray newitems = new JSONArray(result);
      for (int i = 0; i < newitems.length(); i++){
      //add or update records in database

      }
      runOnUiThread(new Runnable(){
      @Override
      public void run() {
      //anything you want to do on the main thread while synchronizing
      }
      });
      }catch (Exception e){
      }
      }
      });
      updatethread.start();
      }

  29. owh ya, thanks for advance….

  30. madhuri says:

    Hi
    Can you please give me an idea how to insert new rows into database that is already exited in asset folder and copied .

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: