Building a Robust SQLite Database in Android: Detailed Tutorial

Android sqlite example:

In this android database tutorial we will learn how to use sqlite database in android app.Inserting, deleting of values are shown with a detailed example.

Firstly SQLite DB is a type of database which is stored within the mobile/device of the user. It is a simple database which is also a light-weight db which is used by most of the users to save and retrieve data.

So this is considered to be a local database which will be erased once you clear the data of the app or uninstall the app.So please make sure you use it according to the requirement.

We will be seeing different types of databases in further android development tutorials, this tutorial deals with inserting color names into database, retrieving them and also deleting them from db.

Creating a SQLite Database

public static final String DB_NAME = "AndroidCoding";  // where AndroidCoding is the database name
public static final String DB_TABLE = "Colors";                // where colors is the table name

Now, we will write a sql query to create a database using above DB_NAME and DB_TABLE

private static final String CREATE_DATABASE = "CREATE  TABLE" + DB_TABLE + 
" ("+ COLORNAME + " text not null);";

SQLiteAdapter.java :

Here is the complete implementation of  SQLite adapter for android sqlite example.We provide a sql query in order to create a db with specified columns.

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

public class SQLiteAdapter {

    public static final String DB_NAME = "AndroidCoding";
    public static final String DB_TABLE = "Colors";
    public static final int VERSION = 1;
    public static final String COLORNAME = "Content";
    
    private static final String CREATE_DATABASE =
               "create table " + DB_TABLE + " ("+ COLORNAME + " text not null);";

    private SQLiteHelper sqLiteHelper;
    private SQLiteDatabase sqLiteDatabase;

    private Context context;

    public SQLiteAdapter(Context c){
        context = c;
    }

    public SQLiteAdapter openToRead() throws android.database.SQLException {
        sqLiteHelper = new SQLiteHelper(context, DB_NAME, null, VERSION);
        sqLiteDatabase = sqLiteHelper.getReadableDatabase();
        return this;
    }

    public SQLiteAdapter openToWrite() throws android.database.SQLException {
        sqLiteHelper = new SQLiteHelper(context, DB_NAME, null, VERSION);
        sqLiteDatabase = sqLiteHelper.getWritableDatabase();
        return this;
    }

    public void close(){
        sqLiteHelper.close();
    }

    public long insert(String content){

        ContentValues contentValues = new ContentValues();
        contentValues.put(COLORNAME, content);
        return sqLiteDatabase.insert(DB_TABLE, null, contentValues);
    }

    public int deleteAll(){
        return sqLiteDatabase.delete(DB_TABLE, null, null);
    }

    public String queueAll(){
        String[] colors = new String[]{COLORNAME};

        Cursor cursor = sqLiteDatabase.query(DB_TABLE, colors,
                null, null, null, null, null);
        String result = "";

        int index_CONTENT = cursor.getColumnIndex(COLORNAME);
        for(cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()){
            result = result + cursor.getString(index_CONTENT) + "\n";
        }

        return result;
    }

    public class SQLiteHelper extends SQLiteOpenHelper {

        public SQLiteHelper(Context context, String name,
                            CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            db.execSQL(CREATE_DATABASE);
        }

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

        }

    }

}

Inserting Data into Database

No we try to insert data into database by making use of the adapter which we have created in android sqlite example.

Adapter = new SQLiteAdapter(MainActivity.this); // initialization

Adapter.openToWrite(); // open to write

Adapter.close(); // closing after inserting data

Adapter.openToRead(); // reading data after inserting

Adapter.deleteAll(); // deleting data

By using edittext i am fetching the values and storing values into database, storing them and retrieving them (CRUD operations).

MainActivity.java :

Now comes the implementation of main activity where we make us of the things we have created till now and make the final product i.e., android sqlite example.

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

public class MainActivity extends Activity {

    private SQLiteAdapter Adapter;
    String getColor;
    EditText editText;
    TextView color;

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

        color = (TextView)findViewById(R.id.color);
        editText = (EditText)findViewById(R.id.editText);

        Button add = (Button)findViewById(R.id.addbutton);
        Button del = (Button)findViewById(R.id.delbutton);

        add.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                Adapter = new SQLiteAdapter(MainActivity.this);
                Adapter.openToWrite();
                Adapter.insert(editText.getText().toString());
                Adapter.close();
                Adapter.openToRead();
                getColor = Adapter.queueAll();
                color.setText(getColor);
            }
        });

        del.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                Adapter.deleteAll();
                Adapter.openToRead();
                getColor = Adapter.queueAll();
                color.setText(getColor);
            }
        });



    }
}

Creating Layout File

Now add the buttons in layout file such that these two buttons will be used to add and delete values from database.

And also a edittext to get user input in android sqlite example.

activity_main.xml :

<LinearLayout 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:orientation="vertical"
 tools:context=".MainActivity" >

<EditText
 android:layout_width="match_parent"
 android:layout_height="wrap_content"
 android:id="@+id/editText"
 android:hint="enter color names"/>

<Button
 android:layout_width="match_parent"
 android:layout_height="wrap_content"
 android:text="Insert Values"
 android:id="@+id/addbutton"
 android:layout_alignParentTop="true"
 android:layout_alignParentLeft="true"
 android:layout_alignParentStart="true" />

<Button
 android:layout_width="match_parent"
 android:layout_height="wrap_content"
 android:text="Delete All"
 android:id="@+id/delbutton"
 android:layout_alignParentTop="true"
 android:layout_toRightOf="@+id/button"
 android:layout_toEndOf="@+id/button" />

<TextView
 android:id="@+id/color"
 android:layout_width="match_parent"
 android:layout_height="wrap_content"/>

</LinearLayout>

AndroidManifest.xml :

There is nothing more to add to this manifest file so leave it as it is.

<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.abhi.androidcoding.sqlitedb">

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

If you have any query’s in this tutorial on android sqlite example do let us know in the comment section below. If you like this tutorial do like and share us for more interesting updates.