Android - ListActivity query DB and display result

This tutorial will introduce following android development knowledge:

package com.example.mydbtest;

import java.util.ArrayList;

import android.app.ListActivity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.os.Bundle;
import android.widget.ArrayAdapter;

public class DBListActivity extends ListActivity {
    /** Called when the activity is first created. */
	  private final String MY_DATABASE_NAME = "mysqliteDB";  
	     
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        ArrayList results = new ArrayList();
        // -- SQLiteOpenHelper dbHelper = new DefaultDBHelper(this, MY_DATABASE_NAME, null, 1);
        SQLiteDatabase myDB = this.openOrCreateDatabase(MY_DATABASE_NAME, SQLiteDatabase.OPEN_READWRITE, null);
        try { 
             /* Create the Database (no Errors if it already exists) */
			// myDB = dbHelper.getWritableDatabase();
			// dbHelper.onCreate(myDB);
        	myDB.execSQL("CREATE TABLE IF NOT EXISTS " 
                    + DefaultDBHelper.MY_DATABASE_TABLE 
                    + " (LastName VARCHAR, FirstName VARCHAR," 
                    + " Country VARCHAR, Age INT(3));");
        	
        	myDB.execSQL("INSERT INTO " 
                    + DefaultDBHelper.MY_DATABASE_TABLE 
                    + " (LastName, FirstName, Country, Age)" 
                    + " VALUES ('Gramlich', 'Nicolas', 'Germany', 20);"); 
        	myDB.execSQL("INSERT INTO " 
                    + DefaultDBHelper.MY_DATABASE_TABLE  
                    + " (LastName, FirstName, Country, Age)" 
                    + " VALUES ('Doe', 'John', 'US', 34);");
        	
			// -- openOrCreateDatabase(name, mode, factory)
			// myDB = dbHelper.getReadableDatabase();
        	Cursor c = myDB.query(DefaultDBHelper.MY_DATABASE_TABLE, null, "Age > 10", null, null, null, null);
            
        	/* Check if our result was valid. */ 
            if (c != null) {
            
            	c.moveToFirst(); // it's very important to do this action otherwise your Cursor object did not get work
            	int firstNameColumn = c.getColumnIndex("FirstName"); 
            	int ageColumn = c.getColumnIndex("Age"); 
                 /* Check if at least one Result was returned. */ 
                 if (c.isFirst()) { 
                      int i = 0; 
                      /* Loop through all Results */ 
                      do { 
                           i++; 
                           String firstName = c.getString(firstNameColumn); 
                           int age = c.getInt(ageColumn); 
                           String ageColumName = c.getColumnName(ageColumn); 
                            
                           /* Add current Entry to results. */ 
                           results.add("" + i + ": " + firstName + " (" + ageColumName + ": " + age + ")"); 
                      } while (c.moveToNext()); 
                 } 
            }
        	
        } catch (SQLiteException e) { 
        } finally { 
             if (myDB != null) 
                  myDB.close(); 
        }
        
        // -- android.R.layout.simple_list_item_1 is object which belong to ListActivity itself
        // -- you only need to add list object in your main layout file
        this.setListAdapter(new ArrayAdapter(this, android.R.layout.simple_list_item_1, results)); 
    }
}

another optional way to create SQLite3 database is to use SQLiteOpenHelper of android

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class DefaultDBHelper extends SQLiteOpenHelper {

	public final static String MY_DATABASE_TABLE = "t_person";
	 
	public DefaultDBHelper(Context context, String name, CursorFactory factory, int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " 
                + MY_DATABASE_TABLE 
                + " (LastName VARCHAR, FirstName VARCHAR," 
                + " Country VARCHAR, Age INT(3));");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w("My DB", "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS notes");
        onCreate(db);
	}
}

the content of layout configuration XML file as following:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
	android:orientation="vertical" android:layout_width="fill_parent"
	android:layout_height="fill_parent">
	<ListView android:id="@id/android:list"
		android:layout_width="fill_parent" android:layout_height="fill_parent"
		android:background="#00FF00" android:layout_weight="1"
		android:drawSelectorOnTop="false">
	</ListView>
	<TextView android:layout_width="fill_parent"
		android:layout_height="wrap_content" android:text="@string/hello" />
</LinearLayout>

Query data using android shell utility tools

Back Home