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]
<?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>
[/xml]

This is the layout for the interface.

activity_main.xml

[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>
[/xml]

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

[java]
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;
}

}
[/java]

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 *