How to insert data in to a SQLite database in Android

Hi Android friends… In my blogging history, I have already written two blog posts related to database handling. One is MySQL with PHP and the next is MySQL with Java. In this blog post I am going to show you, how we can insert data to a table in a SQLite database when we are developing Android applications. This is going to be a starting point for a series of SQLite database related articles, because I am expecting to write Retrieve, Update and Delete in separate posts. 😀

First I will explain the scenario we are going to implement. It is a place where we can store undergraduate name, University ID and GPA (Grade Point Average) value. The first interface shows the current undergraduates, that already registered with this system. But in this blog post you will not able to see the undergraduates names in the first interface, because I am not going to show you how to retrieve data from the database. :O

Figure 1

By pressing the “Add New undergraduate” button, you can go to the second UI (User Interface) where we can provide undergraduate details. If you press the “Cancel” button, you will redirect to the first UI again. You can fill the EditText fields as in Figure 2

Figure 2

When you press the “Save” button, you will be able to see a Toast message with the affected raw ID of the table. At the end of the post I will tell you a way to explore your Android phone emulator database.

I think now you have a clear picture about what we are going to implement. Before looking at the source code directly, let’s think the scenario logically. After user filling the EditText, he/she will press the “Save” button. So we need to take what the user has enter in the provided EditText fields. After that what I am going to do is, pass those values to a POJO (Plain Old Java Object) class. Then I am pass that POJO class instance to an ArrayList with it’s data. I am repeating this action for every entrance. After finishing all that only, I  think to insert those data in to the SQLite database. Following is the source code for the second interface of the Figure 1

package com.anuja.sqlite;

import java.util.ArrayList;

import android.app.Activity;
import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class AddNewUndergraduateActivity extends Activity implements OnClickListener {

	private EditText uGraduateNameEditText;
	private EditText uGraduateUniIdEditText;
	private EditText uGraduateGpaEditText;
	private Button cancelButton;
	private Button saveButton;

	private ArrayList undergraduateDetailsPojoObjArrayList;

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

		uGraduateNameEditText = (EditText) findViewById(R.id.insertNewUgraduate_name_editText);
		uGraduateUniIdEditText = (EditText) findViewById(R.id.insertNewUgraduate_uniId_editText);
		uGraduateGpaEditText = (EditText) findViewById(R.id.insertNewUgraduate_gpa_editText);

		cancelButton = (Button) findViewById(R.id.insertNewUgraduate_cancel_button);
		cancelButton.setOnClickListener(this);
		saveButton = (Button) findViewById(R.id.insertNewUgraduate_save_button);
		saveButton.setOnClickListener(this);

		undergraduateDetailsPojoObjArrayList = new ArrayList();
	}

	@Override
	public void onClick(View v) {
		if(v.getId() == R.id.insertNewUgraduate_cancel_button){
			finish();
		}else if(v.getId() == R.id.insertNewUgraduate_save_button){
			// Get the values provided by the user via the UI
			String providedUgraduateName = uGraduateNameEditText.getText().toString();
			String providedUgraduateUniId = uGraduateUniIdEditText.getText().toString();
			Double providedUgraduateGpa = Double.parseDouble(uGraduateGpaEditText.getText().toString());

			// Pass above values to the setter methods in POJO class
			UndergraduateDetailsPojo undergraduateDetailsPojoObj = new UndergraduateDetailsPojo();
			undergraduateDetailsPojoObj.setuGraduateName(providedUgraduateName);
			undergraduateDetailsPojoObj.setuGraduateUniId(providedUgraduateUniId);
			undergraduateDetailsPojoObj.setuGraduateGpa(providedUgraduateGpa);

			// Add an undergraduate with his all details to a ArrayList
			undergraduateDetailsPojoObjArrayList.add(undergraduateDetailsPojoObj);

			// Inserting undergraduate details to the database is doing in a separate method
			insertUndergraduate(undergraduateDetailsPojoObj);

			// Release from the existing UI and go back to the previous UI
			finish();
		}
	}

	public void insertUndergraduate(UndergraduateDetailsPojo paraUndergraduateDetailsPojoObj){

		// First we have to open our DbHelper class by creating a new object of that
		AndroidOpenDbHelper androidOpenDbHelperObj = new AndroidOpenDbHelper(this);

		// Then we need to get a writable SQLite database, because we are going to insert some values
		// SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.
		SQLiteDatabase sqliteDatabase = androidOpenDbHelperObj.getWritableDatabase();

		// ContentValues class is used to store a set of values that the ContentResolver can process. 
		ContentValues contentValues = new ContentValues();

		// Get values from the POJO class and passing them to the ContentValues class
		contentValues.put(AndroidOpenDbHelper.COLUMN_NAME_UNDERGRADUATE_NAME, paraUndergraduateDetailsPojoObj.getuGraduateName());
		contentValues.put(AndroidOpenDbHelper.COLUMN_NAME_UNDERGRADUATE_UNI_ID, paraUndergraduateDetailsPojoObj.getuGraduateUniId());
		contentValues.put(AndroidOpenDbHelper.COLLUMN_NAME_UNDERGRADUATE_GPA, paraUndergraduateDetailsPojoObj.getuGraduateGpa());

		// Now we can insert the data in to relevant table
		// I am going pass the id value, which is going to change because of our insert method, to a long variable to show in Toast
		long affectedColumnId = sqliteDatabase.insert(AndroidOpenDbHelper.TABLE_NAME_GPA, null, contentValues);

		// It is a good practice to close the database connections after you have done with it
		sqliteDatabase.close();

		// I am not going to do the retrieve part in this post. So this is just a notification for satisfaction ;-)
		Toast.makeText(this, "Values inserted column ID is :" + affectedColumnId, Toast.LENGTH_SHORT).show();

	}
}

Here comes the most important part. Where is our database and tables? :O Don’t worry. We are on the way to create that. To accomplish that target, I am using a separate Java class and I name it AndroidOpenDbHelper. In that class, we are going to create a database and handling version with the help of SQLiteOpenHelper class. You can see the code follow. The comments will explain the whole story to you. Copy it 😉

package com.anuja.sqlite;

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

// A helper class to manage database creation and version management. 
public class AndroidOpenDbHelper extends SQLiteOpenHelper {
	// Database attributes
	public static final String DB_NAME = "undergraduate_gpa_db";
	public static final int DB_VERSION = 1;

	// Table attributes
	public static final String TABLE_NAME_GPA = "undergraduate_details_table";
	public static final String COLUMN_NAME_UNDERGRADUATE_NAME = "undergraduate_name_column";
	public static final String COLUMN_NAME_UNDERGRADUATE_UNI_ID = "undergraduate_uni_id_column";
	public static final String COLLUMN_NAME_UNDERGRADUATE_GPA = "undergraduate_gpa_column";

	public AndroidOpenDbHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

	// Called when the database is created for the first time. 
	//This is where the creation of tables and the initial population of the tables should happen.
	@Override
	public void onCreate(SQLiteDatabase db) {
		// We need to check whether table that we are going to create is already exists.
		//Because this method get executed every time we created an object of this class. 
		//"create table if not exists TABLE_NAME ( BaseColumns._ID integer primary key autoincrement, FIRST_COLUMN_NAME text not null, SECOND_COLUMN_NAME integer not null);"
		String sqlQueryToCreateUndergraduateDetailsTable = "create table if not exists " + TABLE_NAME_GPA + " ( " + BaseColumns._ID + " integer primary key autoincrement, " 
																+ COLUMN_NAME_UNDERGRADUATE_NAME + " text not null, "
																+ COLUMN_NAME_UNDERGRADUATE_UNI_ID + " text not null, "
																+ COLLUMN_NAME_UNDERGRADUATE_GPA + " real not null);";
		// Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.
		db.execSQL(sqlQueryToCreateUndergraduateDetailsTable);
	}

	// onUpgrade method is use when we need to upgrade the database in to a new version
	//As an example, the first release of the app contains DB_VERSION = 1
	//Then with the second release of the same app contains DB_VERSION = 2
	//where you may have add some new tables or alter the existing ones
	//Then we need check and do relevant action to keep our pass data and move with the next structure
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if(oldVersion == 1 && newVersion == 2){
			// Upgrade the database
		}		
	}
}

That is all guys. You did it. You have inserted your first data set to a SQLite table in Android application. Don’t you sure about it? I will prove it to you. Go to;

• Window -> Open Perspective -> DDMS
• open File Explorer window
• explore “data” directory
• inside that you will find another “data” directory. Explore it too
• Then you will be able to find your package name of this application (Ex: com.anuja.sqlite)
• Go inside that
• Now you can see the “database” directory
• Bingo… inside that you have your database with the name you have provided (Ex: undergraduate_gpa_db)

Ooops… how you gonna open that file? Already tried double click, right click etc… 😉 Those don’t work. Look carefully, then you can find an icon with an arrow. The tool-tip given to that icon is “Pull a file from the device“. When you click that icon by selecting the database file, it will ask a place to save that file in your PC. Complete that step also.

Now what? Tried to open with different kinds of text editors? Don’t be hurry, wait till I tell you about a fantastic tool to browse a SQLite databse file. It is call “SQLite Database Browser“. It is a small size of file where you can download freely from the Internet. Don’t need to install or any administration privileges to run it. Just double click it. You have to import the database (saved file) to this application. Follow the steps as here;

• Databases -> Add database
• Then a pop up window will appear
• Browse the file where you have saved it
• click OK
• double click on the database name (Ex: undergraduate_gpa_db) or click on the + mark
• Under the Tables, you can see your table (Ex: undergraduate_details_table)
• Double click in that and go to the Data tab

THE END 🙂 I am not going to upload the source code to GitHub for this post. I will do it after completing Retrieve, Update and Delete as well. Till then have a nice day/s 😉