Android SQLite - RecyclerView - INSERT,SELECT and Show.


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

How to perform CRUD Operations against SQLite Database. We use RecyclerView to render data.

SQLite is the most popular database engine. This is due to it's serverless, zero-configuration nature yet still reasonably performant. SQLite simply exists a single file and runs in a single process. This makes it efficient hence can easily be used in resource-constrained devices like android and iOS environments. It's also free and actively maintaied since it's inception in the year 2000. Read more about SQLite history here.

RecyclerView on the other hand is an adapterview that allows us render large datasets. Especially data coming from databases like sqlite and webservices qualify as good for being rendered in recyclerview. RecyclerViews are popular because of their flexibility and ease of use. Once we've retrieved our data from sqlite we will show this data in recyclerview. Learn more about RecyclerViews here.

Our recyclerview will comprise of Cardviews. Each CardView will correspond to a single data object.

Build.Gradle

Our Dependencies. Here's our build.gradle(Module:app). We are not using any third party libraries.

dependencies {
    compile fileTree(dir: 'libs', include: ['*.jar'])
    testCompile 'junit:junit:4.12'
    compile 'com.android.support:appcompat-v7:23.2.1'
    compile 'com.android.support:design:23.2.1'
    compile 'com.android.support:cardview-v7:23.2.1'

}

Player.java

 Our Model Class

  • Our data object class.
  • Represents a single “Player” with all his/her properties.
package com.tutorials.hp.recyclerinsetselectshow;

public class Player {

    private int id;
    private String name,position;
    private int image;

    public Player(int id, String name, String position, int image) {
        this.id = id;
        this.name = name;
        this.position = position;
        this.image = image;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPosition() {
        return position;
    }

    public void setPosition(String position) {
        this.position = position;
    }

    public int getImage() {
        return image;
    }

    public void setImage(int image) {
        this.image = image;
    }
}

MainActivity.java

Our MainActivity Class

  • Launcher activity.
  • Initialize RecyclerView and set its adapter and layout manager.
  • Handles data input.
  • Call database classes to persist.
package com.tutorials.hp.recyclerinsetselectshow;

import android.app.Dialog;
import android.database.Cursor;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.DefaultItemAnimator;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.support.v7.widget.Toolbar;
import android.view.View;
import android.view.Window;
import android.widget.Button;
import android.widget.EditText;

import java.util.ArrayList;

public class MainActivity extends AppCompatActivity {

    EditText nameTxt,posTxt;
    RecyclerView rv;
    MyAdapter adapter;
    ArrayList<Player> players=new ArrayList<>();

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
        setSupportActionBar(toolbar);

        FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
        fab.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                //SHOW INPUT DIALOG
                showDialog();
            }
        });

        //recycler
       rv= (RecyclerView) findViewById(R.id.mRecycler);

        //SET PROPS
        rv.setLayoutManager(new LinearLayoutManager(this));

        rv.setItemAnimator(new DefaultItemAnimator());

        //ADAPTER
        adapter=new MyAdapter(this,players);

        //RETRIEVE
        retrieve();

    }

    //SHOW INSERT DIALOG
    private void showDialog()
    {
        Dialog d=new Dialog(this);

        //NO TITLE
        d.requestWindowFeature(Window.FEATURE_NO_TITLE);

        d.setContentView(R.layout.custom_layout);

        nameTxt= (EditText) d.findViewById(R.id.nameEditTxt);
        posTxt= (EditText) d.findViewById(R.id.posEditTxt);
        Button saveBtn= (Button) d.findViewById(R.id.saveBtn);
        final Button retrievebtn= (Button) d.findViewById(R.id.retrieveBtn);

        saveBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                save(nameTxt.getText().toString(),posTxt.getText().toString());
            }
        });

        retrievebtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                 retrieve();
            }
        });

        d.show();

    }

    private void save(String name,String pos)
    {
        DBAdapter db=new DBAdapter(this);

        //OPEN DB
        db.openDB();

        //COMMIT
        long result=db.add(name,pos);

        if(result>0)
        {
            nameTxt.setText("");
            posTxt.setText("");
        }else
        {
            Snackbar.make(nameTxt,"Unable To Save",Snackbar.LENGTH_SHORT).show();
        }

        db.closeDB();

        //REFRESH
        retrieve();
    }

    //RETREIEV
    private void retrieve()
    {
        players.clear();

        DBAdapter db=new DBAdapter(this);
        db.openDB();

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

        //LOOP AND ADD TO ARRAYLIST
        while (c.moveToNext())
        {
            int id=c.getInt(0);
            String name=c.getString(1);
            String pos=c.getString(2);

            Player p=new Player(id,name,pos,R.id.playerImage);

            //ADD TO ARRAYLIS
            players.add(p);
        }

        //CHECK IF ARRAYLIST ISNT EMPTY
        if(!(players.size()<1))
        {
            rv.setAdapter(adapter);
        }

        db.closeDB();;

    }

}

Constants.java

 Our Constants Class

  • Constants defining database properties.
  • Also contains our SQL statement for creating table.
package com.tutorials.hp.recyclerinsetselectshow;

public class Constants {
    //COLUMNS
    static final String ROW_ID="id";
    static final String NAME = "name";
    static final String POSITION = "position";

    //DB PROPERTIES
    static final String DB_NAME="d_DB";
    static final String TB_NAME="d_TB";
    static final int DB_VERSION='1';

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

DBHelper.java

Our DBHelper Class

  • Creates database table.
  • Handles sqlite database table versioning and upgrade.
package com.tutorials.hp.recyclerinsetselectshow;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

    public DBHelper(Context context) {
        super(context, Constants.DB_NAME, null, Constants.DB_VERSION);
    }

    //TABLE CREATION
    @Override
    public void onCreate(SQLiteDatabase db) {
        try
        {
            db.execSQL(Constants.CREATE_TB);

        }catch (Exception ex)
        {
            ex.printStackTrace();
        }

    }

    //TABLE UPGRADE
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS "+Constants.TB_NAME);
        onCreate(db);

    }
}

DBAdapter.java"

DBAdapter Class

  • Handles CRUD operations.
  • Saves and retrieves data to and from sqlite database.
package com.tutorials.hp.recyclerinsetselectshow;

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

public class DBAdapter {
    Context c;

    SQLiteDatabase db;
    DBHelper helper;

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

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

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

        return this;
    }

    //CLOSE DATABASE
    public void closeDB()
    {
        try {
          helper.close();

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

    }

    //INSERT
    public long add(String name,String pos)
    {
        try
        {
            ContentValues cv=new ContentValues();
            cv.put(Constants.NAME,name);
            cv.put(Constants.POSITION, pos);

            return db.insert(Constants.TB_NAME,Constants.ROW_ID,cv);
        }catch (SQLException e)
        {
            e.printStackTrace();
        }

        return 0;
    }

    //RETRIEVE
    public Cursor getAllPlayers()
    {
        String[] columns={Constants.ROW_ID,Constants.NAME,Constants.POSITION};

        return db.query(Constants.TB_NAME,columns,null,null,null,null,null);

    }
}

ItemClickListener.java

Our ItemClickListener Interface

  • Defines our onItemClick() method signature.
package com.tutorials.hp.recyclerinsetselectshow;

import android.view.View;

public interface ItemClickListener {

    void onItemClick(View v,int pos);
}

MyHolder.java

Our ViewHolder Class.

  • Holds our views to be recycled.
  • Implements View.OnItemClick Listener
package com.tutorials.hp.recyclerinsetselectshow;

import android.support.v7.widget.RecyclerView;
import android.view.View;
import android.widget.ImageView;
import android.widget.TextView;

public class MyHolder extends RecyclerView.ViewHolder implements View.OnClickListener {

    ImageView img;
    TextView nametxt,posTxt;
    ItemClickListener itemClickListener;

    public MyHolder(View itemView) {
        super(itemView);

        nametxt= (TextView) itemView.findViewById(R.id.nameTxt);
        posTxt= (TextView) itemView.findViewById(R.id.posTxt);
        img= (ImageView) itemView.findViewById(R.id.playerImage);

        itemView.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
        this.itemClickListener.onItemClick(v,getLayoutPosition());
    }

    public void setItemClickListener(ItemClickListener ic)
    {
        this.itemClickListener=ic;

    }
}

MyAdapter.java

Our Adapter Class

  • Handles Layout Inflation.
  • Binds our dataset to our views.
package com.tutorials.hp.recyclerinsetselectshow;

import android.content.Context;
import android.support.design.widget.Snackbar;
import android.support.v7.widget.RecyclerView;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;

import java.util.ArrayList;

public class MyAdapter extends RecyclerView.Adapter<MyHolder> {
    Context c;
    ArrayList<Player> players;

    public MyAdapter(Context c, ArrayList<Player> players) {
        this.c = c;
        this.players = players;
    }

    //INITIALIZE VIEWHODER
    @Override
    public MyHolder onCreateViewHolder(ViewGroup parent, int viewType) {
        //VIEW OBJ
        View v= LayoutInflater.from(parent.getContext()).inflate(R.layout.model,null);

        //HOLDER
        MyHolder holder=new MyHolder(v);

        return holder;
    }

    //BIND VIEW TO DATA
    @Override
    public void onBindViewHolder(MyHolder holder, int position) {
        holder.img.setImageResource(R.drawable.marker);

        holder.nametxt.setText(players.get(position).getName());
        holder.posTxt.setText(players.get(position).getPosition());

        //CLICKED
        holder.setItemClickListener(new ItemClickListener() {
            @Override
            public void onItemClick(View v, int pos) {
                Snackbar.make(v,players.get(pos).getName(),Snackbar.LENGTH_SHORT).show();
            }
        });

    }

    @Override
    public int getItemCount() {
        return players.size();
    }
}

ContentMain.xml

Our ContentMain XML

  • Holds our adapter view.
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    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"
    app:layout_behavior="@string/appbar_scrolling_view_behavior"
    tools:context="com.tutorials.hp.recyclerinsetselectshow.MainActivity"
    tools:showIn="@layout/activity_main">

   <android.support.v7.widget.RecyclerView
       android:layout_width="match_parent"
       android:layout_height="match_parent"
       android:id="@+id/mRecycler"
       ></android.support.v7.widget.RecyclerView>
</RelativeLayout>

Model.xml

Our Model XML

  • Defines our RecyclerView View Item.
<?xml version="1.0" encoding="utf-8"?>
<android.support.v7.widget.CardView xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="horizontal" android:layout_width="match_parent"
    xmlns:card_view="http://schemas.android.com/apk/res-auto"
    android:layout_margin="5dp"
    card_view:cardCornerRadius="10dp"
    card_view:cardElevation="5dp"

    android:layout_height="match_parent">

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent">

        <ImageView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:id="@+id/playerImage"
            android:padding="10dp"
            android:src="@drawable/marker" />

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textAppearance="?android:attr/textAppearanceLarge"
            android:text="Name"
            android:id="@+id/nameTxt"
            android:padding="10dp"
            android:layout_alignParentTop="true"
            android:layout_toRightOf="@+id/playerImage" />

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textAppearance="?android:attr/textAppearanceMedium"
            android:text="Position"
            android:id="@+id/posTxt"
            android:padding="10dp"
            android:layout_alignBottom="@+id/playerImage"
            android:layout_alignParentRight="true" />

    </RelativeLayout>
</android.support.v7.widget.CardView>

InputDialog.xml"

Our InputDialog XML

  • Provides ui for data input.
<?xml version="1.0" encoding="utf-8"?>

    <android.support.v7.widget.CardView xmlns:android="http://schemas.android.com/apk/res/android"
        android:orientation="horizontal" android:layout_width="500dp"
        xmlns:card_view="http://schemas.android.com/apk/res-auto"
        android:layout_margin="1dp"
        card_view:cardCornerRadius="10dp"
        card_view:cardElevation="5dp"

        android:layout_height="match_parent">

    <LinearLayout
            android:layout_width="match_parent"
            android:orientation="vertical"
            android:layout_height="match_parent">

            <ImageView
                android:id="@+id/image"
                android:layout_width="190dp"
                android:layout_height="275dp"
                android:paddingLeft="15dp"
                android:scaleType="fitCenter"
                android:src="@drawable/marker"
                android:layout_alignParentLeft="true"
                android:layout_alignParentStart="true"
                android:layout_below="@+id/posEditTxt" />

            <TextView
                android:id="@+id/profile"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:textStyle="bold"
                android:textColor="@color/colorAccent"
                android:text="PLAYER PROFILE"
                android:textAppearance="?android:attr/textAppearanceLarge"
                android:layout_below="@+id/image"
                android:layout_alignParentLeft="true"
                android:layout_alignParentStart="true" />

            <EditText
                android:id="@+id/nameEditTxt"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_alignParentLeft="true"
                android:paddingLeft="15dp"
                android:hint="Name"
                android:textAppearance="?android:attr/textAppearanceLarge"
                android:layout_below="@+id/profile" />

            <EditText
                android:id="@+id/posEditTxt"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_alignParentLeft="true"
                android:layout_below="@+id/nameTxt"
                android:paddingLeft="15dp"
                android:layout_marginTop="24dp"
                android:hint="Position : "
                android:textAppearance="?android:attr/textAppearanceLarge" />

            <LinearLayout
                android:layout_width="match_parent"
                android:layout_height="52dp"
                android:gravity="center_vertical|end"
                android:orientation="horizontal"
                android:id="@+id/linear1"
                android:padding="8dp"
                android:layout_alignParentBottom="true"
                android:layout_alignParentLeft="true"
                android:layout_alignParentStart="true">

                <Button
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"

                    android:padding="5dp"
                    android:textAppearance="?android:attr/textAppearanceMedium"
                    android:gravity="center_vertical|center_horizontal"
                    android:text="Save"

                    android:id="@+id/saveBtn" />
                <Button
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"

                    android:padding="5dp"
                    android:textAppearance="?android:attr/textAppearanceMedium"
                    android:gravity="center_vertical|center_horizontal"
                    android:text="Retrieve"

                    android:id="@+id/retrieveBtn" />

            </LinearLayout>

</LinearLayout>

    </android.support.v7.widget.CardView>

Download

You can Download the full Project here. Source code is well commented.

  • We also have a video tutorial for this example explained in a step by step manner.
  • Moreover you can view the project demo there.
  • The full source code reference is of course in the link above.

Below is the video tutorial.If you prefer more explanations and demo visit it.

No. Location Link
1. GitHub Direct Download)
2. GitHub Browse
3. YouTube Video Tutorial
4. YouTube Our YouTube Channel

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