NOTE:

NOTE: Of late, I have been getting requests for very trivial problems that many of you are facing in your day-to-day work. This blog is not to solve your "project" problems - surely not a "Support" site.
I just love to share my knowledge in my spare time and would appreciate any questions or feedback on the articles and code I have shared. I also do appreciate thought-provoking questions that would lead me to write more articles and share.
But please do not put your day-to-day trivial problems here. Even if you do, you most probably would not get a response here.
Thanks

Search This Blog

Loading...
x

Wednesday, 7 October 2009

SQLite DB Example | Android Developer Tutorial (Part 12)


There are 4 ways of storing data on the android platform:

  • 1.    Preferences
  • 2.    SQLite Database
  • 3.    Files
  • 4.    Network

A word about each of them here and then I will move on to an example that shows how to work with SQLite DB that comes along with the android platform.


Preferences
Basically used for storing user preferences for a single application or across applications for a mobile. This is typically name-value pairs accessible to the context.


Databases –
Android supports creating of databases based on SQLite db. Each database is private to the applications that creates it

Files –
Files can be directly stored on the mobile or on to an extended storage medium. By default other applications cannot access it.


Network –
Data can be stored and retrieved from the network too depending on the availability.


If an application wants to store and retrieve data for its own use, without having to share the data across applications, it can access the SQLite DB directly. There is no need of a content provider. We have seen in an earlier post how to use content providers.


In this example, we will do the following:
1.    Create a database (typically a one time activity)
2.    Create a table (typically a one time activity)
3.    Insert values into the table
4.    Retrieve the values from the table
5.    Display the retrieved values as a List view
6.    Delete all the records from the table before closing the connection to the database


Step 1: Create a database:


   sampleDB =  this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_PRIVATE, null);

This opens a database defined in the constant SAMPLE_DB_NAME, if it already exists. Else it creates a database and opens it. The second parameter is operating mode : MODE_PRIVATE meaning it is accessible to only this context. The other modes are and MODE_WORLD_WRITABLE. MODE_WORLD_READABLE


Step 2: Create a Table:


sampleDB.execSQL("CREATE TABLE IF NOT EXISTS " +
                        SAMPLE_TABLE_NAME +
                        " (LastName VARCHAR, FirstName VARCHAR," +
                        " Country VARCHAR, Age INT(3));");


Step 3: Insert values into the table:


sampleDB.execSQL("INSERT INTO " +
                        SAMPLE_TABLE_NAME +
                        " Values ('Makam','Sai Geetha','India',25);");


Step 4: Retrieve values


Cursor c = sampleDB.rawQuery("SELECT FirstName, Age FROM " +
                        SAMPLE_TABLE_NAME +
                        " where Age > 10 LIMIT 5", null);
           
      if (c != null ) {
            if  (c.moveToFirst()) {
                  do {
String firstName = c.getString(c.getColumnIndex("FirstName"));
                  int age = c.getInt(c.getColumnIndex("Age"));
                  results.add("" + firstName + ",Age: " + age);
                  }while (c.moveToNext());
            }
       }


Step 5: Display the values as a list
           
       this.setListAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,results));


The statement displays it as a list as the class extends a ListActivity.


Step 6: Delete the values from the table in the finally part of the try block


finally {
            if (sampleDB != null)
                  sampleDB.execSQL("DELETE FROM " + SAMPLE_TABLE_NAME);
                  sampleDB.close();
        }


It is as simple as this to work with the SQLite DB even in android. No different from a desktop application. However, there are various overloaded methods of query() provided by the SQLIteDatabase class which can be more optimally used instead of execSQL.


The complete code for this example is downloadable here.

92 comments:

  1. Hi,

    Really your tutorial helps me lot..
    But DELETE query not working for me. No error. Please post tutorial for updating the row.

    ReplyDelete
  2. Can you please give more details about what are you trying to do in your delete query? Is it just the tutorial sample code?
    Which version of SDK are you using?

    ReplyDelete
  3. hi,
    when i run this sqlitedbsample it is giving errors.

    ReplyDelete
  4. Hi Sai I am an android & java beginner I just start to learn android programming and found your blog . You write this database sample very clear and when I start your downloaded project it work very well but now I create new project and try to copy your code and I had a problem with this line of code

    " this.setListAdapter(new ArrayAdapter(this, android.R.layout.simple_list_item_1,results));"

    Error :"The method setListAdapter(ArrayAdapter) is undefined for the type create"

    It's look like method in C# but I can found it in your project .

    Where do I mistake ???

    ReplyDelete
    Replies
    1. I had a similiar error on a different project
      I solved the error by passing YourActivity.this in the argument of ArrayAdapter

      Delete
    2. DOES YOUR ACTIVTY EXTENDS THE LIST ACTIVITY????

      CHECK THIS ALSO:-http://stackoverflow.com/questions/3033791/the-method-setlistadapterarrayadapter-is-undefined-for-the-type-create.

      Delete
    3. Change ListAdapter to ArrayAdapter:
      write this:
      ArrayAdapter adapter = new ArrayAdapter(this,
      android.R.layout.simple_list_item_1, results);
      then create one listview in layout, then:
      listview.setAdapter(adapter);

      Delete
  5. Hi Geetha,

    Thanks a lot for such a well written blog and it has helped me a lot to get start with android. I was following this DB sample project of yours and wondering... what modifications should I make into your program to insert values into the DB which I capture through edittext widgets? Or in other words how can I capture values from a form and add them to my database? Looking forward for a reply.

    Thanks

    ReplyDelete
  6. never mind... got it working. wrote a method like this

    public void handleDataBase(String username, String email, String mobNum,
    String password, String country, String remember) {
    remember = "false";
    SQLiteDatabase sampleDB = null;
    try {
    sampleDB = this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_WORLD_READABLE,
    null);

    sampleDB.execSQL("INSERT INTO " + SAMPLE_TABLE_NAME + " Values ('"
    + username + "','" + email + "','" + mobNum + "','"
    + password + "','" + country + "','" + remember + "');");

    Cursor c = sampleDB.rawQuery("SELECT * FROM " +
    SAMPLE_TABLE_NAME , null);
    if (c != null ) {
    if (c.moveToFirst()) {
    do {
    String name = c.getString(c.getColumnIndex("UserName"));
    System.out.println("Name ----- "+ name);
    }while (c.moveToNext());
    }
    }

    } catch (SQLiteException se) {
    Log.e(getClass().getSimpleName(),
    "Could not create or Open the database");
    }
    finally {
    if (sampleDB != null)
    sampleDB.close();
    }
    }

    ReplyDelete
  7. 2 things, First... In your delete Query you don't specify WHAT to delete... I think it should read

    finally {
    if (sampleDB != null)
    sampleDB.execSQL("DELETE * FROM " + SAMPLE_TABLE_NAME);
    sampleDB.close();
    }
    you forgot to put a "*" to signify "all" from the database table between delete and from...

    Secondly... the line

    this.setListAdapter(new ArrayAdapter(this, android.R.layout.simple_list_item_1,results))

    should actually read

    this.setListAdapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1,results);

    ReplyDelete
  8. Hi John,

    DELETE FROM TABLE and DELETE * FROM TABLE are both acceptable ways of doing the same thing.

    The same with your second comment too. I hope you realize that my program s executing the way it is intended to. So, at least for that reason, they are not wrong syntactically

    ReplyDelete
  9. Hi Geetha ,
    Really your tutorail helps me lot..
    I'm new to the android , I have the EditText followed by save button in (Settings.java) when click on save button the text has to be saved and the same thing has to be retrieved when click on send button (send.java).How can i do dat.Pls help me..

    ReplyDelete
  10. hi Geeta, yout docementation is good and also i used your code its works fine, and i need one thing how can i retrieve the stored EditText from DB, can u privide the sample code for it...

    ReplyDelete
  11. Hi Geetha , here is my code i'm unable to retreive the data from the DB can u help me please.....
    http://pastebin.com/8vcKcwwE
    http://pastebin.com/kHhp1bP6
    http://pastebin.com/q9UAKrUJ

    ReplyDelete
  12. Hai Geetha

    I am new to android this example easily explain the db concepts in Android

    Can you explain how to open the new page on button clicking

    ReplyDelete
  13. It is really short and sweet tutorial. It really helps me a lot.
    Keep posting

    ReplyDelete
  14. hi sai,
    how can i create sqlite database in my android first time?
    u r example explains about existing database.

    ReplyDelete
  15. hi sai,
    how can i create sqlite database in my android first time?
    u r example explains about existing database.

    ReplyDelete
  16. It is really Appreciable Geetha, this Blog is really good work put up by you for sharing. Also i would like to thank you for this.

    ReplyDelete
  17. HELLO MADAM,
    THANK-YOU FOR PROVIDING
    PERFECT DATABASE EXAMPLE
    FROM PAST SEVERAL DAYS I
    WAS TRYING TO WORK AROUND
    DATABASE & ANDROID,BUT
    WAS GETTING CONFUSED,UNTIL I FOUND YOUR HELPFUL BLOG
    RAHUL SHAH(PUNE)

    ReplyDelete
  18. hai Geetha, Thanks .Ur tutorial was really helpful. Its working for me.
    Now please explain how can we create a new sqlite database.Pls explain where the database is stored physically .

    ReplyDelete
  19. Hi Geetha

    In my program i want to enter a value in an edit box,and on clicking the submit button this value should be stored in the database.how can i do this.expecting a fast reply.thanks

    ReplyDelete
  20. Hi Sai, I followed ur tutorial,which looks very useful.but when i did the same code in a new project it is not running even though there is no errors.how can i solve this

    ReplyDelete
  21. Hi i am very new to the database, Can you please tell me where will the database be stored once its created. I couldnot find where its created. Please help.!!But it works fine

    ReplyDelete
  22. How to insert data into database after retrieving it from edittext.

    ReplyDelete
  23. how to insert data from edittext into database

    ReplyDelete
  24. thaks for your tutorial miss...

    ReplyDelete
  25. thanks for your tutorial miss...

    ReplyDelete
  26. Hi Anon - who asked about edit text - you would need to extract the text from an EditText Dialog using its getTExt() method - store it in a local variable - and construct a dynamic SQL query using those variable for insertion into the DB

    ReplyDelete
  27. Hi HArish,

    The database is created in the specific application folder. In my example, it is in /data/data/com.collabera.labs.sai.db/databases/myFriendsDb
    To view this in Eclipse, you must view through the DDMS perspective

    ReplyDelete
  28. Best and easiest guide I have seen yet, thank you thank you thank you!

    ReplyDelete
  29. hi sai geetha, ur tutorials are very helpfull bt im nt able to solve a bug while retriving a particular record in database. the code is below plzzz help.


    Cursor c=Main.db.rawQuery("SELECT * FROM details", null);

    if (c != null ) {
    c.moveToFirst();
    do {
    String firstName = c.getString(0);
    String pass = c.getString(1);



    if(firstName.equals("harish")){
    Intent welcome = new Intent();
    welcome.setClass(Main.this, Welcome.class);
    startActivity(welcome);

    }


    }while (c.moveToNext());
    }

    }

    } );

    ReplyDelete
  30. Hello Sai Geetha,

    First, thank you for a simple and all-covered SQLite tutorial!

    I would like to take this learning to next level by creating a small app where I can do all this dynamically, except for DB creation - which I want do it during onCreate().

    My requirements: Separate UI
    1. To insert records
    2. To viewing DB records (simple first 10 records)
    3. To delete DB record/s (simple - accept first, last name and age and delete) .

    Can you please suggest the approach to go about? I am asking w.r.t. design perspective (and not code!).

    ReplyDelete
  31. hai geetha your tutorials are very helpful to me .thanks for you................ARUN,HYD

    ReplyDelete
  32. hai geetha please explain this meaning:

    " where Age > 10 LIMIT 5"

    ReplyDelete
  33. Hi Abg,

    This is a way of paginating the results. If the number of records with AGE > 10 are large in number, this is going to get you only 5 records at a time.

    ReplyDelete
  34. Hello mam,
    I am Android beginner and I want to store List in database my list is hardcoded. and on the click event the list would be store in DB and after clicking on button "OK" I wan to see my selected list. Please help.

    ReplyDelete
  35. Hello Mam,
    I use dbhelper class to store data in sqlite database.when system is rebooting I want to access these data without opening my project??I want to use APpservice class??so how can I use these data in Appservice/wakefullIntentService class.Is there any concept like jdbc?? Mam can u help me ???

    ReplyDelete
  36. mam How should I connect to jdbc using sqlite.

    Class.forName("android.database.sqlite.JDBC");
    Connection conn =
    DriverManager.getConnection("jdbc:sqlite:Eventsdb","","");
    Statement stat = conn.createStatement();
    System.out.println("conn success");
    conn.close();
    System.out.println("conn close");


    when I am connecting using above code I got below error

    02-28 13:07:48.514: WARN/System.err(281): java.lang.ClassNotFoundException: android.database.sqlite.JDBC
    02-28 13:07:48.533: WARN/System.err(281): at java.lang.Class.classForName(Native Method)
    02-28 13:07:48.533: WARN/System.err(281): at java.lang.Class.forName(Class.java:235)
    02-28 13:07:48.543: WARN/System.err(281): at java.lang.Class.forName(Class.java:182)
    02-28 13:07:48.543: WARN/System.err(281): at com.gallait.AlarmManager.AppService.doWakefulWork(AppService.java:38)
    02-28 13:07:48.556: WARN/System.err(281): at com.gallait.AlarmManager.WakefulIntentService.onHandleIntent(WakefulIntentService.java:81)
    02-28 13:07:48.563: WARN/System.err(281): at android.app.IntentService$ServiceHandler.handleMessage(IntentService.java:59)
    02-28 13:07:48.573: WARN/System.err(281): at android.os.Handler.dispatchMessage(Handler.java:99)
    02-28 13:07:48.573: WARN/System.err(281): at android.os.Looper.loop(Looper.java:123)
    02-28 13:07:48.573: WARN/System.err(281): at android.os.HandlerThread.run(HandlerThread.java:60)
    02-28 13:07:48.583: WARN/System.err(281): Caused by: java.lang.NoClassDefFoundError: android.database.sqlite.JDBC

    please help me out from this error.

    ReplyDelete
  37. Nice DataBase Tutorial..!

    [Divyaraj Rana]
    [Ahmedabad,India]
    [9998510918]

    ReplyDelete
  38. Madam, is it possible to add two tables using foreign key constraint in sqlite.Kindly give one example for that

    ReplyDelete
  39. Hi,
    I copied and paste the code but it is giving run time error.Please tell me why it could be and if there any additional work to be done..

    ReplyDelete
  40. how can i set the user value in the sqlite database using android

    ReplyDelete
  41. db.execSQL("CREATE TABLE IF NOT EXISTS " + DeviceType + "(Id INTEGER PRIMARY KEY AUTOINCREMENT," + "DeviceTypeName VARCHAR);");
    db.execSQL("CREATE TABLE IF NOT EXISTS " + AlertType + "(Id INTEGER PRIMARY KEY AUTOINCREMENT," + "AlertName VARCHAR);");
    db.execSQL("CREATE TABLE IF NOT EXISTS " + Alert + "(Id INTEGER PRIMARY KEY AUTOINCREMENT," + "AlertTypeId Int(3)," + "AlertTypeName VARCHAR);");
    db.execSQL("CREATE TABLE IF NOT EXISTS " + Device + "(Id INTEGER PRIMARY KEY AUTOINCREMENT," + "DeviceTypeId Int(3)," + "DeviceTypeName VARCHAR," + "DeviceName VARCHAR," + "MacAddress VARCHAR);");
    db.execSQL("CREATE TABLE IF NOT EXISTS " + Configuration + "(Id INTEGER PRIMARY KEY AUTOINCREMENT," + "DeviceId Int(3)," + "DeviceName VARCHAR," + "AlertId Int(3)," + "AlertTypeName VARCHAR," + "ScanDuration INT(3)," + "MailId VARCHAR);");


    in the above code the foreign key is not genreted pls solve the problem

    ReplyDelete
  42. Hello Sai,
    Works fine in emulator but creating the query forces app closed on HTC Desire. Is there something I am doing wrong which is obvious?
    Thanks

    Kevin

    ReplyDelete
  43. Hello Sai Geetha.

    this blog really help me alot.
    can you please tell me.
    from we get resource to learn java for android

    ReplyDelete
  44. hi ma'm...nice one

    i am beginner for android ......
    i want to know how do? add/edit/delete to database using php/mysql......android emulator must show all this button..... once we click button .....it must be work how to do that? ......i try many way ....but i could not do that.....pls help me .....

    thank you .....

    ReplyDelete
  45. Thanks for ur nice blog and it really helpful for getting idea.
    If you have also blog for android app plz give link of all that.
    Thanks.

    ReplyDelete
  46. can you help me on this?
    http://stackoverflow.com/questions/6532303/problem-to-load-swf-file-in-android

    ReplyDelete
  47. Hi Geetha,
    I would like to know how and in what format do I store image in SQLite?

    Thanks

    ReplyDelete
  48. Hi Sai Geetha,

    Thanks for posting more useful android content for learning.

    I have followed the same as you had done in the above blog for SQLite data base creation and querying the data in order.
    But i am unable to get the data in order even after using ORDER BY also. can you please let me know any other things i need to maintain to get the ordered data from the tables.
    It is very helpful for me if you provide some examples on this topic.

    Thanks in andvance.

    Sathish.

    ReplyDelete
  49. i try to execute the sample code. i face error.
    is any problem by using ListActivity

    ReplyDelete
  50. nyc blog.. itz very help for db .but i have one problem data not inserted to edit box on submit button..can any one help

    ReplyDelete
    Replies
    1. public void addRow(String rowStringOne, String rowStringTwo,String rowStringthree,
      String rowStringfour,String rowStringfive)
      {
      // this is a key value pair holder used by android's SQLite functions
      ContentValues values = new ContentValues();
      values.put(TABLE_ROW_ONE, rowStringOne);
      values.put(TABLE_ROW_TWO, rowStringTwo);
      values.put(TABLE_ROW_THREE, rowStringthree);
      values.put(TABLE_ROW_FOUR, rowStringfour);
      values.put(TABLE_ROW_FIVE, rowStringfive);
      db.insert(TABLE_NAME, null, values);


      // ask the database object to insert the new data
      try{

      // System.out.println(values);
      }
      catch(Exception e)
      {
      Log.e("DB ERROR", e.toString());
      e.printStackTrace();
      }
      }

      Delete
  51. This is very use full for beginner,
    Thanks

    ReplyDelete
  52. hi i want a sqlite query using query() method to retrive a rows from table using multiple where clause using "?"

    for eg:i want to retrive rows based on the string in 1 of the column name and also according to the category at the same time

    ReplyDelete
  53. hi geetha :
    Im FACING a prob with "like" statement in sqlite.. since couple of days..
    My code is char a[]=String.toCharArray();
    Cursor c2=db2.query("addingjobsdetails",column,"Jobname"+"like"+"'%a[0]'",null,null,null,null);
    HERE a[0]..is as per user ..eg: T,G,J..etc.
    Could u plz explain this..

    ReplyDelete
  54. Hi Sai... your blogs are amazing...

    I am new to Android and have a question... What is a context? And, more importantly, why at all is it required to be passes?

    Krishna Kant Ojha

    ReplyDelete
  55. thaaaaaaaaaaanks very simple code I like :)))))

    ReplyDelete
  56. mam, your blogs always rock, mam its too easy to understand. mam please put some another example on some advance issues like GPS, Sensors, wi-fi and Blue-tooth.

    ReplyDelete
  57. mam, i need a Database which contains Images and the same want to copy(in Application) and display in my application... i tried but while opening database i am getting error code=14. please help me to solve this problem.....

    ReplyDelete
  58. Is it possible this example to work with a database created with sqlite and in the assets folder ?

    ReplyDelete
    Replies
    1. Ok i think your answer is in here http://saigeethamn.blogspot.pt/search?updated-max=2011-02-18T15:44:00%2B05:30&max-results=2&start=10&by-date=false

      Thanks

      Delete
  59. two thumbs up, thanks! a lot, healed me heaps.

    ReplyDelete
  60. I like your post and also inspired to arrive daily to get new latest and new technique of codding part..Thanks

    ---------------------
    IT services Orange County

    ReplyDelete
  61. Hello Mam,
    I am developing an application in which i have use BroadCastReceiver Class to receive incoming number when someone call to me, now i will get the incoming number through onReceive() method. Now i want to compare the incoming number with records of my database table inwhich i have allready save the phonbook contact......but i have problem to call database in my BroadCastReceiver class.
    In BroadCastReceiver Class when i make object of DBAdapter Class and make cursor to get data one by one it throws nullpointerException.

    Please mam help me..............i m waiting for your reply .........

    Thank you in andvance............

    ReplyDelete
  62. What a nice tutorial is this REALLY !!

    Thanks a lot dear!!

    ReplyDelete
  63. Thanks a lot, it saved me from a lot of complex code that i used earlier

    ReplyDelete
  64. Thanks buddy for such a nice tutorial.................

    ReplyDelete
  65. heyyy

    can u please provide me an example of panning in android, i want my canvas to move from left to right automatically without any click.

    please help asap

    ReplyDelete
  66. hi
    thanks for blog
    how can we see the data stored in file explorer

    ReplyDelete
  67. Hi,
    Really useful this tutorial.

    thanks a lot.!!

    ReplyDelete
  68. how can i insert table in a DATABASE

    ReplyDelete
  69. how can i insert image in a table in the DATABASE

    ReplyDelete
  70. Hello Mam i want to know one thing.
    This MODE_PRIVATE will work ,if i create my db in sdcard.
    means other app can access it if db is in sdcard or not.

    ReplyDelete
  71. how to store and retrive music file from sqlite database?

    ReplyDelete
  72. This comment has been removed by a blog administrator.

    ReplyDelete
  73. Can i get your help related to Internet Data usage in android??

    ReplyDelete
  74. Thank you Sai Geetha. This one of the great ans simple Tutorial. But a problem with this type of method is if you want to add data say 100 nos then your code look shabby and it will be very difficult tot enter the data. You can use another method by which we can enter the data using a Database Manager and later attach it to the Application. A simple tutorial with source code is given in the following link
    http://android-helper4u.blogspot.com/2013/03/d-databse-and-spinner-tutorial.html

    ReplyDelete
  75. Another simple tutorial with source code
    http://android-helper4u.blogspot.com/2013/03/d-databse-and-spinner-tutorial.html

    ReplyDelete
  76. Hai sai mam,
    I am a fresher.i want one program.just having two edit text and one button.In first edit text we entered the date and the second edit text just enter one name after that click the ok button it will be stored in database.
    Then another one edittext and one button in the same layout.In this edittext enter the date and click the search button which fetch the match result from that date and display the result below the same activity in list view manner.please send my mail id deesh003@gmail.com
    Regards
    Satheesh.R

    ReplyDelete
  77. hi...
    i m new for android so...pls help ,me
    if i create table ...and type table name then it generate error like this....
    sampleDB.execSQL("CREATE TABLE IF NOT EXISTS " +
    tab +
    " (LastName VARCHAR, FirstName VARCHAR," +
    " Country VARCHAR, Age INT(3));");

    ReplyDelete
  78. hi mam..im fresher to android..wil you send me complete sqlite database example to my mail id
    sandeeptadka@gmail.com

    ReplyDelete
  79. Hi Geetha your tutorials are very clear....

    ReplyDelete
  80. Hi, i created a sqlite database in my android application.
    This database contains a table of image, i want in this application to get the image from a database then to put it into a button
    I created a method, but it doesn't work, i don't know why exactly!!
    here's a method, please hepl me to find the error
    public String getImage(int id)//afficher une image
    {
    database = DBHelper.getReadableDatabase(); // open database to perform some operation

    Cursor c = database.rawQuery("SELECT " + COL_ADRESS_IMAGE + "FROM "
    + TABLE_IMAGE + "WHERE id_image IN (SELECT " + COL_IMAGE_CATEGORY + "FROM "
    + TABLE_CATEGORY + "WHERE " + COL_ID_CATEGORY + "= '" + id +"')",null );

    if(c.moveToFirst())
    { //Edited based on suggestion from SAM
    String strCatName = c.getString(c.getColumnIndex(COL_ADRESS_IMAGE ));
    c.close();
    database.close();
    return strCatName;
    }
    else
    {
    return "";
    }

    ReplyDelete
  81. i created a database and stored a name .now i want to display that name in my user page. how to fetch that name in a text View? reply ASAP

    ReplyDelete