Android provides several ways to store user and app data. SQLite is one way of storing user data. SQLite is a very light weight database which comes with Android OS. In this blog, I’ll be discussing how to write classes to handle all SQLite operations.

In this blog, I am taking an example of storing user contacts in SQLite database. I am using a table called peopleTable to store user hotness. This table contains three columns id (INT), persons_name (TEXT), persons_hotness(TEXT).

Writing Sqlite Database Handler Class

We need to write our own class to handle all database CRUD(Create, Read, Update, and Delete) operations.

1. Create a new project by going to File ⇒ New Android Project.
2. Once the project is created, create a new class in your project src directory and name it DbHelper.java ( Right Click on src/package ⇒ New ⇒ Class)
3. Now extend your DbHelper.java class from SQLiteOpenHelper.

4. After extending your class from SQLiteOpenHelper you need to override two methods onCreate() andonUpgrage()
onCreate() – These is where we need to write create table statements. This is called when database is created.
onUpgrade() – This method is called when database is upgraded like modifying the table structure, adding constraints to database etc.,

private static class DbHelper extends SQLiteOpenHelper {

  public DbHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    // TODO Auto-generated constructor stub
  }

  @Override public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
        KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
        KEY_NAME + " TEXT NOT NULL, " +
        KEY_HOTNESS + " TEXT NOT NULL);");
  }

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

ourHelper = new DbHelper(ourContext);
ourDatabase = ourHelper.getWritableDatabase();

ourHelper.close();

Inserting new Record 

public long createEntry(String name, String hotness) {
  // TODO Auto-generated method stub
  ContentValues cv = new ContentValues();
  cv.put(KEY_NAME, name);
  cv.put(KEY_HOTNESS, hotness);
  return ourDatabase.insert(DATABASE_TABLE, null, cv);
}

Retrieving single Row

public String getName(long l) throws SQLException {
  // TODO Auto-generated method stub
  String[] columns = new String[] { KEY_ROWID, KEY_NAME, KEY_HOTNESS };
  Cursor c =
      ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "=" + l, null, null, null, null);
  if (c != null) {
    c.moveToFirst();
    String name = c.getString(1);
    return name;
  }
  return null;
}

Retrieving All Row(s)

public String getData() {
  // TODO Auto-generated method stub
  String[] columns = new String[] { KEY_ROWID, KEY_NAME, KEY_HOTNESS };
  Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
  String result = "";

  int iRow = c.getColumnIndex(KEY_ROWID);
  int iName = c.getColumnIndex(KEY_NAME);
  int iHotness = c.getColumnIndex(KEY_HOTNESS);

  for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
    result = result
        + c.getString(iRow)
        + " "
        + c.getString(iName)
        + " "
        + c.getString(iHotness)
        + "\n";
  }

  return result;
}

Updating  Row(s)

public void updateEntry(long lRow, String mName, String mHotness) throws SQLException {
  // TODO Auto-generated method stub
  ContentValues cvUpdate = new ContentValues();
  cvUpdate.put(KEY_NAME, mName);
  cvUpdate.put(KEY_HOTNESS, mHotness);
  ourDatabase.update(DATABASE_TABLE, cvUpdate, KEY_ROWID + "=" + lRow, null);
}

Deleting Row(s)

public void deleteEntry(long lRow1) throws SQLException {
 // TODO Auto-generated method stub
 ourDatabase.delete(DATABASE_TABLE, KEY_ROWID + "=" + lRow1, null);
}

Complete code looks like 

public class HotOrNot {
  public static final String KEY_ROWID = "_id";
  public static final String KEY_NAME = "persons_name";
  public static final String KEY_HOTNESS = "persons_hotness";

  private static final String DATABASE_NAME = "HotOrNotdb";
  private static final String DATABASE_TABLE = "peopleTable";
  private static final int DATABASE_VERSION = 1;

  private DbHelper ourHelper;
  private final Context ourContext;
  private SQLiteDatabase ourDatabase;

  private static class DbHelper extends SQLiteOpenHelper {

    public DbHelper(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
      // TODO Auto-generated constructor stub
    }

    @Override public void onCreate(SQLiteDatabase db) {
      // TODO Auto-generated method stub
      db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
          KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
          KEY_NAME + " TEXT NOT NULL, " +
          KEY_HOTNESS + " TEXT NOT NULL);");
    }

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

  public HotOrNot(Context c) {
    ourContext = c;
  }

  public HotOrNot open() throws SQLException {
    ourHelper = new DbHelper(ourContext);
    ourDatabase = ourHelper.getWritableDatabase();
    return this;
  }

  public void close() {
    ourHelper.close();
  }

  public long createEntry(String name, String hotness) {
    // TODO Auto-generated method stub
    ContentValues cv = new ContentValues();
    cv.put(KEY_NAME, name);
    cv.put(KEY_HOTNESS, hotness);
    return ourDatabase.insert(DATABASE_TABLE, null, cv);
  }

  public String getData() {
    // TODO Auto-generated method stub
    String[] columns = new String[] { KEY_ROWID, KEY_NAME, KEY_HOTNESS };
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
    String result = "";

    int iRow = c.getColumnIndex(KEY_ROWID);
    int iName = c.getColumnIndex(KEY_NAME);
    int iHotness = c.getColumnIndex(KEY_HOTNESS);

    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
      result = result
          + c.getString(iRow)
          + " "
          + c.getString(iName)
          + " "
          + c.getString(iHotness)
          + "\n";
    }

    return result;
  }

  public String getName(long l) throws SQLException {
    // TODO Auto-generated method stub
    String[] columns = new String[] { KEY_ROWID, KEY_NAME, KEY_HOTNESS };
    Cursor c =
        ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "=" + l, null, null, null, null);
    if (c != null) {
      c.moveToFirst();
      String name = c.getString(1);
      return name;
    }
    return null;
  }

  public String getHotness(long l) throws SQLException {
    // TODO Auto-generated method stub
    String[] columns = new String[] { KEY_ROWID, KEY_NAME, KEY_HOTNESS };
    Cursor c =
        ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "=" + l, null, null, null, null);
    if (c != null) {
      c.moveToFirst();
      String hotness = c.getString(2);
      return hotness;
    }
    return null;
  }

  public void updateEntry(long lRow, String mName, String mHotness) throws SQLException {
    // TODO Auto-generated method stub
    ContentValues cvUpdate = new ContentValues();
    cvUpdate.put(KEY_NAME, mName);
    cvUpdate.put(KEY_HOTNESS, mHotness);
    ourDatabase.update(DATABASE_TABLE, cvUpdate, KEY_ROWID + "=" + lRow, null);
  }

  public void deleteEntry(long lRow1) throws SQLException {
    // TODO Auto-generated method stub
    ourDatabase.delete(DATABASE_TABLE, KEY_ROWID + "=" + lRow1, null);
  }
}

 SQLite use like below code

public class SQLView extends Activity{

  @Override
  protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.sqlview);
    TextView tv = (TextView) findViewById(R.id.tvSQLinfo);
    HotOrNot info = new HotOrNot(this);

    info.open();
    String data = info.getData();
    info.close();
    tv.setText(data);

  }
}