Internal Database Example

TestDemoActivity.java



package com.android.testdemo;

import android.app.Activity;
import android.app.ListActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.Spinner;
import android.widget.Toast;

public class TestDemoActivity extends Activity {
private Cursor mCountryCur;
CountryTable conTable;
private StateTable stateTable;
private Cursor mStateCur;
private CityTable cityTable;
private Cursor mCityCur;

private static final String STATE="STATE";
private static final String COUNTRY="COUNTRY";
private static final String CITY="CITY";

private static int backCount=0;

private static String Current=STATE;

private Spinner spnrCountry,spnrState;
private ListView lstCity;

/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
spnrCountry=(Spinner)findViewById(R.id.spnrCountry);
spnrState=(Spinner)findViewById(R.id.spnrState);
lstCity=(ListView)findViewById(R.id.lstCity);
fillCountryList();


spnrCountry.setOnItemSelectedListener(new OnItemSelectedListener() {

@Override
public void onItemSelected(AdapterView<?> arg0, View arg1,
int arg2, long arg3) {
Toast.makeText(TestDemoActivity.this,"List position: "+arg2,Toast.LENGTH_SHORT).show();
fillStateList(arg2+1);

if(lstCity.getChildCount()>1){
lstCity.removeAllViews();
}
}

@Override
public void onNothingSelected(AdapterView<?> arg0) {


}
});

spnrState.setOnItemSelectedListener(new OnItemSelectedListener() {

@Override
public void onItemSelected(AdapterView<?> arg0, View arg1,
int arg2, long arg3) {
fillCityList(arg2+1);

}

@Override
public void onNothingSelected(AdapterView<?> arg0) {
Toast.makeText(TestDemoActivity.this,"Nothing",Toast.LENGTH_SHORT).show();

}
});

}

private void fillCountryList() {
fetchCountryData();
String[] from = new String[] { CountryTable.COUNTRY_NAME };
int[] to = new int[] {android.R.id.text1};
SimpleCursorAdapter adapter = new SimpleCursorAdapter(this,
android.R.layout.simple_spinner_item, mCountryCur, from, to);
adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
spnrCountry.setAdapter(adapter);
Current=COUNTRY;
}

private void fillStateList(int pos) {
fetchStateData(pos);
String[] from = new String[] { StateTable.STATE_NAME,StateTable._ID };
int[] to = new int[] {android.R.id.text1,};
SimpleCursorAdapter adapter = new SimpleCursorAdapter(this,
android.R.layout.simple_spinner_item, mStateCur, from, to);

adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
spnrState.setAdapter(adapter);
Current=STATE;
backCount=0;
}

private void fillCityList(int pos) {
fetchCityData(pos);
String[] from = new String[] { CityTable.CITY_NAME };
int[] to = new int[] { android.R.id.text1 };
SimpleCursorAdapter adapter = new SimpleCursorAdapter(this,
android.R.layout.simple_list_item_1, mCityCur, from, to);
lstCity.setAdapter(adapter);
}

public void fetchCountryData() {
try {
conTable = new CountryTable(this);
/*conTable.DeleteAllData();
conTable.insert(1, "India");
conTable.insert(2, "U.S.A");
conTable.insert(3, "China");*/

mCountryCur = conTable.fetAllData();
for (int i = 0; i < mCountryCur.getCount(); i++) {
mCountryCur.moveToPosition(i);
Log.i("TEST",
"Country: "
+ mCountryCur.getString(mCountryCur
.getColumnIndex(CountryTable._ID))
+ " : "
+ mCountryCur.getString(mCountryCur
.getColumnIndex(CountryTable.COUNTRY_NAME)));
}
} catch (Exception e) {
e.printStackTrace();
}
}

public void fetchStateData(int pos) {
try {

stateTable = new StateTable(this);
stateTable.DeleteAllData();
stateTable.insert(1, "Gujarat", 1);
stateTable.insert(2, "Maharashtra", 1);
stateTable.insert(3, "Rajasthan", 1);

mStateCur = stateTable.fetchData(pos);
startManagingCursor(mStateCur);
for (int i = 0; i < mStateCur.getCount(); i++) {
mStateCur.moveToPosition(i);
Log.i("TEST",
"state: "
+ mStateCur.getString(mStateCur
.getColumnIndex(StateTable._ID))
+ " : "
+ mStateCur.getString(mStateCur
.getColumnIndex(StateTable.STATE_NAME)));
}
} catch (Exception e) {
e.printStackTrace();
}
}

public void fetchCityData(int pos) {
try {
cityTable = new CityTable(this);
//cityTable.DeleteAllData();
cityTable.insert(10, "Udaipur", 3);
cityTable.insert(11, "Jaipur", 3);
cityTable.insert(12, "Vilasa", 3);

mCityCur = cityTable.fetchData(pos);
startManagingCursor(mCityCur);
for (int i = 0; i < mCityCur.getCount(); i++) {
mCityCur.moveToPosition(i);
Log.i("TEST",
"City: "
+ mCityCur.getString(mCityCur
.getColumnIndex(CountryTable._ID))
+ " : "
+ mCityCur.getString(mCityCur
.getColumnIndex(CityTable.CITY_NAME)));
}
} catch (Exception e) {
e.printStackTrace();
}
}

/*@Override
protected void onListItemClick(ListView l, View v, int pos, long id) {
if(Current.equals(COUNTRY)){
fillStateList(pos+1);
}else if(Current.equals(STATE)){
fillCityList(pos+1);
}else if(Current.equals(CITY)){
//fillCityList();
}

Toast.makeText(this,"List position: "+pos,Toast.LENGTH_SHORT).show();
super.onListItemClick(l, v, pos, id);
}*/

@Override
public void onBackPressed(){
finish();

}
}


CountryTable.java



package com.android.testdemo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class CountryTable extends SQLiteOpenHelper {
public static final String DATABASE_NAME="testdemo8";
public static final String TABLE_NAME="country3";

public static final String _ID="_id";
public static final String COUNTRY_NAME="Name";

private SQLiteDatabase mDb;

private static final String CREATE_TABLE="create table if not exists "+TABLE_NAME+" ("+_ID +" integer primary key,"+COUNTRY_NAME +" text)";

public CountryTable(Context context) {
super(context, DATABASE_NAME, null, 1);

}

@Override
public void onCreate(SQLiteDatabase db) {
try{
db.execSQL(CREATE_TABLE);
}catch (Exception e) {
e.printStackTrace();
}

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub

}

public void insert(int id,String name){
try{
mDb=getWritableDatabase();
ContentValues val= new ContentValues();
val.put(_ID, id);
val.put(COUNTRY_NAME, name);
mDb.insert(TABLE_NAME, null, val);
}catch (Exception e) {
e.printStackTrace();

// TODO: handle exception
}finally{
mDb.close();

}
}

public Cursor fetAllData(){
Cursor c=null;
try{
mDb=getReadableDatabase();
String query = "select * from "+ TABLE_NAME;
//c=mDb.query(TABLE_NAME, new String[]{_ID,COUNTRY_NAME},null, null,null,null,null);
c=mDb.rawQuery(query, null);


}catch (Exception e) {
e.printStackTrace();
}finally{
// mDb.close();
}
return c;
}

public void DeleteAllData(){

try{
mDb=getReadableDatabase();
String query = "delete  from "+ TABLE_NAME;
mDb.execSQL(query);

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

}

}

}

StateTable.java

package com.android.testdemo;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

public class StateTable extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "testdemo10";

public static final String TABLE_NAME = "State2";

public static final String _ID = "_id";

public static final String STATE_NAME = "Name";

public static final String COUNTRY_ID = "CountryId";

private SQLiteDatabase mDb;

private static final String CREATE_TABLE = "create table if not exists "

+ TABLE_NAME + " (" + _ID + " integer primary key,"

+ STATE_NAME + " text," + COUNTRY_ID + " INTEGER)";

public StateTable(Context context) {

super(context, DATABASE_NAME, null, 1);

}

@Override

public void onCreate(SQLiteDatabase db) {

try {

db.execSQL(CREATE_TABLE);

} catch (Exception e) {

e.printStackTrace();

}

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

// TODO Auto-generated method stub

}

public void insert(int id,String name, int Con_id) {

try {

mDb = getWritableDatabase();

ContentValues val = new ContentValues();

val.put(_ID, id);

val.put(STATE_NAME, name);

val.put(COUNTRY_ID, Con_id);

long i=mDb.insert(TABLE_NAME, null, val);

} catch (Exception e) {

e.printStackTrace();

// TODO: handle exception

} finally {

mDb.close();

}

}

public Cursor fetchData(int conid) {

Cursor c = null;

try {

mDb = getReadableDatabase();

String query = "select * from " + TABLE_NAME + " where "

+ COUNTRY_ID + " = " + conid;

c = mDb.rawQuery(query, null);

c.moveToFirst();

} catch (Exception e) {

e.printStackTrace();

} finally {

mDb.close();

}

return c;

}

public void DeleteAllData() {

try {

mDb = getReadableDatabase();

String query = "delete  from " + TABLE_NAME;

mDb.execSQL(query);

} catch (Exception e) {

e.printStackTrace();

} finally {

}

}

}

CityTable.java

package com.android.testdemo;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

public class CityTable extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "testdemo11";

public static final String TABLE_NAME = "City2";

public static final String _ID = "_id";

public static final String CITY_NAME = "Name";

public static final String STATE_ID = "StateId";

private SQLiteDatabase mDb;

private static final String CREATE_TABLE = "create table if not exists "

+ TABLE_NAME + " (" + _ID + " integer primary key,"

+ CITY_NAME + " text," + STATE_ID + " INTEGER)";

public CityTable(Context context) {

super(context, DATABASE_NAME, null, 1);

}

@Override

public void onCreate(SQLiteDatabase db) {

try {

db.execSQL(CREATE_TABLE);

} catch (Exception e) {

e.printStackTrace();

}

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

// TODO Auto-generated method stub

}

public void insert(int id,String name, int Con_id) {

try {

mDb = getWritableDatabase();

ContentValues val = new ContentValues();

val.put(_ID, id);

val.put(CITY_NAME, name);

val.put(STATE_ID, Con_id);

mDb.insert(TABLE_NAME, null, val);

} catch (Exception e) {

e.printStackTrace();

// TODO: handle exception

} finally {

mDb.close();

}

}

public Cursor fetchData(int conid) {

Cursor c = null;

try {

mDb = getReadableDatabase();

String query = "select * from " + TABLE_NAME + " where "

+ STATE_ID + " = " + conid;

c = mDb.rawQuery(query, null);

c.moveToFirst();

} catch (Exception e) {

e.printStackTrace();

} finally {

mDb.close();

}

return c;

}

public void DeleteAllData() {

try {

mDb = getReadableDatabase();

String query = "delete  from " + TABLE_NAME;

mDb.execSQL(query);

} catch (Exception e) {

e.printStackTrace();

} finally {

}

}

}

main.xml

<?xml version="1.0" encoding="utf-8"?>

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

    android:layout_width="fill_parent"

    android:layout_height="fill_parent"

    android:orientation="vertical" >

    <Spinner

        android:id="@+id/spnrCountry"

        android:layout_width="fill_parent"

        android:layout_height="wrap_content" />

     <Spinner

        android:id="@+id/spnrState"

        android:layout_width="fill_parent"

        android:layout_height="wrap_content" />

    

 <ListView

        android:id="@+id/lstCity"

        android:layout_width="fill_parent"

        android:layout_height="wrap_content" />

</LinearLayout>