Android SQLite Database - GridView - 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. Today we explore : Android SQlite GridView - Insert,Select and Show.We explore how to Insert data into SQLite database,retrieve that data and show it in a GridView.

If you prefer a video tutorial for this Android SQLite GridView,or more explanations check out our video tutorial : [embedyt] http://www.youtube.com/watch?v=0_2_5pZFsC8[/embedyt]

Project Demo

SECTION 1 : Our MainActivity class

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.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.GridView;
import android.widget.Toast;

public class MainActivity extends Activity {

  GridView gv;
  EditText nameTxt,posTxt;
  Button saveBtn,retrieveBtn;

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

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

        //INITALZE
        gv=(GridView) findViewById(R.id.gridView1);
        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=new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,players);

        //DB
        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();

        //INSERT
        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();

      }
    });

        //RETRIVE
        retrieveBtn.setOnClickListener(new OnClickListener() {

      @Override
      public void onClick(View v) {

        players.clear();

        //OPEN
        db.openDB();

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

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

        db.close();

        gv.setAdapter(adapter);

      }
    });

        gv.setOnItemClickListener(new OnItemClickListener() {

      @Override
      public void onItemClick(AdapterView<?> arg0, View arg1, int pos,
          long arg3) {
        // TODO Auto-generated method stub

        Toast.makeText(getApplicationContext(), players.get(pos), Toast.LENGTH_SHORT).show();

      }
    });

    }
}

SECTION 2 : Our DBAdapter 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 = "DBAdapter";

  //DB PROPERTIES
   static final String DBNAME="g_DB";
   static final String TBNAME="g_TB";
   static final int DBVERSION='1';

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

   final Context c;
   SQLiteDatabase db;
   DBHelper helper;

   public DBAdapter(Context c) {
    // TODO Auto-generated constructor stub

     this.c=c;
     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) {

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

    }

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

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

      db.execSQL("DROP TABLE IF EXISTS g_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 Layouts

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

    <TextView
        android:id="@+id/textView3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_above="@+id/gridView1"
         android:text="DATABASE VALUES GRIDVIEW"
        android:textAppearance="?android:attr/textAppearanceLarge"
        />

        <GridView
        android:id="@+id/gridView1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:numColumns="3">

        </GridView>

    </LinearLayout>

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

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