ZOFTINO.COM android and web dev tutorials

How to Use SQLite Database in Android

SQLite database is a relational database which is embedded in android system and is used to store structured data in android apps. In this post, I am going to show how to create database and tables, query, insert, modify and delete data.

Android provides SQLite api to manage SQLite database. Helper class SQLiteOpenHelper is used to perform all database operation as it efficiently manages closing and opening database. First you need to instantiate SQLiteOpenHelper object by passing data base name you want to create or open to constructor.

To get data base, you need to call getReadableDatabase() or getWritableDatabase() methods depending on the type of operations you want to perform. It is a best practice to close database in onDestry() call back method of the activity the database is associated with.

Insert Data into a SQLite Database Table

To insert data into SQLite database table, you need to create ContentValues object putting key and values. Key is a column name and value is the data to be stored in column for the row. Then call SQLiteOpenHelper insert() method passing table name and content values.

public void addBill(View v) {
    ContentValues contentValues = new ContentValues();

    EditText billId = (EditText)findViewById(R.id.billId);
    contentValues.put(BillContract.BILL_ID, billId.getText().toString());

    EditText billerName = (EditText)findViewById(R.id.billerName);
    contentValues.put(BillContract.BILLER_NAME, billerName.getText().toString());

    EditText billDate = (EditText)findViewById(R.id.billDate);
    contentValues.put(BillContract.BILL_DATE, billDate.getText().toString());

    EditText billAmt = (EditText)findViewById(R.id.billAmt);
    contentValues.put(BillContract.BILL_AMT, billAmt.getText().toString());

    EditText modeOfPayment = (EditText)findViewById(R.id.modeOfPayment);
    contentValues.put(BillContract.PAY_MODE, modeOfPayment.getText().toString());

    EditText paymentStatus = (EditText)findViewById(R.id.paymentStatus);
    contentValues.put(BillContract.PAY_STATUS, paymentStatus.getText().toString());


    SQLiteDatabase billDB = sqLiteOpenHelper.getWritableDatabase();
    billDB.insert(BillContract.TABLE_NAME, null, contentValues);
}

Update SQLite Database Table Data

To update sqlite table data, you need to call update() method of SQLiteOpenHelper passing where clause, selection values and content values.

public void updateBill(View v) {
    String billId = ((EditText)findViewById(R.id.billId)).getText().toString();

    ContentValues contentValues = new ContentValues();
    EditText modeOfPayment = (EditText)findViewById(R.id.modeOfPayment);
    contentValues.put(BillContract.PAY_MODE, modeOfPayment.getText().toString());

    EditText paymentStatus = (EditText)findViewById(R.id.paymentStatus);
    contentValues.put(BillContract.PAY_STATUS, paymentStatus.getText().toString());

    String mSelectionClause = BillContract.BILL_ID+ " = ?";
    String[] mSelectionArgs = {billId};

    SQLiteDatabase billDB = sqLiteOpenHelper.getWritableDatabase();
    billDB.update(BillContract.TABLE_NAME,contentValues,mSelectionClause,mSelectionArgs);
}

Delete SQLite Database Table Data

To delete records in sqlite table, you need to call delete method of SQLiteOpenHelper passing where clause and selection values.

public void deleteBill(View view){

    String billId = ((EditText)findViewById(R.id.billId)).getText().toString();

    String mSelectionClause = BillContract.BILL_ID+ " = ?";
    String[] mSelectionArgs = {billId};

    SQLiteDatabase billDB = sqLiteOpenHelper.getWritableDatabase();
    billDB.delete(BillContract.TABLE_NAME,mSelectionClause,mSelectionArgs);
}

Read SQLite Database Table Data

To query sqlite database you need to call query method of SQLiteOpenHelper passing list of columns you want to retrieve values for, selection clause and selection values.

public void getBillInfo(View view){

    String paymentStatus = ((EditText)findViewById(R.id.paymentStatus)).getText().toString();

    String[] mProjection =
            {
                    "_id",
                    BillContract.BILL_ID,
                    BillContract.BILLER_NAME,
                    BillContract.BILL_DATE,
                    BillContract.BILL_AMT,
            };

    String selection = BillContract.PAY_STATUS+ " = ?";
    String[] selectionArgs = {paymentStatus};

    String orderBy = BillContract.BILL_DATE+" DESC";

    SQLiteDatabase billDB = sqLiteOpenHelper.getReadableDatabase();
   Cursor cur = billDB.query(BillContract.TABLE_NAME, mProjection, selection, selectionArgs, null, null,orderBy );

    String[] cursorColumns =
            {
                    BillContract.BILLER_NAME,
                    BillContract.BILL_DATE,
                    BillContract.BILL_AMT
            };

    int[] viewIds = {R.id.billerName, R.id.billDate, R.id.billAmt};
    SimpleCursorAdapter simpleCursorAdapter = new SimpleCursorAdapter(
            this,
            R.layout.bill_row,
            cur,
            cursorColumns,
            viewIds,
            0);

    billLst.setAdapter(simpleCursorAdapter);

}

Complete SQLite Database Example Code

SQLiteOpenHelper

package com.zoftino.content;


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

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    private String sql;
    MySQLiteOpenHelper(Context context, String dbName, String msql) {
        super(context, dbName, null, 1);
        sql = msql;
    }

    public void onCreate(SQLiteDatabase db) {
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

Contract class

package com.zoftino.content;

public class BillContract {

    public static final String DB_NAME = "zoftino_bills";

    public static final String TABLE_NAME = "bills";

    public static final String  BILL_ID= "BILL_ID";
    public static final String  BILLER_NAME= "BILLER_NAME";
    public static final String  BILL_DATE= "BILL_DATE";
    public static final String  BILL_AMT= "BILL_AMT";
    public static final String  PAY_MODE= "PAY_MODE";
    public static final String  PAY_STATUS= "PAY_STATUS";

}

Activity

 package com.zoftino.content;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v4.widget.SimpleCursorAdapter;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.EditText;
import android.widget.ListView;

public class SQLiteDatabaseActivity extends AppCompatActivity {

    private MySQLiteOpenHelper sqLiteOpenHelper;

    private SQLiteDatabase billsDB;

    private static final String SQL_CREATE_TABLE = "CREATE TABLE " +
            BillContract.TABLE_NAME +
            "(" +
            "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
            BillContract.BILL_ID +" TEXT, " +
            BillContract.BILLER_NAME +" TEXT, " +
            BillContract.BILL_DATE + " NUMERIC, " +
            BillContract.BILL_AMT + " REAL, " +
            BillContract.PAY_MODE + " TEXT, " +
            BillContract.PAY_STATUS + " TEXT)";
    
    ListView billLst;

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

        sqLiteOpenHelper = new MySQLiteOpenHelper(this, BillContract.DB_NAME, SQL_CREATE_TABLE );

        billLst = (ListView) findViewById(R.id.billsLst);
    }
    @Override
    protected void onDestroy() {
        sqLiteOpenHelper.close();
        super.onDestroy();
    }
    public void addBill(View v) {
        ContentValues contentValues = new ContentValues();

        EditText billId = (EditText)findViewById(R.id.billId);
        contentValues.put(BillContract.BILL_ID, billId.getText().toString());

        EditText billerName = (EditText)findViewById(R.id.billerName);
        contentValues.put(BillContract.BILLER_NAME, billerName.getText().toString());

        EditText billDate = (EditText)findViewById(R.id.billDate);
        contentValues.put(BillContract.BILL_DATE, billDate.getText().toString());

        EditText billAmt = (EditText)findViewById(R.id.billAmt);
        contentValues.put(BillContract.BILL_AMT, billAmt.getText().toString());

        EditText modeOfPayment = (EditText)findViewById(R.id.modeOfPayment);
        contentValues.put(BillContract.PAY_MODE, modeOfPayment.getText().toString());

        EditText paymentStatus = (EditText)findViewById(R.id.paymentStatus);
        contentValues.put(BillContract.PAY_STATUS, paymentStatus.getText().toString());


        SQLiteDatabase billDB = sqLiteOpenHelper.getWritableDatabase();
        billDB.insert(BillContract.TABLE_NAME, null, contentValues);
    }
    public void updateBill(View v) {
        String billId = ((EditText)findViewById(R.id.billId)).getText().toString();

        ContentValues contentValues = new ContentValues();
        EditText modeOfPayment = (EditText)findViewById(R.id.modeOfPayment);
        contentValues.put(BillContract.PAY_MODE, modeOfPayment.getText().toString());

        EditText paymentStatus = (EditText)findViewById(R.id.paymentStatus);
        contentValues.put(BillContract.PAY_STATUS, paymentStatus.getText().toString());

        String mSelectionClause = BillContract.BILL_ID+ " = ?";
        String[] mSelectionArgs = {billId};

        SQLiteDatabase billDB = sqLiteOpenHelper.getWritableDatabase();
        billDB.update(BillContract.TABLE_NAME,contentValues,mSelectionClause,mSelectionArgs);
    }
    public void deleteBill(View view){

        String billId = ((EditText)findViewById(R.id.billId)).getText().toString();

        String mSelectionClause = BillContract.BILL_ID+ " = ?";
        String[] mSelectionArgs = {billId};

        SQLiteDatabase billDB = sqLiteOpenHelper.getWritableDatabase();
        billDB.delete(BillContract.TABLE_NAME,mSelectionClause,mSelectionArgs);
    }
    public void getBillInfo(View view){

        String paymentStatus = ((EditText)findViewById(R.id.paymentStatus)).getText().toString();

        String[] mProjection =
                {
                        "_id",
                        BillContract.BILL_ID,
                        BillContract.BILLER_NAME,
                        BillContract.BILL_DATE,
                        BillContract.BILL_AMT,
                };

        String selection = BillContract.PAY_STATUS+ " = ?";
        String[] selectionArgs = {paymentStatus};

        String orderBy = BillContract.BILL_DATE+" DESC";

        SQLiteDatabase billDB = sqLiteOpenHelper.getReadableDatabase();
       Cursor cur = billDB.query(BillContract.TABLE_NAME, mProjection, selection, selectionArgs, null, null,orderBy );

        String[] cursorColumns =
                {
                        BillContract.BILLER_NAME,
                        BillContract.BILL_DATE,
                        BillContract.BILL_AMT
                };

        int[] viewIds = {R.id.billerName, R.id.billDate, R.id.billAmt};
        SimpleCursorAdapter simpleCursorAdapter = new SimpleCursorAdapter(
                this,
                R.layout.bill_row,
                cur,
                cursorColumns,
                viewIds,
                0);

        billLst.setAdapter(simpleCursorAdapter);

    }
}
 

Activity layout xml

 <?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_sqlite"
    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"
    android:orientation="vertical"
    tools:context="com.zoftino.content.SQLiteDatabaseActivity">
    <EditText  android:id="@+id/billId"
        android:hint="Bill Id"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" ></EditText>
    <EditText  android:id="@+id/billerName"
        android:hint="Bill Name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" ></EditText>
    <EditText  android:id="@+id/billDate"
        android:hint="Bill Date"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" ></EditText>
    <EditText  android:id="@+id/billAmt"
        android:hint="Bill Amount"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" ></EditText>
    <EditText  android:id="@+id/modeOfPayment"
        android:hint="Payment Mode"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" ></EditText>
    <EditText  android:id="@+id/paymentStatus"
        android:hint="Payment Status"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" ></EditText>
    <Button
        android:id="@+id/button2"
        android:text="Add Bill"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="addBill"></Button>
    <Button
        android:id="@+id/button3"
        android:text="Get Bill Info"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="getBillInfo"></Button>
    <Button
        android:id="@+id/button4"
        android:text="Update Bill"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="updateBill"></Button>
    <Button
        android:id="@+id/button5"
        android:text="Delete Bill"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="deleteBill"></Button>

    <ListView android:id="@+id/billsLst"
        android:layout_width="match_parent"
        android:layout_height="match_parent"></ListView>
</LinearLayout>
 

Bill row layout xml

 <?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/bill_row"
    android:orientation="vertical"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:paddingTop="@dimen/activity_vertical_margin">
     <TextView android:id="@+id/billerName" android:layout_width="match_parent"
        android:layout_height="wrap_content" ></TextView>
    <TextView android:id="@+id/billDate" android:layout_width="match_parent"
        android:layout_height="wrap_content" ></TextView>
    <TextView android:id="@+id/billAmt" android:layout_width="match_parent"
        android:layout_height="wrap_content" ></TextView>
</LinearLayout>