How to insert data in to a SQLite database in Android

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 , because I am expecting to write Retrieve, Update and Delete in separate posts.

First I will explain what 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. 

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

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

Here is the Acitivity class..

package com.mahesh.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;

public 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.

Here is the AndroidOpenDbHelper  class.

package com.mahesh.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

inside that you have your database with the name you have provided