How to load a spinner with values from SQlite Database in android?

By | November 18, 2012

Here is a simple example showing how to load a database values in a spinner in android.

Spinner from SQLite

OK we will start.

This is the layout for the spinner row.
spinner_row.xml

<?xml version="1.0" encoding="utf-8"?>
<TextView xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation     =      "vertical"
    android:id="@+id/tv"
    android:layout_margin="10dp">   
</TextView>

This is the layout for the interface.

activity_main.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <TextView
        android:id="@+id/tv"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_margin="10dp"
        android:textStyle="bold"
        android:text="Load DB values into Spinner"
         >
    </TextView>

    <Spinner
        android:id="@+id/spinner1"
        android:layout_below="@+id/tv"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_alignParentTop="true" />

</LinearLayout>

Now this is the MainActivity.java file that uses the spinner and the database.

package com.coderzheaven.loadspinnerfromdb;

import   java  .util  .ArrayList;

import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.widget.ArrayAdapter;
import android.widget.Spinner;
import android.widget.Toast;

public class MainActivity extends Activity {

	SQLiteDatabase mydb;
	private   static String DBNAME = "PERSONS.db";
	private static String TABLE = "MY_TABLE";

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);

		createTable();
		insertIntoTable();

		ArrayList<String> my_array = new ArrayList<String>();
		my_array = getTableValues();

		Spinner My_spinner = (Spinner) findViewById(R.id.spinner1);
		ArrayAdapter my_Adapter = new ArrayAdapter(this, R.layout.spinner_row,
				my_array);
		My_spinner.setAdapter(my_Adapter);
	}

	// CREATE TABLE IF NOT EXISTS
	public void createTable() {
		try {
			mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE, null);
			mydb.execSQL("CREATE TABLE IF  NOT EXISTS " + TABLE
					+ " (ID INTEGER PRIMARY KEY, NAME TEXT, PLACE TEXT);");
			mydb.close();
		} catch (Exception e) {
			Toast.makeText(getApplicationContext(), "Error in creating table",
					Toast.LENGTH_LONG);
		}
	}

	// THIS FUNCTION INSERTS DATA TO THE DATABASE
	public void insertIntoTable() {
		try {
			mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE, null);
			mydb.execSQL("INSERT INTO " + TABLE
					+ "(NAME, PLACE) VALUES('CODERZHEAVEN','GREAT INDIA')");
			mydb.execSQL("INSERT INTO " + TABLE
					+ "(NAME, PLACE) VALUES('ANTHONY','USA')");
			mydb.execSQL("INSERT INTO " + TABLE
					+ "(NAME, PLACE) VALUES('SHUING','JAPAN')");
			mydb.execSQL("INSERT INTO " + TABLE
					+ "(NAME, PLACE) VALUES('JAMES','INDIA')");
			mydb.execSQL("INSERT INTO " + TABLE
					+ "(NAME, PLACE) VALUES('SOORYA','INDIA')");
			mydb.execSQL("INSERT INTO " + TABLE
					+ "(NAME, PLACE) VALUES('MALIK','INDIA')");
			mydb.close();
		} catch (Exception e) {
			Toast.makeText(getApplicationContext(),
					"Error in inserting into table", Toast.LENGTH_LONG);
		}
	}

	// THIS FUNCTION SHOWS DATA FROM THE DATABASE
	public ArrayList<String> getTableValues() {

		ArrayList<String> my_array = new ArrayList<String>();
		try {
			mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE, null);
			Cursor allrows = mydb.rawQuery("SELECT * FROM " + TABLE, null);
			System.out.println("COUNT : " + allrows.getCount());

			if (allrows.moveToFirst()) {
				do {

					String ID = allrows.getString(0);
					String NAME = allrows.getString(1);
					String PLACE = allrows.getString(2);
					my_array.add(NAME);

				} while (allrows.moveToNext());
			}
			allrows.close();
			mydb.close();
		} catch (Exception e) {
			Toast.makeText(getApplicationContext(), "Error encountered.",
					Toast.LENGTH_LONG);
		}
		return my_array;
	}

}

Join the Forum discussion on this post

Note : Please remove the “span” tags from the post when you copy it.

Download the complete source code for this example from here.

2 thoughts on “How to load a spinner with values from SQlite Database in android?

  1. Krushnasinh

    Simple Method to fill up Spinner from database values

    private void getCategory()
    {

    DHE=new Database_Handler_Expense(ExpenseForm.this);
    cate_list.clear();

    ArrayListfromdb=DHE.Get_All_Categorie();

    for (int i = 0; i < fromdb.size(); i++)
    {
    String categories=fromdb.get(i).getCategory();
    Category cate=new Category();
    cate.setCategory(categories);
    cate_list.add(categories);

    }

    Fill up Array List onCreate

    ArrayAdapter adt=new ArrayAdapter(this,android.R.layout.simple_list_item_1,cate_list);
    spcategory.setAdapter(adt);

    Reply
    1. James Post author

      Hi Krushnasinh ,
      What is the point here? after all you have to write the functions mentioned in your post.
      I demonstrated how to insert as well not simply retrieving…

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *