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.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive

Leave a Reply

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