Digital Engineering
Mobile

Working with sqlite Database in Android

Published On: 24 January 2017.By surya prakash.

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

  }
}

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

}

Auriga: Leveling Up for Enterprise Growth!

By ronak|2024-07-03T13:37:59+05:303 July 2024|Categories: Uncategorized|

Auriga’s journey began in 2010 crafting products for India’s [...]

1,367

Stay Close to What We’re Building

Get insights on product engineering, AI, and real-world technology decisions shaping modern businesses.

Working with sqlite Database in Android