I did something similar to this in a sales tax app some years ago. Trimming away the fat leaves something like:
private static final String STATES_TABLE_CREATE =
"CREATE TABLE " + STATES_TABLE + " ("
+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ KEY_NAME + " TEXT);";
private static final String COUNTIES_TABLE_CREATE =
"CREATE TABLE " + COUNTIES_TABLE + " ("
+ KEY_ID + " INTEGER REFERENCES " + STATES_TABLE + "(" + KEY_ID + "), "
+ KEY_NAME + " TEXT);";
...
class Info
{
public long lStateId;
public String sName;
public Info( long lStateId, String sName )
{
this.lStateId = lStateId;
this.sName = sName;
}
@Override
public String toString()
{
return sName;
}
}
...
ArrayList<Info> states = new ArrayList<Info>(50);
ArrayList<Info> counties = new ArrayList<Info>();
db = dbHelper.getReadableDatabase();
Cursor cursor = db.query(DatabaseHelper.STATES_TABLE, new String[]{DatabaseHelper.KEY_ID, DatabaseHelper.KEY_NAME}, null, null, null, null, DatabaseHelper.KEY_NAME);
if (cursor.moveToFirst())
{
do
{
long lId = cursor.getLong(cursor.getColumnIndex(DatabaseHelper.KEY_ID));
String sName = cursor.getString(cursor.getColumnIndex(DatabaseHelper.KEY_NAME));
states.add(new Info(lId, sName));
} while (cursor.moveToNext());
cursor.close();
}
db.close();
...
@Override
public void onItemSelected( AdapterView<?> parent, View view, int position, long id )
{
Info i = states.get(position);
counties.clear();
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor = db.query(DatabaseHelper.COUNTIES_TABLE,
new String[]{DatabaseHelper.KEY_ID, DatabaseHelper.KEY_NAME},
DatabaseHelper.KEY_ID + " = ?",
new String[]{String.valueOf(i.lStateId)},
null,
null,
DatabaseHelper.KEY_NAME);
if (cursor.moveToFirst())
{
do
{
long lId = cursor.getLong(cursor.getColumnIndex(DatabaseHelper.KEY_ID));
String sName = cursor.getString(cursor.getColumnIndex(DatabaseHelper.KEY_NAME));
counties.add(new Info(lId, sName));
} while (cursor.moveToNext());
cursor.close();
}
if (counties.size() == 0)
counties.add(new Info(-1, "No counties found for the selected state"));
adapterCounties.notifyDataSetChanged();
db.close();
}
This should get you close to what you are after.