Android SQLite Spinner


Android SQLite Spinner Tutorials and Examples.

How to perform several SQLite Database CRUD operations in android with Spinner as the adapterview.

Android SQLite - Spinner - INSERT,SELECT and Show

Android SQLite - Spinner - INSERT,SELECT and Show Tutorial.

This is an android sqlite tutorial.We see how save data to sqlite database from edittext,retrieve that data and show it in a simple spinner.

Nowadays most apps need or require to store content or data in some form.There is the cloud but it isn’t a replacement for local storage.Neither will it be in the near future.

Storing data locally is important because its easily retrievable,doesn’t require internet connection and is fast. Good old SQLite is still the way to go.Together with Realm database,they are two ways that are now common in data storage.The package we shall be using is android.database.sqlite

It Was added in API 1 and contains classes which your app can use when talking to and managing your database. Android does ship with SQLite database. As of now SQLite 3.4.0. One of those classes is Android.database.sqlite.SQliteDatabase .

This class derives from android.database.SQLite.SQLiteCloseable and obviously,java.lang.Object. This class has methods that we can use to basically, manage our SQLite database. It has methods for CRUD stuff. Also execute SQL statements. A database has to be unique for each single application, within that application.

Android SQLite Spinner tutorial.Today we explore how to insert/save data into sqlite database,then retrieve that data and show it in a spinner.We shall be saving data from EditTexts on button click.

However if you prefer a video version of this tutorial with maybe more explanations,watch our tutorial here.

Project Demo

SECTION 1 : Our MainActivity

This is the launcher activity for the project. This will represent the user interface with which the user will interact with app. This means the user will have widgets to enter or save data to firebase. These widgets include:

  1. EditText - This will be used to type or enter data to be saved in sqlite.
  2. Button - This will initiate the manipulation of our database when clicked. Either we save or retrieve.
  3. Spinner - This is the component or widget onto which we render our data retrieved from SQLite database.
  • We save data from edittext to SQLite using our database adapter class object.
  • We retrieve our sqlite data then bind the data to our spinner
package com.tutorials.dbgridview;

import java.util.ArrayList;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.Toast;

public class MainActivity extends Activity {

  Spinner sp;
  EditText nametxt,posTxt;
  Button saveBtn,retrievebtn;

  ArrayList<String> names=new ArrayList<String>();
  ArrayAdapter<String> adapter;

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

        sp=(Spinner) findViewById(R.id.spinner1);
        nametxt=(EditText) findViewById(R.id.nameTxt);
        posTxt=(EditText) findViewById(R.id.posTxt);

        saveBtn=(Button) findViewById(R.id.saveBtn);
        retrievebtn=(Button) findViewById(R.id.retrievebtn);

        //ADAPTER
        adapter=new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,names);

        final DBAdapter db=new DBAdapter(this);

        //EVENTS
        saveBtn.setOnClickListener(new OnClickListener() {

      @Override
      public void onClick(View v) {
        // TODO Auto-generated method stub

        //OPEN
        db.openDB();

        long result=db.add(nametxt.getText().toString(), posTxt.getText().toString());

        if( result != 0)
        {
           nametxt.setText("");
                      posTxt.setText("");

        }else
        {
            Toast.makeText(getApplicationContext(), "Failure", Toast.LENGTH_SHORT).show();
        }

        //CLOSE
        db.close();
      }
    });

        //RETERIEVE
        retrievebtn.setOnClickListener(new OnClickListener() {

      @Override
      public void onClick(View arg0) {
        // TODO Auto-generated method stub

        names.clear();

        //OPEN
        db.openDB();

        //RETRIEVE
        Cursor c=db.getAllValues();

        while(c.moveToNext())
        {
          String name=c.getString(1);
          names.add(name);
        }

        //CLOSE
        db.close();

        //SET IT TO SPINNER
        sp.setAdapter(adapter);
      }
    });

    }
}

SECTION 2 : Our SQLite database Adapter

  • We perform CRUD here.
  • We save and retrieve data to SQLite database.
  • We have DBHelper class that helps in handling database table upgrade and creation.

This class as the name DBAdapter suggests, is going to adapt our data to SQLite database. In fact this is where we perform our basic CRUD.

We plan to:

  1. Open Database Connection.
  2. Save data typed in edittext.
  3. CLose Database Connection.

and

  1. Open Database Connection.
  2. Retrieve data saved in SQLite database.
  3. Close Connection.
  4. Return an arraylist containing retrieved data

That ArrayList can then be bound to the spinner.

package com.tutorials.dbgridview;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter {

  //COLUMNS
  static final String ROWID="id";
  static final String NAME="name";
  static final String POSITION = "position";

  static final String TAG = "DBSpinner";

  //DB PROPERTIES
  static final String DBNAME = "s_DB";
  static final String TBNAME = "s_TB";
  static final int DBVERSION = '1';

  //CREATE TB
  static final String CREATE_TB="CREATE TABLE s_TB(id INTEGER PRIMARY KEY AUTOINCREMENT,"
            + "name TEXT NOT NULL,position TEXT NOT NULL);";

  final Context c;
  SQLiteDatabase db;
  DBHelper helper;

  public DBAdapter(Context ctx)
  {
     this.c=ctx;
     helper=new DBHelper(c);
  }

  // INNER HELPER DB CLASS
  private static class DBHelper extends SQLiteOpenHelper
  {

    public DBHelper(Context context) {
      super(context, DBNAME, null, DBVERSION);
      // TODO Auto-generated constructor stub
    }

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

      try
      {
        db.execSQL(CREATE_TB);
      }catch (SQLException e) {
                e.printStackTrace();
            }

    }

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

      Log.w(TAG, "Upgrading DB");

      db.execSQL("DROP TABLE IF EXISTS s_TB");

      onCreate(db);
    }

  }

  // OPEN THE DB
  public DBAdapter openDB()
  {
    try
    {
      db=helper.getWritableDatabase();

    }catch (SQLException e) {
            e.printStackTrace();
        }

    return this;
  }

   //CLOSE THE DB
  public void close()
  {
    helper.close();
  }

  //INSERT INTO TABLE
  public long add(String name,String pos)
  {
    try
    {

      ContentValues cv=new ContentValues();
      cv.put(NAME, name);
      cv.put(POSITION, pos);

      return db.insert(TBNAME, ROWID, cv);

    }catch (SQLException e) {
            e.printStackTrace();
        }

    return 0;
  }

  //GET ALL VALUES
  public Cursor getAllValues()
  {
    String[] columns={ROWID,NAME,POSITION};

    return db.query(TBNAME, columns, null, null, null, null, null);
  }
}

SECTION 3 : Our MainActivity Layout

This is the layout for our MainActivity. It uses RelativeLayout to organize widgets. A RelativeLayout is a type of View we call a ViewGroup. That is since it wraps and arranges other views. Some of those views it will be wrapping include a button, EditTexts and spinner.

<RelativeLayout 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:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context=".MainActivity" >

    <Button
        android:id="@+id/saveBtn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/posTxt"
        android:layout_marginTop="34dp"
        android:text="Save" />

    <EditText
        android:id="@+id/nameTxt"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentTop="true"
        android:layout_marginTop="20dp"
        android:layout_toRightOf="@+id/saveBtn"
        android:ems="10" />

    <EditText
        android:id="@+id/posTxt"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/nameTxt"
        android:layout_below="@+id/nameTxt"
        android:ems="10" >

        <requestFocus
            android:layout_width="wrap_content"
            android:layout_height="wrap_content" />

    </EditText>

    <Button
        android:id="@+id/retrievebtn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/saveBtn"
        android:layout_alignBottom="@+id/saveBtn"
        android:layout_alignRight="@+id/posTxt"
        android:text="Retrieve" />

    <TextView
        android:id="@+id/textView1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_above="@+id/posTxt"
        android:layout_alignParentLeft="true"
        android:text="Name"
        android:textAppearance="?android:attr/textAppearanceSmall" />

    <TextView
        android:id="@+id/textView2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBottom="@+id/posTxt"
        android:layout_alignParentLeft="true"
        android:text="Position"
        android:textAppearance="?android:attr/textAppearanceSmall" />

    <LinearLayout
        android:id="@+id/linearLayout1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/saveBtn"
        android:layout_marginRight="16dp"
        android:orientation="vertical" >

    </LinearLayout>

    <TextView
        android:id="@+id/textView3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignRight="@+id/retrievebtn"
        android:layout_below="@+id/saveBtn"
        android:layout_marginTop="36dp"
        android:text="DATABASE VALUES SPINNER :"
        android:textAppearance="?android:attr/textAppearanceLarge" />

    <Spinner
        android:id="@+id/spinner1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/textView3"
        android:layout_below="@+id/textView3"
        android:layout_marginTop="30dp" />

</RelativeLayout>

How do You Feel after reading this?

According to scientists, we humans have 8 primary innate emotions: joy, acceptance, fear, surprise, sadness, disgust, anger, and anticipation. Feel free to tell us how you feel about this article using these emotes or via the comment section. This feedback helps us gauge our progress.

Help me Grow.

I set myself some growth ambitions I desire to achieve by this year's end regarding this website and my youtube channel. Am halfway. Help me reach them by:




Recommendations


What do You Think


Previous Post Next Post