populating spinner with external Sqlite database

Question

I have three spinners. The third spinner is dependent on the second one and the second spinner is dependent on the first spinner. the fields in the spinner is being populated by an external sqlite database. Now the problem is if i have a huge database say with thousands of records do i have to write conditions for all thousand records so that when i click on the first position of the first spinner the resultant values should be displayed on the second spinner and so on the third spinner.

Answer 1

//This is my helper class

   public class CarHelper extends SQLiteOpenHelper{

private static final String DATABASE_PATH= "//data//data//com.example.dropdown//databases//";
private static final String DATABASE_NAME= "car_Database.sqlite";
private static final int SCHEMA_VERSION =1;
public static final String COLUMN_ID = "_id";
public static final String TABLE_NAME = "Make";
public static final String COLUMN_TITLE = "car_make";

public static final String TABLE_MODEL = "Model";
public static final String COLUMN_MODEL = "car_model";

public static final String TABLE_VER = "Version";
public static final String COLUMN_VER = "car_ver";


public SQLiteDatabase dbsqlite,db1;
private final Context myContext;

public CarHelper(Context context) {
    super(context, DATABASE_NAME, null, SCHEMA_VERSION);
    this.myContext = context;
    // TODO Auto-generated constructor stub
}

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub

}

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

}

public void createDatabase()
{
    createDB();
}

private void createDB()
{
    boolean dbExist = DBExists();
    if(!dbExist)
    {
        this.getReadableDatabase();
        copyDBFromResource();
    }
}

private boolean DBExists()
{
    SQLiteDatabase db = null;
    try
    {
        String databasePath = DATABASE_PATH + DATABASE_NAME;
        db = SQLiteDatabase.openDatabase(databasePath,null, SQLiteDatabase.OPEN_READWRITE);
        db.setLocale(Locale.getDefault());
        db.setLockingEnabled(true);
        db.setVersion(1);
    }
    catch(SQLiteException e)
    {
        Log.e("AqlHelper","database not found");
    }
    if(db!=null)
    {
        db.close();
    }
    return db!=null ? true : false;

}

private void copyDBFromResource()
{
    InputStream ip = null;
    OutputStream op = null;
    String dbFilePath = DATABASE_PATH + DATABASE_NAME;
    try
    {
        ip= myContext.getAssets().open(DATABASE_NAME);
        op = new FileOutputStream(dbFilePath);
        byte[] buffer = new byte[1024];
        int length;
        while((length = ip.read(buffer))>0)
        {
            op.write(buffer, 0, length);
        }
        op.flush();
        op.close();
        ip.close();
    }
    catch(IOException e)
    {
        throw new Error("Problemcopying database from Resource");
    }
}

public void openDataBase() throws SQLException
{
    String myPath = DATABASE_PATH + DATABASE_NAME;
    dbsqlite = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
}
@Override
public synchronized void close()
{
    if(dbsqlite != null)
    {
        dbsqlite.close();
    }
    super.close();
}

public Cursor getCursor()
{
    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
    queryBuilder.setTables(TABLE_NAME);
    String[] asColumnsToReturn = new String[] {COLUMN_ID,COLUMN_TITLE};
    Cursor mCursor = queryBuilder.query(dbsqlite, asColumnsToReturn, null, null, null, null, "car_make ASC");
    return mCursor;
}


public void m_pop(String st){
    String a = st;
    dbsqlite.execSQL("delete from temp");
    dbsqlite.execSQL("insert into temp(name) select car_model from Model where make_id="+a); 


}
public List<String> getAllLabels(){
    List<String> labels = new ArrayList<String>();
    String selectQuery = "select name from temp";
    dbsqlite = this.getReadableDatabase();
    Cursor cursor = dbsqlite.rawQuery(selectQuery, null);


    if (cursor.moveToFirst()) {
        do {
            labels.add(cursor.getString(cursor.getColumnIndex("name")));


        } while (cursor.moveToNext());
    }

    cursor.close();
    dbsqlite.close();

    return labels;
}





public void v_pop(String st){
    String a = st;
    dbsqlite.execSQL("delete from temp");
    dbsqlite.execSQL("insert into temp(name) select car_ver from Version where _id="+a); 


}
public List<String> getAllLabel(){
    List<String> label = new ArrayList<String>();

    String selectQuery = "select name from temp";
    dbsqlite = this.getReadableDatabase();
    Cursor cursor = dbsqlite.rawQuery(selectQuery, null);


    if (cursor.moveToFirst()) {
        do {
            label.add(cursor.getString(cursor.getColumnIndex("name")));


        } while (cursor.moveToNext());
    }

    cursor.close();
    dbsqlite.close();

    return label;
}



public String getName(Cursor c){
    return (c.getString(1));
}


   }

Answer 2

//This is my Main Activity Class

   public class MainActivity extends Activity implements OnItemSelectedListener{
   private CarHelper dbcarhelper=null;
   private Cursor ourCursor = null,cur=null,vercur=null;
   private CarAdapter car_adapter = null;
   public Spinner spinner,spinner2,spinner3,spinner4;
   public String a;


   @Override
   protected void onCreate(Bundle savedInstanceState)
   {
try
{

    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    spinner = (Spinner)findViewById(R.id.spinner1);
    spinner2=(Spinner)findViewById(R.id.spinner2);
    spinner3= (Spinner)findViewById(R.id.spinner3);

    dbcarhelper = new CarHelper(this);
    dbcarhelper.createDatabase();
    dbcarhelper.openDataBase();

    ourCursor = dbcarhelper.getCursor();
    startManagingCursor(ourCursor);
    car_adapter = new CarAdapter(ourCursor);
    spinner.setAdapter(car_adapter);
    spinner.setOnItemSelectedListener(this);

    startManagingCursor(cur);
    spinner2.setOnItemSelectedListener(this);
    startManagingCursor(vercur);


}
catch(Exception e)
{
    Log.e("ERROR","ERROR IN CODE:" + e.toString());
    e.printStackTrace();
}
   }

   class CarAdapter extends CursorAdapter
   {

 CarAdapter(Cursor c ) {
    super(MainActivity.this, c);
 }

@Override
public void bindView(View row, Context ctxt, Cursor c) {
    CarHolder holder= (CarHolder)row.getTag();
    holder.populateFrom(c,dbcarhelper);

}

@Override
public View newView(Context ctxt, Cursor c, ViewGroup parent) {
    // TODO Auto-generated method stub
    LayoutInflater inflater = getLayoutInflater();
    View row = inflater.inflate(R.layout.row, parent, false);
    CarHolder holder= new CarHolder(row);
    row.setTag(holder);
    return (row);
}

}
   static class CarHolder {
       private TextView make = null, model= null, ver=null;
   CarHolder (View row)
   {
   make = (TextView)row.findViewById(R.id.carText);
   model = (TextView)row.findViewById(R.id.carText);
   ver = (TextView)row.findViewById(R.id.carText);
   }

   void populateFrom(Cursor c,CarHelper r)
   {
   make.setText(r.getName(c));
   model.setText(r.getName(c));
   ver.setText(r.getName(c));
   }


   }

   public void onItemSelected(AdapterView<?> parent, View arg1,int position,long        arg3) 
   {
       int id = parent.getId();
   switch(id)
   {
case R.id.spinner1:

int b = spinner.getSelectedItemPosition();
b++;
a = Integer.toString(b);

if(a!=null)
{
    dbcarhelper.m_pop(a);
}   

CarHelper ch = new CarHelper(getApplicationContext());

List<String> lables = ch.getAllLabels();
//ch.getAllLabels(str);

ArrayAdapter<String> dataAdapter = new ArrayAdapter<String>(this,
    android.R.layout.simple_spinner_item, lables);

dataAdapter
    .setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);

spinner2.setAdapter(dataAdapter);

break;

case R.id.spinner2:
    int i = spinner.getSelectedItemPosition();
    i++;
    a = Integer.toString(i);

    if(a!=null)
    {
        dbcarhelper.v_pop(a);
    }   

    CarHelper c = new CarHelper(getApplicationContext());

    List<String> lable = c.getAllLabel();

    ArrayAdapter<String> dAdapter = new ArrayAdapter<String>(this,
        android.R.layout.simple_spinner_item, lable);

    dAdapter
        .setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);

    spinner3.setAdapter(dAdapter);
    break;

}

   }

   @Override
   public void onNothingSelected(AdapterView<?> arg0) {
// TODO Auto-generated method stub

   }