Android SQLite Database Tutorial

Android have many method to store local data in android device. Android SQLite database is one of the best method to allow smart phone to stores data in a text file. It is a good and efficient approach to store the data in the smart phones. Besides that, SQLite database is a light weight database and supports all relational database features. By the use of SQLite Database in Android, the records and data can be easily to create, retrieve, delete and modify by just using the simple SQL statements. In this tutorial, I will give you some example on implement the SQLite database in Android device. I will give all the steps and explain how it works.

SQLite Database Demo

Creating an Empty Project

1. Open Android Studio IDE in your computer.
2. Create a new project and Edit the Application name to “SQLiteExample”.
(Optional) You can edit the company domain or select the suitable location for current project tutorial. After that click next button to proceed.
3. Select Minimum SDK (API 15:Android 4.0.3 (IceCreamSandwich) and Click Next button.
4. Choose “Empty Project” and Click Next button
5. Lastly, press finish button.

Prepare a Database table

Try to prepare a database table you will need to create a SQLite database later. I name my table name as DBExample and below are the structure and content of the table.

Add a New Class

Right click your package name > New > Java Class. You can type any java class name for example My class name is DBHelper.

Add a new java class - Android SQLite

Extends SQLiteOpenHelper and edit the class

The Following are the source code of DBHelper.java class, this class should extends SQLiteOpenHelper to enable the create, retrieve, update and delete features.

void onCreate(SQLiteDatabase db)
– This method will call when the database is create for the first time.

void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
– This method will activate when the Database version has been changed.

boolean insertContact (String name, String phone, String email)
– It allow to insert the contact name, phone and email to the contact table in the database.

Cursor getData(int id)
– Get the specific contact information from the contact table.

boolean updateContact (Integer id, String name, String phone, String email)
– It allow to update the contact name, phone and email to the contact table in the database.

Integer deleteContact (Integer id)
– It allow to delete specific contact record in the database.

ArrayList <String> getid()
– Get the all id from the contact table.

import java.util.ArrayList;

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


public class DBHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "DBExample.db";
    public static final String CONTACTS_TABLE_NAME = "contacts";
    public static final String CONTACTS_COLUMN_ID = "id";
    public static final String CONTACTS_COLUMN_NAME = "name";
    public static final String CONTACTS_COLUMN_EMAIL = "email";
    public static final String CONTACTS_COLUMN_PHONE = "phone";


    public DBHelper(Context context)
    {
        super(context, DATABASE_NAME , null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL(
                "create table contacts " +
                        "(id integer primary key, name text,phone text,email text)"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS contacts");
        onCreate(db);
    }

    public boolean insertContact  (String name, String phone, String email)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", name);
        contentValues.put("phone", phone);
        contentValues.put("email", email);

        db.insert("contacts", null, contentValues);
        return true;
    }

    public Cursor getData(int id){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res =  db.rawQuery( "select * from contacts where id="+id+"", null );
        return res;
    }

    public int numberOfRows(){
        SQLiteDatabase db = this.getReadableDatabase();
        int numRows = (int) DatabaseUtils.queryNumEntries(db, CONTACTS_TABLE_NAME);
        return numRows;
    }

    public boolean updateContact (Integer id, String name, String phone, String email)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", name);
        contentValues.put("phone", phone);
        contentValues.put("email", email);
        db.update("contacts", contentValues, "id = ? ", new String[] { Integer.toString(id) } );
        return true;
    }

    public Integer deleteContact (Integer id)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete("contacts",
                "id = ? ",
                new String[] { Integer.toString(id) });
    }



    public ArrayList<String> getid()
    {
        ArrayList<String> arrayList = new ArrayList<String>();


        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res =  db.rawQuery( "select * from contacts", null );
        res.moveToFirst();

        while(res.isAfterLast() == false){
            arrayList.add(res.getString(res.getColumnIndex(CONTACTS_COLUMN_ID)));
            res.moveToNext();
        }
        return arrayList;
    }
}

Edit activity_main.xml Layout

The activity_main.xml is to display all the contacts list of database and the add button. This xml is the layout of the MainActivity.java.

<RelativeLayout 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:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    android:paddingBottom="@dimen/activity_vertical_margin" tools:context=".MainActivity">


    <ListView
        android:id="@+id/listView1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_centerHorizontal="true"
        android:layout_above="@+id/btnAdd"
        android:layout_alignParentTop="true">
    </ListView>

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Add"
        android:id="@+id/btnAdd"
        android:layout_alignParentBottom="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        android:layout_alignParentRight="true"
        android:layout_alignParentEnd="true" />

</RelativeLayout>

Edit MainActivity.java

This is the code in the MainActivity.java. This activity display all the contacts and allow to navigate to the DisplayContacts activity when any contact is clicked.

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.content.Intent;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Button;
import android.widget.ListView;
import java.util.ArrayList;

public class MainActivity extends AppCompatActivity implements View.OnClickListener{
    public final static String EXTRA_MESSAGE = "MESSAGE";
    private ListView obj;
    DBHelper mydb;
    Button btnAdd;

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

        btnAdd=(Button)findViewById(R.id.btnAdd);

        btnAdd.setOnClickListener(this);

        mydb = new DBHelper(this);
        final ArrayList array_list = mydb.getid();
        ArrayAdapter arrayAdapter=new ArrayAdapter(this,android.R.layout.simple_list_item_1, array_list);


        obj = (ListView)findViewById(R.id.listView1);
        obj.setAdapter(arrayAdapter);
        obj.setOnItemClickListener(new OnItemClickListener(){
            @Override
            public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,long arg3) {
                // TODO Auto-generated method stub

                    int id_To_Search = Integer.parseInt(array_list.get(arg2).toString());
                   // Toast.makeText(getApplicationContext(),""+id_To_Search,Toast.LENGTH_LONG).show();
                    Bundle dataBundle = new Bundle();
                    dataBundle.putInt("id", id_To_Search);

                    Intent intent = new Intent(getApplicationContext(),DisplayContact.class);
                    intent.putExtras(dataBundle);
                    startActivity(intent);


            }
        });
    }


     @Override
    public void onClick(View v) {
        if (v.equals(btnAdd)){
            Bundle dataBundle = new Bundle();
            dataBundle.putInt("id", 0);

            Intent intent = new Intent(getApplicationContext(),DisplayContact.class);
            intent.putExtras(dataBundle);

            startActivity(intent);
        }
    }

}

Create a new Activity to display contact

Add a new activity in your package (Right Click package name> New > Activity) and name it as “DisplayContact”. This activity is to display the contact information that selected by the user.

Edit the activity_display_contact.xml layout

Below are the activity_display_contact.xml to display the layout of DisplayContact.java. There have edit text to let user key in and the buttons to delete or edit the contact.

<ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/scrollView1"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    tools:context=".DisplayContact" >

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="370dp"
        android:paddingBottom="@dimen/activity_vertical_margin"
        android:paddingLeft="@dimen/activity_horizontal_margin"
        android:paddingRight="@dimen/activity_horizontal_margin"
        android:paddingTop="@dimen/activity_vertical_margin">

        <EditText
            android:id="@+id/editTextName"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignParentLeft="true"
            android:layout_marginTop="5dp"
            android:layout_marginLeft="82dp"
            android:ems="10"
            android:inputType="text" >
        </EditText>

        <EditText
            android:id="@+id/editTextEmail"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginTop="22dp"
            android:ems="10"
            android:inputType="textEmailAddress"
            android:layout_alignLeft="@+id/editTextName"
            android:layout_below="@+id/editTextPhone" />

        <TextView
            android:id="@+id/textView1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignBottom="@+id/editTextName"
            android:layout_alignParentLeft="true"
            android:text="@string/name"
            android:textAppearance="?android:attr/textAppearanceMedium" />

        <Button
            android:id="@+id/button1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="edit"
            android:text="Save or Edit"
            android:layout_below="@+id/editTextEmail"
            android:layout_centerHorizontal="true"
            android:layout_marginTop="56dp" />

        <TextView
            android:id="@+id/textView2"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignBottom="@+id/editTextEmail"
            android:layout_alignLeft="@+id/textView1"
            android:text="@string/email"
            android:textAppearance="?android:attr/textAppearanceMedium" />

        <TextView
            android:id="@+id/textView5"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignBottom="@+id/editTextPhone"
            android:layout_alignLeft="@+id/textView1"
            android:text="@string/phone"
            android:textAppearance="?android:attr/textAppearanceMedium" />

        <EditText
            android:id="@+id/editTextPhone"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_below="@+id/editTextName"
            android:ems="10"
            android:inputType="phone|text"
            android:layout_alignLeft="@+id/editTextName" />

        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="DELETE"
            android:onClick="delete"
            android:id="@+id/btndelete"
            android:layout_alignParentBottom="true"
            android:layout_alignLeft="@+id/button1"
            android:layout_alignStart="@+id/button1"
            android:layout_alignRight="@+id/button1"
            android:layout_alignEnd="@+id/button1" />

    </RelativeLayout>
</ScrollView>

Edit DisplayContact.java code

Go to displaycontact.java class to paste the following code, the purpose of this class to perform the edit and delete feature from the DBHelper.java.

import android.os.Bundle;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.TextView;
import android.widget.Toast;

public class DisplayContact extends AppCompatActivity {

    private DBHelper mydb ;
    TextView name ;
    TextView phone;
    TextView email;
    int id_To_Update = 0;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_display_contact);
        name = (TextView) findViewById(R.id.editTextName);
        phone = (TextView) findViewById(R.id.editTextPhone);
        email = (TextView) findViewById(R.id.editTextEmail);

        mydb = new DBHelper(this);

        Bundle extras = getIntent().getExtras();
        if(extras !=null)
        {
            int Value = extras.getInt("id");

            if(Value>0){
                //means this is the view part not the add contact part.
                Cursor rs = mydb.getData(Value);
                id_To_Update = Value;
                rs.moveToFirst();

                String nam = rs.getString(rs.getColumnIndex(DBHelper.CONTACTS_COLUMN_NAME));
                String phon = rs.getString(rs.getColumnIndex(DBHelper.CONTACTS_COLUMN_PHONE));
                String emai = rs.getString(rs.getColumnIndex(DBHelper.CONTACTS_COLUMN_EMAIL));

                if (!rs.isClosed())
                {
                    rs.close();
                }
                name.setText((CharSequence)nam);
                phone.setText((CharSequence)phon);
                email.setText((CharSequence)emai);

            }
        }
         }


 public void edit(View view) {
     Bundle extras = getIntent().getExtras();
     if (extras != null) {
         int Value = extras.getInt("id");
         if (Value > 0) {
             if (mydb.updateContact(id_To_Update, name.getText().toString(), phone.getText().toString(), email.getText().toString())) {
                 Toast.makeText(getApplicationContext(), "Updated", Toast.LENGTH_SHORT).show();
                 Intent intent = new Intent(getApplicationContext(), MainActivity.class);
                 startActivity(intent);
             } else {
                 Toast.makeText(getApplicationContext(), "not Updated", Toast.LENGTH_SHORT).show();
             }
         } else {
             if (mydb.insertContact(name.getText().toString(), phone.getText().toString(), email.getText().toString())) {
                 Toast.makeText(getApplicationContext(), "done", Toast.LENGTH_SHORT).show();
             } else {
                 Toast.makeText(getApplicationContext(), "not done", Toast.LENGTH_SHORT).show();
             }
             Intent intent = new Intent(getApplicationContext(), MainActivity.class);
             startActivity(intent);
         }
     }
 }
    public void delete(View view)
    {
        AlertDialog.Builder builder = new AlertDialog.Builder(this);
        builder.setMessage(R.string.deleteContact)
                .setPositiveButton(R.string.yes, new DialogInterface.OnClickListener() {
                    public void onClick(DialogInterface dialog, int id) {
                      //  int Value = extras.getInt("id");
                        mydb.deleteContact(id_To_Update);
                        Toast.makeText(getApplicationContext(), "Deleted Successfully", Toast.LENGTH_SHORT).show();
                        Intent intent = new Intent(getApplicationContext(),MainActivity.class);
                        startActivity(intent);
                    }
                })
                .setNegativeButton(R.string.no, new DialogInterface.OnClickListener() {
                    public void onClick(DialogInterface dialog, int id) {
                        // User cancelled the dialog
                    }
                });
        AlertDialog d = builder.create();
        d.setTitle("Are you sure");
        d.show();
    }


}

Edit strings.xml file

The following is strings.xml in the res/values package. This will show string value for xml and java file to make source code more efficient. By using this approach, the string can be reuse in every file.

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="app_name">SQLiteExample</string>
    <string name="action_settings">Settings</string>
    <string name="hello_world">Hello world!</string>
    <string name="Add_New">Add New</string>
    <string name="edit">Edit Contact</string>
    <string name="delete">Delete Contact</string>
    <string name="title_activity_display_contact">DisplayContact</string>
    <string name="name">Name</string>
    <string name="phone">Phone</string>
    <string name="email">Email</string>

    <string name="save">Save Contact</string>
    <string name="deleteContact">Are you sure, you want to delete it.</string>
    <string name="yes">Yes</string>
    <string name="no">No</string>
</resources>

Run Your Project

You are done to coding the SQLite database, now you can run the project in your android device to test out the features.

 (Android SQLite Database Tutorial)

Source Code

(Visited 847 times, 1 visits today)
Advertisements

Yong Loon Ng

Ng Yong Loon, better known as Kristofer is a software engineer and computer scientist who doubles up as an entrepreneur.

You may also like...

3 Responses

  1. May 5, 2016

    […] to https://questdot.com/2016/05/android-sqlite/ and get your sqlite database in your […]

  2. June 9, 2016

    […] You can get sample SQLite project from this link SQLite Database Example. […]

  3. September 27, 2016

    […] provide SQLite database for local database in the android application. SQLite is hard to setting up because it […]

Leave a Reply

Your email address will not be published. Required fields are marked *