Dynamic List of Spinner from SQLite DB–Android

This is an example to dynamically populate list of items in Android spinner. The list of items is extracted from a table of a SQLite DB (local DB).

The DB structure is as follow

db.execSQL("CREATE TABLE menujabatan " +
        "(jid TEXT, jnama TEXT);");
        //insert pre-configured records
        db.execSQL("INSERT INTO menujabatan (jid, jnama) VALUES('it','Teknologi Maklumat');");
        db.execSQL("INSERT INTO menujabatan (jid, jnama) VALUES('hr','Sumber Manusia');");
        db.execSQL("INSERT INTO menujabatan (jid, jnama) VALUES('pz','Pungutan Zakat');");
        db.execSQL("INSERT INTO menujabatan (jid, jnama) VALUES('pr','Promo');");

The complete source - https://docs.google.com/file/d/0B34ZxOOoeSDdNWp0Qk9UZ1pZSTQ/edit

The Java source-code

package net.kerul.menuspinner;

import java.util.ArrayList;

import com.google.ads.AdRequest;
import com.google.ads.AdView;

import android.os.Bundle;
import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.view.Menu;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.ArrayAdapter;
import android.widget.Spinner;
import android.widget.Toast;

public class Spinner_Items_from_DB extends Activity {
    private DBcon dbhelper = new DBcon(this);
    private SQLiteDatabase db;
    private Spinner sjbtn, spersonel;
    private ArrayList<String> arrayList1 = new ArrayList<String>();
    private ArrayList<String> arrayList2;
    private ArrayList<String> arrayjid = new ArrayList<String>();
    private ArrayAdapter<String> adp,adp2;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_senarai);
        
        //UI
        //AdView
        //admob widget
        AdView adView = (AdView)findViewById(R.id.ad);
        adView.loadAd(new AdRequest());
        //Dynamic String ArrayList
        //fetch data from menujabatan
        sjbtn=(Spinner)findViewById(R.id.spinner1);
        
        arrayList1.add("Pilih jabatan");
        adp = new ArrayAdapter<String> (this,android.R.layout.simple_spinner_dropdown_item,arrayList1);
                
      //spinner personel
        arrayList2= new ArrayList<String>();
        arrayList2.add("Pilih personel");
        adp2 = new ArrayAdapter<String> (this,android.R.layout.simple_spinner_dropdown_item,arrayList2);
        spersonel=(Spinner)findViewById(R.id.spinner2);
        spersonel.setAdapter(adp2);
        spersonel.setEnabled(false);
        
        //fetch list of jabatan from table menujabatan
          db = dbhelper.getReadableDatabase();
          Cursor cjbtn=db.rawQuery("SELECT * FROM menujabatan;", null);
        cjbtn.moveToFirst();//go to first row
        do{
            String row=cjbtn.getString(1);
            arrayjid.add(cjbtn.getString(0));
            arrayList1.add(row);
        }while (cjbtn.moveToNext());
        
        sjbtn.setAdapter(adp);
        sjbtn.setOnItemSelectedListener(new OnItemSelectedListener() { 
                @Override
                public void onItemSelected(AdapterView<?> arg0, View arg1, int arg2,
                        long arg3) {
                    // TODO Handling selected item to generate list of personel
                    //Toast.makeText(getParent().getApplicationContext(), arrayjid.get(arg2), Toast.LENGTH_SHORT).show();
                    
                }
                public void onNothingSelected(AdapterView<?> arg0) {
                    // TODO Auto-generated method stub
                    
                }
                }
                );//end itemListener

            //continue spinner personel
            spersonel.setOnItemSelectedListener(new OnItemSelectedListener() {
                public void onItemSelected(AdapterView<?> arg0, View arg1,
                        int arg2, long arg3) {
                    // TODO Auto-generated method stub
                }
    
                @Override
                public void onNothingSelected(AdapterView<?> arg0) {
                    // TODO Auto-generated method stub
                }
            } );
        
        //personel
        
    }//end onCreate

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.activity_senarai, menu);
        return true;
    }

    

}

class DBcon extends SQLiteOpenHelper {
final protected static String DATABASE_NAME="staffzpp";
public DBcon(Context context) {
    super(context, DATABASE_NAME, null,1);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion >= newVersion) return;
            db.execSQL("DROP DATABASE IF EXISTS " + DATABASE_NAME +";");
            onCreate(db);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        //here is the database definition
        db.execSQL("CREATE TABLE menujabatan " +
        "(jid TEXT, jnama TEXT);");
        //insert pre-configured records
        db.execSQL("INSERT INTO menujabatan (jid, jnama) VALUES('it','Teknologi Maklumat');");
        db.execSQL("INSERT INTO menujabatan (jid, jnama) VALUES('hr','Sumber Manusia');");
        db.execSQL("INSERT INTO menujabatan (jid, jnama) VALUES('pz','Pungutan Zakat');");
        db.execSQL("INSERT INTO menujabatan (jid, jnama) VALUES('pr','Promo');");
        
        db.execSQL("CREATE TABLE personel " +
                "(pnama TEXT, pemail TEXT, jid TEXT);");
                //insert pre-configured records
        db.execSQL("INSERT INTO personel (pnama, pemail, jid) " +
                "VALUES('Rusli Mukhtar','rusli@zakat.com','it');");
        db.execSQL("INSERT INTO personel (pnama, pemail, jid) " +
                "VALUES('Johan','johan@zakat.com','it');");
        db.execSQL("INSERT INTO personel (pnama, pemail, jid) " +
                "VALUES('Shazrul','shazrul@zakat.com','it');");
        db.execSQL("INSERT INTO personel (pnama, pemail, jid) " +
                "VALUES('Khirulnizam','khirul@zakat.com','hr');");
        db.execSQL("INSERT INTO personel (pnama, pemail, jid) " +
                "VALUES('Minah','minah@zakat.com','pr');");
        db.execSQL("INSERT INTO personel (pnama, pemail, jid) " +
                "VALUES('Lee','lee@zakat.com','pz');");
    }

}