Home Android SQLite : Spinner – INSERT from EditTetxt,SELECT,SHOW

Android SQLite : Spinner – INSERT from EditTetxt,SELECT,SHOW

Android SQLite : Spinner – INSERT from EditTetxt,SELECT,SHOW


Language :
Platform :
Type :
Owner :
Main Category :
Sub Category :
License :
Price :


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.

Section 1 : Database Adapter Class

  • 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.
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 2 : MainActivity

  • 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 3 : Layouts

  • Contains edittext,buttons 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>

Section 4 : Result

SQLite database Spinner Save and Retrieve