Working with sqlite Database in Android

Published On: 24 January 2017.By surya prakash.

Digital Engineering
Mobile

Working with sqlite Database in Android

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);
  }
}

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);

}

Open database

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

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

Close database

ourHelper.close();

1	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);
}

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;
}

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;
}

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);
}

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);
}

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);
  }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

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);

  }
}