Android SQLite Database Tutorial


Android provides multiple options to save your application's data. If your application has structured data, you need a database to store it. SQLite facilitates storing of structured data in a private database. If you create an SQLite database in your Android application, then it can be accessed by any class. Other applications have no access. Use SQLite database to make your data available only to your application.

SQLite Database Example

Suppose you are dating multiple GirlFriend. You want to store their information inside the database. For this, you need an object that can encapsulate information related to your GirlFriend. For example name, phone number and unique id.

Create a GirlFriend class to encapsulate information. We will use an instance of this class to store information inside the database.

SQLite Database Table

GirlFriend.java

package com.example.kb4devsqlite;

public class GirlFriend {
   private String name;
   private String phone_number;
   private int private_id;
   
   // get the name of the girl friend.
   public String getGFName(){
	   return this.name;
   }
   
   // set the name of your new girl friend.
   public void setGFName(String name){
	   this.name = name;
   }
   
   // get phone number of your girl friend.
   public String getGFPhoneNumber(){
	   return this.phone_number;	   
   }
   
   //set phone number of your GF
   public void setGFPhoneNumber(String name){
	   this.phone_number = name;
   }
   
   // get the uniquely GF identifying id
   public int getprivateId(){
	   return this.private_id;
   }
   
   //set id
   public void setPrivateId(int private_id){
	   this.private_id = private_id;
   }
   
}

Accesing the Database using CRUD Operations

Create a helper class that will be used to handle all the task related to the database. For example:

  • creation of database.
  • Insert record.
  • Delete record.
  • Update record.
  • View record.

To handle tasks related to the database we need a subclass of SQLiteOpenHelper. So create a class named GFDatabaseHelper, which extends SQLiteOpenHelper class.

public class GFDatabaseHelper extends SQLiteOpenHelper {
    
}

You need to add a constructor to this class so that it can call the constructor of SQLiteOpenHelper using super.

public class GFDatabaseHelper extends SQLiteOpenHelper {
        
    private static final int DATABASE_VERSION = 2;
    private static final String DATABASE_NAME = "GirlFriendDatabase";         
 
    GFDatabaseHelper(Context context){
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        
    }
}

In the super constructor, we are passing the database version and the name of our database.

Database Helper Class

Create the following Helper Class.

package com.example.kb4devsqlite;

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

public class GFDatabaseHelper extends SQLiteOpenHelper {
	// database name and version
        private static final int DATABASE_VERSION = 2;
	private static final String DATABASE_NAME = "GirlFriendDatabase";
	//constructor - call super class constructor to set database properties
        GFDatabaseHelper(Context context){
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		
	}

        // we will use onCreate() method to create a Table for database.
	@Override
	public void onCreate(SQLiteDatabase arg0) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub
		
	}
}


In the onCreate() method, we will create a table in our database. We will execute SQL queries to access SQLite data.

Create Table Method

We will use the following code to create our new table.

    @Override
    public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
    String query = "CREATE TABLE GirlFriendDetails(private_id INTEGER PRIMARY KEY, name TEXT, phone_number TEXT)";
    db.execSQL(query);
	   
	}

Insert Operation

After creating the table, we need to create a function that will take GirlFriend object as an argument and add it to the database.

Steps involved

  • create a function called addGF() inside GFDatabaseHelper class.
  • Ask a reference to the database using this.getWritableDatabase() with which we can perform write operation in our database.
  • Now we need to add our data to a format which can pe processed by the sqlite database. So, create a reference of ContentValues. ContentValues class is used to store a set of values that our database can process.
  • ContentValues has a put() method which is used to add key-value pairs.
  • Now insert() method of writeable database reference helps to add the ContentVaules inside the given table and hence our data gets successfully added to the table.

Code to Insert a row in table

	void addGF(GirlFriend gf) {
		SQLiteDatabase db = this.getWritableDatabase();
                ContentValues values = new ContentValues();
		values.put("name", gf.getGFName());
		values.put("phone_number", gf.getGFPhoneNumber());
		values.put("private_id", gf.getprivateId());
                db.insert("GirlFriendDetails", null, values);
		Log.i("gf added", "added");
		db.close();
	}

Make sure to close the database by executing close() method of writeable database reference.

Using Databasehelper Class in our Code Example

Now we will use our database helper class in our MainActivity. Create a reference of GFDatabaseHelper. The database is created by calling super() and the table is created with the help of onCreate() method of GFDatabaseHelper. Create an instance of your girlfriend. Set its name, number and private id. Add your girlfriend's detail inside database by calling addGF() method of GFDatabaseHelper class.

Open the MainActivity class and add the following code to your onCreate() method of Activity class.

MainActivity

package com.example.kb4devsqlite;

import java.util.List;

import android.os.Bundle;
import android.app.Activity;
import android.util.Log;
import android.view.Menu;

public class MainActivity extends Activity {

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		Log.i("main", "i am here");
		GFDatabaseHelper db = new GFDatabaseHelper(this);
		Log.i("main", "i am after constructor");
		// create an instance of a girl
                GirlFriend gf = new GirlFriend();
	        
                // set property of first girlfriend
		 gf.setGFName("shikha");
		 gf.setGFPhoneNumber("987189");
		 gf.setPrivateId(1003);
	
                //insert into database
		db.addGF(gf);
		
                //set property of second gf
		gf.setGFName("Ana");
		gf.setGFPhoneNumber("990821");
		gf.setPrivateId(1004);	
                // insert into database
		db.addGF(gf);
		
		Log.i("main", "added");
 
	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.main, menu);
		return true;
	}
}

You can execute the code now. Make sure you do not get any error in your LogCat. You will not see any output yet. But if you didn't get any errors in LogCat then you are on right track. We will retrieve the added values from the database, and we will display it as a Toast message and inside the LogCat.

We will load the data from the table in a list, using the following steps:

  • Go to your database helper class and add the following function.
    public List<GirlFriend> getGirlFriendsList() {
    }
  • Create a query to select all data from table.
    String query = "SELECT  * FROM GirlFriendDetails";
  • Get the instance of the database and execute the query.
  • Store the result in a cursor.
  • Get the first result of the cursor, and execute the do-while if the cursor has elements in it.
    Cursor cursor = db.rawQuery(query, null);
    			if (cursor.moveToFirst()) {
    				do {
                                        // do something.
    				} while (cursor.moveToNext());
    			}
    
    

The complete code for loading items from the database

public List<GirlFriend> getGirlFriendsList() {
			List<GirlFriend> gfList = new ArrayList<GirlFriend>();
			String query = "SELECT  * FROM GirlFriendDetails";

			SQLiteDatabase db = this.getWritableDatabase();
			Cursor cursor = db.rawQuery(query, null);
			if (cursor.moveToFirst()) {
				do {
					GirlFriend gf = new GirlFriend();
					gf.setPrivateId(Integer.parseInt(cursor.getString(0)));
					gf.setGFName(cursor.getString(1));
					gf.setGFPhoneNumber(cursor.getString(2));
					gfList.add(gf);
				} while (cursor.moveToNext());
			}

			return gfList;
		}

Access the data from Activity

  • Call the function
     List<GirlFriend> gflist = db.getGirlFriendsList(); 
  • Access the data using below line of codes
    for (GirlFriend girl : gflist) {
                // access data
            }

Complete code for MainActivity

  • Create an instance of the helper class.
  • Create a new GF object.
  • Insert item using addGF().
  • Display all girl friend's data as explained above.
package com.example.kb4devsqlite;

import java.util.List;

import android.os.Bundle;
import android.app.Activity;
import android.util.Log;
import android.view.Menu;

public class MainActivity extends Activity {

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		Log.i("main", "i am here");
		GFDatabaseHelper db = new GFDatabaseHelper(this);
		Log.i("main", "i am after constructor");
		GirlFriend gf = new GirlFriend();
		
		gf.setGFName("shikha");
		gf.setGFPhoneNumber("987189");
		gf.setPrivateId(1003);	
		db.addGF(gf);
		
		gf.setGFName("Ana");
		gf.setGFPhoneNumber("990821");
		gf.setPrivateId(1004);	
		db.addGF(gf);
		
		Log.i("main", "added");
		
        List<GirlFriend> gflist = db.getGirlFriendsList();       
 
        for (GirlFriend girl : gflist) {
            
        Log.d("GirlFriend Details", "Gfname" + girl.getGFName()+"her ph-no"+girl.getGFPhoneNumber()+"her unique id" + girl.getprivateId());
        
        }
	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.main, menu);
		return true;
	}

}

Complete code of GFDatabaseHelper

package com.example.kb4devsqlite;


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

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

public class GFDatabaseHelper extends SQLiteOpenHelper {
	private static final int DATABASE_VERSION = 2;
	private static final String DATABASE_NAME = "GirlFriendDatabase";
	GFDatabaseHelper(Context context){
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		
		
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String query = "CREATE TABLE GirlFriendDetails(private_id INTEGER PRIMARY KEY, name TEXT, phone_number TEXT)";
	    db.execSQL(query);
	    Log.i("sqlite", "table created successfully");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
		// TODO Auto-generated method stub
		Log.i("yooooo", "yes");
		db.execSQL("DROP TABLE IF EXISTS GirlFriendDetails");

		onCreate(db);
	}
	void addGF(GirlFriend gf) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		//values.put("private_id", gf.getprivateId());
		values.put("name", gf.getGFName());
		values.put("phone_number", gf.getGFPhoneNumber());
		values.put("private_id", gf.getprivateId());
        
		db.insert("GirlFriendDetails", null, values);
		Log.i("success", "hey");
		db.close();
	}
	GirlFriend getContact(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query("GirlfriendDetails", new String[] { "private_id", 
				"name", "phone_number" }, "private_id" + "=?", 
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		GirlFriend contact = new GirlFriend(Integer.parseInt(cursor.getString(0)), 
				cursor.getString(1), cursor.getString(2));
		// return contact
		return contact;
	}
	// Getting All Contacts
		public List<GirlFriend> getGirlFriendsList() {
			List<GirlFriend> gfList = new ArrayList<GirlFriend>();
			// Select All Query
			String query = "SELECT  * FROM GirlFriendDetails";

			SQLiteDatabase db = this.getWritableDatabase();
			Cursor cursor = db.rawQuery(query, null);

			// looping through all rows and adding to list
			if (cursor.moveToFirst()) {
				do {
					GirlFriend gf = new GirlFriend();
					gf.setPrivateId(Integer.parseInt(cursor.getString(0)));
					gf.setGFName(cursor.getString(1));
					gf.setGFPhoneNumber(cursor.getString(2));
					// Adding contact to list
					gfList.add(gf);
				} while (cursor.moveToNext());
			}

			// return contact list
			return gfList;
		}
}

ScreenShot

Finally, we have successfully inserted and retrieved the items from the database.

Shared Preferences in Android

Android provides multiple methods of storing your applications data. We can use content providers, shared preferences, and SQLite database to store useful information.

Content providers help to provide data such as contacts information, calendar entries, settings, etc. We use SQLite to store relational data. Shared preferences facilitate storing of data as a key value pair for our Android application.

One of the easiest ways is to store data with the help of shared preferences. If you want to store some user settings or preferences, then you can use SharedPreferences, which stores the data in a key-value pair.

The below image presents the working of shared preferences. Your Android application can fetch and store key-value pair data using shared preferences.

Get the content of the SharedPreferences file. You need to pass the name of the file. If the file doesn't exist, it would be automatically created. We are using mode as MODE_PRIVATE.

SharedPreferences userPreference = getSharedPreferences("userPreference", MODE_PRIVATE);			

Now we have a userPreference file in which we can save the details of the user. Data will be stored as a key value pair.

To access this file, you need to create an editor. With the help of an editor, you can modify the data.

Editor edit = userPreference.edit();

Now call clear() to remove all values from the preferences

edit.clear();

How to store data -Store the data in key-value pair

edit.putString("tonePath", tonePath);
edit.commit();	

How to retrieve the value from shared preferences

  • First we get the SharedPreferences file.
     SharedPreferences userPreference = getSharedPreferences("userPreference", MODE_PRIVATE);
  • Then we check if the key exist in preferences or not.
     if(userPreference.contains("tonePath")){
    }
  • After that we access the value of the key.
    String tonePath = userPreference.getString("tonePath", "");

Set the notification sound

We need to pass the Uri and set it as the notification sound.

Convert String to Uri

Uri uri = Uri.parse(tonePath);

Set as notification

notificationObject.setSound(uri);