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

x

Friday 18 February 2011

ListView of Data from SQLiteDatabase - Android

This is a next level tutorial where I am mixing two concepts – The ListView concept that is explicitly explained in the ListView Tutorial and the SQLiteDB concept in into own tutorial.

Here I am intending to query a database using a SQLiteDatabase API. The results I obtain are in a Cursor object that I iterate and create an ArrayList that is passed to the ListView. Let us see the steps involved in this exercise:

In the onCreate(…) method I have 2 methods corresponding to the two steps described above:

openAndQueryDatabase();
       
      displayResultList();

Let us see what each of them does:

Here is the first step:

private void openAndQueryDatabase() {
            try {
                  DBHelper dbHelper = new DBHelper(this.getApplicationContext());
                  newDB = dbHelper.getWritableDatabase();
                  Cursor c = newDB.rawQuery("SELECT FirstName, Age FROM " +
                        tableName +
                        " where Age > 10 LIMIT 4", null);

            if (c != null ) {
                  if  (c.moveToFirst()) {
                        do {
                              String firstName = c.getString(c.getColumnIndex("FirstName"));
                              int age = c.getInt(c.getColumnIndex("Age"));
                              results.add("Name: " + firstName + ",Age: " + age);
                        }while (c.moveToNext());
                  }
            }                
            } catch (SQLiteException se ) {
            Log.e(getClass().getSimpleName(), "Could not create or Open the database");
        } finally {
            if (newDB != null)
                  newDB.execSQL("DELETE FROM " + tableName);
                  newDB.close();
        }

      }       

Now, DBHelper is a class I have written extending the SQLiteOpenHelper class. All that it does is create a database by name “sample”, create a table within it and insert values into the table. It does this if the database does not already exist.  The table name is “Resource” and the columns in the table are Lastname, Firstname, Country, Age.

The code for this example can be downloaded here and you can look into the DBHelper code as well (which I do not want to elaborate here)

So, from the DBHelper class we get an open database which we call the newDB. Using this handle, we query the table for values. To keep it simple, while retrieving values I have hard-coded the column names which need not be the case. So I run a rawQuery and get the results into a Cursor.

Next I iterate through the Cursorand populate the results into an ArrayList of Strings “result”.  In the finally block, I not only close the database but before that I delete all the entries inserted into the database by the DBHelper class just to clean up.

Next how do I display the results in a ListView. If you know ListView– how it works it is rather simple. Else you could look at the ListViewTutorial. Here is the method:

      private void displayResultList() {
            TextView tView = new TextView(this);
        tView.setText("This data is retrieved from the database and only 4 " +
                  "of the results are displayed");
        getListView().addHeaderView(tView);
       
        setListAdapter(new ArrayAdapter<String>(this,
                android.R.layout.simple_list_item_1, results));
        getListView().setTextFilterEnabled(true);
           
      }

The only additional bit I have done here is to add a HeaderView (which is using the concept of programming UI explained in another tutorial).

I have written this tutorial based on some of the requests in my blog. Hope this helps.

37 comments:

  1. Hi sai geetha,

    This article helped me alot. If u dont mind, I have one suggestion, in this article provide one more functionality that the user can be able to delete the values from database and list.

    ReplyDelete
  2. hi sai geetha,can u post the code for voice (or)Audio recorder.Is the possible to record audio in Emulato?

    ReplyDelete
  3. Hi Sai,
    I have One question regarding this listview and Database.
    If I have 10000 entries in a Database Tables. Now I want to set these entries to ListView.
    My Approach:
    For this I take a simple custom Adapter class(Base Adapter/SimpleCursorTreeAdapter) and set the result to ListView.But initially loading it takes more time to display in ListView.So,is there any good way to solve this issue.
    Thanks in advance..

    ReplyDelete
    Replies
    1. You can do that using recycle view. Recycle view is same as that of List-View but the major advantage is that it won't load all the entries in the list view at once. As you start scrolling, it will keep loading those lists at the same time.

      Delete
  4. Hi Sai Geetha,
    Thanks for sharing a nice articles.
    My android app is a standalone and requires 7MB preloaded database,but Android application not accepting reading more than 1 MB of SQLite DB,please help me how to overcome this MAXSIZE limitation?I appreciate any of your inputs
    reply to satyamurthy.hyd@gmail.com

    ReplyDelete
  5. hai
    i am sree i need some help for consructing dynamic expandandable list will u pls provide the informaiton

    ReplyDelete
  6. Hi,
    I ma getting Source Not Found Error.How to get rid of it..Please Help..

    ReplyDelete
  7. can this work for retrieving data from mySql database?

    ReplyDelete
  8. hi can u plz help me to do retriving back data from database to current listview in android

    ReplyDelete
  9. hi ,
    i am not able to display the result in listview .can you please tell , i can display it in toast

    ReplyDelete
  10. i retrieve data from database and put it into listview.
    now i want to add edit and delete option to it.
    how i can delete or edit specific record.
    plz help me

    ReplyDelete
  11. Hi Sai Geetha, Can you share your knowledge about creating offline map views in android?

    ReplyDelete
  12. Hi sai.. I have few images which are retrieved from my web service.
    I wanna display these images in grid view in android and wanna create grid view dynamically without using xml page.
    How can i do this??

    Thanks in advance..

    ReplyDelete
  13. Thank u mam. Could u please post how to delete and edit from list and DB.

    ReplyDelete
  14. dis post very well hepled me can you please post how to populate spinner from database it's been a week and ive been stucked a long on ma project

    ReplyDelete
  15. Hai Mam,

    the artical is very helpfull but if u don't mind,please help me to list the values in inth custumlist formate by using sqlite3

    ReplyDelete
    Replies
    1. http://lmgtfy.com/?q=custom+list+format+sqlite+android

      Delete
    2. cannot, because ListView need extends ListActivity

      Delete
  16. Hai Mam ,
    I am working on one App Iwant your help in it,
    I want to list the list or playlist of our youtude account in the android application I am fresher,Please help me

    ReplyDelete
  17. Hello sister,

    today i need to work on android database and searched net a lot for quick sample, but i failed.
    got your post and sample and got so happy, so nice sample

    i pray for your success in future.
    regards

    ReplyDelete
  18. hello madam
    my problem in httpget method in android application?

    my code below:

    public static ArrayList getvideoList(String q) {

    ArrayList fetchchildvideo_Type = new ArrayList();

    String result = "";
    InputStream is = null;
    Log.v("log_tag", "DataQ :::"+q);
    try {
    HttpClient client = new DefaultHttpClient();
    // String getURL = "http://www.google.com";

    String url = "http://gdata.youtube.com/feeds/api/videos?v=2&alt=jsonc&max-results=25&start-index=1&q=";
    String data=url+q;
    Log.v("log_tag", "DataQuestion :::"+data);
    HttpGet get = new HttpGet(data);
    HttpResponse responseGet = client.execute(get);
    HttpEntity resEntityGet = responseGet.getEntity();
    is = resEntityGet.getContent();
    if (resEntityGet != null) {
    // do something with the response
    Log.i("GET RESPONSE", EntityUtils.toString(resEntityGet));
    }

    } catch (Exception e) {
    e.printStackTrace();
    }

    // convert response to string
    try {
    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();
    Log.v("log_tag", "REsultvideolink:::" + result);

    } catch (Exception e) {
    Log.e("log_tag", "Error converting result " + e.toString());
    }
    Log.v("log_tag", " videolink " + result);

    // parse json data
    try {
    JSONObject json_obj = new JSONObject(result);
    JSONObject j_Array = json_obj.getJSONObject("data");
    JSONArray jv_arry=j_Array.getJSONArray("items");
    for (int i = 0; i < jv_arry.length(); i++) {
    All_quiz_list_dto question_dto = new All_quiz_list_dto();
    JSONObject js = jv_arry.getJSONObject(i);
    JSONObject js_thumbnails=js.getJSONObject("thumbnail");
    JSONObject js_player=js.getJSONObject("player");
    JSONObject js_content=js.getJSONObject("content");
    question_dto.quiz_js_id = js.getString("id");
    question_dto.quiz_js_title=js.getString("title");
    question_dto.quiz_js_sqdefault=js_thumbnails.getString("sqDefault");
    question_dto.quiz_js_mobile=js_player.getString("mobile");
    question_dto.quiz_js_1=js_content.getString("1");
    fetchchildvideo_Type.add(question_dto);

    }

    } catch (JSONException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }

    //Log.v("logtag", " database::: " + result);
    return fetchchildvideo_Type;

    }










    error:




    09-15 13:06:31.493: ERROR/log_tag(574): Error converting result java.lang.NullPointerException

    Thanks!!
    Hitesh

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

    ReplyDelete
  20. hey!!
    your example is very good...i just have one doubt..there is another java file also in which the actual class is presents which extends activity??

    ReplyDelete
  21. > SQLiteDB concept in into own tutorial.

    What is "concept in into own" mean? (And many other non-sense phrases here.)

    ReplyDelete
  22. Hai Sai geetha,

    I saved the datas from a form design for my android tablet. Now i am getting the output. but i want to see the data.

    How to see the data whther it is saved or not in TABLET.. Kindly help me...

    ReplyDelete
  23. hi Geetha,

    Thanks for Your great contribution.

    ReplyDelete
  24. Hi Geetha,

    I created a pop up window with the list view on a edittext click,
    now i am storing the values in default as a string. i need to fetch the data from the database and view it in the listview i need your help.

    Regards
    Rajesh.

    ReplyDelete
  25. Thanks for .. But how can i use more then one test from list view
    multiline

    http://blog.swaptechs.com/

    ReplyDelete
  26. how can i delete item from this listview i hav tried a lot but my code has stucked. please help i m new in android thanks in advance

    ReplyDelete
  27. hiii..This is ATISH KUMAR i am new in android i have one problem in creating database....

    my question is: how to save the form value(edittext/textview,checkbox ,radio button) values to database while clicking submit button .

    and how to see the save values from database into view...and also one Edit button is there if i click edit button it should display the whole form with saved values and again if i edit something and press submit its value should be updated to the database.
    please help me....... atishjkumar@gmail.com
    thank you.

    ReplyDelete
  28. Thanks a lot Ma'm.

    ReplyDelete
  29. Hi please always insert image on your post or a demo video

    ReplyDelete
  30. what is 'results' in setListAdapter()

    ReplyDelete
  31. while execute this query i get syntax error

    curser1=DB.rawQuery("SELECT "+ CurrentTime+" FROM "+TbllocationName+" ORDERBY "+CurrentTime,null);

    ReplyDelete