Android SQLite - Spinner - INSERT,SELECT and Show

android crud

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 : [embedyt] http://www.youtube.com/watch?v=su_gqbD3d0Y[/embedyt] Cheers.

Project Demo

SECTION 1 : Our MainActivity

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 class

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

<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>

Our Manifest

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.tutorials.dbgridview"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="19"
        android:targetSdkVersion="19" />

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name="com.tutorials.dbgridview.MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

Android SQLite Spinner - Save,Retrieve and Show

Android SQLite Spinner

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

Dear readers drop us your comments below. We are building a community of students and learners. Start by dropping us your suggestions below. What tutorials do you want us to do for example? Where can we improve? What are some awesome resources out there? Do you have any code you want to share with us?
By the way that example or snippet you have lying in your computer can really help beginner programmers. We can share it here with other students.

Previous Post Next Post