Store and retrieve data from SQLite in Android

By xngo on February 19, 2019

Overview

Android allows you to store your data in SQLite database. What you have to do is to extend SQLiteOpenHelper class. It requires your class to provide the database filename, version number and override onCreate() and onUpgrade() functions.

  1. Database filename: Any filename you want.
  2. Database version number: Use to compare your version against the version that is installed on the devices.
  3. onCreate(): Implement what to do onCreate().
  4. onUpgrade(): Implement what to do onUpgrade().

Implement DBHelper

Here is the full source code of DBHelper class that extends SQLiteOpenHelper class.

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
 
public class DBHelper extends SQLiteOpenHelper {
 
    private static final String dbName="mydatabase.db";
    private static final int dbVersion=1;
 
    public DBHelper(Context content){
        super(content, dbName, null, dbVersion);
    }
 
    @Override
    public void onCreate(SQLiteDatabase db) {
        String query="CREATE TABLE Persons (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                                                        "firstname TEXT, age INTEGER)";
        db.execSQL(query);
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Upgrade.
    }
}
  • onCreate() is called when your database doesn't exist.
  • onUpgrade() is called when your version number is higher than the version from the devices. Otherwise, a SQLiteException: Can't downgrade database is thrown. oldVersion is database version number from the device.

Use DBHelper class

Look inside addData() and retrieveData() functions to see how DBHelper class is being used in MainActivity class. It is self explanatory.

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.Gravity;
import android.view.ViewGroup;
import android.widget.LinearLayout;
import android.widget.TextView;
 
public class MainActivity extends AppCompatActivity {
 
    LinearLayout.LayoutParams layoutParams = new LinearLayout.LayoutParams(ViewGroup.LayoutParams.MATCH_PARENT,
            ViewGroup.LayoutParams.MATCH_PARENT);
 
    LinearLayout linearLayout;
 
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        this.linearLayout = new LinearLayout(this);
        this.linearLayout.setLayoutParams(this.layoutParams);
        this.linearLayout.setOrientation(LinearLayout.VERTICAL);
        this.linearLayout.setGravity(Gravity.CENTER_HORIZONTAL);
        setContentView(this.linearLayout);
 
        this.addData();
 
        this.retrieveData();
    }
 
    private void addData(){
        DBHelper dbHelper = new DBHelper(this);
        SQLiteDatabase db = dbHelper.getWritableDatabase(); // Open connection.
        db.execSQL("INSERT INTO Persons(firstname, age) VALUES('John', 22)");
        db.execSQL("INSERT INTO Persons(firstname, age) VALUES('Kate', 27)");
        db.close();
    }
 
    private void retrieveData(){
        DBHelper dbHelper = new DBHelper(this);
        SQLiteDatabase db = dbHelper.getReadableDatabase(); // Open connection.
 
        Cursor cursor = db.rawQuery("SELECT _id, firstname, age FROM Persons", null);
        while(cursor.moveToNext()){
            int i=0;
            String id        = cursor.getString(i++);
            String firstname = cursor.getString(i++);
            String age       = cursor.getString(i++);
 
            String text = String.format("id=%s, firstname=%s, age=%s", id, firstname, age);
            TextView textView = new TextView(this);
            textView.setText(text);
            this.linearLayout.addView(textView);
        }
        cursor.close();
        db.close();
    }
}

Screenshot

Every time, you run the application, it will add 2 more rows in your database.

Store & Retrieve data from SQLite screenshot

Github

  • https://github.com/xuanngo2001/android-sqlite-basic

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.