Databases in Android using SQLite – Part III

Till now, we have restricted ourselves to very simple databases and hence we declare and define the database inside the Main Activity itself. However, this is mostly not the case. Often times in your application you would want to keep your Database and related functions separately from you activity. This is a much better idea because then your activity will consist of all related components and your database class will have all the methods related to creation, access or updation of the database.

In this post I will show you how to keep two classes, one for the Activity and another for your Database related operations. So create a project and follow along. Complete Source code is at the bottom.

• Create a new Java Class, the super class of which is SQLiteOpenHelper. Name this DatabaseHelper.java
• Once this is created, you can see that there are two methods that are already present, onCreate() and onUpgrade().
• Create a class variable named DatabaseName of the type String and assign it a value “NeroDB“ or anything you like. This will be the name of your Database.
• We will need to create a constructor for this Class, so write down the following lines just before onCreate() method
  [java]
  public DatabaseHelper(Context context) {
  super(context, DatabaseName, null, 1);
  }
  [/java]
• Here we are calling the constructor of the Super Class and passing to it the Context, name of the database, CursorFactory(which is null here) and the version of our Database.
• Now write the following lines in the onCreate(SQLiteDatabase db) method
  [java]
  db.execSQL("CREATE TABLE IF NOT EXISTS NeroTable(LastName VARCHAR, FirstName VARCHAR, Age INT(3));");
  [/java]
• Again, write the following line in the onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) method
  [java]
  db.execSQL("DROP TABLE NeroTable;");
  db.execSQL("CREATE TABLE IF NOT EXISTS NeroTable(LastName VARCHAR, FirstName VARCHAR, Age INT(3));");
  [/java]
• We have just told Android that when the database is created, create a table named NeroTable with the specified columns and if there is an upgrade in the version of the Database, drop the table and create it again leading to deletion of all data.
• Now to insert values in the table, write the following method after the onUpgrade()
  
  [java]
  public void InsertValues()
  {
  SQLiteDatabase db = this.getWritableDatabase();
  db.execSQL("INSERT INTO NeroTable VALUES(‘NeroLast1’, ‘NeroFirst1’, 20);");
  db.execSQL("INSERT INTO NeroTable VALUES(‘NeroLast2’, ‘NeroFirst2’, 21);");
  db.execSQL("INSERT INTO NeroTable VALUES(‘NeroLast3’, ‘NeroFirst3’, 22);");
  db.close();
  }
  [/java]
• Now that we are done with the DatabaseHelper class, switch over to the Main activity and create an object of the DatabaseHelper class. Remember to declare it outside all methods and inside the class.
  [java]
  DatabaseHelper db;
  [/java]
• Define this inside the onCreate() method of the Main activity.
  [java]
  db = new DatabaseHelper(this);
  [/java]
• By creating the object and defining it, the constructor of the DatabaseHelper class has been called and hence the Database and the table has been created.
• Now to insert the values,
  [java]
  db.InsertValues();
  [/java]
• Remember that we are only using the basic OOP Concepts here of calling a class’s constructor and using the object of that class to call it’s methods.
• Save your work and execute it on the emulator.

Understanding the Code

• The onCreate() and the onUpgrade() methods are quite clear.
• The constructor of the Super method passes the Database Version too. If this Database version is to be changed, the onUpgrade() method comes into play and the table is deleted and recreated. You can try it out by changing the value to a different number.

COMPLETE SOURCE CODE

DatabaseHelper.java

[java]
package com.nero.myfirstapp;

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

public class DatabaseHelper extends SQLiteOpenHelper {

static String DatabaseName="NeroDB";

public DatabaseHelper(Context context) {
super(context, DatabaseName, null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS NeroTable(LastName VARCHAR, FirstName VARCHAR, Age INT(3));");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE NeroTable;");
db.execSQL("CREATE TABLE IF NOT EXISTS NeroTable(LastName VARCHAR, FirstName VARCHAR, Age INT(3));");
}

public void InsertValues()
{
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL("INSERT INTO NeroTable VALUES(‘NeroLast1’, ‘NeroFirst1’, 20);");
db.execSQL("INSERT INTO NeroTable VALUES(‘NeroLast2’, ‘NeroFirst2’, 21);");
db.execSQL("INSERT INTO NeroTable VALUES(‘NeroLast3’, ‘NeroFirst3’, 22);");
db.close();
}
}

[/java]

Main.java

[java]
package com.nero.myfirstapp;

import android.media.MediaPlayer;
import android.os.Bundle;
import android.preference.PreferenceManager;
import android.app.Activity;
import android.app.AlertDialog;
import android.app.Dialog;
import android.app.ProgressDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class Main extends Activity {
DatabaseHelper db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db = new DatabaseHelper(this);
db.InsertValues();
db.close();
}
}

[/java]