Basic sqlite CRUD examples in Android

1. DatabaseHandler.java, declaring the database version, database name, table name and table columns.
[code language=”java”]
public class DatabaseHandler extends SQLiteOpenHelper {
// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "contacts_db";

// Contact table name
private static final String TABLE_CONTACTS = "contacts";

// Contact Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_FNANE = "first_name";
private static final String KEY_LNANE = "last_name";
private static final String KEY_EMAIL = "email";
private static final String KEY_PH_NO = "phone";
private static final String KEY_DOB = "dob";
private static final String KEY_GENDER = "gender";

private final ArrayList<Contact> contactList = new ArrayList<Contact>();
}
[/code]

2. The constructor for the DatabaseHandler, it needs the context object from the activity. The super call in the constructor needs the context, database naame and database version.
[code language=”java”]
public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
[/code]

3. The onCreate() method for creating the database and the onUpgrade() method for upgrading the database. These two methods will be executed if the database is already created and the version number from the constructor is greater than the current database. In this demo, it will drop the table alone with all the data in it. You probably wounldn’t want to do this, or at least save the data before drop it.
[code language=”java”]
// Creating contacts table
@Override
public void onCreate(SQLiteDatabase db) {
Log.d("DatabaseHandler", "creating database");
String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
+ KEY_ID + " INTEGER PRIMARY KEY,"
+ KEY_FNANE + " TEXT,"
+ KEY_LNANE + " TEXT,"
+ KEY_EMAIL + " TEXT,"
+ KEY_PH_NO + " TEXT,"
+ KEY_DOB + " TEXT,"
+ KEY_GENDER + " TEXT" + ")";
db.execSQL(CREATE_CONTACTS_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.d("DatabaseHandler", "upgrading database");

// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
// Create tables again
onCreate(db);
}
[/code]

4. Adding a row in sqlite table.
[code language=”java”]
public long addContact(Contact contact) {
long insertId = -1;
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_FNANE, contact.getFirstName());
values.put(KEY_LNANE, contact.getLastName());
values.put(KEY_EMAIL, contact.getEmail());
values.put(KEY_PH_NO, contact.getPhoneNumber());
values.put(KEY_DOB, contact.getDob());
values.put(KEY_GENDER, contact.getGender());

// Inserting Row
insertId = db.insert(TABLE_CONTACTS, null, values);
db.close(); // Closing database connection
return insertId;
}
[/code]

5. Reading from sqlite table.
[code language=”java”]
public Contact getContact(int id) {
SQLiteDatabase db = this.getReadableDatabase();

Cursor cursor = db.query(TABLE_CONTACTS,
new String[] {KEY_ID, KEY_FNANE, KEY_LNANE, KEY_EMAIL, KEY_PH_NO, KEY_DOB, KEY_GENDER },
KEY_ID + "=?",
new String[] {String.valueOf(id)}, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();

Contact contact = new Contact(
Integer.parseInt(cursor.getString(0)),
cursor.getString(1),
cursor.getString(2),
cursor.getString(3),
cursor.getString(4),
cursor.getString(5),
cursor.getString(6));

// return contact
cursor.close();
db.close();

return contact;
}
[/code]

6. Reading multiple rows from a sqlite table.
[code language=”java”]
public ArrayList<Contact> getContactList() {
try {
contactList.clear();

// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_CONTACTS;

SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);

// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Contact contact = new Contact();
contact.setId(Integer.parseInt(cursor.getString(0)));
contact.setFirstName(cursor.getString(1));
contact.setLastName(cursor.getString(2));
contact.setEmail(cursor.getString(3));
contact.setPhoneNumber(cursor.getString(4));
contact.setDob(cursor.getString(5));
contact.setGender(cursor.getString(6));
// Adding contact to list
contactList.add(contact);
} while (cursor.moveToNext());
}
// return contact list
cursor.close();
db.close();
return contactList;
} catch (Exception e) {
// TODO: handle exception
Log.e("getContactList", "" + e);
}
return contactList;
}
[/code]

7. Updating data in sqlite.
[code language=”java”]
// Updating single contact
public int updateContact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_FNANE, contact.getFirstName());
values.put(KEY_LNANE, contact.getLastName());
values.put(KEY_EMAIL, contact.getEmail());
values.put(KEY_PH_NO, contact.getPhoneNumber());
values.put(KEY_DOB, contact.getDob());
values.put(KEY_GENDER, contact.getGender());

// updating row
return db.update(TABLE_CONTACTS,
values,
KEY_ID + " = ?",
new String[] { String.valueOf(contact.getId()) });
}
[/code]

8. Deleting data in sqlite.
[code language=”java”]
// Deleting single contact
public void removeContact(long id) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
new String[] { String.valueOf(id) });
db.close();
}
[/code]

9. Get the number of rows in a table in sqlite.
[code language=”java”]
// Getting contacts Count
public int getContactSize() {
String countQuery = "SELECT count(*) FROM " + TABLE_CONTACTS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);

int count = 0;
if(cursor != null && cursor.getCount() > 0) {
cursor.moveToFirst();
count = cursor.getInt(0);
}

cursor.close();
// return count
return count;
}
[/code]

10. The database hanlder class all in one.
[code language=”java”]
public class DatabaseHandler extends SQLiteOpenHelper {
// All Static variables
// Database Version
private static final int DATABASE_VERSION = 3;

// Database Name
private static final String DATABASE_NAME = "contacts_db";

// Contact table name
private static final String TABLE_CONTACTS = "contacts";

// Contact Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_FNANE = "first_name";
private static final String KEY_LNANE = "last_name";
private static final String KEY_EMAIL = "email";
private static final String KEY_PH_NO = "phone";
private static final String KEY_DOB = "dob";
private static final String KEY_GENDER = "gender";

private final ArrayList<Contact> contactList = new ArrayList<Contact>();

public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// Creating contacts table
@Override
public void onCreate(SQLiteDatabase db) {
Log.d("DatabaseHandler", "creating database");
String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
+ KEY_ID + " INTEGER PRIMARY KEY,"
+ KEY_FNANE + " TEXT,"
+ KEY_LNANE + " TEXT,"
+ KEY_EMAIL + " TEXT,"
+ KEY_PH_NO + " TEXT,"
+ KEY_DOB + " TEXT,"
+ KEY_GENDER + " TEXT" + ")";
db.execSQL(CREATE_CONTACTS_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.d("DatabaseHandler", "upgrading database");

// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
// Create tables again
onCreate(db);
}

/**
* All CRUD(Create, Read, Update, Delete) Operations
*/

// Adding new contact
public long addContact(Contact contact) {
long insertId = -1;
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_FNANE, contact.getFirstName());
values.put(KEY_LNANE, contact.getLastName());
values.put(KEY_EMAIL, contact.getEmail());
values.put(KEY_PH_NO, contact.getPhoneNumber());
values.put(KEY_DOB, contact.getDob());
values.put(KEY_GENDER, contact.getGender());

// Inserting Row
insertId = db.insert(TABLE_CONTACTS, null, values);
db.close(); // Closing database connection
return insertId;
}

// Getting single contact
public Contact getContact(int id) {
SQLiteDatabase db = this.getReadableDatabase();

Cursor cursor = db.query(TABLE_CONTACTS,
new String[] {KEY_ID, KEY_FNANE, KEY_LNANE, KEY_EMAIL, KEY_PH_NO, KEY_DOB, KEY_GENDER },
KEY_ID + "=?",
new String[] {String.valueOf(id)}, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();

Contact contact = new Contact(
Integer.parseInt(cursor.getString(0)),
cursor.getString(1),
cursor.getString(2),
cursor.getString(3),
cursor.getString(4),
cursor.getString(5),
cursor.getString(6));

// return contact
cursor.close();
db.close();

return contact;
}

// Getting All Contacts
public ArrayList<Contact> getContactList() {
try {
contactList.clear();

// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_CONTACTS;

SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);

// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Contact contact = new Contact();
contact.setId(Integer.parseInt(cursor.getString(0)));
contact.setFirstName(cursor.getString(1));
contact.setLastName(cursor.getString(2));
contact.setEmail(cursor.getString(3));
contact.setPhoneNumber(cursor.getString(4));
contact.setDob(cursor.getString(5));
contact.setGender(cursor.getString(6));
// Adding contact to list
contactList.add(contact);
} while (cursor.moveToNext());
}
// return contact list
cursor.close();
db.close();
return contactList;
} catch (Exception e) {
// TODO: handle exception
Log.e("getContactList", "" + e);
}
return contactList;
}

// Updating single contact
public int updateContact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_FNANE, contact.getFirstName());
values.put(KEY_LNANE, contact.getLastName());
values.put(KEY_EMAIL, contact.getEmail());
values.put(KEY_PH_NO, contact.getPhoneNumber());
values.put(KEY_DOB, contact.getDob());
values.put(KEY_GENDER, contact.getGender());

// updating row
return db.update(TABLE_CONTACTS,
values,
KEY_ID + " = ?",
new String[] { String.valueOf(contact.getId()) });
}

// Deleting single contact
public void removeContact(long id) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
new String[] { String.valueOf(id) });
db.close();
}

// Getting contacts Count
public int getContactSize() {
String countQuery = "SELECT count(*) FROM " + TABLE_CONTACTS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);

int count = 0;
if(cursor != null && cursor.getCount() > 0) {
cursor.moveToFirst();
count = cursor.getInt(0);
}

cursor.close();
// return count
return count;
}
}
[/code]

To use this database base hanlder class in an Activity class.
[code language=”java”]
DatabaseHandler dbHandler = new DatabaseHandler(getApplicationContext());
dbHandler.addContact(c);
[/code]

The pojo model class Contact.java
[code language=”java”]
public class Contact {

private long id;
private String firstName;
private String lastName;
private String email;
private String phoneNumber;
private String dob;
private String gender;

public Contact(){}

public Contact(String firstName, String lastName, String email, String phone, String dob, String gender) {
this.firstName = firstName;
this.lastName = lastName;
this.email = email;
this.phoneNumber = phone;
this.dob = dob;
this.gender = gender;
}

public Contact(long id, String firstName, String lastName, String email, String phoneNumber, String dob, String gender) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
this.email = email;
this.phoneNumber = phoneNumber;
this.dob = dob;
this.gender = gender;
}

public long getId() {
return id;
}

public void setId(long id) {
this.id = id;
}

public String getFirstName() {
return firstName;
}

public void setFirstName(String firstName) {
this.firstName = firstName;
}

public String getLastName() {
return lastName;
}

public void setLastName(String lastName) {
this.lastName = lastName;
}

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public String getPhoneNumber() {
return phoneNumber;
}

public void setPhoneNumber(String phone) {
this.phoneNumber = phone;
}

public String getDob() {
return dob;
}

public void setDob(String dob) {
this.dob = dob;
}

public String getGender() {
return gender;
}

public void setGender(String gender) {
this.gender = gender;
}
}
[/code]