SQLite and ListView

Basic Tutorials concerning: GUI, Views, Activites, XML, Layouts, Intents, ...

SQLite and ListView

Postby eberk_dankil » Wed Mar 17, 2010 7:31 am

I'm trying to extract data from a SQLite to ListView. Initially I want all the Records displayed but I want to limit it down based on certain restrictions like a boolean field in the database.
Once Poster
Once Poster
Posts: 1
Joined: Fri Mar 12, 2010 8:08 pm


Postby waynesilver » Wed Mar 17, 2010 6:09 pm

I did something very similar. I have a database table with 60,000 - 70,000 rows and 15 columns in each row in the main table. The user is going to select one of those rows and grab all the data there as well as data in other tables. To help the user get to their data faster, I offer them choices and options which are displayed on drop down lists populated by the database. I then use auto-complete lists for the final selection.

I defined my list in the activity as:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. String[] namelistArray = null;
  2. Cursor myCursor = null;
Parsed in 0.011 seconds, using GeSHi

This gives me a pointer into an array of strings without having to know how big the array is, because when the program initializes, the user will choose options which affects the number of elements in the list. All I know is the list will be somewhere in the neighborhood of 900 to 2000 elements long give or take. When I do the extraction of the data from the database, it looks like this:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.         public static String[] loadMyList(Cursor c) {
  2.                 String temp=null;
  3.                 String[] results = new String[c.getCount()];
  5. // Arg1 and Arg2 are defined by the user
  6. // SELECT name FROM addresstable WHERE country = Arg1 AND state = Arg2 ORDER by name
  8.                 c.moveToFirst();
  10.                 //... For loop using index.
  11.                 for (int i = 0; i < c.getCount(); i++) {
  12.                    temp=(c.getString(c.getColumnIndex("name")));
  13.                    c.move(1);
  14.                    results[i]= new String (temp);
  15.                          if(DEBUGING){android.util.Log.v("LOADLIST", "Array load: " + i + " " + results[i]);};
  17.                 }      
  19.                 return results;
  21.         }
Parsed in 0.012 seconds, using GeSHi

Back in the onResume() method, I do the list population call, because I have all the user input I need. I then connect the list with the auto-complete widget and put the screen up, using the following code:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.                          namedb=(new NameListSQLiteHelper(this)).getReadableDatabase();
  3.                          myCursor=namedb.getAll(namedb, prefs.getString("COUNTRY", "blank"),
  4.                                          prefs.getString("STATE", "blank"));
  5.                          startManagingCursor(myCursor);
  6.                          namelistArray=loadMyList(myCursor);
  7.                          textView = (AutoCompleteTextView) findViewById(R.id.autocomplete_name);
  8.                          ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, R.layout.name_list, namelistArray);
  9.                          textView.setAdapter(adapter);
  10.                          textView.setOnItemClickListener(this);
Parsed in 0.011 seconds, using GeSHi

This might not be the most efficient way of loading a list, but it worked. You need to look at the SQLite Tutorial written by plusminus: http://www.anddev.org/working_with_the_sqlite-database_-_cursors-t319.html and there is some good information in the book "Android Programming Tutorials" published by Commonsware. The example is pretty lame, but it does teach you about how to handle databases. I think the book and the tutorial will get you going.

I hope this helped and I'll watch this thread for replies.
Posts: 28
Joined: Mon Jan 04, 2010 9:01 pm
Location: Seattle


Return to Novice Tutorials

Who is online

Users browsing this forum: No registered users and 2 guests