Tuesday, September 29, 2009

Create Android Database


This sample code illustrates how we can use Android SQLite Database to store data in Android.

Following example will give output as Saranga/22 since I have created a text view and set the out put to it. If you want to add your own fields, first drop the existing table using given source code.

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.widget.TextView;
public class DBTest extends Activity {
 /** Called when the activity is first created. */
 @Override
 public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);

  SQLiteDatabase myDB= null;
  String TableName = "myTable";

  String Data="";

  /* Create a Database. */
  try {
   myDB = this.openOrCreateDatabase("DatabaseName", MODE_PRIVATE, null);

   /* Create a Table in the Database. */
   myDB.execSQL("CREATE TABLE IF NOT EXISTS "
     + TableName
     + " (Field1 VARCHAR, Field2 INT(3));");

   /* Insert data to a Table*/
   myDB.execSQL("INSERT INTO "
     + TableName
     + " (Field1, Field2)"
     + " VALUES ('Saranga', 22);");

   /*retrieve data from database */
   Cursor c = myDB.rawQuery("SELECT * FROM " + TableName , null);

   int Column1 = c.getColumnIndex("Field1");
   int Column2 = c.getColumnIndex("Field2");

   // Check if our result was valid.
   c.moveToFirst();
   if (c != null) {
    // Loop through all Results
    do {
     String Name = c.getString(Column1);
     int Age = c.getInt(Column2);
     Data =Data +Name+"/"+Age+"\n";
    }while(c.moveToNext());
   }
   TextView tv = new TextView(this);
   tv.setText(Data);
   setContentView(tv);
  }
  catch(Exception e) {
   Log.e("Error", "Error", e);
  } finally {
   if (myDB != null)
    myDB.close();
  }
 }
}

You can download source code here.

Password: sara

Tested with Android 2.3.3 Platform SDK.

If you are interested please refer Android User Interface Design to learn simple way to Designing interfaces.

79 comments:

  1. Great article!!!! Simple but it works

    ReplyDelete
  2. Hey Noori,
    I tested this code with Eclipse IDE.
    I recommend to use Eclipse new version to built your applications.
    You can find nice article on Installing and Updating ADT in Android web site.

    ReplyDelete
  3. I'm so glad to find a database code that works! Thank you!!!

    If I may be greedy, would you mind doing a tutorial on parsing too?

    ReplyDelete
  4. Its a very simple, easy to understand code, and it works well... Thanks a lot!

    ReplyDelete
  5. where is the table stored in my project?

    ReplyDelete
  6. @ Anonymous ,
    The SQLite database is stored in the /data/data /databases folder of an Android device. There remember that the database you create for an application is only accessible to itself.

    ReplyDelete
  7. i m store images in sqlite database in my android activity.
    in a blob datatype..

    when i retrived blob type from databse and converted into byte array. my bitmap object return null value.

    also in my code
    when i inserted byte arry in database that time the
    size of array was 2280.
    and when getting back from database that time the size of the byte array was just 12.
    i use Bitmap.decodeByteArray() method.
    thnx

    ReplyDelete
  8. hi
    I have problem ,please help me !
    I have file /sdcard/data.db . I can read data in that file but I can't insert data into file /sdcard/data.db

    thank you !

    ReplyDelete
  9. This article is helpful.
    But could you guide me, how to get the data from the database located on remote server ? Or we need to download a file and then query the data in that file ?

    ReplyDelete
  10. @ Anonymous,
    I'll put a post on how to create and access databases using SDCard.
    thanks !

    @ Vivek Deshpande,
    thanks, sorry I have no idea how to do that. If you found a way please post it here.
    thanks !

    ReplyDelete
  11. Do you have an example of an application using the creating of database and retrieving of the database so that I can learn? Thanks.

    ReplyDelete
  12. Dear Noelle,
    The above code is full example for creating and retrieving datababe,
    Please follow the HellowWorld example and replace the "HelloAndroid" class using above class.
    thanks !

    ReplyDelete
  13. Hey,
    thaks very much for this code,it's just great.
    i have a question...
    i am a beginner with android,i am building an application with googlemap en GPS i need to save coordinated from googlemap in this data base
    do you have any idees how can i do it?
    pleaaase help me!!!

    ReplyDelete
  14. yes, you can use above code to save the values of latitude and longitude. I was able to lean most of stuff from The Friend Finder - MapActivity using GPS.

    ReplyDelete
  15. I found this post to have alot of great discussion, thank you for your insights, they are very helpful and have saved me much time.

    Signed: Big Country

    ReplyDelete
  16. Simply superb man... Keep up the good work....

    How to delete the database values.....

    ReplyDelete
  17. how to create data base in android and the db should be growable such that user input should be stored in database automatically.

    ReplyDelete
  18. Hi Every one
    I am a beginner android,i'm writting my app with database.I have a database with name food.db.
    How to add food.db into my app and query it.

    Please help me.
    thanks

    ReplyDelete
  19. You need to look for asians if you want to learn something. We on this side of the globe are doomed! You guys are going to rule the world soon.

    I've looked into so many web sites, so many examples, but all of them were flawed or not easily adaptable.

    YOUR EXAMPLE IS PERFECT ! ! !

    THANK YOU ! ! !

    ReplyDelete
  20. @ Anonymous,
    Thank you for your comment.

    ReplyDelete
  21. This example is perfect for me... I am new to android!
    This code really help me a lot... Thank budy.

    ReplyDelete
  22. Could you please post the XML file for this code? The code works without errors, but it doesn't display my SQLite data.

    Thanks in advance!

    ReplyDelete
  23. Thanks a lot mate, seriously all the other SQLite database tutorials for android have been too detailed so it confused me a bit but then looking at your straight forward code I understood it right away so thanks for putting this up mate, subscribed.

    ReplyDelete
  24. @ Arsal,
    Thank you very much.

    @ Top Android Apps,
    Thank you very much for your comment.

    ReplyDelete
  25. really really really really really really really helpful. You saved my life

    ReplyDelete
  26. Hey Saranga Rathnayake
    I have used this same code and it dint showed me any errors.. but it shows me the run time error..
    Application closed forcefully..

    Can you tell me where i am getting problem ??

    ReplyDelete
  27. @ Sahil,
    This should be work, first try adding "Drop Table" at line 21 and run.
    myDB.execSQL("DROP TABLE your-database-name.your-table-name");
    it will remove the exiting table.
    More queries:
    http://www.sqlite.org/lang.html

    ReplyDelete
  28. thanks a lot please do these type of things for IT
    It will help in my project of android

    ReplyDelete
  29. Hi saranga..,

    I am trying with this coding to create database for mobile using android applications but its not able to open the database in the emulator while running..Can u help me if u have time then..?

    Regards
    munirasu

    ReplyDelete
  30. @ Munirasu,
    Did you get an error ?
    Can you please tell me what happens when you run the code ?

    ReplyDelete
  31. "device has stopped unexpectdly "

    need help

    ReplyDelete
  32. the code is so understandable:) :) but i get error... forceful exit.. can u help pls

    ReplyDelete
  33. @sunwicked, Balaji,

    I tested it with Android 2.3.3 Platform too, it is working fine. May be your database and table is already created, comment line 22 to 31 and try again.

    Thank You !

    ReplyDelete
  34. thanks a lot, it works so well but when i add a field it dosen't work please help me and thanks again

    ReplyDelete
  35. @ Jack D'silva,
    You are welcome, thanks for the comment.

    @ Anonymous,
    Try dropping the existing table and then add fields you want, this code will remove existing table.

    package com.db;

    import android.app.Activity;
    import android.database.Cursor;
    import android.database.sqlite.SQLiteDatabase;
    import android.os.Bundle;
    import android.util.Log;
    import android.widget.TextView;
    public class DBTest extends Activity {
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);

    SQLiteDatabase myDB= null;
    String TableName = "myTable";

    String Data="";

    /*
    * Create a Database.
    * */
    try {
    myDB = this.openOrCreateDatabase("DatabaseName", MODE_PRIVATE, null);

    myDB.execSQL("DROP TABLE "+TableName+";");


    }
    catch(Exception e) {
    Log.e("Error", "Error", e);
    } finally {
    if (myDB != null)
    myDB.close();
    }
    }
    }

    ReplyDelete
  36. hi thx for the code, can i ask if i have 3 text box and the id is textbox1,textbox2,textbox3, which is string,how to get the string from the text box and store in database?is it juz change this line of code?

    myDB.execSQL("INSERT INTO "
    + TableName
    + " (Field1, Field2,Field3)"
    + " VALUES (textbox1, textbox2,textbox3);");

    ReplyDelete
  37. @ emosushilun,
    Before that you have to change the table as follows,
    /* Create a Table in the Database. */
    myDB.execSQL("CREATE TABLE IF NOT EXISTS "
    + TableName
    + " (Field1 VARCHAR, Field2 VARCHAR, Field3 VARCHAR);");
    Then only you can add three values.

    ReplyDelete
    Replies
    1. hello i want to store three value in database i have tried the same process but i m not getting the desired result by code is

      /* Create a Table in the Database. */
      myDB.execSQL("CREATE TABLE IF NOT EXISTS "
      + TableName
      + " (Field1 VARCHAR, Field2 VARCHAR , Field3 VARCHAR);");

      /* Insert data to a Table*/
      myDB.execSQL("INSERT INTO "
      + TableName
      + " (Field1, Field2, Field3)"
      + " VALUES ('text1', 'text2' ,'text'3 );");

      /*retrieve data from database */
      Cursor c = myDB.rawQuery("SELECT * FROM " + TableName , null);

      int Column1 = c.getColumnIndex("Field1");
      int Column2 = c.getColumnIndex("Field2");
      int Column3 = c.getColumnIndex("Field3");

      // Check if our result was valid.
      c.moveToFirst();
      if (c != null) {
      // Loop through all Results
      do {
      String Name = c.getString(Column1);
      int Age = c.getString(Column2);
      int Ag = c.getString(Column3);
      Data =Data +Name+"/"+Age+"/"+Ag+"\n";

      please guide me how to do that ??

      Delete
    2. hi thanks for this code
      i have tried to store 3 text fields in the database but i could'nt i don't know where the problem i have modified code as
      /* Create a Table in the Database. */
      myDB.execSQL("CREATE TABLE IF NOT EXISTS "
      + TableName
      + " (Field1 VARCHAR, Field2 VARCHAR , Field3 VARCHAR);");

      /* Insert data to a Table*/
      myDB.execSQL("INSERT INTO "
      + TableName
      + " (Field1, Field2, Field3)"
      + " VALUES ('text1', 'text2 ,'text3' );");

      /*retrieve data from database */
      Cursor c = myDB.rawQuery("SELECT * FROM " + TableName , null);

      int Column1 = c.getColumnIndex("Field1");
      int Column2 = c.getColumnIndex("Field2");
      int Column3 = c.getColumnIndex("Field3");

      // Check if our result was valid.
      c.moveToFirst();
      if (c != null) {
      // Loop through all Results
      do {
      String Name = c.getString(Column1);
      int Age = c.getString(Column2);
      int Ag = c.getString(Column3);
      Data =Data +Name+"/"+Age+"/"+Ag+"\n";

      please tel me where the problem is ??

      Delete
  38. merci pour ce tutoriel, c'est très bien fait.
    Moi j'ai déjà crée une base dans sqlite et j'aimerais juste savoir comment faire pour afficher les données que j'ai déjà stocké dans ma table à l'aide du curseur.
    Merci

    ReplyDelete
  39. bonjour saragna merci pour ce beau travail.
    En faite j'ai déjà crée une table avec comme champs "id","nom" et "prenom" sur sqlite et j'aimerais savoir comment faire pour afficher ces champs sur android.

    ReplyDelete
  40. hi,
    i have my own databaseserver,which have different user.i want to create an application ,...

    i want to enter the username and password in the edittext and pass the values to serverdatabase(php function(which is already ther in my database).and the output it returns should be shown in the next view . can u pls guide me ...

    ReplyDelete
  41. @ kishore,
    You can use HTTP POST Request with HttpClient to get the work done.
    Check this code;
    http://www.androidsnippets.com/executing-a-http-post-request-with-httpclient

    ReplyDelete
  42. greats inform... i have to do that os. so this is so importan to me for read your blog..thank you :)

    ReplyDelete
  43. @ emy,
    thank you very much for your comment.

    ReplyDelete
  44. in the code u hv inserted the values 'saranga' and 22 but suppose if i want to insert an array of values then how should i do it ?
    My requirement is :
    1) show a text box which says "Please enter a value"
    2) user enters value and clicks submit button
    3) on submitting the value user gave in the text box is given as a parameter to that insert function and that value gets stored in the database

    hw to go abt doing that ? can u post a code for the same coz i m very new to android programming

    ReplyDelete
  45. @ Anonymous,
    You cannot directly insert an array to database. Instead of that you can create string using some delimiter character and save it. As an example if you want to insert following array of strings {"string1","string2","string3"} you can create new string like "string1#string2#string3" and save it. When you retrieve it, you have to split it using "#" and get actual values.

    You can follow my posts Android User Interface Design and Event Handling In Android to solve your other problems.

    ReplyDelete
  46. Hey I have another similar problem .. I have a code to connect a local my sql database ( the one we get with wampserver) to android phone but i have some problems with it ..i m posting the code below..see if u can solve the errors please


    //city.java
    package com.list;
    import java.io.BufferedReader;
    import java.io.InputStream;
    import java.io.InputStreamReader;
    import java.util.ArrayList;
    import org.apache.http.HttpEntity;
    import org.apache.http.HttpResponse;
    import org.apache.http.NameValuePair;
    import org.apache.http.client.HttpClient;
    import org.apache.http.client.entity.UrlEncodedFormEntity;
    import org.apache.http.client.methods.HttpPost;
    import org.apache.http.impl.client.DefaultHttpClient;
    import org.json.JSONArray;
    import org.json.JSONException;
    import org.json.JSONObject;
    import android.app.ListActivity;
    import android.net.ParseException;
    import android.os.Bundle;
    import android.util.Log;
    import android.widget.Toast;
    public class city extends ListActivity {
    JSONArray jArray;
    String result = null;
    InputStream is = null;
    StringBuilder sb=null;
    @Override
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    ArrayList nameValuePairs = new ArrayList();
    //http post
    try{
    HttpClient httpclient = new DefaultHttpClient();
    HttpPost httppost = new HttpPost("http://10.0.2.2/city.php");
    httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
    HttpResponse response = httpclient.execute(httppost);
    HttpEntity entity = response.getEntity();
    is = entity.getContent();
    }catch(Exception e){
    Log.e("log_tag", "Error in http connection"+e.toString());
    }
    //convert response to string
    try{
    BufferedReader reader = new BufferedReader(new InputStreamReader(is,"iso-8859-1"),8);
    sb = new StringBuilder();
    sb.append(reader.readLine() + "\n");
    String line="0";
    while ((line = reader.readLine()) != null) {
    sb.append(line + "\n");
    }
    is.close();
    result=sb.toString();
    }catch(Exception e){
    Log.e("log_tag", "Error converting result "+e.toString());
    }
    //paring data
    int ct_id;
    String ct_name;
    try{
    jArray = new JSONArray(result);
    JSONObject json_data=null;
    for(int i=0;i

    //my sql table
    CITY ( CITY_ID int , CITY_NAME varchar(30),primary key(CITY_ID));

    ReplyDelete
  47. @ Karan Balkar,

    Please check my post Android Login Screen Using HttpClient. There check whether you are posting and receiving data correctly.

    ReplyDelete
  48. Thank you Saranga. You mentioned as a reply to one of the comments "I'll put a post on how to create and access databases using SDCard.
    thanks !". Have you made this post cause I cant seem to find it?

    ReplyDelete
  49. Hey Thanks for this tutorial
    It was very nice and 'to the point'.
    would please give some guidance for converting or parsing data from MS-Excel to android database?

    ReplyDelete
  50. i am begginer in android and i m working on a apps in which i have to insert value and fetch value how could i be able to do i have written ur java code but can you sent the xml file of this code too?

    ReplyDelete
  51. @ Thomas,
    Sorry I couldn't find time to create it. I'll put it soon.

    @sagar patel,
    Sorry I have no such experiences. Thanks for your comment.

    @Lalit,
    I have uploaded the source code and you can find the link at the end of the post.

    ReplyDelete
  52. Hi,

    Simple literarure, but I have few basic questions:

    Where is the database stored? On SD Card? If yes, which folder?

    Or, is the database a part of the .apk file? That is, if .apk is uninstalled, database also "disappears/uninstalled"?

    I am working on android 4.0, and new to database. I am facing lot of problems with rooting on phone, which I want to avoid, by baking my database into the .apk file during install.

    Any advice will be very useful.

    Rgds,

    ReplyDelete
  53. VERY VERY SIMPLE!!! IM VERY HELPED!! THANKS.. TWO TUMBS UP :D

    ReplyDelete
  54. Can you put a post on how to create and access databases using SDCard.

    ReplyDelete
  55. Can put a post on how to create and access databases using SDCard.

    ReplyDelete
  56. Hi CHANDAN

    I am developing an application for android phone, In this I need to use at least 400 audio file which can be played for some respective texts, Now my question is which is the optimized way to do this.. One solution is putting all the audio files in the resource folder and referring from there, this will never be a feasible solution as the application size will increase. Is there any way to convert the audio file into some format and dump into the SQLite database and retrieve flexibly.

    ReplyDelete
  57. Hi CHANDAN

    I am developing an application for android phone, In this I need to use at least 400 audio file which can be played for some respective texts, Now my question is which is the optimized way to do this.. One solution is putting all the audio files in the resource folder and referring from there, this will never be a feasible solution as the application size will increase. Is there any way to convert the audio file into some format and dump into the SQLite database and retrieve flexibly.

    ReplyDelete
  58. Hi dear!!!!!!!!..
    Help me
    how to create the database on server
    i m on drop box for android ,so plz help me,
    if any body knows that
    thanks

    ReplyDelete
  59. Hi dear!!!!!!!!
    Help me.
    I am working on dropbox for android,
    plz help ,how to create the database on server for android ,,
    plz if any body knows any idea or code for this so plz help me
    thanks.

    ReplyDelete
  60. This comment has been removed by the author.

    ReplyDelete
  61. I am not able to run my simple database app in android v2.3.5 but it is running on emulator. Any help would be highly appreciable.

    Ranjit (rsr.jitu@gmail.com)

    ReplyDelete
  62. hi.
    Saranga Rathnayake.

    can u provide me code for login using sqlite database.for example comparing user data with sqlite retrive data.

    ReplyDelete
  63. Very Good Article

    My question is how do I create a database of information with only data to be accessed (queried)

    For example I have three spinners and a button. I'll put an example

    in 1st Spinner I have school choices, hospitals, squares (Public Places) ...
    in 2nd Spinner I have the options of city areas (zone 1, zona2, zona3 ..)
    3rd spinner in the main districts of the city (The neighborhood, neighborhood B. ..)

    if I choose one spinner School
    and 3 spinner I choose the District "A",
    and the person clicks on the search button
    how do I display the next screen all schools in the district?


    I want this information from Spinners, as I create this table?
    Will function as a search filter

    ReplyDelete
  64. how to insert multiple rows at a time using this query......

    myDB.execSQL("INSERT INTO "
    + TableName
    + " (Field1, Field2)"
    + " VALUES ('Saranga', 22);");

    ReplyDelete
  65. How to posting the data fromURL?

    ReplyDelete
  66. How to posting the data from url? without internet

    ReplyDelete
  67. hi guys
    i want to create application,
    in that application i have database then in that how to fetch the data from my apps to database

    ReplyDelete
  68. sir i m making android app my project is help live the all data in api web services but i am facing problem i want talk to u i m krish mishra

    ReplyDelete