How to read data from Android SQLite Database

In our previous android sqlite database code example, we looked at how to add data to android sqlite database.

Now, we are going to learning how to read data from Android sqlite database with a simple code example.

You are free to use or modify the example code in your project. It will save you some time you would have used to reinvent the wheel.

When we store data for safe keeping in our application, we will also like to retrieve our stored data when we need it.

Since we are going with with a dictionary table in our sqlite database, we will display all the words we have added in a RecyclerView

1. Create a DatabaseHelper Class

In your android project, create a new Java class and name it DatabaseHelper.java. This class will extends SQLiteOpenHelper class and will override the onCreate() and onUpgrade() methods.

Inside the onCreate() method, we will create all the tables that will leave in our database while we will drop tables in onUpgrade() method.

We will add a new method that will read data from the dictionary table and return it as a list of string object. We will get to that soon.

Open the DatabaseHelper class and add the code below.

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

import androidx.annotation.Nullable;

import com.inducesmile.sqliteexample.readDataFromSqlite.DictionaryModel;

import java.util.ArrayList;
import java.util.List;

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "sqlite.db";

    public static final String TABLE_NAME = "dictionary";

    public static final String COL_ID = "_id";

    public static final String COL_2 = "word";

    private SQLiteDatabase sqLiteDatabase;

    public DatabaseHelper(@Nullable Context context) {
        super(context, DATABASE_NAME, null,2);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_DICTIONARY_TABLE = "CREATE TABLE " + TABLE_NAME + "(" + COL_ID + " INTEGER PRIMARY KEY," + COL_2 + " TEXT" + ")";
        db.execSQL(CREATE_DICTIONARY_TABLE);
    }

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

    public List<DictionaryModel> getDictionary(){
        List<DictionaryModel> dic = new ArrayList<>();

        String sql = "select * from " + TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.rawQuery(sql, null);
        if(cursor.moveToFirst()){
            do{
                int id = Integer.parseInt(cursor.getString(0));
                String word = cursor.getString(1);

                dic.add(new DictionaryModel(id, word));
            }while (cursor.moveToNext());
        }
        cursor.close();

        return dic;
    }
}

In the code above we have add a new method named getDictionary(). This method returns our dictionary table records as a List of DictionaryModel object.

2. Display Sqlite Data in RecyclerView

Since we have a method that will return a list of our dictionary table records, what we will do now is to add a RecyclerView and set an adapter to it. We will then feed the adapter with a data source which is our list of records.

Lets start by creating an Adapter.

i. Create a new Java file and name it DictionaryAdapter.java

Open the file and add the code below.

import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;

import androidx.annotation.NonNull;
import androidx.recyclerview.widget.RecyclerView;

import java.util.List;

public class DictionaryAdapter extends RecyclerView.Adapter<DictionaryViewHolder> {

    private static final String TAG = DictionaryAdapter.class.getSimpleName();

    private List<DictionaryModel> dictionaryModels;


    public DictionaryAdapter(List<DictionaryModel> dictionaryModels) {
        this.dictionaryModels = dictionaryModels;
    }

    @NonNull
    @Override
    public DictionaryViewHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {
        View view = LayoutInflater.from(parent.getContext()).inflate(R.layout.dictionary_layout, parent, false);
        return new DictionaryViewHolder(view);
    }

    @Override
    public void onBindViewHolder(@NonNull DictionaryViewHolder holder, int position) {
        final DictionaryModel model = dictionaryModels.get(position);

        holder.dicWord.setText(model.getWord());
        holder.view.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                //Get item click id
            }
        });
    }

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

ii. Create a ViewHolder Class

Create a new Java class and name it DictionaryViewHolder.java.

Open the class and add the code below

import android.view.View;

import androidx.annotation.NonNull;
import androidx.appcompat.widget.AppCompatTextView;
import androidx.recyclerview.widget.RecyclerView;


public class DictionaryViewHolder extends RecyclerView.ViewHolder {

    public AppCompatTextView dicWord;

    public View view;

    public DictionaryViewHolder(@NonNull View itemView) {
        super(itemView);
        dicWord = (AppCompatTextView)itemView.findViewById(R.id.item_text);
        view = itemView;
    }
}

ii. Create a data model class

As you can see in the Adapter class above, we passed a List of DictionaryModel class as parameter. Lets create the model class.

Right click on your package folder and go to New > Java class > create a new Java class and name it DictionaryModel.java.

Open the file and add the code below.

public class DictionaryModel {

    private int id;
    private String word;

    public DictionaryModel(int id, String word) {
        this.id = id;
        this.word = word;
    }

    public int getId() {
        return id;
    }

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

    public String getWord() {
        return word;
    }

    public void setWord(String word) {
        this.word = word;
    }
}

iv. Create the item layout file

We need to create the RecyclerView item layout. The layout file will contain a TextView and the default View which.

Go to res > layout > dictionary_layout.xml.

Open the new dictionary_layout.xml and add the code below.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="match_parent"
    android:layout_height="wrap_content">

    <androidx.appcompat.widget.AppCompatTextView
        android:id="@+id/item_text"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Testing"
        android:padding="16dp"
        android:textColor="@color/colorAccent"
        android:textSize="15sp"/>

    <View
        android:layout_width="match_parent"
        android:layout_height="1dp"
        android:background="@color/colorAsh"/>

</LinearLayout>

3. Create new Activity class

To display our database content, we are going to create a new Activity file.

Go to your project package folder and right click.

Select New > Activity > Empty Activity > ReadSqliteDataActivity.java

i. Open the activity_read_sqlite_data.xml file and add a RecyclerView as shown below

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
    tools:context=".readDataFromSqlite.ReadSqliteDataActivity">

    <androidx.recyclerview.widget.RecyclerView
        android:id="@+id/list_content"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />
</androidx.constraintlayout.widget.ConstraintLayout>

ii. Open the ReadSqliteDataActivity class and add the code below.

import android.os.Bundle;

import androidx.appcompat.app.AppCompatActivity;
import androidx.recyclerview.widget.DefaultItemAnimator;
import androidx.recyclerview.widget.LinearLayoutManager;
import androidx.recyclerview.widget.RecyclerView;

import com.inducesmile.sqliteexample.databaseHelper.DatabaseHelper;

import java.util.List;

public class ReadSqliteDataActivity extends AppCompatActivity {

    private static final String TAG = ReadSqliteDataActivity.class.getSimpleName();

    private RecyclerView recyclerView;

    private DictionaryAdapter dictionaryAdapter;

    private DatabaseHelper databaseHelper;

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

        setTitle("Add Data to Sqlite");

        //set up recyclerview
        recyclerView = (RecyclerView)findViewById(R.id.list_content);
        setUpRecyclerView();

        //create instance of database and return dictionary data
        databaseHelper = new DatabaseHelper(this);
        List<DictionaryModel> dictionaryModels = databaseHelper.getDictionary();

        //populate list
        populateRecyclerViewFromSqliteDatabase(dictionaryModels);
    }


    private void setUpRecyclerView(){
        LinearLayoutManager linearLayoutManager = new LinearLayoutManager(this);
        recyclerView.setLayoutManager(linearLayoutManager);
        recyclerView.setHasFixedSize(true);
        recyclerView.setItemAnimator(new DefaultItemAnimator());
    }


    private void populateRecyclerViewFromSqliteDatabase(List<DictionaryModel> dictionaryModels){
        dictionaryAdapter = new DictionaryAdapter(dictionaryModels);
        recyclerView.setAdapter(dictionaryAdapter);
    }
}

4. Let debug our database

If you have not used any android local database debug library, this is the time to use one.

We are going to make use of it in many other tutorials and code examples related to storing data locally in android.

Say hi to Android-Debug-Database library. You can read more about it from its Github page

Open build.gradle file and add the dependency below.

debugImplementation 'com.amitshekhar.android:debug-db:1.0.6'

4. Run your app

Click on the run button icon in Android Studio if you are using android studio.

If everything goes well, you will see an image like the screenshot below.

Add some text to the input field and click on the save button to see your added text content saved in the database.

Open the android debug database using the link provided in Android Studio logcat. The local server will open a new web page which will contain information about our database and all tables in the database.

The image below shows all the text content that we added to the dictionary table.

If you have any question or suggestions kindly use the comment box or you can contact us directly through our contact page below.

 

Add a Comment