package com.minich.sqldemo1;

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.util.Log;
import android.widget.ArrayAdapter;
import android.widget.TextView;

public class SQLDemo1Client extends ListActivity 
{
	
	private ArrayList<String> results = new ArrayList<String>();
	private String tableName = DBHelper.tableName;
	private SQLiteDatabase newDB;
    
    @Override
    public void onCreate(Bundle savedInstanceState) 
    {
        super.onCreate(savedInstanceState);
        
        openAndQueryDatabase();	// results is filled with the records gathered by the query
        
        displayResultList();	// results is displayed in the ListView
    }
    
	private void displayResultList() 
	{
		TextView tView = new TextView(this);
        tView.setText("Student Roster");
        getListView().addHeaderView(tView);
        
        setListAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, results));
        getListView().setTextFilterEnabled(true);
	}
	
	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 ORDER BY firstName DESC 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();
        }

	}
    
}